How to Recover Deleted Database

Immediate Actions

# 1. Stop writes to database
sudo systemctl stop mysql

# 2. Backup current data directory
sudo cp -r /var/lib/mysql /var/lib/mysql_backup

Recover from Binary Logs (MySQL)

If binary logging enabled:

# Find binary logs
sudo ls -la /var/log/mysql/mysql-bin.*

# Show events around drop time
sudo mysqlbinlog /var/log/mysql/mysql-bin.000123 | grep -i "drop database"

# Recover from point before drop (using position)
sudo mysqlbinlog --stop-position=123456 /var/log/mysql/mysql-bin.000123 | mysql -u root -p

Recover from Daily Backups

# Find your backup files
ls -la /backup/
# Or
ls -la ~/backups/

# Restore MySQL
sudo mysql -u root -p < /backup/database_backup.sql

# Restore PostgreSQL
sudo -u postgres psql database_name < /backup/db_backup.sql

Using Undrop Tool (MySQL)

# Install undrop
git clone https://github.com/twindb/undrop-for-innodb.git
cd undrop-for-innodb
make

# Scan for deleted data
./stream_parser -f /var/lib/mysql/ibdata1
./c_parser -4Df pages-ibdata1/FILENAME -t dictionary/SYS_TABLES.sql

Recover from Filesystem (Last Resort)

# Check if table files still exist
ls -la /var/lib/mysql/database_name/

# If .ibd files exist but table missing
# Recreate table structure then import .ibd
ALTER TABLE tablename IMPORT TABLESPACE;

Prevention

# Set up automated backups
# MySQL
0 2 * * * mysqldump --all-databases > /backup/mysql_$(date +%Y%m%d).sql

# Enable binary logs
# In my.cnf:
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7