mysqlbinlgdelete语句解析为insert语句--688IT编程网

mysqlbinlgdelete语句解析为insert语句

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

原贴：

测试

(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:52 |

| 2 | yhq1 | 2020-11-13 00:56:01 |

| 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. ##执⾏完成后，发现该⽂件没有内容

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, [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`

--------------------------------墨西哥时区，冬季⾃动切换，导致系统时间⽐⽇志⼤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. ##原贴中的执⾏语句是有问题的，这⾥已经修正[mysql@mail binlog]$ more test.

### 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.

[mysql@mail binlog]$ more test.

DELETE FROM `test`.`yq`

WHERE

mysql中delete语句@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.

# sed -i "s/^WHERE/VALUES(/g" test.

# sed -i '/@13=.*/a );' test.

[mysql@mail binlog]$ more test.

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. | 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-1

3 00:56:01' ,INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 00:56:02' ,INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 00:56:02' ,INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 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-1

3 00:56:01' ,INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 00:56:02' ,INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 00:56:02' ,INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 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-1

3 00:56:01' );INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 00:56:02' );INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 00:56:02' );INSERT INTO `test`.`yq`

VALUES(

2 ,'yhq1' ,'2020-11-1

3 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:52 |

| 2 | yhq1 | 2020-11-13 00:56:01 |

| 2 | yhq1 | 2020-11-13 00:56:02 |

| 2 | yhq1 | 2020-11-13 00:56:03 |

+------+------+---------------------+

9 rows in set (0.00 sec)

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

如果是⽣产环境，若字段内容⽐较复杂，数据⾏数⽐较多的时候，还是不建议使⽤这个⽅法。毕竟在测试的时候使⽤的是最简单的内容和字段。

688IT编程网

mysqlbinlgdelete语句解析为insert语句

发表评论

推荐文章

negotiable certificates of deposits -回复

trandate翻译 -回复

tenoke翻译 -回复

避免合同倒签的方法

cut down the transaction cost

热门文章

Truncate Table

truncate table audit_result_temp; -回复

truncate 语法

truncated incorrect double value 21分钟

truncate()函数

统计学专业辞汇英语翻译

铁塔专业术语英语

大数据英语翻译

A MEANS FOR SAWING ELONGATE UNITS FROM A TREE-TRUN

外文翻译--拥塞控制中的算法

TOC术语中英文对照

conditionalty贸易术语 -回复

termination_date翻译

determined 翻译

truncatedsvd函数作用

tangential翻译

表面处理方面的缺陷术语(Defect Terms)

exterminate翻译

delamination翻译

antiquated翻译

最新文章

negotiable certificates of deposits -回复

teams中presenting duplicate -回复

老同志独断专行处理流程详解

quota share reinsurance contract -回复

attempted to resolve relative to -回复

concurrent modification during iteration dart

标签列表

688IT编程网

mysqlbinlgdelete语句解析为insert语句

发表评论

推荐文章

negotiable certificates of deposits -回复

trandate翻译 -回复

tenoke翻译 -回复

避免合同倒签的方法

cut down the transaction cost

热门文章

Truncate Table

truncate table audit_result_temp; -回复

truncate 语法

truncated incorrect double value 21分钟

truncate()函数

统计学专业辞汇英语翻译

铁塔专业术语 英语

大数据英语翻译

A MEANS FOR SAWING ELONGATE UNITS FROM A TREE-TRUN

外文翻译--拥塞控制中的算法

TOC术语中英文对照

conditionalty贸易术语 -回复

termination_date翻译

determined 翻译

truncatedsvd函数作用

tangential翻译

表面处理方面的缺陷术语(Defect Terms)

exterminate翻译

delamination翻译

antiquated翻译

最新文章

negotiable certificates of deposits -回复

teams中presenting duplicate -回复

老同志独断专行处理流程详解

quota share reinsurance contract -回复

attempted to resolve relative to -回复

concurrent modification during iteration dart

标签列表

铁塔专业术语英语