暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / MySQL利用binlog恢复误操作数据（Python脚本）

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

DBA的辛酸事儿 2021-04-07
1264

看了就要关注我，喵呜~


在人工手动进行一些数据库写操作的时候（比方说数据订正），尤其是一些不可控的批量更新或删除，通常都建议备份后操作。不过不怕万一，就怕一万，有备无患总是好的。在线上或者测试环境误操作导致数据被删除或者更新后，想要恢复，一般有两种方法。

方法一、利用最近的全量备份+增量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、创建测试表

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进行审计

MYSQL无备份情况下恢复误删除的user权限表

终于有人把“TCC分布式事务”的实现原理讲明白了

MySQL中基于XA实现的分布式事务


关注公众号，获取更多学习资源

mysqlpython
文章转载自DBA的辛酸事儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论