文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >专栏 >MySQL利用binlog恢复误操作数据（Python脚本）

MySQL利用binlog恢复误操作数据（Python脚本）

作者头像
SEian.G
发布于 2021-03-03 10:06:06
发布于 2021-03-03 10:06:06
1.3K0
举报
文章被收录于专栏：SEian.G学习记录SEian.G学习记录
在人工手动进行一些数据库写操作的时候（比方说数据订正），尤其是一些不可控的批量更新或删除，通常都建议备份后操作。不过不怕万一，就怕一万，有备无患总是好的。在线上或者测试环境误操作导致数据被删除或者更新后，想要恢复，一般有两种方法。

方法一、利用最近的全量备份+增量binlog备份，恢复到误操作之前的状态，但是随着数据量的增大，binlog的增多，恢复起来很费时。

方法二、如果binlog的格式为row，那么就可以将binlog解析出来生成反向的原始SQL

以下是利用方法二写的一个python脚本binlog_rollback.py，可利用此脚本生成反向的原始SQL。

说明： 0、前提是binlog的格式为row 1、要恢复的表操作前后表结构没有发生变更，否则脚本无法解析 2、只生成DML(insert/update/delete)的rollback语句 3、最终生成的SQL是逆序的，所以最新的DML会生成在输入文件的最前面，并且带上了时间戳和偏移点，方便查找目标 4、需要提供一个连接MySQL的只读用户，主要是为了获取表结构 5、如果binlog过大，建议带上时间范围，也可以指定只恢复某个库的SQL 6、SQL生成后，请务必在测试环境上测试恢复后再应用到线上

（备注：获取实验中的Python脚本，请关注公众号（DBA的辛酸事儿）或扫描文末二维码，后台恢复“binlog_rollback”获取完整Python脚本）

实验演示

1、创建测试表

代码语言：javascript
复制
root@localhost [wjq]>CREATE TABLE `pytest1` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `c1` int(11) DEFAULT NULL,
    -> `c2` varchar(20) DEFAULT NULL,
    -> `c3` int(11) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.07 sec)

2、插入数据

代码语言：javascript
复制
root@localhost [wjq]>insert into pytest1 values(null,1,'wjq',3),(null,2,'seiang',4),(null,3,'pytest',5);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

3、更新数据

代码语言：javascript
复制
root@localhost [wjq]>update pytest1 set c2='qcloud' where id=1;    
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
root@localhost [wjq]>insert into pytest1 values(null,4,'dba',6);
Query OK, 1 row affected (0.02 sec)

4、删除数据

代码语言：javascript
复制
root@localhost [wjq]>delete from pytest1 where id=4;
Query OK, 1 row affected (0.00 sec)

5、查看当前binlog和pos点

代码语言：javascript
复制
root@localhost [wjq]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql3306.000006 |     1652 |              |                  | c9eeb85d-de5b-11e8-80d4-525400cf9369:1-101154 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 row in set (0.00 sec)

6、查看binlog的内容

