Restore / Recover dropped MySQL database from binary logs

Restore / Recover dropped MySQL database from binary logs

In this post I will share a recovery scenario of a MySQL database restore from the binary logs.

Recently someone accidentally dropped an important MySQL database and the backup was not present!

As we know the the binary log contains DMLs to table data and that’s where our hope lies. Luckily the binary log retention period (expire_logs_days) was set to 30 days and the CREATE DATABASE was still present in the binary logs.

Thus this restore of the dropped MySQL database can be done from extracting respective DMLs point-in-time. Here we will play the binary logs against a temporary MySQL instance and extract the required database.

Let’s begin the recovery of database.

Kick-off the MySQL instance:

– We will quickly bring up a temporary MySQL instance to start the restore from binary log.

# Prepare data-directory
mkdir /var/lib/mysql3307

# Setup mysql user & group permission 
chown -R mysql:mysql /var/lib/mysql3307

# Prepare the configuration
cp /etc/my.cnf /etc/my3307.cnf

# Initialize MySQL data directory and create system tables
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3307 --defaults-file=/etc/my3307.cnf

# Start MySQL instance
mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &

Convert binary logs to sql:

– We will need to convert the binary logs to SQL to be loaded The create database syntax was found in bin.000005 while drop was in bin.000008.

# To note here the MySQL version is 5.1.73 and default binlog format is STATEMENT.
mysqlbinlog bin.000005 > bin.000005.sql
mysqlbinlog bin.000006 > bin.000006.sql
mysqlbinlog bin.000007 > bin.000007.sql
mysqlbinlog bin.000008 > bin.000008.sql

– Removed lines from “DROP DATABASE” command onwards from bin.000008.sql and prepared for load.

Load binlogs to newly created temporary MySQL instance:

mysql -uroot --socket=/var/lib/mysql/mysql3307.sock --force < bin.000005.sql 1> 5.sql.log 2>&1
mysql -uroot --socket=/var/lib/mysql/mysql3307.sock --force < bin.000006.sql 1> 6.sql.log 2>&1
mysql -uroot --socket=/var/lib/mysql/mysql3307.sock --force < bin.000007.sql 1> 7.sql.log 2>&1
mysql -uroot --socket=/var/lib/mysql/mysql3307.sock --force < bin.000008.sql 1> 8.sql.log 2>&1

Here –force will make sure the sql execution will continue to load despite of any errors.

Connected and verified the database required is available and is in acceptable condition.

Backup required database to restore:

mysqldump -uroot -p --socket=/var/lib/mysql/mysql3307.sock --databases dropped-database | gzip > dump.sql.gz

Restore to main MySQL instance:

zcat dump.sql.gz | mysql -uroot -p 

Database restore in case of backups available:

– In case we have a logical backup available with us, a mysqldump, we can restore a single database by extracting it from the full MySQL dump.
We can extract the single database from the mysql dump using mysql-dump-splitter as follows:

./mydumpsplitter.sh fulldump-filename.sql -d database-to-restore

– For the case of physical backup say xtrabackup, we will have to do complete restore and extract the required database.

alright so that ends our quest of database restore from an accidental drop. Hope this helps.