Recover a Dropped MySQL Database with Binlog and Full Backup Steps

Work Scenario

In daily operations a MySQL database is backed up nightly at 00:00. One morning a colleague accidentally executed DROP DATABASE, deleting a database that must be restored immediately using the nightly full backup and the incremental binlog files.

Recovery Idea

Identify the binlog file and position that contain the incremental changes after the full backup.

Use mysqlbinlog to export those binlog entries to an SQL file, removing any DROP statements.

Apply the full backup SQL file first, then apply the cleaned incremental SQL to bring the database back to the state just before the drop.

Step‑by‑Step Example

1. Ensure binlog is enabled in /etc/my.cnf under the [mysqld] section.

2. Restart MySQL service.

3. Create a test table in the ops database.

4. Perform a full backup with mysqldump using options:

-B database -F -R -x --master-data

5. Insert additional data, then accidentally drop the test database.

6. Locate the binlog file generated after the full backup (e.g., mysql-bin.000002 at line 106) and move it aside.

7. Export the relevant binlog to SQL and edit out any DROP statements.

8. Restore the full backup first, then apply the cleaned incremental SQL file.

9. Verify that the data created between the full backup and the accidental drop has been recovered.

Key Takeaways

This method applies to accidental SQL errors or situations without master‑slave replication.

Binlog must be enabled and both full and incremental data must be available.

During restoration, stop external updates to avoid new binlog entries.

Restore the full backup first, then sequentially apply cleaned incremental logs.