代码语言：javascript
复制
[root@VM_54_118_centos log]# mysqlbinlog --base64-output=decode-rows -v -v --stop-position=1652 /data/mysql/mysql_3306/log/mysql3306.000006         
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190320 16:35:41 server id 3306  end_log_pos 123 CRC32 0xff98687c       Start: binlog v 4, server v 5.7.19-log created 190320 16:35:41
# Warning: this binlog is either in use or was not closed properly.
# at 123
#190320 16:35:41 server id 3306  end_log_pos 194 CRC32 0x2b47a7a4       Previous-GTIDs
# c9eeb85d-de5b-11e8-80d4-525400cf9369:1-101149
# at 194
#190320 16:37:33 server id 3306  end_log_pos 259 CRC32 0xb9c3bdd6       GTID    last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'c9eeb85d-de5b-11e8-80d4-525400cf9369:101150'/*!*/;
# at 259
#190320 16:37:33 server id 3306  end_log_pos 499 CRC32 0x2d333afe       Query   thread_id=30889 exec_time=0     error_code=0
use `wjq`/*!*/;
SET TIMESTAMP=1553071053/*!*/;
SET @@session.pseudo_thread_id=30889/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `pytest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)
/*!*/;
# at 499
#190320 16:38:47 server id 3306  end_log_pos 564 CRC32 0x0aa714d3       GTID    last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'c9eeb85d-de5b-11e8-80d4-525400cf9369:101151'/*!*/;
# at 564
#190320 16:38:47 server id 3306  end_log_pos 635 CRC32 0x051cdec4       Query   thread_id=30889 exec_time=0     error_code=0
SET TIMESTAMP=1553071127/*!*/;
BEGIN
/*!*/;
# at 635
#190320 16:38:47 server id 3306  end_log_pos 689 CRC32 0x724c02f6       Table_map: `wjq`.`pytest1` mapped to number 293
# at 689
#190320 16:38:47 server id 3306  end_log_pos 781 CRC32 0xb9e298c6       Write_rows: table id 293 flags: STMT_END_F
### INSERT INTO `wjq`.`pytest1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='wjq' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @4=3 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `wjq`.`pytest1`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
###   @3='seiang' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @4=4 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `wjq`.`pytest1`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3='pytest' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @4=5 /* INT meta=0 nullable=1 is_null=0 */
# at 781
#190320 16:38:47 server id 3306  end_log_pos 812 CRC32 0xb72f1ce4       Xid = 480076
COMMIT/*!*/;
# at 812
#190320 16:39:44 server id 3306  end_log_pos 877 CRC32 0x852c27ee       GTID    last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'c9eeb85d-de5b-11e8-80d4-525400cf9369:101152'/*!*/;
# at 877
#190320 16:39:44 server id 3306  end_log_pos 948 CRC32 0x5416d14a       Query   thread_id=30889 exec_time=0     error_code=0
SET TIMESTAMP=1553071184/*!*/;
BEGIN
/*!*/;
# at 948
#190320 16:39:44 server id 3306  end_log_pos 1002 CRC32 0xc280e454      Table_map: `wjq`.`pytest1` mapped to number 293
# at 1002
#190320 16:39:44 server id 3306  end_log_pos 1075 CRC32 0x7fd8e386      Update_rows: table id 293 flags: STMT_END_F
### UPDATE `wjq`.`pytest1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='wjq' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @4=3 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='qcloud' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @4=3 /* INT meta=0 nullable=1 is_null=0 */
# at 1075
#190320 16:39:44 server id 3306  end_log_pos 1106 CRC32 0xb14d6809      Xid = 480100
COMMIT/*!*/;
# at 1106
#190320 16:55:47 server id 3306  end_log_pos 1171 CRC32 0x88ee41dd      GTID    last_committed=3        sequence_number=4       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'c9eeb85d-de5b-11e8-80d4-525400cf9369:101153'/*!*/;
# at 1171
#190320 16:55:47 server id 3306  end_log_pos 1242 CRC32 0xa09af5cb      Query   thread_id=30889 exec_time=0     error_code=0
SET TIMESTAMP=1553072147/*!*/;
BEGIN
/*!*/;
# at 1242
#190320 16:55:47 server id 3306  end_log_pos 1296 CRC32 0x64e00afd      Table_map: `wjq`.`pytest1` mapped to number 293
# at 1296
#190320 16:55:47 server id 3306  end_log_pos 1348 CRC32 0xe8a8378a      Write_rows: table id 293 flags: STMT_END_F
### INSERT INTO `wjq`.`pytest1`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3='dba' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @4=6 /* INT meta=0 nullable=1 is_null=0 */
# at 1348
#190320 16:55:47 server id 3306  end_log_pos 1379 CRC32 0xaaee67f3      Xid = 480487
COMMIT/*!*/;
# at 1379
#190320 16:56:24 server id 3306  end_log_pos 1444 CRC32 0x84b6e040      GTID    last_committed=4        sequence_number=5       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'c9eeb85d-de5b-11e8-80d4-525400cf9369:101154'/*!*/;
# at 1444
#190320 16:56:24 server id 3306  end_log_pos 1515 CRC32 0xa5a58ba8      Query   thread_id=30889 exec_time=0     error_code=0
SET TIMESTAMP=1553072184/*!*/;
BEGIN
/*!*/;
# at 1515
#190320 16:56:24 server id 3306  end_log_pos 1569 CRC32 0xd761113d      Table_map: `wjq`.`pytest1` mapped to number 293
# at 1569
#190320 16:56:24 server id 3306  end_log_pos 1621 CRC32 0x521f876b      Delete_rows: table id 293 flags: STMT_END_F
### DELETE FROM `wjq`.`pytest1`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3='dba' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @4=6 /* INT meta=0 nullable=1 is_null=0 */
# at 1621
#190320 16:56:24 server id 3306  end_log_pos 1652 CRC32 0x69b501c7      Xid = 480502
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

