binlog2sql闪回工具的使用
一、下载安装依赖的python
yum install openssl-devel bzip2-devel expat-devel gdbm-devel readline-devel sqlite-devel yum install python34 -y python3 --version wget --no-check-certificate https://bootstrap.pypa.io/get-pip.py python3 get-pip.py pip3 -V
切换升级本地python版本
python -V; python2.6 -V # 查看当前python版本 这两个应该都是原始的2.6.x ls -hli /usr/bin/python* cp -f /usr/bin/python2.6 /usr/bin/python2.6.bak rm -f /usr/bin/python /usr/bin/python cp /usr/bin/python3.4 /usr/bin/python ls -hli /usr/bin/python* python -V; python2.6 -V # 验证python版本是否OK(python->2.7, python2.6->python2.6.x) yum -h # 验证yum已经不可用,命令会出错退出 head -n 2 /usr/bin/yum sed -i ‘s/python$/&2.6/‘ /usr/bin/yum head -n 2 /usr/bin/yum # 将/usr/bin/yum 中的python 替换为python2.6 yum -h # 验证yum可用,此时不会再出错退出
二、安装binlog2sql
# 参考资料：https://www.runoob.com/w3cnote/python-pip-install-usage.html cd /opt/ yum -y install python-pip git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql pip install -r requirements.txt -i https://pypi.tuna.tsinghua.edu.cn/simple
查看帮助
[[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py --help usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS] [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]] [-t [TABLES [TABLES ...]]] [--only-dml] [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B] [--back-interval BACK_INTERVAL] Parse MySQL binlog to SQL you want optional arguments: --stop-never Continuously parse binlog. default: stop at the latest event when you start. --help help information -K, --no-primary-key Generate insert sql without primary key if exists -B, --flashback Flashback data to start_position of start_file --back-interval BACK_INTERVAL Sleep time between chunks of 1000 rollback sql. set it to 0 if do not need sleep connect setting: -h HOST, --host HOST Host the MySQL database server located -u USER, --user USER MySQL Username to log in as -p [PASSWORD [PASSWORD ...]], --password [PASSWORD [PASSWORD ...]] MySQL Password to use -P PORT, --port PORT MySQL port to use interval filter: --start-file START_FILE Start binlog file to be parsed --start-position START_POS, --start-pos START_POS Start position of the --start-file --stop-file END_FILE, --end-file END_FILE Stop binlog file to be parsed. default: ‘--start-file‘ --stop-position END_POS, --end-pos END_POS Stop position. default: latest position of ‘--stop- file‘ --start-datetime START_TIME Start time. format %Y-%m-%d %H:%M:%S --stop-datetime STOP_TIME Stop Time. format %Y-%m-%d %H:%M:%S; schema filter: -d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]] dbs you want to process -t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]] tables you want to process type filter: --only-dml only print dml, ignore ddl --sql-type [SQL_TYPE [SQL_TYPE ...]] Sql type you want to process, support INSERT, UPDATE, DELETE. [[email protected]_test_002 binlog2sql]#
三、使用该工具的前提
1. binlog_format为ROW，且binlog_row_image为full或noblog，默认为full。
2. 必须开启MySQL Server，理由有如下两点：
1> 它是基于BINLOG_DUMP协议来获取binlog内容
2> 需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
该工具所需权限如下：
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘flashbak_user‘@‘%‘ identified by ‘flashbak_user_pwd‘;**权限说明** * select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
* super/replication client：两个权限都可以，需要执行‘SHOW MASTER STATUS‘, 获取server端的binlog列表
* replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
因为是伪装成slave来获取主的二进制事件，故无需对binlog有可读权限。
四、测试验证
4-1、创建测试表
# 创建测试表 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4; insert into user(name) values(‘小赵‘); insert into user(name) values(‘小王‘); insert into user(name) values(‘小李‘); insert into user(name) values(‘小钱‘); insert into user(name) values(‘小孙‘); mysql> select * from user; ---- -------- | id | name | ---- -------- | 6 | 小赵 | | 7 | 小王 | | 8 | 小李 | | 9 | 小钱 | | 10 | 小孙 | ---- -------- 5 rows in set (0.00 sec)
4-2、update回滚实践
# update误操作闪回 mysql> update user set name=‘王源‘ where id=8; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 #发现修改错了。 [[email protected]_test_001 binlogdir]$ /data/mysql/percona_server/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv mysql-bin.000024 >./24.binlog # 搜索到王源,找到position点：--startposition=2082 --stop-position=2268 # 先解析出原始语句，以便确认 # 主库IP： 10.10.50.60 用户名：flashbak_user 密码：flashbak_user_pwd /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘flashbak_user‘@‘%‘ IDENTIFIED BY PASSWORD ‘*0589CD47DC016BC67FC7720BADEC3C1368C15F60‘; USE b‘sbtest‘; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4; INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, ‘小赵‘); #start 822 end 995 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (7, ‘小王‘); #start 1074 end 1247 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, ‘小李‘); #start 1326 end 1499 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, ‘小钱‘); #start 1578 end 1751 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, ‘小孙‘); #start 1830 end 2003 time 2019-12-23 10:38:41 UPDATE `sbtest`.`user` SET `id`=8, `name`=‘王源‘ WHERE `id`=8 AND `name`=‘小李‘ LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20 [[email protected]_test_002 binlog2sql]# # 进一步筛选 /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=UPDATE --start-position=2082 --stop-position=2268 [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=UPDATE --start-position=2082 --stop-position=2268 UPDATE `sbtest`.`user` SET `id`=8, `name`=‘王源‘ WHERE `id`=8 AND `name`=‘小李‘ LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20 [[email protected]_test_002 binlog2sql]# # 生成回滚语句 [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -B -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=UPDATE --start-position=2082 --stop-position=2268 UPDATE `sbtest`.`user` SET `id`=8, `name`=‘小李‘ WHERE `id`=8 AND `name`=‘王源‘ LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20 [[email protected]_test_002 binlog2sql]# mysql> select * from user; ---- -------- | id | name | ---- -------- | 6 | 小赵 | | 7 | 小王 | | 8 | 王源 | | 9 | 小钱 | | 10 | 小孙 | ---- -------- 5 rows in set (0.00 sec) mysql> UPDATE `sbtest`.`user` SET `id`=8, `name`=‘小李‘ WHERE `id`=8 AND `name`=‘王源‘ LIMIT 1; #start 2082 end 2268 time 2019-12-23 10:41:20 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; ---- -------- | id | name | ---- -------- | 6 | 小赵 | | 7 | 小王 | | 8 | 小李 | | 9 | 小钱 | | 10 | 小孙 | ---- -------- 5 rows in set (0.00 sec) mysql>
4-3、delete操作回滚实践
# delete语句回滚 mysql> delete from user where id in (6,8,9,10); Query OK, 4 rows affected (0.01 sec) mysql> select * from user; ---- -------- | id | name | ---- -------- | 7 | 小王 | ---- -------- 1 row in set (0.00 sec) [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘DELETE‘ GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘flashbak_user‘@‘%‘ IDENTIFIED BY PASSWORD ‘*0589CD47DC016BC67FC7720BADEC3C1368C15F60‘; USE b‘sbtest‘; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4; DELETE FROM `sbtest`.`user` WHERE `name`=‘小赵‘ AND `id`=6 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34 DELETE FROM `sbtest`.`user` WHERE `name`=‘小李‘ AND `id`=8 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34 DELETE FROM `sbtest`.`user` WHERE `name`=‘小钱‘ AND `id`=9 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34 DELETE FROM `sbtest`.`user` WHERE `name`=‘小孙‘ AND `id`=10 LIMIT 1; #start 2612 end 2821 time 2019-12-23 11:03:34 # 生成delete的回滚语句 [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -B -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘DELETE‘ --start-position=2612 --stop-position=2821 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, ‘小孙‘); #start 2612 end 2821 time 2019-12-23 11:03:34 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, ‘小钱‘); #start 2612 end 2821 time 2019-12-23 11:03:34 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, ‘小李‘); #start 2612 end 2821 time 2019-12-23 11:03:34 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, ‘小赵‘); #start 2612 end 2821 time 2019-12-23 11:03:34 [[email protected]_test_002 binlog2sql]# # 应用回滚 mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, ‘小孙‘); #start 2612 end 2821 time 2019-12-23 11:03:34 Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, ‘小钱‘); #start 2612 end 2821 time 2019-12-23 11:03:34 Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, ‘小李‘); #start 2612 end 2821 time 2019-12-23 11:03:34 Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, ‘小赵‘); #start 2612 end 2821 time 2019-12-23 11:03:34 Query OK, 1 row affected (0.00 sec) mysql> select * from user; ---- -------- | id | name | ---- -------- | 6 | 小赵 | | 7 | 小王 | | 8 | 小李 | | 9 | 小钱 | | 10 | 小孙 | ---- -------- 5 rows in set (0.00 sec) mysql>
4-4、insert操作回滚实践
# insert语句回滚 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (1, ‘小赵x‘); INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (2, ‘小赵x‘); INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (3, ‘小赵x‘); INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (5, ‘小赵x‘); [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘INSERT‘ GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘flashbak_user‘@‘%‘ IDENTIFIED BY PASSWORD ‘*0589CD47DC016BC67FC7720BADEC3C1368C15F60‘; USE b‘sbtest‘; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4; INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, ‘小赵‘); #start 822 end 995 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (7, ‘小王‘); #start 1074 end 1247 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, ‘小李‘); #start 1326 end 1499 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, ‘小钱‘); #start 1578 end 1751 time 2019-12-23 10:38:40 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, ‘小孙‘); #start 1830 end 2003 time 2019-12-23 10:38:41 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (10, ‘小孙‘); #start 2900 end 3073 time 2019-12-23 11:13:28 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (9, ‘小钱‘); #start 3152 end 3325 time 2019-12-23 11:13:28 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (8, ‘小李‘); #start 3404 end 3577 time 2019-12-23 11:13:28 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (6, ‘小赵‘); #start 3656 end 3829 time 2019-12-23 11:13:30 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (1, ‘小赵x‘); #start 3908 end 4082 time 2019-12-23 11:19:55 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (2, ‘小赵x‘); #start 4161 end 4335 time 2019-12-23 11:19:55 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (3, ‘小赵x‘); #start 4414 end 4588 time 2019-12-23 11:19:55 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (5, ‘小赵x‘); #start 4667 end 4841 time 2019-12-23 11:19:56 [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘INSERT‘ --start-position=3908 --stop-position=4841 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (1, ‘小赵x‘); #start 3908 end 4082 time 2019-12-23 11:19:55 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (2, ‘小赵x‘); #start 4161 end 4335 time 2019-12-23 11:19:55 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (3, ‘小赵x‘); #start 4414 end 4588 time 2019-12-23 11:19:55 INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (5, ‘小赵x‘); #start 4667 end 4841 time 2019-12-23 11:19:56 [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py -B -h 10.10.50.60 -P3306 -uflashbak_user -pflashbak_user_pwd -dsbtest -tuser --start-file=‘mysql-bin.000024‘ --stop-file=‘mysql-bin.000024‘ --sql-type=‘INSERT‘ --start-position=3908 --stop-position=4841 DELETE FROM `sbtest`.`user` WHERE `id`=5 AND `name`=‘小赵x‘ LIMIT 1; #start 4667 end 4841 time 2019-12-23 11:19:56 DELETE FROM `sbtest`.`user` WHERE `id`=3 AND `name`=‘小赵x‘ LIMIT 1; #start 4414 end 4588 time 2019-12-23 11:19:55 DELETE FROM `sbtest`.`user` WHERE `id`=2 AND `name`=‘小赵x‘ LIMIT 1; #start 4161 end 4335 time 2019-12-23 11:19:55 DELETE FROM `sbtest`.`user` WHERE `id`=1 AND `name`=‘小赵x‘ LIMIT 1; #start 3908 end 4082 time 2019-12-23 11:19:55 [[email protected]_test_002 binlog2sql]# # 应用回滚语句 mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (1, ‘小赵x‘); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (2, ‘小赵x‘); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (3, ‘小赵x‘); INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (5, ‘小赵x‘);Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO `sbtest`.`user`(`id`, `name`) VALUES (5, ‘小赵x‘); Query OK, 1 row affected (0.00 sec) mysql> select * from user; ---- --------- | id | name | ---- --------- | 1 | 小赵x | | 2 | 小赵x | | 3 | 小赵x | | 5 | 小赵x | | 6 | 小赵 | | 7 | 小王 | | 8 | 小李 | | 9 | 小钱 | | 10 | 小孙 | ---- --------- 9 rows in set (0.00 sec) mysql> DELETE FROM `sbtest`.`user` WHERE `id`=5 AND `name`=‘小赵x‘ LIMIT 1; #start 4667 end 4841 time 2019-12-23 11:19:56 DELETE FROM `sbtest`.`user` WHERE `id`=3 AND `name`=‘小赵x‘ LIMIT 1; #start 4414 end 4588 time 2019-12-23 11:19:55 Query OK, 1 row affected (0.00 sec) mysql> DELETE FROM `sbtest`.`user` WHERE `id`=3 AND `name`=‘小赵x‘ LIMIT 1; #start 4414 end 4588 time 2019-12-23 11:19:55 DELETE FROM `sbtest`.`user` WHERE `id`=2 AND `name`=‘小赵x‘ LIMIT 1; #start 4161 end 4335 time 2019-12-23 11:19:55 Query OK, 1 row affected (0.01 sec) mysql> DELETE FROM `sbtest`.`user` WHERE `id`=2 AND `name`=‘小赵x‘ LIMIT 1; #start 4161 end 4335 time 2019-12-23 11:19:55 Query OK, 1 row affected (0.01 sec) mysql> DELETE FROM `sbtest`.`user` WHERE `id`=1 AND `name`=‘小赵x‘ LIMIT 1; #start 3908 end 4082 time 2019-12-23 11:19:55 Query OK, 1 row affected (0.01 sec) mysql> select * from user; ---- -------- | id | name | ---- -------- | 6 | 小赵 | | 7 | 小王 | | 8 | 小李 | | 9 | 小钱 | | 10 | 小孙 | ---- -------- 5 rows in set (0.00 sec) mysql>
附录：
# binlog2sql参数 [[email protected]_test_002 binlog2sql]# /usr/bin/python /opt/binlog2sql/binlog2sql/binlog2sql.py --help File "/opt/binlog2sql/binlog2sql/binlog2sql.py", line 73 with temp_open(tmp_file, "w") as f_tmp, self.connection as cursor: ^ SyntaxError: invalid syntax [[email protected]_test_002 binlog2sql]# # 出现上述提示，请更换python版本即可，不要使用python2.6版本