1. 确认binlog格式

show VARIABLES like 'binlog_format';
复制代码

2. 确认下binlog目录

show VARIABLES like 'datadir';
复制代码

3. 确认当前binlog

show master status;
复制代码

4. 确认当前binlog的events

show BINLOG events in 'binlog.000002';
复制代码

偏移量从973 ~ 1328

5. 根据偏移量导出sql文

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position=973 --stop-position=1328 /var/lib/mysql/binlog.000002 > temp_order.sql
复制代码

/*!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=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@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=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@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*/;
复制代码

到这个只需提出DELETE语句，再进行转换成恢复SQL语句

提出来SQL文如下：

### 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
复制代码

数据恢复SQL如下：

INSERT INTO `temp_db`.`temp_order`
SELECT
1,
1,
'tanaka',
'paypay',
'2-8-201, Shiki 6-chome, Shiki City, Saitama Prefecture',
0.00
复制代码

执行后，删掉的数据就可以恢复完成。