【MySQL】数据库闪回工具--binlog2sql

[root@wallet01 ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) [root@wallet01 ~]# python -VPython 2.7.5[root@wallet01 ~]# yum install -y python-pip[root@wallet01 ~]# pip -Vpip 8.1.2 from /usr/lib/python2.7/site-packages (python 2.7)[root@wallet01 ~]# git clone https://github.com/danfengcao/binlog2sql.gitInitialized empty Git repository in /root/binlog2sql/.git/remote: Enumerating objects: 323, done.remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323Receiving objects: 100% (323/323), 151.51 KiB | 245 KiB/s, done.Resolving deltas: 100% (170/170), done.[root@wallet01 ~]# cd binlog2sql[root@wallet01 binlog2sql]# pip install -r requirements.txt[root@wallet01 binlog2sql]# cd binlog2sql/[root@wallet01 binlog2sql]# python binlog2sql.py --helpusage: 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]                     --start-file --起始解析文件--stop-file  --终止解析文件--start-position --起始解析位置--stop-position  --终止解析位置--start-datetime --起始解析时间，格式'%Y-%m-%d %H:%M:%S'。--stop-datetime --终止解析时间，格式'%Y-%m-%d %H:%M:%S'。-d --仅解析目标db的sql-t --仅解析目标table的sql--only-dml --仅解析dml，忽略ddl。--sql-type --仅解析指定类型，支持insert，update，delete。-B --生成回滚SQL[root@wallet01 ~]# mysql -uroot -pEnter password: mysql> grant select,replication client,replication slave on *.* to 'fb'@'%' identified by 'fb@2019';    Query OK, 0 rows affected (0.05 sec)mysql> flush privileges;Query OK, 0 rows affected (0.11 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000008 |      120 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> select now();+---------------------+| now()               |+---------------------+| 2019-10-12 17:18:22 |+---------------------+1 row in set (0.06 sec)mysql> use gsoamysql> select count(*) from bd_city;+----------+| count(*) |+----------+|      372 |+----------+1 row in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> delete from bd_city;Query OK, 372 rows affected (0.09 sec)mysql> commit;Query OK, 0 rows affected (0.04 sec)mysql> select count(*) from bd_city;+----------+| count(*) |+----------+|        0 |+----------+1 row in set (0.01 sec)mysql> select now();+---------------------+| now()               |+---------------------+| 2019-10-12 17:22:26 |+---------------------+1 row in set (0.00 sec)恢复已提交事务中删除的记录[root@wallet01 binlog2sql]# python binlog2sql.py -hlocalhost -P3306 -ufb -p'fb@2019' -dgsoa -tbd_city \--start-file='mysql-bin.000008' --only-dml --sql-type=delete \--start-datetime='2019-10-12 17:18:22' --stop-datetime='2019-10-12 17:22:26' >fb.log[root@wallet01 binlog2sql]# more fb.log DELETE FROM `gsoa`.`bd_city` WHERE `status`='1' AND `parent_name`='中国' AND `code`='110000' AND `name`='北京市' AND `parent_code`='CN' LIMIT 1; #start 423 end 8756 time 2019-10-12 17:21:26[root@wallet01 binlog2sql]# tail -n 1 fb.log DELETE FROM `gsoa`.`bd_city` WHERE `status`='1' AND `parent_name`='' AND `code`='CN' AND `name`='中国' AND `parent_code`='' LIMIT 1; #start 423 end 19319 time 2019-10-12 17:21:26[root@wallet01 binlog2sql]# python binlog2sql.py -hlocalhost -P3306 -ufb -p'fb@2019' -dgsoa -tbd_city \--start-file='mysql-bin.000008' --start-position=423 --stop-position=19319 -B > rollback.sql[root@wallet01 binlog2sql]# more rollback.sql | grep '北京'INSERT INTO `gsoa`.`bd_city`(`status`, `parent_name`, `code`, `name`, `parent_code`) VALUES ('1', '中国', '110000', '北京市', 'CN'); #start 423 end 8756 time 2019-10-12 17:21:26[root@wallet01 binlog2sql]# mysql -uroot -p < rollback.sql Enter password: [root@wallet01 ~]# mysql -uroot -pEnter password: mysql> use gsoamysql> select count(*) from bd_city;+----------+| count(*) |+----------+|      372 |+----------+1 row in set (0.00 sec)