Mysql binlogs são logs de todos os comandos enviados para o mysql, ele captura todos os comandos executados, desde um simples INSERT até um DROP DATABASE e insere indices atraves de datas e numeros sequenciais. É uma ferramenta muito poderosa, utilizada na replicação de banco de dados e também na realização de backups incrementais.
Por padrão, diversas distribuições linux deixam essa opção desabilitada nos pacotes tradicionais do mysql-server, se este for o caso da sua instalação, habilite o binlog no seu servidor
1 2 | |
O expire_logs_days é a quantidade de dias que os bin-logs devem permanecer no servidor antes que sejam deletados, já na primeira linha, indica onde os bin-logs serão salvos, neste caso em /var/lib/mysql, é uma boa idéia separar a pasta onde os arquivos serão salvos caso você deseje realizar o backup incremental.
[root@jazz ~]# ls -la /var/lib/mysql/mysql-bin.* -rw-rw---- 1 mysql mysql 27287 Jul 15 15:02 /var/lib/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql 35873 Jul 15 15:02 /var/lib/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql 107 Jul 15 15:03 /var/lib/mysql/mysql-bin.000003 -rw-rw---- 1 mysql mysql 57 Jul 15 15:03 /var/lib/mysql/mysql-bin.index [root@jazz ~]#
Embora no diretorio destino exista mais de um binlog, apenas um está ativo no momento, você pode verificar qual e em qual posição o mesmo se encontra executando o seguinte comando:
deadcow@jazz ~ $ mysql -u root -e 'SHOW MASTER STATUS;' +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 107 | | | +------------------+----------+--------------+------------------+ deadcow@jazz ~ $
Agora iremos criar uma database para exemplificar o uso do binlog
1 2 3 4 5 6 7 8 | |
e então inserir algumas entradas
1 2 3 4 | |
Agora temos um banco de dados ativo com entradas validas para a realização de um backup, primeiro iremos realizar um full backup em nosso banco
mysqldump -uroot -p --all-databases --single-transaction --flush-logs \ --master-data=2 > full_backup.sql
A flag –flush-logs é utilizada para que o binlog ativo seja fechado e um novo seja criado, –master-data=2 adiciona comentários no dump com os indices do binlog, já a flag –single-transaction é utilizada para a realização consistente de dumps de bancos InnoDB, para quem não está familiarizado, uma forma simples de explicar uma transaction é dizer que é um unico comando, caso alguma entrada falhe, o comando inteiro será descartado e nenhuma alteração será feita no banco.
[root@jazz ~]# ls -la /var/lib/mysql/mysql-bin.* -rw-rw---- 1 mysql mysql 27287 Jul 15 15:02 /var/lib/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql 35873 Jul 15 15:02 /var/lib/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql 1208 Jul 15 15:13 /var/lib/mysql/mysql-bin.000003 -rw-rw---- 1 mysql mysql 57 Jul 15 15:03 /var/lib/mysql/mysql-bin.index [root@jazz ~]# mysqldump -uroot -p --all-databases --single-transaction \ > --flush-logs --master-data=2 > full_backup.sql Enter password: [root@jazz ~]# ls -la /var/lib/mysql/mysql-bin.* -rw-rw---- 1 mysql mysql 27287 Jul 15 15:02 /var/lib/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql 35873 Jul 15 15:02 /var/lib/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql 1251 Jul 15 15:26 /var/lib/mysql/mysql-bin.000003 -rw-rw---- 1 mysql mysql 107 Jul 15 15:26 /var/lib/mysql/mysql-bin.000004 -rw-rw---- 1 mysql mysql 76 Jul 15 15:26 /var/lib/mysql/mysql-bin.index [root@jazz ~]#
Agora iremos adicionar algumas entradas novas ao banco para exemplificar o backup incremental
1 2 3 4 | |
Como realizamos o flush-logs no backup full, as novas entradas devem estar no arquivo mysql-bin.000004, para realizarmos o backup incremental de forma consistente, precisamos iniciar um novo binlog e copiar o arquivo (ou arquivos) para outra localidade, para iniciar um novo binlog execute
[root@jazz ~]# mysqladmin -uroot -p flush-logs [root@jazz ~]# ls -la /var/lib/mysql/mysql-bin.* -rw-rw---- 1 mysql mysql 27287 Jul 15 15:02 /var/lib/mysql/mysql-bin.000001 -rw-rw---- 1 mysql mysql 35873 Jul 15 15:02 /var/lib/mysql/mysql-bin.000002 -rw-rw---- 1 mysql mysql 1251 Jul 15 15:26 /var/lib/mysql/mysql-bin.000003 -rw-rw---- 1 mysql mysql 885 Jul 15 15:38 /var/lib/mysql/mysql-bin.000004 -rw-rw---- 1 mysql mysql 107 Jul 15 15:38 /var/lib/mysql/mysql-bin.000005 -rw-rw---- 1 mysql mysql 95 Jul 15 15:38 /var/lib/mysql/mysql-bin.index [root@jazz ~]#
Agora temos os componentes para realizar um restore a partir de um backup full e um incremental, nosso banco agora está desta forma
mysql> USER bk_test; Database changed mysql> SHOW TABLES; +-------------------+ | Tables_in_bk_test | +-------------------+ | bk_test_t1 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM bk_test_t1; +----+------------+---------------------+ | id | test_field | time_created | +----+------------+---------------------+ | 1 | val1 | 2012-07-15 15:13:53 | | 2 | val2 | 2012-07-15 15:13:53 | | 3 | val3 | 2012-07-15 15:13:54 | | 4 | val4 | 2012-07-15 15:33:50 | | 5 | val5 | 2012-07-15 15:33:50 | | 6 | val6 | 2012-07-15 15:33:52 | +----+------------+---------------------+ 6 rows in set (0.00 sec) mysql>
Digamos agora que acidentalmente a database que utilizamos foi completamente removida
mysql> DROP DATABASE bk_test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
Tudo bem! temos o backup full e estamos a salvo certo?!
deadcow@jazz ~ $ mysql -u root < full_backup.sql deadcow@jazz ~ $ mysql -u root mysql> use bk_test; Database changed mysql> SELECT * FROM bk_test_t1; +----+------------+---------------------+ | id | test_field | time_created | +----+------------+---------------------+ | 1 | val1 | 2012-07-15 15:13:53 | | 2 | val2 | 2012-07-15 15:13:53 | | 3 | val3 | 2012-07-15 15:13:54 | +----+------------+---------------------+ 3 rows in set (0.00 sec) mysql>
Este backup não está completo, havia 6 entradas e agora só existem 3, mas ainda temos o backup incremental de um periodo posterior ao backup full, vamos aplica-lo
[root@jazz ~]# cd /var/lib/mysql/ [root@jazz mysql]# mysqlbinlog mysql-bin.000004 | mysql -u root bk_test [root@jazz mysql]# mysql -u root bk_test mysql> SELECT * FROM bk_test_t1; +----+------------+---------------------+ | id | test_field | time_created | +----+------------+---------------------+ | 1 | val1 | 2012-07-15 15:13:53 | | 2 | val2 | 2012-07-15 15:13:53 | | 3 | val3 | 2012-07-15 15:13:54 | | 4 | val4 | 2012-07-15 15:33:50 | | 5 | val5 | 2012-07-15 15:33:50 | | 6 | val6 | 2012-07-15 15:33:52 | +----+------------+---------------------+ 6 rows in set (0.00 sec) mysql>
E tudo está recuperado, sem dores de cabeça. a ferramenta mysqlbinlog acompanha o pacote de instalação do mysql-server, ela retorna os comandos do binlog em plaintext e através dela é possivel recuperar o banco de dados, também pode se redirecionar a saida do programa para um arquivo e então editá-lo para excluir queries destrutivas antes de executar no mysql.
Além disso é possivel passar como parametro um periodo especifico de tempo para o mysqlbinlog, é muito util para recuperar pequenas quantidades de entradas perdidas em um banco de dados, desde que se conheca o periodo.
mysqlbinlog --start-datetime="2012-07-15 15:13:53" \ --stop-datetime="2012-07-15 15:13:54" mysql-bin.000004|mysql -uroot bk_test
Existem outras dezenas de possibilidades no MySQL que poucas pessoas conhecem, procure blogs, assine feeds e procure crescer profissionalmente através do seu conhecimento, facilite sua vida e a da sua empresa!
Referencias: