第51章 MySQL主流闪回工具详解

51.2.1　安装和使用要求

• 1．安装binlog2sql

[root@localhost~]# git clone https://github.com/danfengcao/binlog2sql.git && cd\ binlog2sql
[root@localhost~]# pip install -r requirements.txt

• 3．基本用法

[root@localhost~]# python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' –dtest\ -t test3 test4 --start-file='MySQL-bin.000002'

[root@localhost~]# python binlog2sql.py --flashback -h127.0.0.1 -P3306 –uadmin\ -p'admin' -dtest -ttest3 --start-file='MySQL-bin.000002' --start-position=763 --stop-\ position=1147

51.2.3　实战演示

• 1．造数

# 建库、建表
mysql> CREATE DATABASE `test`;use test;
mysql> CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

# 插入测试数据
mysql> insert into test(name) values('test1');
mysql> insert into test(name) values('test2');
mysql> insert into test(name) values('test3');
mysql> insert into test(name) values('test4');
mysql> insert into test(name) values('test5');
mysql> insert into test(name) values('test6');
mysql> insert into test(name) values('test7');
mysql> insert into test(name) values('test8');

• 2．INSERT闪回

mysql> use test
Database changed
mysql> show tables;
......
1 row in set (0.00 sec)

mysql> select * from test;
......
8 rows in set (0.00 sec)

