运用mysqlbinlog_flashback闪回误删除的表

2024-07-24 12:31:46

字体：大中小

来源：转载

供稿：网友

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

此脚本为阿里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 |
+------+------+

二、查看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 /* xid=29 */                              |
| mysql-bin.000005 | 474 | Anonymous_Gtid |    330631 |         539 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000005 | 539 | Query          |    330631 |         611 | BEGIN                                            |

#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 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# 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 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# 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`

###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `test`.`t8`
### WHERE
###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
###   @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `test`.`t8`
### WHERE
###   @1=7 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=7 /* INT meta=0 nullable=1 is_null=0 */
###   @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `test`.`t8`
### WHERE
###   @1=8 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=8 /* INT meta=0 nullable=1 is_null=0 */
###   @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# 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
/*!*/;

#181127 17:40:18 server id 330631 end_log_pos 2195 CRC32 0x2b861950 Xid = 34
COMMIT/*!*/;
DELIMITER ;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
反向解析binlog
[root@node1 data]# mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -B -v -v --start-position=291 --stop-position=2195 mysql-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
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: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 /* INT meta=0 nullable=1 is_null=0 */
###   @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `test`.`t8`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `test`.`t8`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `test`.`t8`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
#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 /* INT meta=0 nullable=1 is_null=0 */
###   @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `test`.`t8`
### WHERE
###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
###   @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=6 /* INT meta=0 nullable=1 is_null=0 */
###   @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### UPDATE `test`.`t8`

四、数据恢复
将反向解析出来的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教学锁

下一篇：MySQL 数据备份mysqldump与复原mysql