- 查询MySQL当前使用的binlog文件
#查看是否开启binlog
show variables like 'log_%';
#查看所有binlog日志列表
show master logs;
#查询当前使用的binlog文件
show master status;
- 根据删除语句大概执行时间确定执行点
mysqlbinlog -vv --start-datetime='2022-11-27 18:30:00' --stop-datetime='2022-11-27 18:35:00' /mysql-logs/binlogs/mysql-bin.000671 > binlog.txt
输出大概是这种格式:
BEGIN
/*!*/;
# at 11742 注意这个是deleted的开始节点
#221130 21:22:52 server id 1 end_log_pos 11869 CRC32 0xd23d8935 Table_map: `test`.`nucleic_acid_record_info_copy1` mapped to number 110
# at 11869
#221130 21:22:52 server id 1 end_log_pos 19010 CRC32 0x407cfd2f Delete_rows: table id 110 flags: STMT_END_F
BINLOG '
LFmHYxMBAAAAfwAAAF0uAAAAAG4AAAAAAAEABHRlc3QAHm51Y2xlaWNfYWNpZF9yZWNvcmRfaW5m
b19jb3B5MQASCA8SEg8PDw8PEhL+/AEPDw8PGwABAAAACAAIAAiQAZABAAD+BAIAAvwD/AP8A/73
AwEBAAID/P8ANYk90g==
LFmHYyABAAAA5RsAAEJKAAAAAG4AAAAAAAEAAgAS5lcAKi4BAAAAAACZrkowUQ0A6Zi05oCn
77yILe+8iQEwAA4AMDE3MTEwNjIyMDE1NDABAC8BAC/mVwArLgEAAAAAAJmuSjBRDQDpmLTmgKfv
NzExMDE0NDYxMjUyL/18QA==
'/*!*/;
### DELETE FROM `test`.`nucleic_acid_record_info_copy1`
### WHERE
### @1=77354 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=NULL /* VARSTRING(256) meta=256 nullable=1 is_null=1 */
### @3=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
### @4='2022-11-05 03:01:17' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @5='阴性(-)' /* VARSTRING(2048) meta=2048 nullable=1 is_null=0 */
### @6=NULL /* VARSTRING(2048) meta=2048 nullable=1 is_null=1 */
### @7=NULL /* VARSTRING(2048) meta=2048 nullable=1 is_null=1 */
### @8=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
### @9=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
### @10=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
### @11=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
### @12='0' /* STRING(4) meta=65028 nullable=0 is_null=0 */
### @13=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
### @14=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @15=NULL /* VARSTRING(512) meta=512 nullable=1 is_null=1 */
### @16='01711062201540' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### @17='/' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### @18='/' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
# at 19010 注意这个是deleted的结束节点
#221130 21:22:52 server id 1 end_log_pos 19041 CRC32 0xf1af8408 Xid = 289
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*/;
- 根据deleted执行的开始节点和结束节点将binlog导出
mysqlbinlog -vv --start-position=159953715 --base64-output=decode-rows --stop-position=162292672 /mysql-logs/binlogs/mysql-bin.000671 |grep ^"###" > logs/binlog.data
- 执行命令语句将binlog文件转为insert语句
cat logs/binlog.data | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' >/data/insert.sql
- 最终的执行语句格式还得手动修改,修改完后直接mysql执行脚本即可