mysql binlg delete语句解析为insert语句

翻到一个帖子，是把binlog中的delete语句解析为insert，感觉挺有意思，于是测试了一下，之前都是用myflush。

原贴：https://www.cnblogs.com/wshenjin/p/11423300.html

测试

(system@127.0.0.1:3306) [(none)]> create database test;
(system@127.0.0.1:3306) [(none)]> use test;
(system@127.0.0.1:3306) [test]> create table yq (id int,name varchar(100),ctime datetime default now());
(system@127.0.0.1:3306) [test]> insert into yq (id,name) values (1,'yhq');
(system@127.0.0.1:3306) [test]> select * from yq;
+------+------+---------------------+
| id   | name | ctime               |
+------+------+---------------------+
|    1 | yhq  | 2020-11-13 00:55:44 |
|    1 | yhq  | 2020-11-13 00:55:48 |
|    1 | yhq  | 2020-11-13 00:55:49 |
|    1 | yhq  | 2020-11-13 00:55:49 |
|    1 | yhq  | 2020-11-13 00:55:52 |
|    2 | yhq1 | 2020-11-13 00:56:01 |
|    2 | yhq1 | 2020-11-13 00:56:02 |
|    2 | yhq1 | 2020-11-13 00:56:02 |
|    2 | yhq1 | 2020-11-13 00:56:03 |
+------+------+---------------------+
(system@127.0.0.1:3306) [test]> show variables like 'bin%';
| binlog_format                              | ROW          |
(system@127.0.0.1:3306) [test]> delete from yq;
Query OK, 9 rows affected (0.01 sec)

(system@127.0.0.1:3306) [test]> select * from yq;
Empty set (0.00 sec)
(system@127.0.0.1:3306) [test]> show binary logs;
| mysql-bin.000013 | 1073743459 |
| mysql-bin.000014 |  466205241 |
+------------------+------------+
(system@127.0.0.1:3306) [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-11-13 00:58:55 |
+---------------------+
1 row in set (0.00 sec)

[mysql@mail binlog]$ mysqlbinlog  -vv -d test mysql-bin.000014 | awk '/DELETE FROM/ && (/test.yq/ || /`test.`yq`/){
    while(1){
        print $0;
        getline;
        if($0 !~ /^###/){
            break;
        };
    }
}' > test.yq.delete.txt ##执行完成后，发现该文件没有内容
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.
##于是使用binlog解析
[mysql@mail binlog]$ mysqlbinlog -v -v -d test --base64-output=DECODE-ROWS --set-charset=UTF8 mysql-bin.000014 --stop-datetime="2020-11-12 00:10:44" --start-datetime="2020-11-12 23:55:44" > t1.sql
[mysql@mail binlog]$ more t1.sql 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES UTF8 */;
DELIMITER /*!*/;
# at 4
#201109  6:24:47 server id 2008032334  end_log_pos 123 CRC32 0x08c3470f     Start: binlog v 4, server v 5.7.27-log created 201109  6:24:47
# Warning: this binlog is either in use or was not closed properly.
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
##发现内容不对，于是全部解析查看一下
[mysql@mail binlog]$ mysqlbinlog -v -v -d test --base64-output=DECODE-ROWS --set-charset=UTF8 mysql-bin.000014  > t2.sql
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.
[mysql@mail binlog]$ more t2.sql
[mysql@mail binlog]$ cat t2.sql |grep yq ##里面是存在yh表的
create table yq (id int,name varchar(100),ctime datetime default now())
#201112 23:55:44 server id 2008032334  end_log_pos 466026268 CRC32 0x534d72f3     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:55:48 server id 2008032334  end_log_pos 466030450 CRC32 0xa25b6d64     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:55:49 server id 2008032334  end_log_pos 466030726 CRC32 0x588fff1b     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:55:49 server id 2008032334  end_log_pos 466031658 CRC32 0x8e2024d4     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:55:52 server id 2008032334  end_log_pos 466036552 CRC32 0xa7289e5d     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:56:01 server id 2008032334  end_log_pos 466049199 CRC32 0xf62b0e54     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:56:02 server id 2008032334  end_log_pos 466051503 CRC32 0x43542024     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:56:02 server id 2008032334  end_log_pos 466052220 CRC32 0xe8fb8d9c     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:56:03 server id 2008032334  end_log_pos 466052497 CRC32 0x26f140f9     Table_map: `test`.`yq` mapped to number 262
### INSERT INTO `test`.`yq`
#201112 23:57:55 server id 2008032334  end_log_pos 466170937 CRC32 0xd2e54a57     Table_map: `test`.`yq` mapped to number 262
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
### DELETE FROM `test`.`yq`
--------------------------------墨西哥时区，冬季自动切换，导致系统时间比日志大1个小时，也就是 binlog的时间比now() 晚一个小时
[mysql@mail binlog]$ mysqlbinlog -v -v -d test --base64-output=DECODE-ROWS --set-charset=UTF8 mysql-bin.000014 --stop-datetime="2020-11-13 00:10:44" --start-datetime="2020-11-12 23:55:44" > t1.sql

