使用binlog2sql闪回误操作DML语句

we6100

于 2022-09-24 11:24:16 发布

阅读量103

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/we6100/article/details/127023028

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

原文：https://www.yuque.com/wei01/wql35u/xv2xhv

美团的python闪回工具 只支持DML，不能回滚关联表,默认只支持5.6，5.7,可以将mysql-replication包升级到最新，测试也可以支持8.0

binlog_format为ROW模式
show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+


[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服务器上, 可以远程登录, 解析本地服务器上的日志