【MySQL Database】通过binlog2sql恢复误删除的数据

[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 binlog2sql]# mysql -uroot -p
Enter password: 

mysql> grant select,replication client,replication slave on *.* to 'fb'@'%' identified by 'fb@2020';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | 32069570 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-29 17:36:44 |
+---------------------+
1 row in set (0.00 sec)

mysql> use soe
Database changed

mysql> select count(*) from warehouse;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from warehouse where w_city = 'OdeUC8HZm9';
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from warehouse;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-29 17:39:22 |
+---------------------+
1 row in set (0.00 sec)

[root@wallet01 binlog2sql]# python binlog2sql.py -hlocalhost -P3306 -ufb -p'fb@2020' -dsoe -twarehouse \
--start-file='mysql_bin.000001' --only-dml --sql-type=delete \
--start-datetime='2020-12-29 17:36:44' --stop-datetime='2020-12-29 17:39:22' >fb.log

[root@wallet01 binlog2sql]# cat fb.log
DELETE FROM `soe`.`warehouse` WHERE `w_city`='OdeUC8HZm9' AND `w_state`='St' AND `w_name`='urv4zPDL2' AND `w_zip`='600511111' AND `w_tax`=0.1300 AND `w_ytd`=3000000.00 AND `w_id`=1 AND 
`w_street_1`='unfSbIluWGrkZaEptSwh' AND `w_street_2`='iFUfKZWf7QlD' LIMIT 1; #start 33323325 end 33323655 time 2020-12-29 17:38:31

[root@wallet01 binlog2sql]# python binlog2sql.py -hlocalhost -P3306 -ufb -p'fb@2020' -dsoe -twarehouse \
--start-file='mysql_bin.000001' --start-position=33323325 --stop-position=33323655 -B > rollback.sql

[root@wallet01 binlog2sql]# cat rollback.sql
INSERT INTO `soe`.`warehouse`(`w_city`, `w_state`, `w_name`, `w_zip`, `w_tax`, `w_ytd`, `w_id`, `w_street_1`, `w_street_2`) VALUES ('OdeUC8HZm9', 'St', 'urv4zPDL2', '600511111', 0.1300, 3000000.00, 
1, 'unfSbIluWGrkZaEptSwh', 'iFUfKZWf7QlD'); #start 33323325 end 33323655 time 2020-12-29 17:38:31

[root@wallet01 binlog2sql]# mysql -uroot -p < rollback.sql 
Enter password: 

[root@wallet01 binlog2sql]# mysql -uroot -p
Enter password: 

mysql> use soe
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from warehouse;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)