How to recover deleted data from mysql database

Accidentally deleted the database and want to restore the data, In addition to backup, there are also the following methods.

How to recover a large amount of data when mysql sometimes executes an incorrect update or delete. No transaction was opened during execution, and no data was processed. At this time, you need to use the sqlbinlog tool.

sqlbinlog needs to be opened, the specific opening method will not be mentioned

Using sqlbinlog will generate bin files, and these files are needed for recovery. All operations of the database are recorded in the file. (The operation of this method is to re-execute the statement previously executed by the database to achieve the recovery effect)

Specific steps:

1, first find bin files are usually in the data folder of mysql and end with .00000X or other forms.

2, find the time point that needs to be restored, use the statement mysqlbinlog file name, for example (MySQLbinlog xxbin.000001) to view the content, and then find the corresponding specific time

3, export the sql statement, use the statement mysqlbinlog file name>sql file path Example (mysqlbinlog xxxbin,00001>>a.sql | mysql -u root -p )

If you need to specify the time to export--start--date -stop='' --date='' to export statement examples executed at the specified time (sqlbinlog --start-stop='2015-11-22 10:00:00' xxbin.000001>a.sql | mysql -u root -p ) this The sentence means that the sentence is exported before 10 o'clock on 2015-11-22. On the contrary, start is after the export time. start and stop can be used at the same time.

If there are multiple bin files, export them as needed.

4, use mysql to execute the exported statement once.

The above is the detailed content of How to recover deleted data from mysql database. For more information, please follow other related articles on the PHP Chinese website!