Shell script extracts Binlog DELETE statement for recovering data

Source: Internet
Author: User
Tags crc32

The Binlog log is as follows:
/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 120
#160111 16:37:29 Server ID 333336 end_log_pos 195 CRC32 0x5759daa6 Query thread_id=6 exec_time=0 error_code =0
SET timestamp=1452587849/*!*/;
SET @ @session. pseudo_thread_id=6/*!*/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;
SET @ @session. sql_mode=1073741824/*!*/;
SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
/*!\c latin1 *//*!*/;
SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/;
SET @ @session. lc_time_names=0/*!*/;
SET @ @session. collation_database=default/*!*/;
BEGIN
/*!*/;
# at 195
#160111 16:37:29 server ID 333336 end_log_pos CRC32 0x6c642807 table_map: ' Gaoquan '. ' T1 ' mapped to number 70
# at 250
#160111 16:37:29 Server ID 333336 end_log_pos 348 CRC32 0xde1ef5e7 delete_rows:table ID flags:stmt_end_f
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=1
# # # @2= ' Gaoquan '
# # @3=22
# # # @4= ' Male '
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=2
# # # @2= ' son '
# # @3=22
# # # @4= ' Male '
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=3
# # # @2= ' Gaoquan1 '
# # @3=32
# # # @4= ' Male '
# at 348
#160111 16:37:29 Server ID 333336 end_log_pos 379 CRC32 0x05ee85fb Xid = 52
commit/*!*/;
# at 379
#160111 16:37:32 Server ID 333336 end_log_pos 426 CRC32 0x8649f4ea Rotate to mysql-bin.000006 pos:4
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 120
#160112 16:37:29 Server ID 333336 end_log_pos 195 CRC32 0x5759daa6 Query thread_id=6 exec_time=0 error_code =0
SET timestamp=1452587849/*!*/;
SET @ @session. pseudo_thread_id=6/*!*/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;
SET @ @session. sql_mode=1073741824/*!*/;
SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
/*!\c latin1 *//*!*/;
SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/;
SET @ @session. lc_time_names=0/*!*/;
SET @ @session. collation_database=default/*!*/;
BEGIN
/*!*/;
# at 195
#160112 16:37:29 server ID 333336 end_log_pos CRC32 0x6c642807 table_map: ' Gaoquan '. ' T1 ' mapped to number 70
# at 250
#160112 16:37:29 Server ID 333336 end_log_pos 348 CRC32 0xde1ef5e7 delete_rows:table ID flags:stmt_end_f
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=1
# # # @2= ' Gaoquan '
# # @3=22
# # # @4= ' Male '
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=2
# # # @2= ' son '
# # @3=22
# # # @4= ' Male '
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=3
# # # @2= ' Gaoquan1 '
# # @3=32
# # # @4= ' Male '
# at 348
#160112 16:37:29 Server ID 333336 end_log_pos 379 CRC32 0x05ee85fb Xid = 52
commit/*!*/;
# at 379
#160112 16:37:32 Server ID 333336 end_log_pos 426 CRC32 0x8649f4ea Rotate to mysql-bin.000006 pos:4
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 120
#160113 16:37:29 Server ID 333336 end_log_pos 195 CRC32 0x5759daa6 Query thread_id=6 exec_time=0 error_code =0
SET timestamp=1452587849/*!*/;
SET @ @session. pseudo_thread_id=6/*!*/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;
SET @ @session. sql_mode=1073741824/*!*/;
SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
/*!\c latin1 *//*!*/;
SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/;
SET @ @session. lc_time_names=0/*!*/;
SET @ @session. collation_database=default/*!*/;
BEGIN
/*!*/;
# at 195
#160113 16:37:29 server ID 333336 end_log_pos CRC32 0x6c642807 table_map: ' Gaoquan '. ' T1 ' mapped to number 70
# at 250
#160113 16:37:29 Server ID 333336 end_log_pos 348 CRC32 0xde1ef5e7 delete_rows:table ID flags:stmt_end_f
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=1
# # # @2= ' Gaoquan '
# # @3=22
# # # @4= ' Male '
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=2
# # # @2= ' son '
# # @3=22
# # # @4= ' Male '
# # # DELETE from ' Gaoquan '. ' T1 '
# # WHERE
# # @1=3
# # # @2= ' Gaoquan1 '
# # @3=32
# # # @4= ' Male '
# at 348
#160113 16:37:29 Server ID 333336 end_log_pos 379 CRC32 0x05ee85fb Xid = 52
commit/*!*/;
# at 379
#160113 16:37:32 Server ID 333336 end_log_pos 426 CRC32 0x8649f4ea Rotate to mysql-bin.000006 pos:4
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

Requirements:
Extract the January 12, 2016 DELETE statement from the Binlog log for data recovery

The script is as follows:
#!/bin/bash

#bl表列数 #语句yj
Bl=4
Yj=delete
zs1= ' awk '/#160112/,/#160113/{print $} '/tmp/delete|awk ' "/$yj/",/# at/{print $} ' |grep ^###|grep ' @ ' |cut-d ' = '-f2 '
Zs2= ' echo $zs 1|awk ' {print NF} '
Zt= ' echo ' $zs 2/$bl "|BC"
Hs=0

Databa=gz
Tab=wubx
ztt=$ (($zt +0))
Ii=0

A1=1
a2=2
A3=3
A4=4
while [[$ii-lt $ZTT]];d o
L1= ' echo $zs 1|awk ' {print $ ' "$a 1" '} '
L2= ' echo $zs 1|awk ' {print $ ' "$a 2" '} '
L3= ' echo $zs 1|awk ' {print $ ' "$a 3" '} '
L4= ' echo $zs 1|awk ' {print $ ' "$a 4" '} '
echo "use $databa; insert into $tab values ($l 1, $l 2, $l 3, $l 4)" >>HF

a1=$ (($a 1+ $bl))
a2=$ ($a $bl))
a3=$ (($a $bl))
a4=$ (($a 4+ $bl))

ii=$ (($ii + 1));
Done

Operation Result:
[Email protected]:~> sh mysql.sh && cat HF
Use Gz;insert into WUBX values (1, ' Gaoquan ', A, ' male ')
Use Gz;insert to WUBX values (2, ' son ', ' male ')
Use Gz;insert into WUBX values (3, ' gaoquan1 ', +, ' male ')

Summary: Preliminary write a Delete script extract, there is a need to improve the place, welcome to comment, discuss together.

Shell script extracts Binlog DELETE statement for recovering data

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.