How to recover accidentally deleted table data in MySQL (must read)

If there is a backup, it is very simple. You only need to generate the most recent backup data and then use mysqlbinlog to retrieve the data after the backup time point and then restore it to the current network.

If there is no backup, it may be troublesome and the cost of retrieving data is also very high.

Here's how to use mysqlbinlog to retrieve data after the backup time point:

Do a simple experiment, delete the mysql table data, and then use mysqlbinlog to retrieve the data of the table just deleted.

The creation time of the app table and the insertion of data: 2013-02-04 10:00:00

Principle: mysqlbinlog

Prerequisite: mysql has bin log enabled

Before testing deletion:

mysql> show tables;
+-----------------------+
| Tables_in_report_sina |
+-----------------------+
| app |
| test |
+-----------------------+

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-02-04 11:45:44 |
+---------------------+
1 row in set (0.01 sec)


mysql> select count(1) from app;
+----------+
| count(1) |
+----------+
| 10 |
+----------+
1 row in set (0.01 sec)

Start deleting data:

mysql> delete from app where id =1;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> delete from app where id < 6;
Query OK, 4 rows affected (0.01 sec)


mysql> select count(1) from app;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)

 

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-02-04 12:08:45 |
+---------------------+

Start retrieving data:

1. Find the location of the bin log:

/app/mysql/log

-rw-rw---- 1 mysql mysql 17K Feb 4 11:43 alert.log
-rw-rw---- 1 mysql mysql 1.0K Nov 1 14:52 master-bin.000001
-rw-rw---- 1 mysql mysql 126 Dec 25 14:00 master-bin.000002
-rw-rw---- 1 mysql mysql 126 Dec 25 14:02 master-bin.000003
-rw-rw---- 1 mysql mysql 126 Dec 25 14:02 master-bin.000004
-rw-rw---- 1 mysql mysql 107 Dec 25 14:02 master-bin.000005
-rw-rw---- 1 mysql mysql 13K Feb 4 12:02 master-bin.000006

You can see that the only recently modified bin log is master-bin.000006

(If the accidental deletion spans several bin logs, you must retrieve the data one by one when retrieving the bin logs.)

Save all SQL statements executed during this period into the SQL file to be restored.

mysqlbinlog --start-date='2013-02-04 10:00:00' --stop-date='2013-02-04 12:08:45' /app/mysql/log/master-bin.000006 >/app/mysql/mysql_restore_20130204.sql

Of course, in the current network environment, this time may not be so accurate, and there may be interference from other transaction SQL statements.

Creating a temporary database

create database for_bak;

Export the tables that were accidentally deleted in the current database app

mysqldump -uroot -ppwd my_db app > /app/mysql/app.sql

Import the current data into a temporary table:

mysql -root -ppwd for_bak < /app/mysql/app.sql

Let's take a look at part of the content of /app/mysql/mysql_restore_20130204.sql: (You can see the evil delete statement)

SET TIMESTAMP=1359949544/*!*/;
BEGIN
/*!*/;
# at 12878
#130204 11:45:44 server id 1 end_log_pos 12975 Query thread_id=5 exec_time=974 error_code=0
SET TIMESTAMP=1359949544/*!*/;
delete from app where id =1
/*!*/;
# at 12975
#130204 11:45:44 server id 1 end_log_pos 13002 Xid = 106
COMMIT /*!*/;
# at 13002
#130204 11:45:44 server id 1 end_log_pos 13077 Query thread_id=5 exec_time=1013 error_code=0
SET TIMESTAMP=1359949544/*!*/;
BEGIN
/*!*/;
# at 13077
#130204 11:45:44 server id 1 end_log_pos 13175 Query thread_id=5 exec_time=1013 error_code=0
SET TIMESTAMP=1359949544/*!*/;
delete from app where id < 6
/*!*/;
# at 13175
#130204 11:45:44 server id 1 end_log_pos 13202 Xid = 107
COMMIT /*!*/;
DELIMITER ;
# End of log file

You can see when the data was deleted. The specific time can also be queried using select from_unixtime(1359949544);

Fortunately, the create table app statement and the insert statement are also in this file. After manually removing the delete statement, source the sql file retrieved from mysqlbinlog in the temporary database

This will restore the app to the state it was in before it was deleted. Then import the data in the temporary library into the existing network data (this is not the focus of this article).

If there is no backup, it may be very troublesome to retrieve all the data related to the app table, especially when there are many binlog files and each one is relatively large.

In that case, the only way is to use mysqlbinlog to retrieve the SQL records of DML operations related to the app table one by one from the creation of the app to the present, and then integrate and restore the data.

I think this situation is generally rare. Although it is troublesome, it is not impossible to recover.

The above article on how to recover accidentally deleted table data in MySQL (must read) is all I want to share with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.