Resumo : neste tutorial, você aprenderá sobre o conceito de recuperação point-in-time do MySQL e como restaurar um banco de dados para um momento específico no passado.
Introdução à recuperação pontual do MySQL
A recuperação pontual permite restaurar um banco de dados MySQL para um momento específico no passado. A recuperação pontual depende de dois componentes principais:
- Backup completo: serve como base para a recuperação, fornecendo o estado inicial do banco de dados.
- Logs binários: Esses arquivos de log binários registram todas as alterações feitas no banco de dados, permitindo que você reproduza essas alterações até o ponto desejado.
Se você não tiver um backup completo ou logs binários habilitados, não poderá realizar a recuperação pontual.
Ilustraremos como realizar a recuperação point-in-time no MySQL para recuperar um banco de dados para um momento específico.
Verificando o status do log binário e fazendo um backup completo de um banco de dados
Primeiro, abra o Prompt de Comando no Windows ou Terminal em sistemas do tipo Unix.
Em seguida, conecte-se ao servidor MySQL :
mysql -u root -p
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em seguida, verifique se o log binário está habilitado mostrando o valor da log_bin
variável:
show global variables like 'log_bin';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A saída indica que o log binário está habilitado no momento. Caso contrário, você precisa habilitar o log binário.
Depois disso, saia do programa mysql:
exit
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Criando um novo banco de dados de amostra e fazendo um backup completo
Primeiro, reconecte-se ao banco de dados do servidor MySQL:
mysql -u root -p
Segundo, crie um novo banco de dados chamado mydb
:
CREATE DATABASE mydb;
Terceiro, altere o banco de dados atual para mydb
:
USE mydb;
Linguagem de código: PHP ( php )
Quarto, crie uma nova tabela chamada contacts
no mydb
banco de dados:
CREATE TABLE IF NOT EXISTS contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
Linguagem de código: PHP ( php )
Quinto, insira três linhas na contacts
tabela:
INSERT INTO contacts (first_name, last_name, email)
VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]'),
('Bob', 'Johnson', '[email protected]');
Linguagem de código: JavaScript ( javascript )
Sexto, saia do mysql
programa:
exit
Linguagem de código: PHP ( php )
Por fim, faça um backup completo do mydb
banco de dados e armazene o arquivo dump no ~/backup/
diretório:
mysql -u root -p > ~/backup/mydb.sql
Linguagem de código: JavaScript ( javascript )
Fazendo alterações no banco de dados
Primeiro, reconecte-se ao servidor MySQL:
mysql -u root -p
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, altere o banco de dados atual para mydb
:
USE mydb;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, insira uma nova linha no mydb
banco de dados:
INSERT INTO contacts(first_name, last_name, email)
VALUES('Bob','Climo', '[email protected]');
Linguagem de código: JavaScript ( javascript )
Quarto, exclua um contato com id 1, mas esqueça de adicionar uma WHERE
cláusula, portanto, a instrução exclui todas as linhas da contacts
tabela:
DELETE FROM contacts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como excluímos todas as linhas da contacts
tabela sem querer, queremos recuperar a contacts
tabela.
Quinto, mostre a posição atual do log binário:
SHOW MASTER STATUS;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 4952 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O arquivo de log binário atual é binlog.000001
.
Sexto, saia do mysql
programa:
exit
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Sétimo, verifique o momento em que excluímos todas as linhas da contacts
tabela no arquivo de log binário usando o mysqlbinlog
programa utilitário:
mysqlbinlog --verbose /var/lib/mysql/binlog.000001 | grep -i -C 10 "delete table"
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A saída é semelhante a esta:
--
#231225 20:26:10 server id 1 end_log_pos 4921 CRC32 0x53432937 Delete_rows: table id 494 flags: STMT_END_F
BINLOG '
8oKJZRMBAAAARAAAAHISAAAAAO4BAAAAAAEABG15ZGIACGNvbnRhY3RzAAQDDw8PBvwD/AP8AwAB
AQACA/z/ADxQW1A=
8oKJZSABAAAAxwAAADkTAAAAAO4BAAAAAAEAAgAE/wABAAAABABKb2huAwBEb2UUAGpvaG4uZG9l
QGV4YW1wbGUuY29tAAIAAAAEAEphbmUFAFNtaXRoFgBqYW5lLnNtaXRoQGV4YW1wbGUuY29tAAMA
AAADAEJvYgcASm9obnNvbhcAYm9iLmpvaG5zb25AZXhhbXBsZS5jb20ABAAAAAMAQm9iBQBDbGlt
bxUAYm9iLmNsaW1vQGV4YW1wbGUuY29tNylDUw==
'/*!*/;
### DELETE FROM `mydb`.`contacts`
### WHERE
### @1=1
### @2='John'
### @3='Doe'
### @4='[email protected]'
### DELETE FROM `mydb`.`contacts`
### WHERE
### @1=2
### @2='Jane'
### @3='Smith'
### @4='[email protected]'
### DELETE FROM `mydb`.`contacts`
### WHERE
### @1=3
### @2='Bob'
### @3='Johnson'
### @4='[email protected]'
### DELETE FROM `mydb`.`contacts`
### WHERE
### @1=4
### @2='Bob'
### @3='Climo'
### @4='[email protected]'
# at 4921
#231225 20:26:10 server id 1 end_log_pos 4952 CRC32 0x634fce0a Xid = 3194
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A saída mostra que 2023-12-25 20:26:10
excluímos todas as linhas da contacts
tabela. Portanto, precisamos recuperar o banco de dados até 2023-12-25 20:26:10
.
Executando uma recuperação pontual
Primeiro, reconecte-se ao servidor MySQL:
mysql -u root -p
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, elimine o mydb
banco de dados e recrie-o:
DROP DATABASE mydb;
CREATE DATABASE mydb;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, saia do programa mysql:
exit
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quarto, restaure o mydb
banco de dados do backup completo:
mysql -u root -p mydb < ~/backup/mydb.sql
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quinto, recupere as linhas da contacts
tabela do arquivo de log binário usando o mysqlbinlog
programa utilitário:
mysqlbinlog --stop-datetime="2023-12-25 20:26:10" --verbose /var/lib/mysql/binlog.000001 | mysql -u root -p
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Este comando lê o arquivo de log binário do MySQL ( /var/lib/mysql/binlog.000001
), parando em uma data e hora especificada ( 2023-12-25 20:26:10
), exibe o conteúdo com detalhamento adicional e, em seguida, canaliza essa saída para o mysql
comando, que executa as instruções SQL registradas no log binário em um servidor MySQL, usando o usuário especificado ( root
) e solicitando uma senha.
Vamos detalhar o comando:
- mysqlbinlog:
mysqlbinlog
é um utilitário de linha de comando fornecido pelo MySQL para processar e exibir o conteúdo de arquivos de log binários.--stop-datetime="2023-12-25 20:26:10"
: esta opção especifica um valor de data e hora emysqlbinlog
processará o log binário até atingir a data e hora especificada. Neste caso, ele interrompe o processamento emDecember 25, 2023, 20:26:10
.--verbose
: esta opção exibemysqlbinlog
informações adicionais junto com o conteúdo real do log. Pode ser útil para depurar e compreender as entradas de log./var/lib/mysql/binlog.000001
: Este é o caminho para o arquivo de log binário quemysqlbinlog
será processado.
- | (cano):
- O símbolo de barra vertical (
|
) é usado para redirecionar a saída do primeiro comando (mysqlbinlog
) para a entrada do segundo comando (mysql
).
- O símbolo de barra vertical (
- mysql:
mysql
é outro utilitário de linha de comando usado para executar instruções SQL com base na saída demysqlbinlog
.-u root -p
: Estas são opções para especificar o usuário MySQL (root
neste caso) e solicitar uma senha (-p
).
Sexto, conecte-se ao servidor MySQL:
mysql -u root -p
Sétimo, mude o banco de dados atual para mydb
:
use mydb;
Linguagem de código: PHP ( php )
Finalmente, recupere os dados da contacts
tabela:
SELECT * FROM contacts;
Saída:
+----+------------+-----------+-------------------------+
| id | first_name | last_name | email |
+----+------------+-----------+-------------------------+
| 1 | John | Doe | [email protected] |
| 2 | Jane | Smith | [email protected] |
| 3 | Bob | Johnson | [email protected] |
| 4 | Bob | Climo | [email protected] |
+----+------------+-----------+-------------------------+
4 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
A saída indica que recuperamos com sucesso as linhas da contacts
tabela logo antes de emitirmos a DELETE
instrução que removeu todas as linhas da tabela.
Finalmente, saia do programa mysql:
exit
Linguagem de código: PHP ( php )
Resumo
- Use a recuperação pontual para restaurar um banco de dados MySQL para um momento específico no passado.
- Restaure um banco de dados a partir de um backup completo e restaure para um horário específico a partir do log binário para executar uma recuperação pontual.