How to retrieve the data of a table accidentally deleted by mysql (mandatory)

How to retrieve the data of a table accidentally deleted by mysql (mandatory)

It is easy to back up data. You only need to generate the latest backup data and use mysqlbinlog to retrieve the data after the backup time point and restore it to the current network.

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

The following describes how to use mysqlbinlog to retrieve data after the backup time point:

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

App table creation time and data insertion: 10:00:00

Principle: mysqlbinlog

Prerequisites: mysql has enabled bin log

Before the test is deleted:

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 to delete 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. Locate the bin log location:

/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

We can see that the recently modified bin log only has master-bin.000006

(If you accidentally delete data that has been retrieved across several bin logs, You must retrieve the bin logs one by one)

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

Mysqlbinlog -- start-date = '2017-02-04 10:00:00 '-- stop-date = '2017-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 with other transaction SQL statements.

Create temporary database

Create database for_bak;

Export the accidentally deleted table app in the current database

Mysqldump-uroot-ppwd my_db app>/app/mysql/app. SQL

Import the current data to the temporary table:

Mysql-root-ppwd for_bak </app/mysql/app. SQL

Let's take a look at part of/app/mysql/mysql_restore_20130204. SQL: (we 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=0SET TIMESTAMP=1359949544/*!*/;delete from app where id =1/*!*/;# at 12975#130204 11:45:44 server id 1 end_log_pos 13002 Xid = 106COMMIT/*!*/;# at 13002#130204 11:45:44 server id 1 end_log_pos 13077 Query thread_id=5 exec_time=1013 error_code=0SET 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=0SET TIMESTAMP=1359949544/*!*/;delete from app where id <6/*!*/;# at 13175#130204 11:45:44 server id 1 end_log_pos 13202 Xid = 107COMMIT/*!*/;DELIMITER ;# End of log file

You can see when the data is deleted. You can also use select from_unixtime (1359949544); to query the specific time.

It is gratifying that the create table app statement and insert statement are also in this file. After manually removing the delete statement, run the SQL file source mysqlbinlog in the temporary database.

Then, the app is restored to the status before it is deleted. Then import the data from the temporary database to the current network (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 the large number of binlog files and each of them is relatively large.

In this case, only the SQL records of dml operations related to the app table are retrieved one by one using mysqlbinlog from the app creation to the present, and then the data is integrated and restored.

I think this is usually rare. Although it is troublesome, it cannot be recovered.

The above mysql Data Retrieval Method (which is mandatory) for table deletion by mistake is all the content shared by Alibaba Cloud. I hope you can give us a reference and provide more support to the customer center.