binlog2sql之MySQL数据闪回实践

gaohan

　　DBA或开发人员，有时会误删或者误更新数据，如果是线上环境并且影响较大，就需要能快速回滚。传统恢复方法是利用备份重搭实例，再应用去除错误sql后的binlog来恢复数据。此法费时费力，甚至需要停机维护，并不适合快速回滚。也有团队利用LVM快照来缩短恢复时间，但快照的缺点是会影响mysql的性能。现在有不少好用而且效率又高的开源闪回工具如binlog2sql、mysqlbinlog_flashback，这些工具在工作中给DBA减轻了不少痛苦，以下针对binlog2sql的使用进行实践演练。
　　binlog2sql的用途：

• 数据快速回滚(闪回)
  
• 主从切换后数据不一致的修复
  
• 从binlog生成标准SQL，带来的衍生功能
  

　　安装binlog2sql前先安装git和pip：
　　

yum -y install epel-release 　　

　　

yum -y install git  python-pip　　

　　安装binlog2sql：
　　

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql　　

　　

pip install -r requirements.txt　　

　　MySQL的配置要开启以下选项：
　　

[mysqld]　　
server_id
= 1　　
log_bin
= /var/log/mysql/mysql-bin.log　　
max_binlog_size
= 1G　　
binlog_format
= row　　
binlog_row_image
= full　　

　　要授权一个用户有以下权限：
　　

SELECT, REPLICATION SLAVE, REPLICATION CLIENT　　

　　权限说明：

• select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
  
• super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
  
• replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
  

　　binlog2sql的使用参数说明：
　　mysql连接配置
　　-h host; -P port; -u user; -p password
　　解析模式
　　--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
　　-K, --no-primary-key 对INSERT语句去除主键。可选。
　　-B, --flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。
　　解析范围控制
　　--start-file 起始解析文件。必须。
　　--start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。
　　--stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
　　--stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
　　--start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
　　--stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
　　对象过滤
　　-d, --databases 只输出目标db的sql。可选。默认为空。
　　-t, --tables 只输出目标tables的sql。可选。默认为空。
　　进行用户授权操作（这里只是举例子）：
　　

mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  'flashuser'@'127.0.0.1'>
Query OK, 0 rows affected (0.00 sec)　　

　　我们可以看看现在有的数据：
　　

mysql>  show global variables like 'binlog_format';　　

+---------------+-------+　　
| Variable_name | Value |
　　
+---------------+-------+
　　
| binlog_format | ROW   |
　　
+---------------+-------+
　　
1 row in set (0.00 sec)
　　

　　
mysql>
　　

　　
mysql> show master status;
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| mysql-bin.000107 |      120 |              |                  |                   |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> select * from tb1;
　　
+------+------+
　　
|>　　
+------+------+
　　
|    1 | aa   |
　　
|    2 | bb   |
　　
+------+------+
　　
2 rows in set (0.00 sec)
　　

　　
mysql>
　　

　　我们现在进行数据的DML操作：
　　

mysql>  insert into tb1 values (3,'cc');　　
Query OK,
1 row affected (0.00 sec)　　

　　
mysql
>  insert into tb1 values (4,'dd');　　
Query OK,
1 row affected (0.00 sec)　　

　　
mysql
>  update tb1 set name='new_aa' where>
Query OK,
1 row affected (0.00 sec)　　
Rows matched:
1  Changed: 1  Warnings: 0　　

　　
mysql
>  delete from tb1 where>
Query OK, 1 row affected (0.00 sec)　　

　　
mysql
>  select * from tb1;　　

+------+--------+
　　
|>　　
+------+--------+
　　
|    1 | new_aa |
　　
|    3 | cc     |
　　
|    4 | dd     |
　　
+------+--------+
　　
3 rows in set (0.00 sec)
　　

　　
mysql>
　　

　　下面我们使用binlog2sql进行格式为ROW的binlog生成标准SQL，带个-d的参数指定库名：
　　

