1. Confirm the binlog format
show VARIABLES like 'binlog_format';
Copy the code
2. Check the binlog directory
show VARIABLES like 'datadir';
Copy the code
3. Check the current binlog
show master status;
Copy the code
4. Confirm the events in the current binlog
show BINLOG events in 'binlog.000002';
Copy the code
The offset ranges from 973 to 1328
5. Export the SQL file based on the offset
mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position=973 --stop-position=1328 /var/lib/mysql/binlog.000002 > temp_order.sql
Copy the code/ *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/ *! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER / *! * /;
# at 973
#210603 10:11:41 server id 1 end_log_pos 1048 CRC32 0x88fd079f Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes original_committed_timestamp=1622715101797314 immediate_commit_timestamp=1622715101797314 transaction_length=386
/ *! 50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*// *! * /;
# original_commit_timestamp=1622715101797314 (2021- 06- 03 10:11:41.797314 UTC)
# immediate_commit_timestamp=1622715101797314 (2021- 06- 03 10:11:41.797314 UTC)
/ *! 80001 SET @@session.original_commit_timestamp=1622715101797314*// *! * /;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/ *! * /;
# at 1048
#210603 10:11:41 server id 1 end_log_pos 1126 CRC32 0xa0750ac9 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1622715101/ *! * /;
SET @@session.pseudo_thread_id=9/ *! * /;
SET @@session.foreign_key_checks=1The @@session.sql_auto_is_null=0The @@session.unique_checks=1The @@session.autocommit=1/ *! * /;
SET @@session.sql_mode=1168113696/ *! * /;
SET @@session.auto_increment_increment=1The @@session.auto_increment_offset=1/ *! * /;
/ *! \C utf8mb4 *// *! * /;
SET @@session.character_set_client=255The @@session.collation_connection=255The @@session.collation_server=255/ *! * /;
SET @@session.lc_time_names=0/ *! * /;
SET @@session.collation_database=DEFAULT/ *! * /;
/ *! 80005 SET @@session.default_collation_for_utf8mb4=255*// *! * /;
BEGIN
/ *! * /;
# at 1126
#210603 10:11:41 server id 1 end_log_pos 1201 CRC32 0x2e322102 Table_map: `temp_db`.`temp_order` mapped to number 75
# at 1201
#210603 10:11:41 server id 1 end_log_pos 1328 CRC32 0xdd1eb5fd Delete_rows: table id 75 flags: STMT_END_F
ROLLBACK /* added by mysqlbinlog */ / *! * /;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ / *! * /;
DELIMITER ;
# End of log file
/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/ *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
root@171133c63ae9:~# mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position=973 --stop-position=1328 /var/lib/mysql/binlog.000002
/ *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/ *! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER / *! * /;
# at 973
#210603 10:11:41 server id 1 end_log_pos 1048 CRC32 0x88fd079f Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes original_committed_timestamp=1622715101797314 immediate_commit_timestamp=1622715101797314 transaction_length=386
/ *! 50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*// *! * /;
# original_commit_timestamp=1622715101797314 (2021- 06- 03 10:11:41.797314 UTC)
# immediate_commit_timestamp=1622715101797314 (2021- 06- 03 10:11:41.797314 UTC)
/ *! 80001 SET @@session.original_commit_timestamp=1622715101797314*// *! * /;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/ *! * /;
# at 1048
#210603 10:11:41 server id 1 end_log_pos 1126 CRC32 0xa0750ac9 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1622715101/ *! * /;
SET @@session.pseudo_thread_id=9/ *! * /;
SET @@session.foreign_key_checks=1The @@session.sql_auto_is_null=0The @@session.unique_checks=1The @@session.autocommit=1/ *! * /;
SET @@session.sql_mode=1168113696/ *! * /;
SET @@session.auto_increment_increment=1The @@session.auto_increment_offset=1/ *! * /;
/ *! \C utf8mb4 *// *! * /;
SET @@session.character_set_client=255The @@session.collation_connection=255The @@session.collation_server=255/ *! * /;
SET @@session.lc_time_names=0/ *! * /;
SET @@session.collation_database=DEFAULT/ *! * /;
/ *! 80005 SET @@session.default_collation_for_utf8mb4=255*// *! * /;
BEGIN
/ *! * /;
# at 1126
#210603 10:11:41 server id 1 end_log_pos 1201 CRC32 0x2e322102 Table_map: `temp_db`.`temp_order` mapped to number 75
# at 1201
#210603 10:11:41 server id 1 end_log_pos 1328 CRC32 0xdd1eb5fd Delete_rows: table id 75 flags: STMT_END_F
### DELETE FROM `temp_db`.`temp_order`
### WHERE# # #@ 1=1# # #@ 2=1# # #@ 3='tanaka'# # #@ 4='paypay'# # #@ 5='2-8-201, Shiki 6-chome, Shiki City, Saitama Prefecture'# # #@ 6=0.00
ROLLBACK /* added by mysqlbinlog */ / *! * /;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ / *! * /;
DELIMITER ;
# End of log file
/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/ *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Copy the codeTo get there, simply issue the DELETE statement and convert it to a restore SQL statement
Put forward the SQL text as follows:
# # #DELETE FROM `temp_db`.`temp_order`
### WHERE# # #@ 1=1# # #@ 2=1# # #@ 3='tanaka'# # #@ 4='paypay'# # #@ 5='2-8-201, Shiki 6-chome, Shiki City, Saitama Prefecture'# # #@ 6=0.00
Copy the codeThe SQL for data recovery is as follows:
INSERT INTO `temp_db`.`temp_order`
SELECT
1.1.'tanaka'.'paypay'.'2-8-201, Shiki 6-chome, Shiki City, Saitama Prefecture'.0.00
Copy the codeAfter execution, the deleted data can be restored.