How to restore deleted table in mysql? Mysql method to retrieve data from accidentally deleted table

The editor below will bring you an articlemysql How to retrieve data from an accidentally deleted table (a must-read). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

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

It may be more troublesome if there is no backup, and the cost of retrieving data is also very high.

The following is an introduction to mysqlbinlog retrieving data after the backup time point. Method:

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

Creation time of app table and data insertion

Principle: mysqlbinlog

Premise: mysql opens bin log

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 recently modified bin log is only master-bin.000006

（ If the deletion spans several bin logs by mistake, you must retrieve the bin logs one by one when retrieving the data)

Store all the SQL statements executed during this period into the SQL file to be recovered. middle.

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 will be interference from other transaction SQL statements.

Create temporary database

create database for_bak;

Export 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 the temporary table:

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

Let’s take a look at some of the contents of /app/mysql/mysql_restore_20130204.sql: (Yes 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 at what point in time the data was deleted. You can also use select from_unixtime(1359949544); to query the specific time

What is gratifying is that 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 library and restore the app to the

state before it was deleted. Then import the data from the temporary database to the live 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 since there are many binlog files and each one is relatively large.

In that case, we can only 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 relatively rare. Although it is troublesome, it is not impossible to recover.

The above is the detailed content of How to restore deleted table in mysql? Mysql method to retrieve data from accidentally deleted table. For more information, please follow other related articles on the PHP Chinese website!