[iyunv@db_server_xuanzhi ~]#python binlog2sql.py -uflashuser -h127.0.0.1 -pflash123 -dxuanzhi --start-file='mysql-bin.000107' > xuanzhi.sql　　
[iyunv@db_server_xuanzhi
~]#cat xuanzhi.sql　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (3, 'cc'); #start 4 end 290 time 2017-03-23 10:41:34　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (4, 'dd'); #start 321 end 491 time 2017-03-23 10:41:38　　
UPDATE `xuanzhi`.`tb1` SET `
id`=1, `name`='new_aa' WHERE `id`=1 AND `name`='aa' LIMIT 1; #start 522 end 705 time 2017-03-23 10:41:42　　
DELETE FROM `xuanzhi`.`tb1` WHERE `
id`=2 AND `name`='bb' LIMIT 1; #start 736 end 906 time 2017-03-23 10:41:50　　
[iyunv@db_server_xuanzhi
~]#　　

　　我们可以看到，刚刚执行过的sql都生成出来了。
　　我们现在对xuanzhi这个库的所有操作生成反向SQL，这个时候需要在上面语句的基础上带一个-B参数，就是flashback闪回的意思：
　　

[iyunv@db_server_xuanzhi ~]#python binlog2sql.py -uflashuser -h127.0.0.1 -pflash123 -dxuanzhi --start-file='mysql-bin.000107' -B  > rollback_xuanzhi.sql　　
[iyunv@db_server_xuanzhi
~]#cat rollback_xuanzhi.sql　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (2, 'bb'); #start 736 end 906 time 2017-03-23 10:41:50　　
UPDATE `xuanzhi`.`tb1` SET `
id`=1, `name`='aa' WHERE `id`=1 AND `name`='new_aa' LIMIT 1; #start 522 end 705 time 2017-03-23 10:41:42　　
DELETE FROM `xuanzhi`.`tb1` WHERE `
id`=4 AND `name`='dd' LIMIT 1; #start 321 end 491 time 2017-03-23 10:41:38　　
DELETE FROM `xuanzhi`.`tb1` WHERE `
id`=3 AND `name`='cc' LIMIT 1; #start 4 end 290 time 2017-03-23 10:41:34　　
[iyunv@db_server_xuanzhi
~]#　　

　　可以看到生成了跟上面标准SQL相反的SQL了，通过这些反向SQL可以进行误操的数据恢复。
　　下面我们模拟对线上数据进行误操及恢复的过程：
　　模拟一：误操把一个表的某些重要记录删除了，进行恢复
　　我们把tb1的id>=3的数据删除：
　　

mysql> select * from tb1;　　

+----+------+
　　
|>　　
+----+------+
　　
|  1 | aa   |
　　
|  2 | bb   |
　　
|  3 | cc   |
　　
|  4 | dd   |
　　
+----+------+
　　
4 rows in set (0.00 sec)
　　


　　
mysql> delete from tb1 where>　　
Query OK, 2 rows affected (0.00 sec)
　　

　　
mysql> select * from tb1;            
　　
+----+------+

　　
|>　　
+----+------+
　　
|  1 | aa   |
　　
|  2 | bb   |
　　
+----+------+
　　
2 rows in set (0.00 sec)
　　

　　
mysql> show master status;
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| mysql-bin.000109 |      329 |              |                  |                   |
　　
+------------------+----------+--------------+------------------+-------------------+
　　

　　现在通过binlog2sql进行生成反向SQL，binlog2sql可以指定生成那个库的那个表的标准SQL或者反向SQL，带一个-t的选择：
　　

[iyunv@db_server_xuanzhi ~]# python binlog2sql.py -uflashuser -h127.0.0.1 -pflash123 -dxuanzhi -ttb1 --start-file='mysql-bin.000109' -B  > rollback_tb1.sql　　
[iyunv@db_server_xuanzhi
~]# cat rollback_tb1.sql　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (4, 'dd'); #start 4 end 298 time 2017-03-23 12:39:20　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (3, 'cc'); #start 4 end 298 time 2017-03-23 12:39:20　　

　　我们可以看刚刚对tb1进行误删的操作，都生成了反向的SQL语句也就是INSERT INTO，我们进行导入操作，看数据能否正常恢复
　　

