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: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. ##执⾏完成后,发现该⽂件没有内容
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`
### 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. ##原贴中的执⾏语句是有问题的,这⾥已经修正[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: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)
测试发现,内容还是可以还原的。
如果是⽣产环境,若字段内容⽐较复杂,数据⾏数⽐较多的时候,还是不建议使⽤这个⽅法。毕竟在测试的时候使⽤的是最简单的内容和字段。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论