在人工手动进行一些数据库写操作的时候（比方说数据订正），尤其是一些不可控的批量更新或删除，通常都建议备份后操作。不过不怕万一，就怕一万，有备无患总是好的。在线上或者测试环境误操作导致数据被删除或者更新后，想要恢复，一般有两种方法。
方法一、利用最近的全量备份+增量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生成后，请务必在测试环境上测试恢复后再应用到线上
实验演示
1、创建测试表
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、插入数据
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、更新数据
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、删除数据
root@localhost [wjq]>delete from pytest1 where id=4; Query OK, 1 row affected (0.00 sec)
5、查看当前binlog和pos点
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的内容
[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
[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会生成在输入文件的最前面
[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、执行回滚操作
[root@VM_54_118_centos ~]# mysql -hlocalhost -uroot -pqcloud@2018 -S /tmp/mysql3306.sock < /tmp/rollback.sql
具体的参数使用方法如下：
[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
（备注：获取上述实验中的Python脚本，请关注下方公众号（DBA的辛酸事儿），后台恢复“binlog_rollback”获取完整脚本）
如果您觉得本站对你有帮助，那么可以收藏和推荐本站，帮助本站更好地发展，在此谢过各位网友的支持。
转载请注明原文链接：MySQL利用binlog恢复误操作数据（Python脚本）
转载请注明原文链接：MySQL利用binlog恢复误操作数据（Python脚本）
