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.
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.
Create a temporary 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 &
Converting 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.
Loading SQL Files to 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
Restoring the Database to the 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.
Conclusion
Accidental database drops can cause chaos, but armed with the power of MySQL binary logs, recovery is achievable. This step-by-step guide illuminates the process of point-in-time restoration, ensuring that lost data finds its way back to your main MySQL instance. With logical and physical backup insights, you’re now equipped to tackle database recovery challenges effectively.