使用binlog2sql闪回误操作DML语句

美团的python闪回工具 只支持DML，不能回滚关联表
binlog_format为ROW模式
show variables like ‘binlog_format’;
+———————-+———-+
| Variable_name | Value |
+———————-+———-+
| binlog_format | ROW |
+———————-+———-+

这里先手动安装好python3.6(3.7可能有其他问题)

[root@mysql1 binlog2sql]# ll
total 272
drwxr-xr-x 2 root root 4096 Oct 12 2018 binlog2sql
drwxr-xr-x 2 root root 4096 Oct 12 2018 example
-rw-r—r— 1 root root 35141 Oct 12 2018 LICENSE
-rw-r—r— 1 root root 33021 Dec 13 2019 mysql-replication-0.13.tar.gz
-rw-r—r— 1 root root 78875 Dec 13 2019 PyMySQL-0.7.11-py2.py3-none-any.whl
-rw-r—r— 1 root root 9514 Oct 12 2018 README.md
-rw-r—r— 1 root root 54 Oct 12 2018 requirements.txt
drwxr-xr-x 2 root root 4096 Oct 12 2018 tests
-rw-r—r— 1 root root 66878 Dec 13 2019 wheel-0.29.0-py2.py3-none-any.whl

[root@mysql1 binlog2sql]# pip3 install —no-index —find-links=/root/binlog2sql -r requirements.txt
Looking in links: /root/binlog2sql
Collecting PyMySQL==0.7.11 (from -r requirements.txt (line 1))
Collecting wheel==0.29.0 (from -r requirements.txt (line 2))
Collecting mysql-replication==0.13 (from -r requirements.txt (line 3))
Installing collected packages: PyMySQL, wheel, mysql-replication
Found existing installation: PyMySQL 1.0.2
Uninstalling PyMySQL-1.0.2:
Successfully uninstalled PyMySQL-1.0.2
Running setup.py install for mysql-replication … done
Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0

模拟生产误删,同一时刻有多个活动会话
session 1 执行
mysqlslap -a -uroot -proot1234 -c 50 -i 100

session 2 误删除9998行记录
select count() from employees;
+—————+
| count() |
+—————+
| 300024 |
+—————+
1 row in set (0.14 sec)

delete from employees where emp_no>10001 and emp_no<20000;
Query OK, 9998 rows affected (4.07 sec)

导出binlog
python3 /root/binlog2sql/binlog2sql/binlog2sql.py -h 192.168.247.50 -u root -p root1234 -d employees -t employees —start-file mysql-bin.000017 —start-datetime ‘2021-07-08 12:22:00’> /tmp/raw.sql

找到误删除的start pos (同一条语句的start pos是一样的), 然后根据start pos找到最后的end pos
USE b’mysqlslap’;
DROP SCHEMA IF EXISTS mysqlslap;
USE b’mysqlslap’;
CREATE SCHEMA mysqlslap;
USE b’mysqlslap’;
CREATE TABLE t1 (intcol1 INT(32) ,charcol1 VARCHAR(128))^@;
DELETE FROM employees.employees WHERE emp_no=10002 AND birth_date=’1964-06-02’ AND first_name=’Bezalel’ AND last_name=’Simmel’ AND gender=’F’ AND hire_date=’1985-11-21’ LIMIT 1; #start 40459806 end 40468244 time 2021-07-08 12:22:52
DELETE FROM employees.employees WHERE emp_no=10003 AND birth_date=’1959-12-03’ AND first_name=’Parto’ AND last_name=’Bamford’ AND gender=’M’ AND hire_date=’1986-08-28’ LIMIT 1; #start 40459806 end 40468244 time 2021-07-08 12:22:52
DELETE FROM employees.employees WHERE emp_no=10004 AND birth_date=’1954-05-01’ AND first_name=’Chirstian’ AND last_name=’Koblick’ AND gender=’M’ AND hire_date=’1986-12-01’ LIMIT 1; #start 40459806 end 40468244 time 2021-07-08 12:22:52
DELETE FROM employees.employees WHERE emp_no=10005 AND birth_date=’1955-01-21’ AND first_name=’Kyoichi’ AND last_name=’Maliniak’ AND gender=’M’ AND hire_date=’1989-09-12’ LIMIT 1; #start 40459806 end 40468244 time 2021-07-08 12:22:52
….
DELETE FROM employees.employees WHERE emp_no=19995 AND birth_date=’1962-12-31’ AND first_name=’Ziyad’ AND last_name=’Schueller’ AND gender=’F’ AND hire_date=’1990-08-25’ LIMIT 1; #start 40459806 end 40735255 time 2021-07-08 12:22:52
DELETE FROM employees.employees WHERE emp_no=19996 AND birth_date=’1954-03-07’ AND first_name=’Berni’ AND last_name=’Chinen’ AND gender=’M’ AND hire_date=’1985-08-17’ LIMIT 1; #start 40459806 end 40735255 time 2021-07-08 12:22:52
DELETE FROM employees.employees WHERE emp_no=19997 AND birth_date=’1957-12-06’ AND first_name=’Otilia’ AND last_name=’Zumaque’ AND gender=’M’ AND hire_date=’1986-02-28’ LIMIT 1; #start 40459806 end 40735255 time 2021-07-08 12:22:52
DELETE FROM employees.employees WHERE emp_no=19998 AND birth_date=’1959-07-31’ AND first_name=’Fuqing’ AND last_name=’Maksimenko’ AND gender=’M’ AND hire_date=’1990-05-02’ LIMIT 1; #start 40459806 end 40735255 time 2021-07-08 12:22:52
DELETE FROM employees.employees WHERE emp_no=19999 AND birth_date=’1953-10-16’ AND first_name=’Jahangir’ AND last_name=’Speer’ AND gender=’F’ AND hire_date=’1989-09-29’ LIMIT 1; #start 40459806 end 40735255 time 2021-07-08 12:22:52
USE b’mysqlslap’;
DROP SCHEMA IF EXISTS mysqlslap;
USE b’mysqlslap’;
CREATE SCHEMA mysqlslap;
USE b’mysqlslap’;

生成回滚语句
python3 /root/binlog2sql/binlog2sql/binlog2sql.py -h 192.168.247.50 -u root -p root1234 -d employees -t employees —start-file mysql-bin.000017 —start-file mysql-bin.000017 —start-datetime ‘2021-07-08 12:00:00’ —start-position=40459806 —stop-position=40735255 -B > /tmp/flashback.sql

wc -l /tmp/flashback.sql 查看总条数
注:中间有一些SELECT SLEEP(1.0);会导致不一致, 具体生产环境需要跟业务方确认

导入数据库(可以先恢复到临时表)
mysql -uroot -proot1234</tmp/flashback.sql

数据验证
select count() from employees;
+—————+
| count() |
+—————+
| 300024 |
+—————+

建议: 将binlog2sql放在binlog服务器上, 可以远程登录, 解析本地服务器上的日志

由于语雀不支持上传压缩包,放在csdn上了
下载地址:https://download.csdn.net/download/veblue/20083611