MySQL使用MariaDB的binlog闪回

原文： https://www.yuque.com/wei01/wql35u/xcig4l
8.0之前应该都支持

解压mariadb安装包:
tar xvf mariadb-10.4.20-linux-x86_64.tar.gz 


模拟误删除
session 1:
mysqlslap -a -uroot -proot1234 -c 50 -i 100
mysqlslap: [Warning] Using a password on the command line interface can be insecure.

session 2
delete from tr;
Query OK, 10000 rows affected (2.45 sec)



解析binlog, 找到误删时间点
/root/mariadb-10.4.20-linux-x86_64/bin/mysqlbinlog -uroot -proot1234 --database=test --table=tr -vv --base64-output=DECODE-ROWS mysql-bin.000017 --start-datetime '2021-07-08 14:11:00'> /tmp/raw.sql


#210708 14:11:28 server id 24750  end_log_pos 47123213 CRC32 0xd76755ff         Ignorable
# Ignorable event type 29 (MySQL Rows_query)
# at 47123213
#210708 14:11:28 server id 24750  end_log_pos 47123262 CRC32 0xd9e8559c         Table_map: `test`.`tr` mapped to number 964
# at 47123262
# at 47131469
# at 47139676
# at 47147883
# at 47156090
# at 47164297
# at 47172504
# at 47180711
# at 47188918
# at 47197125
# at 47205332
# at 47213539
#210708 14:11:28 server id 24750  end_log_pos 47131469 CRC32 0x50535c4b         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47139676 CRC32 0x9f9f77b2         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47147883 CRC32 0x59eeedaa         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47156090 CRC32 0xdb0a684a         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47164297 CRC32 0xdf79875c         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47172504 CRC32 0x2e1e01b9         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47180711 CRC32 0xcff83af6         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47188918 CRC32 0x0987243a         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47197125 CRC32 0xe145a0a2         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47205332 CRC32 0x1169c490         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47213539 CRC32 0x483cb228         Delete_rows: table id 964
#210708 14:11:28 server id 24750  end_log_pos 47213682 CRC32 0x735d8aca         Delete_rows: table id 964 flags: STMT_END_F
### DELETE FROM `test`.`tr`
...

###   @2=993 /* INT meta=0 nullable=1 is_null=0 */
###   @3=NULL /* VARSTRING(30) meta=30 nullable=1 is_null=1 */
### DELETE FROM `test`.`tr`
### WHERE
###   @1=10000 /* INT meta=0 nullable=0 is_null=0 */
###   @2=516 /* INT meta=0 nullable=1 is_null=0 */
###   @3=NULL /* VARSTRING(30) meta=30 nullable=1 is_null=1 */
# Number of rows: 10000
# at 47213682
#210708 14:11:28 server id 24750  end_log_pos 47213713 CRC32 0xcd8993e2         Xid = 146956
COMMIT/*!*/;

生成闪回语句
/root/mariadb-10.4.20-linux-x86_64/bin/mysqlbinlog --flashback -uroot -proot1234 --database=test --table=tr --start-position=47123213 --stop-position=47213682 mysql-bin.000017 > /tmp/flash.sql

由于开启GTID所以报错,建议先恢复到一个临时环境,然后再导入到生产环境

[root@mysql1 binlog]# mysql</tmp/flash.sql 
ERROR 1782 (HY000) at line 28: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.


[root@mysql1 binlog]# mysql</tmp/flash.sql 
[root@mysql1 binlog]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9848
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[root@localhost][(none)]: use test;
Database changed
[root@localhost][test]: select count(*) from tr;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)