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 -pLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Em seguida, verifique se o log binário está habilitado mostrando o valor da log_binvariá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:
exitLinguagem 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 -pSegundo, 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 contactsno mydbbanco 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 contactstabela:
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 mysqlprograma:
exitLinguagem de código: PHP ( php )Por fim, faça um backup completo do mydbbanco de dados e armazene o arquivo dump no ~/backup/diretório:
mysql -u root -p > ~/backup/mydb.sqlLinguagem de código: JavaScript ( javascript )Fazendo alterações no banco de dados
Primeiro, reconecte-se ao servidor MySQL:
mysql -u root -pLinguagem 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 mydbbanco 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 WHEREcláusula, portanto, a instrução exclui todas as linhas da contactstabela:
DELETE FROM contacts;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Como excluímos todas as linhas da contactstabela sem querer, queremos recuperar a contactstabela.
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 mysqlprograma:
exitLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Sétimo, verifique o momento em que excluímos todas as linhas da contactstabela no arquivo de log binário usando o mysqlbinlogprograma 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:10excluímos todas as linhas da contactstabela. 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 -pLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, elimine o mydbbanco 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:
exitLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Quarto, restaure o mydbbanco de dados do backup completo:
mysql -u root -p mydb < ~/backup/mydb.sqlLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Quinto, recupere as linhas da contactstabela do arquivo de log binário usando o mysqlbinlogprograma 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 mysqlcomando, 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 emysqlbinlogprocessará 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 exibemysqlbinloginformaçõ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 quemysqlbinlogserá 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 (rootneste caso) e solicitar uma senha (-p).
Sexto, conecte-se ao servidor MySQL:
mysql -u root -pSétimo, mude o banco de dados atual para mydb:
use mydb;Linguagem de código: PHP ( php )Finalmente, recupere os dados da contactstabela:
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 contactstabela logo antes de emitirmos a DELETEinstrução que removeu todas as linhas da tabela.
Finalmente, saia do programa mysql:
exitLinguagem 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.
