[root@wallet01 ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) [root@wallet01 ~]# python -V Python 2.7.5 [root@wallet01 ~]# yum install -y python-pip [root@wallet01 ~]# pip -V pip 8.1.2 from /usr/lib/python2.7/site-packages (python 2.7) [root@wallet01 ~]# git clone https://github.com/danfengcao/binlog2sql.git Initialized empty Git repository in /root/binlog2sql/.git/ remote: Enumerating objects: 323, done. remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323 Receiving 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 --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] --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 -p Enter 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 gsoa mysql> 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 -p Enter password: mysql> use gsoa mysql> select count(*) from bd_city; +----------+ | count(*) | +----------+ | 372 | +----------+ 1 row in set (0.00 sec)