7、利用脚本来生成反向SQL

代码语言：javascript
复制
[root@VM_54_118_centos scripts]# python binlog_rollback.py -f /data/mysql/mysql_3306/log/mysql3306.000006  -o /tmp/rollback.sql -h 118.89.107.162 -u root -P 3306 -p qcloud@2018 --stop-position='1652' -d wjq
正在获取参数.....
正在解析binlog.....
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
正在初始化列名.....
正在开始拼凑sql.....
done!
 
[root@VM_54_118_centos scripts]# ll /tmp/rollback.sql 
-rw-r--r-- 1 root root 998 Mar 20 17:15 /tmp/rollback.sql

8、查看反向SQL，最新的DML会生成在输入文件的最前面

代码语言：javascript
复制
[root@VM_54_118_centos scripts]# more /tmp/rollback.sql 
## at 1569
##190320 16:56:24 server id 3306  end_log_pos 1621 CRC32 0x521f876b     Delete_rows: table id 293 flags: STMT_END_F
INSERT INTO `wjq`.`pytest1`
SET
  id=4
  ,c1=4
  ,c2='dba'
  ,c3=6;
## at 1296
##190320 16:55:47 server id 3306  end_log_pos 1348 CRC32 0xe8a8378a     Write_rows: table id 293 flags: STMT_END_F
DELETE FROM `wjq`.`pytest1`
WHERE
  id=4
  AND c1=4
  AND c2='dba'
  AND c3=6;
## at 1002
##190320 16:39:44 server id 3306  end_log_pos 1075 CRC32 0x7fd8e386     Update_rows: table id 293 flags: STMT_END_F
UPDATE `wjq`.`pytest1`
SET
  id=1
  ,c1=1
  ,c2='wjq'
  ,c3=3
WHERE
  id=1
  AND c1=1
  AND c2='qcloud'
  AND c3=3;
## at 689
##190320 16:38:47 server id 3306  end_log_pos 781 CRC32 0xb9e298c6      Write_rows: table id 293 flags: STMT_END_F
DELETE FROM `wjq`.`pytest1`
WHERE
  id=3
  AND c1=3
  AND c2='pytest'
  AND c3=5;
DELETE FROM `wjq`.`pytest1`
WHERE
  id=2
  AND c1=2
  AND c2='seiang'
  AND c3=4;
DELETE FROM `wjq`.`pytest1`
WHERE
  id=1
  AND c1=1
  AND c2='wjq'
  AND c3=3;

9、执行回滚操作

代码语言：javascript
复制
[root@VM_54_118_centos ~]# mysql -hlocalhost -uroot -pqcloud@2018 -S /tmp/mysql3306.sock < /tmp/rollback.sql

具体的参数使用方法如下：

代码语言：javascript
复制
[root@VM_54_118_centos scripts]# python binlog_rollback.py 
==========================================================================================
Command line options :
    --help                  # OUT : print help info
    -f, --binlog            # IN  : binlog file. (required)
    -o, --outfile           # OUT : output rollback sql file. (default 'rollback.sql')
    -h, --host              # IN  : host. (default '127.0.0.1')
    -u, --user              # IN  : user. (required)
    -p, --password          # IN  : password. (required)
    -P, --port              # IN  : port. (default 3306)
    --start-datetime        # IN  : start datetime. (default '1970-01-01 00:00:00')
    --stop-datetime         # IN  : stop datetime. default '2070-01-01 00:00:00'
    --start-position        # IN  : start position. (default '4')
    --stop-position         # IN  : stop position. (default '18446744073709551615')
    -d, --database          # IN  : List entries for just this database (No default value).
    --only-primary          # IN  : Only list primary key in where condition (default 0)
 
Sample :
   shell> python binlog_rollback.py -f 'mysql-bin.000001' -o '/tmp/rollback.sql' -h 192.168.0.1 -u 'user' -p 'pwd' -P 3307 -d dbname
==========================================================================================

文章连接：https://www.cnblogs.com/prayer21/p/6018736.html

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-04-02，如有侵权请联系 cloudcommunity@tencent.com 删除
sql
python
腾讯云测试服务

本文分享自 DBA的辛酸事儿 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

sql
python
腾讯云测试服务
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2025 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论