Snapshot – backup consistente no MySQL

Hoje vamos falar de como realizar um backup (snapshot) consistente do nosso banco de dados.
Primeiramente, em qual situações podemos utilizar o snapshot?

1. Digamos que seu servidor de produção vai ser partir de hoje replicado em um slave, como você faz essa carga inicial de dados no slave? qual era a posição do bin log quando o backup iniciou, durante o período de backup, sera que alguém enviou alguma query para o banco? e se o bin log esta numa posição diferente da de quando o backup iniciou?

2. Caso você queira implementar uma estrategia de backup incremental, você pode fazer uma copia integra da base uma vez na semana, e caso necessite restaurar a base, aplicar os bin logs a partir da copia.

Dito isso, vamos ao que nos interessa.
Para garantir a integridade vamos precisar trabalhar em 2 sessões, a primeira vai ser responsável por garantir a integridade (vamos dar lock em todos os bancos) e a segunda sera responsável pela copia dos dados.

Então, vamos iniciar:

Sessao 1:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Você devera receber um retorno igual a este:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

IMPORTANTE: DEIXE ESTA SESSÃO ABERTA DURANTE TODO O PROCESSO, SE VOCÊ FECHAR ESTA SESSÃO, O LOCK DO BANCO SERA SOLTO.

para realizar o backup vamos utilizar o mysqldump:
Sessao 2:

mysqldump -u usuario -p --all-databases > all_dbs.sql

feito isso, assegure-se de que o backup tenha terminado e o dump termine com:
— Dump completed on xxxx-xx-xx xx:xx:xx

pronto, podemos liberar o lock da sessão 1:

UNLOCK TABLES;

Pronto, já temos o snapshot do nosso banco de dados.

Gostou ? Ajude e Compartilhe!
Esta entrada foi publicada em backup. Adicione o link permanente aos seus favoritos.
  • James

    This technique has the disadvantage of disabling writes to the database during the backup, which could be a significant problem for some sites. I would suggest using the –single-transaction option to ensure a consistent snapshot while allowing writes to continue to the database.

    Also, we can avoid using an additional session by using the –master-data option to write the binary log file name and position to the dump file.


    mysqldump -u USER -p --single-transaction --quick --lock-tables=false --master-data DATABSE > output

    • marceloaltmann

      Hi James, thanks for you comment.
      This technique is not to be used on a daily backup, this is to be used when you need a consistent copy of your master, and you don’t want any other session writing on it.
      Also, if you only use –single-transaction, it won’t block non-transaction storage engines to receive writes.