删库不再跑路之-Mysql闪回实战演练

最新推荐文章于 2024-11-16 18:48:53 发布

西原一点红

最新推荐文章于 2024-11-16 18:48:53 发布

阅读量724

点赞数 1

分类专栏：运维手册文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_47055136/article/details/131173283

版权

运维手册专栏收录该内容

10 篇文章

订阅专栏

文章目录

删库不再跑路之-Mysql闪回实战演练

删库不再跑路之-Mysql闪回实战演练

一、环境及工具说明

数据库版本：Mysql 5.7.41
回滚工具：binlog2sql
库：yjyl
表：shanhui_test

yjyl库shanhui_test表原有数据如下：

mysql> select * from shanhui_test;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | 1    |    1 |   1.00 |
|  2 | 2    |    2 |   2.00 |
|  3 | 3    |    3 |   3.00 |
|  4 | 4    |    4 |   4.00 |
|  5 | 5    |    5 |   5.00 |
|  6 | 6    |    6 |   6.00 |
|  7 | 7    |    7 |   7.00 |
|  8 | 8    |    8 |   8.00 |
|  9 | 9    |    9 |   9.00 |
| 10 | 10   |   10 |  10.00 |
| 11 | 11   |   11 |  11.00 |
| 12 | 12   |   12 |  12.00 |
| 13 | 13   |   13 |  13.00 |
| 14 | 14   |   14 |  14.00 |
| 15 | 15   |   15 |  15.00 |
| 16 | 16   |   16 |  16.00 |
| 17 | 17   |   17 |  17.00 |
| 18 | 18   |   18 |  18.00 |
| 19 | 19   |   19 |  19.00 |
| 20 | 20   |   20 |  20.00 |
| 21 | 21   |   21 |  21.00 |
| 22 | 22   |   22 |  22.00 |
| 23 | 23   |   23 |  23.00 |
| 24 | 24   |   24 |  24.00 |
| 25 | 25   |   25 |  25.00 |
| 26 | 26   |   26 |  26.00 |
| 27 | 27   |   27 |  27.00 |
| 28 | 28   |   28 |  28.00 |
| 29 | 29   |   29 |  29.00 |
| 30 | 30   |   30 |  30.00 |
| 99 | 99   |   99 |  99.00 |
+----+------+------+--------+
31 rows in set (0.00 sec)

背景：张三在16:35左右，误操作删除了shanhui_test表大批量数据,与此同时，正常业务数据也在继续写入

张三执行的sql

delete from shanhui_test where age<99;

正常业务数据sql

insert into  shanhui_test(id,name,age,salary) values('999','999','999','999')
insert into  shanhui_test(id,name,age,salary) values('9999','9999','9999','9999')

现数据如下

mysql> select * from shanhui_test;
+------+------+------+---------+
| id   | name | age  | salary  |
+------+------+------+---------+
|   99 | 99   |   99 |   99.00 |
|  999 | 999  |  999 |  999.00 |
| 9999 | 9999 | 9999 | 9999.00 |
+------+------+------+---------+
3 rows in set (0.00 sec)

二、闪回

2.1 登录mysql，查看目前的binlog文件

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |      4719 |
| mysql-bin.000004 |     12074 |
+------------------+-----------+
4 rows in set (0.01 sec)

得到最新的binlog文件为mysql-bin.000004

2.2 根据误操作大致时间过滤出误操作sql

python binlog2sql/binlog2sql.py -h127.0.0.1 -P5206 -uroot -p -dyjyl -tshanhui_test --start-file='mysql-bin.000004' --start-datetime='2023-06-12 16:35:00' --stop-datetime='2023-06-12 16:50:00'

得到信息如下：

DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=1.00 AND `age`=1 AND `id`=1 AND `name`='1' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=2.00 AND `age`=2 AND `id`=2 AND `name`='2' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=3.00 AND `age`=3 AND `id`=3 AND `name`='3' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=4.00 AND `age`=4 AND `id`=4 AND `name`='4' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=5.00 AND `age`=5 AND `id`=5 AND `name`='5' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=6.00 AND `age`=6 AND `id`=6 AND `name`='6' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=7.00 AND `age`=7 AND `id`=7 AND `name`='7' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=8.00 AND `age`=8 AND `id`=8 AND `name`='8' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=9.00 AND `age`=9 AND `id`=9 AND `name`='9' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=10.00 AND `age`=10 AND `id`=10 AND `name`='10' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=11.00 AND `age`=11 AND `id`=11 AND `name`='11' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=12.00 AND `age`=12 AND `id`=12 AND `name`='12' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=13.00 AND `age`=13 AND `id`=13 AND `name`='13' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=14.00 AND `age`=14 AND `id`=14 AND `name`='14' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=15.00 AND `age`=15 AND `id`=15 AND `name`='15' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=16.00 AND `age`=16 AND `id`=16 AND `name`='16' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=17.00 AND `age`=17 AND `id`=17 AND `name`='17' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=18.00 AND `age`=18 AND `id`=18 AND `name`='18' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=19.00 AND `age`=19 AND `id`=19 AND `name`='19' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=20.00 AND `age`=20 AND `id`=20 AND `name`='20' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=21.00 AND `age`=21 AND `id`=21 AND `name`='21' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=22.00 AND `age`=22 AND `id`=22 AND `name`='22' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=23.00 AND `age`=23 AND `id`=23 AND `name`='23' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=24.00 AND `age`=24 AND `id`=24 AND `name`='24' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=25.00 AND `age`=25 AND `id`=25 AND `name`='25' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=26.00 AND `age`=26 AND `id`=26 AND `name`='26' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=27.00 AND `age`=27 AND `id`=27 AND `name`='27' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=28.00 AND `age`=28 AND `id`=28 AND `name`='28' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=29.00 AND `age`=29 AND `id`=29 AND `name`='29' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
DELETE FROM `yjyl`.`shanhui_test` WHERE `salary`=30.00 AND `age`=30 AND `id`=30 AND `name`='30' LIMIT 1; #start 10726 end 11466 time 2023-06-12 16:37:17
INSERT INTO `yjyl`.`shanhui_test`(`salary`, `age`, `id`, `name`) VALUES (999.00, 999, 999, '999'); #start 11497 end 11754 time 2023-06-12 16:37:28
INSERT INTO `yjyl`.`shanhui_test`(`salary`, `age`, `id`, `name`) VALUES (9999.00, 9999, 9999, '9999'); #start 11785 end 12043 time 2023-06-12 16:37:34

根据位置信息，我们确定了误操作sql来自同一个事务,准确位置在（start 10726 end 11466）之间

2.3 生成回滚sql

python binlog2sql/binlog2sql.py -h127.0.0.1 -P5206 -uroot -p'Qwe@1234' -dyjyl -tshanhui_test --start-file='mysql-bin.000004' --start-position=10726 --stop-position=11466 -B  >shanhui_new.sql

2.4 执行回滚语句

mysql -uroot -p <shanhui_new.sql

2.5 验证

mysql> select * from shanhui_test;
+------+------+------+---------+
| id   | name | age  | salary  |
+------+------+------+---------+
|    1 | 1    |    1 |    1.00 |
|    2 | 2    |    2 |    2.00 |
|    3 | 3    |    3 |    3.00 |
|    4 | 4    |    4 |    4.00 |
|    5 | 5    |    5 |    5.00 |
|    6 | 6    |    6 |    6.00 |
|    7 | 7    |    7 |    7.00 |
|    8 | 8    |    8 |    8.00 |
|    9 | 9    |    9 |    9.00 |
|   10 | 10   |   10 |   10.00 |
|   11 | 11   |   11 |   11.00 |
|   12 | 12   |   12 |   12.00 |
|   13 | 13   |   13 |   13.00 |
|   14 | 14   |   14 |   14.00 |
|   15 | 15   |   15 |   15.00 |
|   16 | 16   |   16 |   16.00 |
|   17 | 17   |   17 |   17.00 |
|   18 | 18   |   18 |   18.00 |
|   19 | 19   |   19 |   19.00 |
|   20 | 20   |   20 |   20.00 |
|   21 | 21   |   21 |   21.00 |
|   22 | 22   |   22 |   22.00 |
|   23 | 23   |   23 |   23.00 |
|   24 | 24   |   24 |   24.00 |
|   25 | 25   |   25 |   25.00 |
|   26 | 26   |   26 |   26.00 |
|   27 | 27   |   27 |   27.00 |
|   28 | 28   |   28 |   28.00 |
|   29 | 29   |   29 |   29.00 |
|   30 | 30   |   30 |   30.00 |
|   99 | 99   |   99 |   99.00 |
|  999 | 999  |  999 |  999.00 |
| 9999 | 9999 | 9999 | 9999.00 |
+------+------+------+---------+
33 rows in set (0.01 sec)

附：binlog2sql简介

binlog2sql 是大众点评开源的一款用于解析 binlog 的工具，可以用于生成闪回语句，项目地址 binlog2sql（https://sourl.cn/ZuNJPN）

安装

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

生成回滚sql

python binlog2sql/binlog2sql.py -B \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name>\
--start-file='<binlog_file>' \
--start-datetime='<start_time>' \
--stop-datetime='<stop_time>' > ./flashback.sql

python binlog2sql/binlog2sql.py -B \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \
--start-file='<binlog_file>' \
--start-position=<start_pos> \
--stop-position=<stop_pos> > ./flashback.sql