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
– 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<\/b><\/a> as follows:<\/p>\n\n\n\n.\/mydumpsplitter.sh fulldump-filename.sql -d database-to-restore\n<\/pre>\n\n\n\n– For the case of physical backup say xtrabackup, we will have to do complete restore and extract the required database.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[400,398,952,403,402,953],"class_list":["post-2021","post","type-post","status-publish","format-standard","category-mysql","category-mysql-articles","tag-accidantal-drop","tag-mysql-database-restore","tag-mysql-point-in-time-recovery","tag-mysql-recovery","tag-mysql-restore","tag-restore-point-in-time-mysql"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t