文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

利用mysqlbinlog_flashback闪回误删除的表

2024-04-02 19:55

关注

本实验模拟把生产库当做测试库,对某张表做了大量DML操作,然后进行闪回的过程。

脚本文件:mysqlbinlog_flashback

此脚本为阿里DBA在mysqlbinlog基础上进行改进来的,网上可以下载到。

一、DML操作

模拟生产库的错误DML操作


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| kk             |
| t1             |
| t2             |
| t3             |
| t4             |
| t5             |
| t6             |
| t7             |
| t8             |
+----------------+
9 rows in set (0.00 sec)
mysql> select * from t8;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | jack |
|    3 | jack |
|    4 | jack |
+------+------+
4 rows in set (0.00 sec)
mysql> insert into t8 values(5,'steven');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t8 values(6,'steven');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t8 values(7,'steven');
Query OK, 1 row affected (0.04 sec)
mysql>  insert  into  t8  values(8,'steven');
Query OK, 1 row affected (0.03 sec)
mysql> update t8 set name='devid' where name='steven';
Query OK, 4 rows affected (0.33 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> delete from t8 where name='jack';
Query OK, 4 rows affected (0.04 sec)

对生产库t8做了大量的DML之后,发现操作错误

二、查看binlog events

查看当前binlog和pos


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |     2195 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看binlog events


mysql> show binlog events in 'mysql-bin.000005';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                             |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------+
| mysql-bin.000005 |    4 | Format_desc    |    330631 |         123 | Server ver: 5.7.23-log, Binlog ver: 4            |
| mysql-bin.000005 |  123 | Previous_gtids |    330631 |         154 |                                                  |
| mysql-bin.000005 |  154 | Anonymous_Gtid |    330631 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000005 |  219 | Query          |    330631 |         291 | BEGIN                                            |
| mysql-bin.000005 |  291 | Rows_query     |    330631 |         348 | # insert into t8 values(5,'steven')              |
| mysql-bin.000005 |  348 | Table_map      |    330631 |         396 | table_id: 116 (test.t8)                          |
| mysql-bin.000005 |  396 | Write_rows     |    330631 |         443 | table_id: 116 flags: STMT_END_F                  |
| mysql-bin.000005 |  443 | Xid            |    330631 |         474 | COMMIT                               |
| mysql-bin.000005 |  474 | Anonymous_Gtid |    330631 |         539 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000005 |  539 | Query          |    330631 |         611 | BEGIN                                            |
| mysql-bin.000005 |  611 | Rows_query     |    330631 |         668 | # insert into t8 values(6,'steven')              |
| mysql-bin.000005 |  668 | Table_map      |    330631 |         716 | table_id: 116 (test.t8)                          |
| mysql-bin.000005 |  716 | Write_rows     |    330631 |         763 | table_id: 116 flags: STMT_END_F                  |
| mysql-bin.000005 |  763 | Xid            |    330631 |         794 | COMMIT                               |
| mysql-bin.000005 |  794 | Anonymous_Gtid |    330631 |         859 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000005 |  859 | Query          |    330631 |         931 | BEGIN                                            |
| mysql-bin.000005 |  931 | Rows_query     |    330631 |         988 | # insert into t8 values(7,'steven')              |
| mysql-bin.000005 |  988 | Table_map      |    330631 |        1036 | table_id: 116 (test.t8)                          |
| mysql-bin.000005 | 1036 | Write_rows     |    330631 |        1083 | table_id: 116 flags: STMT_END_F                  |
| mysql-bin.000005 | 1083 | Xid            |    330631 |        1114 | COMMIT                               |
| mysql-bin.000005 | 1114 | Anonymous_Gtid |    330631 |        1179 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000005 | 1179 | Query          |    330631 |        1251 | BEGIN                                            |
| mysql-bin.000005 | 1251 | Rows_query     |    330631 |        1308 | # insert into t8 values(8,'steven')              |
| mysql-bin.000005 | 1308 | Table_map      |    330631 |        1356 | table_id: 116 (test.t8)                          |
| mysql-bin.000005 | 1356 | Write_rows     |    330631 |        1403 | table_id: 116 flags: STMT_END_F                  |
| mysql-bin.000005 | 1403 | Xid            |    330631 |        1434 | COMMIT                               |
| mysql-bin.000005 | 1434 | Anonymous_Gtid |    330631 |        1499 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000005 | 1499 | Query          |    330631 |        1571 | BEGIN                                            |
| mysql-bin.000005 | 1571 | Rows_query     |    330631 |        1641 | # update t8 set name='devid' where name='steven' |
| mysql-bin.000005 | 1641 | Table_map      |    330631 |        1689 | table_id: 116 (test.t8)                          |
| mysql-bin.000005 | 1689 | Update_rows    |    330631 |        1817 | table_id: 116 flags: STMT_END_F                  |
| mysql-bin.000005 | 1817 | Xid            |    330631 |        1848 | COMMIT                               |
| mysql-bin.000005 | 1848 | Anonymous_Gtid |    330631 |        1913 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000005 | 1913 | Query          |    330631 |        1985 | BEGIN                                            |
| mysql-bin.000005 | 1985 | Rows_query     |    330631 |        2041 | # delete from t8 where name='jack'               |
| mysql-bin.000005 | 2041 | Table_map      |    330631 |        2089 | table_id: 116 (test.t8)                          |
| mysql-bin.000005 | 2089 | Delete_rows    |    330631 |        2164 | table_id: 116 flags: STMT_END_F                  |
| mysql-bin.000005 | 2164 | Xid            |    330631 |        2195 | COMMIT                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------+

可以看到,所做的操作为291-2195之间的SQL,binlog文件为mysql-bin.000005

三、查看并反向解析binlog

查看binlog


[root@node1 data]# mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -v -v --start-position=291 --stop-position=2195 mysql-bin.000005
;
;
;
DELIMITER ;
# at 291
#181127 17:38:25 server id 330631  end_log_pos 348 CRC32 0x1a594faa 	Rows_query
# insert into t8 values(5,'steven')
# at 348
#181127 17:38:25 server id 330631  end_log_pos 396 CRC32 0xa2c0344b 	Table_map: `test`.`t8` mapped to number 116
# at 396
#181127 17:38:25 server id 330631  end_log_pos 443 CRC32 0xd5578b59 	Write_rows: table id 116 flags: STMT_END_F
### INSERT INTO `test`.`t8`
### SET
###   @1=5 
###   @2='steven' 
# at 443
#181127 17:38:25 server id 330631  end_log_pos 474 CRC32 0x8bda2ab2 	Xid = 29
COMMIT;
# at 474
#181127 17:38:29 server id 330631  end_log_pos 539 CRC32 0xb81e9c26 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
# at 539
#181127 17:38:29 server id 330631  end_log_pos 611 CRC32 0xa70c1ff5 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1543311509;
SET @@session.pseudo_thread_id=2;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=1436549152;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
BEGIN
;
# at 611
#181127 17:38:29 server id 330631  end_log_pos 668 CRC32 0xdfb1f79b 	Rows_query
# insert into t8 values(6,'steven')
# at 668
#181127 17:38:29 server id 330631  end_log_pos 716 CRC32 0x41d9c819 	Table_map: `test`.`t8` mapped to number 116
# at 716
#181127 17:38:29 server id 330631  end_log_pos 763 CRC32 0x3642add0 	Write_rows: table id 116 flags: STMT_END_F
### INSERT INTO `test`.`t8`
### SET
###   @1=6 
###   @2='steven' 
# at 763
#181127 17:38:29 server id 330631  end_log_pos 794 CRC32 0xc682eb4c 	Xid = 30
COMMIT;
# at 794
#181127 17:38:33 server id 330631  end_log_pos 859 CRC32 0x2c4b77a1 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
# at 859
#181127 17:38:33 server id 330631  end_log_pos 931 CRC32 0x9b23208e 	Ignorable	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1543311513;
BEGIN
;
# at 931
#181127 17:38:33 server id 330631  end_log_pos 988 CRC32 0x55fc6947 	Rows_query
# insert into t8 values(7,'steven')
# at 988
#181127 17:38:33 server id 330631  end_log_pos 1036 CRC32 0xfb23efb6 	Table_map: `test`.`t8` mapped to number 116
# at 1036
#181127 17:38:33 server id 330631  end_log_pos 1083 CRC32 0xddddd8e4 	Write_rows: table id 116 flags: STMT_END_F
### INSERT INTO `test`.`t8`
### SET
###   @1=7 
###   @2='steven' 
# at 1083
#181127 17:38:33 server id 330631  end_log_pos 1114 CRC32 0x9521a7b4 	Xid = 31
COMMIT;
# at 1114
#181127 17:38:37 server id 330631  end_log_pos 1179 CRC32 0xd43aee35 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
# at 1179
#181127 17:38:37 server id 330631  end_log_pos 1251 CRC32 0x3d0df308 	Ignorable	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1543311517;
BEGIN
;
# at 1251
#181127 17:38:37 server id 330631  end_log_pos 1308 CRC32 0x35d260fd 	Rows_query
# insert into t8 values(8,'steven')
# at 1308
#181127 17:38:37 server id 330631  end_log_pos 1356 CRC32 0x60fbd5f4 	Table_map: `test`.`t8` mapped to number 116
# at 1356
#181127 17:38:37 server id 330631  end_log_pos 1403 CRC32 0xf2dddee2 	Write_rows: table id 116 flags: STMT_END_F
### INSERT INTO `test`.`t8`
### SET
###   @1=8 
###   @2='steven' 
# at 1403
#181127 17:38:37 server id 330631  end_log_pos 1434 CRC32 0xb7d14cb6 	Xid = 32
COMMIT;
# at 1434
#181127 17:40:07 server id 330631  end_log_pos 1499 CRC32 0x5300534c 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
# at 1499
#181127 17:40:07 server id 330631  end_log_pos 1571 CRC32 0xb4c825b9 	Ignorable	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1543311607;
BEGIN
;
# at 1571
#181127 17:40:07 server id 330631  end_log_pos 1641 CRC32 0xea587e3d 	Rows_query
# update t8 set name='devid' where name='steven'
# at 1641
#181127 17:40:07 server id 330631  end_log_pos 1689 CRC32 0xffe4ea33 	Table_map: `test`.`t8` mapped to number 116
# at 1689
#181127 17:40:07 server id 330631  end_log_pos 1817 CRC32 0x07fc2cec 	Update_rows: table id 116 flags: STMT_END_F
### UPDATE `test`.`t8`
### WHERE
###   @1=5 
###   @2='steven' 
### SET
###   @1=5 
###   @2='devid' 
### UPDATE `test`.`t8`
### WHERE
###   @1=6 
###   @2='steven' 
### SET
###   @1=6 
###   @2='devid' 
### UPDATE `test`.`t8`
### WHERE
###   @1=7 
###   @2='steven' 
### SET
###   @1=7 
###   @2='devid' 
### UPDATE `test`.`t8`
### WHERE
###   @1=8 
###   @2='steven' 
### SET
###   @1=8 
###   @2='devid' 
# at 1817
#181127 17:40:07 server id 330631  end_log_pos 1848 CRC32 0xb58eb743 	Xid = 33
COMMIT;
# at 1848
#181127 17:40:18 server id 330631  end_log_pos 1913 CRC32 0x6a90437e 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
# at 1913
#181127 17:40:18 server id 330631  end_log_pos 1985 CRC32 0xb1f69058 	Ignorable	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1543311618;
BEGIN
;
# at 1985
#181127 17:40:18 server id 330631  end_log_pos 2041 CRC32 0x0c772517 	Rows_query
# delete from t8 where name='jack'
# at 2041
#181127 17:40:18 server id 330631  end_log_pos 2089 CRC32 0xb258a5d1 	Table_map: `test`.`t8` mapped to number 116
# at 2089
#181127 17:40:18 server id 330631  end_log_pos 2164 CRC32 0x85f3157d 	Delete_rows: table id 116 flags: STMT_END_F
### DELETE FROM `test`.`t8`
### WHERE
###   @1=1 
###   @2='jack' 
### DELETE FROM `test`.`t8`
### WHERE
###   @1=2 
###   @2='jack' 
### DELETE FROM `test`.`t8`
### WHERE
###   @1=3 
###   @2='jack' 
### DELETE FROM `test`.`t8`
### WHERE
###   @1=4 
###   @2='jack' 
# at 2164
#181127 17:40:18 server id 330631  end_log_pos 2195 CRC32 0x2b861950 	Xid = 34
COMMIT;
DELIMITER ;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' ;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;

反向解析binlog


[root@node1 data]# mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -B -v -v --start-position=291 --stop-position=2195 mysql-bin.000005
;
;
;
DELIMITER ;
#181127 17:38:25 server id 330631  end_log_pos 348 CRC32 0x1a594faa 	Rows_query
# insert into t8 values(5,'steven')
#181127 17:38:25 server id 330631  end_log_pos 396 CRC32 0xa2c0344b 	Table_map: `test`.`t8` mapped to number 116
#181127 17:38:25 server id 330631  end_log_pos 474 CRC32 0x8bda2ab2 	Xid = 29
COMMIT;
#181127 17:38:29 server id 330631  end_log_pos 539 CRC32 0xb81e9c26 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
#181127 17:38:29 server id 330631  end_log_pos 668 CRC32 0xdfb1f79b 	Rows_query
# insert into t8 values(6,'steven')
#181127 17:38:29 server id 330631  end_log_pos 716 CRC32 0x41d9c819 	Table_map: `test`.`t8` mapped to number 116
#181127 17:38:29 server id 330631  end_log_pos 794 CRC32 0xc682eb4c 	Xid = 30
COMMIT;
#181127 17:38:33 server id 330631  end_log_pos 859 CRC32 0x2c4b77a1 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
#181127 17:38:33 server id 330631  end_log_pos 988 CRC32 0x55fc6947 	Rows_query
# insert into t8 values(7,'steven')
#181127 17:38:33 server id 330631  end_log_pos 1036 CRC32 0xfb23efb6 	Table_map: `test`.`t8` mapped to number 116
#181127 17:38:33 server id 330631  end_log_pos 1114 CRC32 0x9521a7b4 	Xid = 31
COMMIT;
#181127 17:38:37 server id 330631  end_log_pos 1179 CRC32 0xd43aee35 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
#181127 17:38:37 server id 330631  end_log_pos 1308 CRC32 0x35d260fd 	Rows_query
# insert into t8 values(8,'steven')
#181127 17:38:37 server id 330631  end_log_pos 1356 CRC32 0x60fbd5f4 	Table_map: `test`.`t8` mapped to number 116
#181127 17:38:37 server id 330631  end_log_pos 1434 CRC32 0xb7d14cb6 	Xid = 32
COMMIT;
#181127 17:40:07 server id 330631  end_log_pos 1499 CRC32 0x5300534c 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
#181127 17:40:07 server id 330631  end_log_pos 1641 CRC32 0xea587e3d 	Rows_query
# update t8 set name='devid' where name='steven'
#181127 17:40:07 server id 330631  end_log_pos 1689 CRC32 0xffe4ea33 	Table_map: `test`.`t8` mapped to number 116
#181127 17:40:07 server id 330631  end_log_pos 1848 CRC32 0xb58eb743 	Xid = 33
COMMIT;
#181127 17:40:18 server id 330631  end_log_pos 1913 CRC32 0x6a90437e 	GTID [commit=no]
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
#181127 17:40:18 server id 330631  end_log_pos 2041 CRC32 0x0c772517 	Rows_query
# delete from t8 where name='jack'
#181127 17:40:18 server id 330631  end_log_pos 2089 CRC32 0xb258a5d1 	Table_map: `test`.`t8` mapped to number 116
#181127 17:40:18 server id 330631  end_log_pos 2195 CRC32 0x2b861950 	Xid = 34
COMMIT;
#181127 17:40:18 server id 330631  end_log_pos 2164 CRC32 0x85f3157d 	Delete_rows: table id 116 flags: STMT_END_F
### INSERT INTO `test`.`t8`
### SET
###   @1=1 
###   @2='jack' 
### INSERT INTO `test`.`t8`
### SET
###   @1=2 
###   @2='jack' 
### INSERT INTO `test`.`t8`
### SET
###   @1=3 
###   @2='jack' 
### INSERT INTO `test`.`t8`
### SET
###   @1=4 
###   @2='jack' 
#181127 17:40:07 server id 330631  end_log_pos 1817 CRC32 0x07fc2cec 	Update_rows: table id 116 flags: STMT_END_F
### UPDATE `test`.`t8`
### WHERE
###   @1=5 
###   @2='devid' 
### SET
###   @1=5 
###   @2='steven' 
### UPDATE `test`.`t8`
### WHERE
###   @1=6 
###   @2='devid' 
### SET
###   @1=6 
###   @2='steven' 
### UPDATE `test`.`t8`
### WHERE
###   @1=7 
###   @2='devid' 
### SET
###   @1=7 
###   @2='steven' 
### UPDATE `test`.`t8`
### WHERE
###   @1=8 
###   @2='devid' 
### SET
###   @1=8 
###   @2='steven' 
#181127 17:38:37 server id 330631  end_log_pos 1403 CRC32 0xf2dddee2 	Write_rows: table id 116 flags: STMT_END_F
### DELETE FROM `test`.`t8`
### WHERE
###   @1=8 
###   @2='steven' 
#181127 17:38:33 server id 330631  end_log_pos 1083 CRC32 0xddddd8e4 	Write_rows: table id 116 flags: STMT_END_F
### DELETE FROM `test`.`t8`
### WHERE
###   @1=7 
###   @2='steven' 
#181127 17:38:29 server id 330631  end_log_pos 763 CRC32 0x3642add0 	Write_rows: table id 116 flags: STMT_END_F
### DELETE FROM `test`.`t8`
### WHERE
###   @1=6 
###   @2='steven' 
#181127 17:38:25 server id 330631  end_log_pos 443 CRC32 0xd5578b59 	Write_rows: table id 116 flags: STMT_END_F
### DELETE FROM `test`.`t8`
### WHERE
###   @1=5 
###   @2='steven' 
DELIMITER ;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' ;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;

四、数据恢复

将反向解析出来的binlog添加到文本文件


mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -B -v -v --start-position=291 --stop-position=2195 mysql-bin.000005 > flash_back_t8.sql

修改flash_back_t8.sql,将最后的ROLLBACK改为COMMIT。

五、恢复数据

mysql -uroot -p test < flash_back_t8.sql

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