[iyunv@db_server_xuanzhi ~]#mysql -uroot -p123456 <./rollback_tb1.sql　　
Warning: Using a password on the command line interface can be insecure.
　　
[iyunv@db_server_xuanzhi
~]#　　

　　登录查看一下数据：
　　

mysql> select * from tb1;　　

+----+------+
　　
|>　　
+----+------+
　　
|  1 | aa   |
　　
|  2 | bb   |
　　
|  3 | cc   |
　　
|  4 | dd   |
　　
+----+------+
　　
4 rows in set (0.00 sec)
　　

　　
mysql>
　　

　　可以看到数据可以正常的恢复。
　　模拟二：误操作把一个表的数据删除了，经常出现的就是delete没带where条件
　　

mysql> select * from tb1;　　

+----+------+
　　
|>　　
+----+------+
　　
|  1 | aa   |
　　
|  2 | bb   |
　　
|  3 | cc   |
　　
|  4 | dd   |
　　
+----+------+
　　
4 rows in set (0.00 sec)
　　

　　
mysql> delete from tb1;
　　
Query OK, 4 rows affected (0.00 sec)
　　

　　
mysql> select * from tb1;
　　
Empty set (0.00 sec)
　　

　　
mysql> show master status;           
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| mysql-bin.000110 |      345 |              |                  |                   |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql>
　　

　　我们用bin2log对这个表进行恢复：
　　

[iyunv@db_server_xuanzhi ~]# python binlog2sql.py -uflashuser -h127.0.0.1 -pflash123 -dxuanzhi -ttb1 --start-file='mysql-bin.000110' -B  > rollback_tb1.sql　　
[iyunv@db_server_xuanzhi
~]# cat rollback_tb1.sql　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (4, 'dd'); #start 4 end 314 time 2017-03-23 13:37:29　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (3, 'cc'); #start 4 end 314 time 2017-03-23 13:37:29　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (2, 'bb'); #start 4 end 314 time 2017-03-23 13:37:29　　
INSERT INTO `xuanzhi`.`tb1`(`
id`, `name`) VALUES (1, 'aa'); #start 4 end 314 time 2017-03-23 13:37:29　　
[iyunv@db_server_xuanzhi
~]# mysql -uroot -p123456 <./rollback_tb1.sql　　
Warning: Using a password on the command line interface can be insecure.
　　

　　

　　再查询一下，数据是否把数据恢复了：
　　

mysql> select * from tb1;　　
Empty
set (0.00 sec)　　

　　

<Slave_1>[xuanzhi]> show master status;　　

+------------------+----------+--------------+------------------+-------------------+　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| mysql-bin.000110 |      345 |              |                  |                   |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> select * from tb1;
　　
+----+------+

　　
|>　　
+----+------+
　　
|  1 | aa   |
　　
|  2 | bb   |
　　
|  3 | cc   |
　　
|  4 | dd   |
　　
+----+------+
　　
4 rows in set (0.00 sec)
　　

　　
mysql>
　　

　　可以看到可以正常恢复，但值得注意的是drop table 和truncate table 是无法生成反向SQL的,所以建议线上程序账号只给insert，upfate，select，delete权限。
　　还有很多选项--stop-position/--end-pos stop-file、--start-datetime/--stop-datetime这些选项就不一 一说明了，binlog2sql总的来说还是比较好用与实用的，以前写过一篇博客binlog-rollback.pl  的博客，这个脚本也可以实现，但遇到比较大的binlog就可能会出现一些问题，下面是我在线上测试对1.1G的binlog用binlog2sql进行解析的时间（阿里云的SSD盘）：

　　参考文章：
　　https://github.com/danfengcao/binlog2sql
　　https://github.com/danfengcao/binlog2sql/blob/master/example/mysql-flashback-priciple-and-practice.md
　　总结：一、线上要对程序做好最小化权限控制，这样可以减少很多不必要的麻烦。
　　二、现在开源比较好用的数据闪回工具有mysqlbinlog_flashback和binlog2sql，给DBA日常维护带来了许多帮助。
　　作者：陆炫志
　　出处：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111
　　您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。