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