How to Recover Accidentally Deleted MySQL Databases: A Step‑by‑Step Guide

A Sad ERP Data Loss Story

During an ERP system upgrade, a third‑party developer asked for the SA password, later mistakenly initialized the database, wiping years of financial and supply‑chain data. The author spent three days in the server room restoring the lost data, highlighting the severe impact of missing backups.

MySQL Accidental Deletion Recovery Guide

Regular backups are essential; however, human error can still drop a database. The following procedure restores MySQL data using a nightly full backup and binary logs.

Recovery Workflow

Identify the full backup SQL file that contains a CHANGE MASTER statement and note the binlog file and position.

Export the relevant portion of the binlog with mysqlbinlog and remove any DROP statements.

Apply the full backup, then replay the cleaned binlog SQL to recover incremental changes.

Verify the restored tables and ensure the server is back to the state before the accidental drop.

Key Commands and Options

mysqldump -u root -p --all-databases --master-data=2 > full_backup.sql

(‑B database, ‑F flush logs, ‑R include routines, ‑‑master-data adds CHANGE MASTER).

Use

mysqlbinlog --start-position=… --stop-position=… binlog_file

to extract needed statements.

Before restoring, move the binlog out of the data directory to prevent new writes during recovery. After applying the full backup, replay the incremental SQL, omitting any harmful statements.

Finally, confirm that the target database (e.g., ops) exists and that all data between the backup and the drop has been restored.