Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to restore the erroneous deletion of MySQL data. I hope I can add and update some knowledge to you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

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 at the time of 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 most recent backup and the backed-up binaries to another CVM B with MySQL instance

Extract the binary file into a 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 the database on server B

Mysql-S / tmp/mysql3309.sock-u root-pMySQL8.0

< testdb.dump 将删除数据前的二进制日志重新应用到数据库 mysqlbinlog --stop-datetime="2018-11-12 10:34:00" \--skip-gtids binlog.000006 | \mysql -S /tmp/mysql3309.sock -u root -pMySQL8.0 将表 nw dump 出来 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 insert statements from 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 restored

Mysql > use sbtest;mysql > source insert.sqlmysql > 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 Suzhou 3338 | 2018-11-12 10 Suzhou 3339 | 2018-11-12 10 Suzhou 3339 | 2018-11-12 10 Suzhou 3340 | 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 perform restore operations on the original server

Read the above about mistakenly deleted MySQL data how to restore, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.