恢复

[mysql@mail binlog]$ cat t1.sql | awk '/DELETE FROM/ && (/test.yq/ || /`test`.`yq`/){
    while(1){
        print $0;
        getline;
        if($0 !~ /^###/){
            break;
        };
    }
}' > test.yq.delete.txt ##原贴中的执行语句是有问题的，这里已经修正
[mysql@mail binlog]$ more test.yq.delete.txt 
### DELETE FROM `test`.`yq`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:55:44' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:55:48' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:55:49' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:55:49' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:55:52' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:56:01' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:56:02' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:56:02' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`yq`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @3='2020-11-13 00:56:03' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
[mysql@mail binlog]$ sed -i 's/^### //g' test.yq.delete.txt
[mysql@mail binlog]$ more test.yq.delete.txt 
DELETE FROM `test`.`yq`
WHERE
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:44' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:48' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:49' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:49' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:52' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:01' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:02' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:02' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
DELETE FROM `test`.`yq`
WHERE
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:03' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
  
  语句转换 DELETE --> INSERT
# sed -i "s/^DELETE FROM/INSERT INTO/g" test.yq.delete.txt
# sed -i "s/^WHERE/VALUES(/g" test.yq.delete.txt
# sed -i '/@13=.*/a );' test.yq.delete.txt
[mysql@mail binlog]$ more test.yq.delete.txt 
INSERT INTO `test`.`yq`
VALUES(
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:44' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:48' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:49' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:49' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=1 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:55:52' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:01' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:02' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:02' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `test`.`yq`
VALUES(
  @1=2 /* INT meta=0 nullable=1 is_null=0 */
  @2='yhq1' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
  @3='2020-11-13 00:56:03' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

# cat test.yq.delete.txt  | awk -F"=|/*" '{
    if($0 ~ /^INSERT|^VALUES|^);/){
        print $0;
    }else{
        printf $2",";
    };
}' > test.yq.insert.sql
[mysql@mail binlog]$ more test.yq.insert.sql 
INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:44' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:48' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:49' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:49' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:52' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:01' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:02' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:02' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:03' ,
[mysql@mail binlog]$ sed -i "s/,);$/);/g" test.yq.insert.sql 
[mysql@mail binlog]$ more test.yq.insert.sql 
INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:44' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:48' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:49' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:49' ,INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:52' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:01' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:02' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:02' ,INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:03' ,
[mysql@mail binlog]$ cp test.yq.insert.sql test.yq.insert2.sql 
[mysql@mail binlog]$ sed -i 's/,INSERT/);INSERT/g' test.yq.insert2.sql
[mysql@mail binlog]$ more test.yq.insert2.sql 
INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:44' );INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:48' );INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:49' );INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:49' );INSERT INTO `test`.`yq`
VALUES(
1 ,'yhq' ,'2020-11-13 00:55:52' );INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:01' );INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:02' );INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:02' );INSERT INTO `test`.`yq`
VALUES(
2 ,'yhq1' ,'2020-11-13 00:56:03' ,

, >> );
[mysql@mail binlog]$ vim test.yq.insert2.sql  #手动修改最后一个逗号为);
[mysql@mail binlog]$ cd ..
[mysql@mail 3306]$ cd ../scripts/
[mysql@mail scripts]$ ./mysqlplus.sh
(system@127.0.0.1:3306) [(none)]> use test;
(system@127.0.0.1:3306) [test]> source /data/mysqldata/3306/binlog/test.yq.insert2.sql;
(system@127.0.0.1:3306) [test]> select * from yq;
+------+------+---------------------+
| id   | name | ctime               |
+------+------+---------------------+
|    1 | yhq  | 2020-11-13 00:55:44 |
|    1 | yhq  | 2020-11-13 00:55:48 |
|    1 | yhq  | 2020-11-13 00:55:49 |
|    1 | yhq  | 2020-11-13 00:55:49 |
|    1 | yhq  | 2020-11-13 00:55:52 |
|    2 | yhq1 | 2020-11-13 00:56:01 |
|    2 | yhq1 | 2020-11-13 00:56:02 |
|    2 | yhq1 | 2020-11-13 00:56:02 |
|    2 | yhq1 | 2020-11-13 00:56:03 |
+------+------+---------------------+
9 rows in set (0.00 sec)

测试发现，内容还是可以还原的。

如果是生产环境，若字段内容比较复杂，数据行数比较多的时候，还是不建议使用这个方法。毕竟在测试的时候使用的是最简单的内容和字段。