In this post I will share a recovery scenario of a MySQL database restore from the binary logs. This post is also a good example of how we can achieve point in time recovery using binary logs.<\/p>\n\n\n\n
Recently someone accidentally dropped an important MySQL database and the backup was not present!<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Let’s begin the recovery of database.
<\/p>\n\n\n\n\n\n\n\n
– We will quickly bring up a temporary MySQL instance to start the restore from binary log.<\/p>\n\n\n\n
# Prepare data-directory\nmkdir \/var\/lib\/mysql3307\n\n# Setup mysql user & group permission \nchown -R mysql:mysql \/var\/lib\/mysql3307\n\n# Prepare the configuration\ncp \/etc\/my.cnf \/etc\/my3307.cnf\n\n# Initialize MySQL data directory and create system tables\nmysql_install_db --user=mysql --basedir=\/usr --datadir=\/var\/lib\/mysql3307 --defaults-file=\/etc\/my3307.cnf\n\n# Start MySQL instance\nmysqld_safe --defaults-file=\/etc\/my3307.cnf --user=mysql &\n<\/pre>\n\n\n\nConverting Binary Logs to SQL<\/h3>\n\n\n\n
– 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.<\/p>\n\n\n\n
# To note here the MySQL version is 5.1.73 and default binlog format is STATEMENT.\nmysqlbinlog bin.000005 > bin.000005.sql\nmysqlbinlog bin.000006 > bin.000006.sql\nmysqlbinlog bin.000007 > bin.000007.sql\nmysqlbinlog bin.000008 > bin.000008.sql\n<\/pre>\n\n\n\n– Removed lines from “DROP DATABASE” command onwards from bin.000008.sql and prepared for load.<\/p>\n\n\n\n
Loading SQL Files to Temporary MySQL Instance<\/h3>\n\n\n\n
mysql -uroot --socket=\/var\/lib\/mysql\/mysql3307.sock --force < bin.000005.sql 1> 5.sql.log 2>&1\nmysql -uroot --socket=\/var\/lib\/mysql\/mysql3307.sock --force < bin.000006.sql 1> 6.sql.log 2>&1\nmysql -uroot --socket=\/var\/lib\/mysql\/mysql3307.sock --force < bin.000007.sql 1> 7.sql.log 2>&1\nmysql -uroot --socket=\/var\/lib\/mysql\/mysql3307.sock --force < bin.000008.sql 1> 8.sql.log 2>&1\n<\/pre>\n\n\n\nHere –force will make sure the sql execution will continue to load despite of any errors.<\/p>\n\n\n\n
Connected and verified the database required is available and is in acceptable condition.<\/p>\n\n\n\n
Backup required database to restore<\/h3>\n\n\n\n
mysqldump -uroot -p --socket=\/var\/lib\/mysql\/mysql3307.sock --databases dropped-database | gzip > dump.sql.gz\n<\/pre>\n\n\n\nRestoring the Database to the Main MySQL Instance<\/h3>\n\n\n\n
zcat dump.sql.gz | mysql -uroot -p \n<\/pre>\n\n\n\n<\/h3>\n\n\n\n
Database restore in case of backups available<\/h3>\n\n\n\n