mysql> insert into test(name) values('test9');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('test10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('test11');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('test12');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
......
12 rows in set (0.00 sec)

mysql> show binary logs;
......
45 rows in set (0.00 sec)

[root@localhost~]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 --start-file=\ 'mysql-bin.000045'
......

[root@localhost~]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 --start-file=\ 'mysql-bin.000045' --start-position=7109 --stop-position=8343
......

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0\ --start-file='mysql-bin.000045' --start-position=7109 --stop-position=8343 –flashback\ |tee flashback_insert_20170524.sql
......

[root@localhost binlog2sql]# cat flashback_insert_20170524.sql 
......

# 如果不希望这些执行闪回操作的语句再同步到其他地方或者写入binlog，则可以使用sql_log_bin=0关闭这个会话的binlog记录功能，等导入完成后再打开。如果希望这些闪回操作也同步到其他地方，则忽略修改sql_log_bin的操作
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /root/binlog2sql-master/binlog2sql/flashback_insert_20170524.sql;
Query OK, 1 row affected (0.01 sec)
......

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
......
8 rows in set (0.00 sec)

• 3．UPDATE闪回

mysql> update test set name='xx' where id in (2,6,8);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> update test set name='yy' where id in (10,14,16);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test;
......
8 rows in set (0.00 sec)

mysql> show binary logs;
......
45 rows in set (0.00 sec)

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 –start\ -file='mysql-bin.000045'
......

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 –start\ -file='mysql-bin.000045' --start-position=8439 --stop-position=9116
......

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 –start\ -file='mysql-bin.000045' --start-position=8439 --stop-position=9116 --flashback |tee\ flashback_update_20170524.sql
......

[root@localhost binlog2sql]# cat flashback_update_20170524.sql 
......

# 如果不希望这些执行闪回操作的语句再同步到其他地方或者写入binlog，则可以使用sql_log_bin=0关闭这个会话的binlog记录功能，等导入完成后再打开。如果希望这些闪回操作也同步到其他地方，则忽略修改sql_log_bin的操作
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /root/binlog2sql-master/binlog2sql/flashback_update_20170524.sql;
......

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
......
8 rows in set (0.00 sec)

• 4．DELETE闪回

mysql> delete from test where id=16;
Query OK, 1 row affected (0.00 sec)

mysql> delete from test where id=14;
Query OK, 1 row affected (0.01 sec)

mysql> delete from test where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> delete from test where id in(2,4,6);
Query OK, 2 rows affected (0.00 sec)

mysql> delete from test;
Query OK, 3 rows affected (0.00 sec)

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

mysql> show binary logs;
......
45 rows in set (0.00 sec)

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 –start\ -file='mysql-bin.000045'
......

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 –start\ -file='mysql-bin.000045' --start-position=9212 --stop-position=10748
......

[root@localhost binlog2sql]# ./binlog2sql.py -h 10.10.30.250 -uadmin -pletsg0 –start\ -file='mysql-bin.000045' --start-position=9212 --stop-position=10748  --flashback |tee\ flashback_delete_20170524.sql
......

[root@localhost binlog2sql]# cat flashback_delete_20170524.sql 
......

# 如果不希望这些执行闪回操作的语句再同步到其他地方或者写入binlog，则可以使用sql_log_bin=0关闭这个会话的binlog记录功能，等导入完成后再打开。如果希望这些闪回操作也同步到其他地方，则忽略修改sql_log_bin的操作

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /root/binlog2sql-master/binlog2sql/flashback_delete_20170524.sql;
Query OK, 1 row affected (0.01 sec)
......

Query OK, 1 row affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
......
8 rows in set (0.00 sec)

51.3.1　安装MyFlash

[root@localhost~]# wget https://raw.githubusercontent.com/Meituan-Dianping/MyFlash/\ master/binary/flashback

51.3.2　命令行选项

# 回滚整个文件
[root@localhost~]# ./flashback --binlogFileNames=haha.000041
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p

# 回滚该文件中的所有INSERT语句
[root@localhost~]# ./flashback  --sqlTypes='INSERT' --binlogFileNames=haha.000041
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p

# 回滚大文件
## 回滚
[root@localhost~]# ./flashback --binlogFileNames=haha.000042
## 切割大文件
[root@localhost~]# ./flashback --maxSplitSize=1 --binlogFileNames=binlog_output_base.\ flashback
## 应用
mysqlbinlog binlog_output_base.flashback.000001 | mysql -h<host> -u<user> -p
...
mysqlbinlog binlog_output_base.flashback.<N> | mysql -h<host> -u<user> -p

51.3.3　实战演示

• 1．造数

mysql> create table test_flashback(id int primary key auto_increment,xid int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_flashback(xid) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_flashback(xid) select xid from test_flashback; 
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
......
mysql> insert into test_flashback(xid) select xid from test_flashback;
Query OK, 512 rows affected (0.00 sec)
Records: 512 Duplicates: 0 Warnings: 0

• 2．INSERT 闪回

mysql> flush binary logs;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test_flashback where xid=2;
Empty set (0.00 sec)

# 插入3行xid值为2的数据
mysql> insert into test_flashback(xid) values(2),(2),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test_flashback where xid=2;
......
3 rows in set (0.01 sec)

[root@localhost~]# ./flashback --binlogFileNames=/data/mysqldata1/binlog/mysql-bin.000009
[root@localhost ~]# ll binlog_output_base.flashback
-rw-r--r-- 1 root root 243 Sep 11 08:51 binlog_output_base.flashback

# 查看该文件的类型，从输出结果中可以看到，该文件就是MySQL的binlog格式
[root@localhost ~]# file binlog_output_base.flashback
binlog_output_base.flashback: MySQL replication log

[root@localhost ~]# mysqlbinlog -vv binlog_output_base.flashback
......

# 如果打开了GTID，则需要使用mysqlbinlog的--skip-gtids选项
[root@localhost ~]# mysqlbinlog --skip-gtids binlog_output_base.flashback |mysql –uroot\ -ppassword -S /tmp/mysql.sock test
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from test_flashback where xid=2;
Empty set (0.00 sec)

• 3．DELETE 闪回

# 插入3行xid=2的数据
mysql> insert into test_flashback(xid) values(2),(2),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test_flashback where xid=2;
......
3 rows in set (0.01 sec)

mysql> flush binary logs;
Query OK, 0 rows affected (0.00 sec)

# 删除xid=2的数据
mysql> delete from test_flashback where xid=2;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from test_flashback where xid=2;
Empty set (0.00 sec)

[root@localhost ~]# ./flashback --binlogFileNames=/data/mysqldata1/ binlog/mysql-bin.\ 000013 --outBinlogFileNameBase=test_delete_flashback
[root@localhost ~]# ll test_delete_flashback.flashback 
-rw-r--r-- 1 root root 243 Sep 11 12:20 test_delete_flashback.flashback

[root@localhost ~]# mysqlbinlog -vv test_delete_flashback.flashback 
......

# 如果打开了GTID，则需要使用mysqlbinlog的--skip-gtids选项
[root@localhost ~]# mysqlbinlog --skip-gtids test_delete_flashback.flashback |mysql\ -uroot -ppassword -S /tmp/mysql.sock test                 
 [root@localhost ~]# 

mysql> select * from test_flashback where xid=2;
......
3 rows in set (0.01 sec)

• 4．UPDATE 闪回

# 插入3行xid=3的测试数据
mysql> insert into test_flashback(xid) values(3),(3),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> flush binary logs;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_flashback where xid=3;
......
3 rows in set (0.00 sec)

# 更新xid=3的行为xid=33
mysql> update test_flashback set xid=33 where xid=3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from test_flashback where xid=3;    
Empty set (0.00 sec)

[root@localhost ~]# ./flashback --binlogFileNames=/data/mysqldata1/ binlog/mysql-bin.\ 000014 --outBinlogFileNameBase=test_update_flashback
[root@localhost ~]# ll test_update_flashback.flashback 
-rw-r--r-- 1 root root 271 Sep 11 14:49 test_update_flashback.flashback

[root@localhost ~]# mysqlbinlog -vv test_update_flashback.flashback
......

# 如果打开了GTID，则需要使用mysqlbinlog的--skip-gtids选项
[root@localhost ~]# mysqlbinlog --skip-gtids test_update_flashback.flashback|mysql –uroot\ -ppassword -S /tmp/mysql.sock test


mysql> select * from test_flashback where xid=3;
......
3 rows in set (0.00 sec)

mysql> select * from test_flashback where xid=33;
Empty set (0.00 sec)

---

上一篇：第50章　MySQL主流备份工具之mydumper详解 | 下一篇：附录A 线程状态信息详解目录