Mysql binlog2sql数据回滚

最新推荐文章于 2024-02-02 16:40:07 发布

原创最新推荐文章于 2024-02-02 16:40:07 发布 · 607 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#binlog2数据回滚

mysql 同时被 2 个专栏收录

12 篇文章

订阅专栏

binlog2数据回滚

1 篇文章

订阅专栏

本文详细介绍了如何使用binlog2sql工具进行MySQL数据回滚。在误删除f表数据后，通过查看binlog定位到误操作SQL，生成并执行回滚SQL，最终成功恢复数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、首先我们安装binlog2sql：

yum -y install epel-release

yum -y install git python-pip

shell> git clonehttps://github.com/danfengcao/binlog2sql.git && cd binlog2sql

shell> pip install -r requirements.txt

--default-timeout=1000

原有数据

mysql> select * from f;

+-----+-----+---------------------+

| uid | did |updateTime |

+-----+-----+---------------------+

| 1 | 18 | 2016-12-06 12:28:18 |

| 2 | 19 | 2016-12-06 12:55:56 |

| 3 | 20 | 2016-12-07 14:00:58 |

| 4 | 21 | 2016-12-07 14:01:00 |

+-----+-----+---------------------+

误操作mysql> delete from f;

Query OK, 4 rows affected (0.00 sec)

f表被清空

mysql> select * from f;

Empty set(0.00 sec)

2、回滚步骤：

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

mysql> show master logs;

+------------------+-----------+

| Log_name |File_size |

+------------------+-----------+

| mysql-bin.000001 | 12262268 |

| mysql-bin.000002 | 132776 |

最新的binlog文件是mysql-bin.000002，我们再定位误操作SQL的binlog位置

$ pythonbinlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f--start-file='mysql-bin.000002'

输出：

DELETE FROM `test`.`f` WHERE `did`=18 AND`updateTime`='2016-12-06 12:28:18' AND `uid`=1 LIMIT 1; #start 4 end 314

DELETE FROM `test`.`f` WHERE `did`=19 AND`updateTime`='2016-12-06 12:55:56' AND `uid`=2 LIMIT 1; #start 4 end 314

DELETE FROM `test`.`f` WHERE `did`=20 AND`updateTime`='2016-12-07 14:00:58' AND `uid`=3 LIMIT 1; #start 4 end 314

DELETE FROM`test`.`f` WHERE `did`=21 AND `updateTime`='2016-12-07 14:01:00' AND `uid`=4LIMIT 1; #start 4 end 314

3、生成回滚sql，并检查回滚sql是否正确

$ pythonbinlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f--start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B

输出：

INSERT INTO `test`.`f`(`did`, `updateTime`,`uid`) VALUES (21, '2016-12-07 14:01:00', 4); #start 4 end 314

INSERT INTO `test`.`f`(`did`, `updateTime`,`uid`) VALUES (20, '2016-12-07 14:00:58', 3); #start 4 end 314

INSERT INTO`test`.`f`(`did`, `updateTime`, `uid`) VALUES (19, '2016-12-06 12:55:56', 2);#start 4 end 314

4、确认回滚sql正确，执行回滚语句。登录mysql，数据回滚成功。

pythonbinlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f--start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B | mysql-h127.0.0.1 -P3306 -uadmin -p'admin'

mysql> select * from f;

+-----+-----+---------------------+

| uid | did |updateTime |

+-----+-----+---------------------+

| 1 | 18 | 2016-12-06 12:28:18 |

| 2 | 19 | 2016-12-06 12:55:56 |

| 3 | 20 | 2016-12-07 14:00:58 |

| 4 | 21 | 2016-12-07 14:01:00 |

5、至此，回滚成功！