Recover MySQL After Accidental Drop Using LVM Snapshots and Binlog

Combining snapshot and replication techniques provides a real‑time online MySQL backup solution; when the master suffers an accidental operation, you can restore the snapshot on the standby and then perform point‑in‑time recovery using the binary log.

Scenario

Master‑slave architecture with no replication delay. An inexperienced DBA mistakenly executes DROP DATABASE cnfol on the master.

1. Prepare test data on the master

mysql> create database cnfol;
Query OK, 1 row affected

mysql> create table cnfol.t (id int primary key);
Query OK, 0 rows affected

mysql> insert into cnfol.t select 1;
Query OK, 1 row affected

mysql> insert into cnfol.t select 2;
Query OK, 1 row affected

2. Verify data on the backup

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cnfol              |
| mysql              |
| test               |
+--------------------+

mysql> select * from cnfol.t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+

3. Apply a global read lock on the backup

mysql> flush tables with read lock;
Query OK, 0 rows affected

4. Create an LVM snapshot of the backup partition

# lvcreate --size 1G --snapshot --name backup_mysql /dev/vg/mysql
Logical volume "backup_mysql" created

# lvs
LV           VG   Attr   LSize Origin Snap%  Move Log Copy%  Convert
backup_mysql vg   swi-a- 1.00G mysql    0.00
mysql        vg   owi-ao 2.00G

5. Capture the binary‑log coordinates

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003| 727      |              |                  |
+------------------+----------+--------------+------------------+

6. Unlock tables

mysql> unlock tables;
Query OK, 0 rows affected

7. Mount the snapshot

# mount /dev/vg/backup_mysql /mnt/backup
# cd /mnt/backup/mysql/data/cnfol/ && ls -alh
-rw-rw---- 1 mysql dba   61 Oct 14 09:57 db.opt
-rw-rw---- 1 mysql dba 8.4K Oct 14 09:57 t.frm
-rw-rw---- 1 mysql dba   14 Oct 14 09:57 t.MYD
-rw-rw---- 1 mysql dba 2.0K Oct 14 10:06 t.MYI

8. Accidental drop on the master

mysql> drop database cnfol;
Query OK, 1 row affected

Timestamp recorded: 2013‑10‑14 10:17:10

9. Verify the backup still contains the database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

10. Archive the snapshot

# cd /mnt/backup
# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *

11. Remove the snapshot

# umount /mnt/backup
# lvremove --force /dev/vg/backup_mysql
Logical volume "backup_mysql" successfully removed

12. Re‑format and mount the original partition

# mysqladmin -uroot -poracle shutdown
# umount /mnt/lvm
# mkfs -t ext3 /dev/vg/mysql
# mount /dev/vg/mysql /mnt/lvm
# lvs
LV   VG   Attr   LSize Origin Snap%  Move Log Copy%  Convert
mysql vg   -wi-ao 2.00G

13. Extract the snapshot archive to the reformatted partition

# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/
# ls /mnt/lvm
lost+found  mysql

14. Start MySQL and perform point‑in‑time recovery

# mysqlbinlog --stop-datetime="2013-10-14 10:17:10" /mnt/lvm/mysql/data/mysql-bin.000003 | mysql -uroot -poracle

15. Confirm data restoration

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cnfol              |
| mysql              |
| test               |
+--------------------+

mysql> select * from cnfol.t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+