How to restore newly deleted table data in mysql

Quickly restore a newly deleted table: use the FLUSH LOGS and ALTER TABLE commands. Deep recovery of long-deleted tables: meet conditions (binary log enabled, event logging), determine event log location, dump log, edit SQL file, apply SQL file.

How to restore just deleted table data in MySQL

Quick recovery

If the table has just been deleted, you can use the following command to restore it immediately:

<code>mysql> FLUSH LOGS;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> ALTER TABLE table_name RENAME TO table_name_backup;</code>

Deep recovery

If the table has been deleted for a long time, you can use MySQL binary log recovery data. This requires the following:

• Binary logging is enabled.
• Event occurrences are recorded in the binary log file.
• Have replication permissions (SUPER or REPLICATION CLIENT).

Steps

1. Determine the event binary log location: Use the SHOW BINARY LOGS command to find the location containing Binary log file and location of the event.

2. Dump the binary log: Use the mysqlbinlog tool to dump the binary log into a SQL file:

   <code>mysqlbinlog --start-position=start_position --stop-position=stop_position binary_log_file > dump.sql</code>

3. Edit SQL file: Open the dumped SQL file, find the DROP TABLE table_name statement, and replace it with the CREATE TABLE table_name statement.

4. Apply the SQL file: Execute the edited SQL file to recreate the table and restore the data:

   <code>mysql -u root -p your_db < dump.sql</code>

Note Issue

• The recovered data may not contain any changes made since the table was deleted.
• The binary log can only recover events recorded in it. If the binary log is incomplete, recovery may be incomplete.
• Before performing recovery operations, make sure you have backed up the database.

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