Database backup statement
mysqldump -S /tmp/mysql3309.sock -u root -pMySQL8.0 \ --single-transaction \ --quick --flush-logs \ --triggers \ --routines \ --events \ --set-gtid-purged=OFF \ --databases testdb > testdb.dump
The following data exists in table nw during backup
mysql> select * from nw; +---------------------+ | dt | +---------------------+ | 2018-11-12 10:20:26 | | 2018-11-12 10:20:27 | | 2018-11-12 10:20:28 | | 2018-11-12 10:20:28 | | 2018-11-12 10:20:29 | | 2018-11-12 10:30:53 | | 2018-11-12 10:30:54 | | 2018-11-12 10:30:55 | | 2018-11-12 10:30:55 | | 2018-11-12 10:30:56 | | 2018-11-12 10:30:57 | | 2018-11-12 10:30:57 | +---------------------+
Copy the latest backup and the binary file after backup to another server B with MySQL instance
Extract binary file as plaintext file and query the time point of data deletion
mysqlbinlog --base64-output=decode-rows -v binlog.000006 > result.sql #181112 10:34:23 server id 3309 end_log_pos 2846 Delete_rows: table id 694 flags: STMT_END_F ### DELETE FROM `testdb`.`nw` ### WHERE ### @1=1541989226 ### DELETE FROM `testdb`.`nw`
Restore database on server B
mysql -S /tmp/mysql3309.sock -u root -pMySQL8.0 < testdb.dump
Reapply the binary log before data deletion to the database
mysqlbinlog --stop-datetime="2018-11-12 10:34:00" \ --skip-gtids binlog.000006 | \ mysql -S /tmp/mysql3309.sock -u root -pMySQL8.0
Dump the table NW
mysqldump -S /tmp/mysql3309.sock -u root -pMySQL8.0 \ --single-transaction \ --quick --flush-logs \ --triggers \ --routines \ --events \ --set-gtid-purged=OFF \ testdb nw > nw.dump
Extract the insert statement in nw.dump
grep "INSERT INTO \`nw\` VALUES" nw.dump > insert.sql
Execute the insert statement in the original database, and the query result data has been recovered
mysql> use sbtest; mysql> source insert.sql mysql> select * from nw; +---------------------+ | dt | +---------------------+ | 2018-11-12 10:20:26 | | 2018-11-12 10:20:27 | | 2018-11-12 10:20:28 | | 2018-11-12 10:20:28 | | 2018-11-12 10:20:29 | | 2018-11-12 10:30:53 | | 2018-11-12 10:30:54 | | 2018-11-12 10:30:55 | | 2018-11-12 10:30:55 | | 2018-11-12 10:30:56 | | 2018-11-12 10:30:57 | | 2018-11-12 10:30:57 | | 2018-11-12 10:33:37 | | 2018-11-12 10:33:38 | | 2018-11-12 10:33:39 | | 2018-11-12 10:33:40 | | 2018-11-12 10:33:40 | | 2018-11-12 10:33:41 | | 2018-11-12 10:33:42 | | 2018-11-12 10:33:42 | | 2018-11-12 10:33:43 | +---------------------+
Note: do not restore on the original server