Creating MySQL Full Database Backups for Recovery Operations using mysqldump and Binary Logs

Introduction

This article will assist MySQL Database Administrator’s with taking full database backups of databases containing innodb tables. After reading the article below, you should have a better understanding on how to apply binary logs for point-in-time and full recovery operations on a MySQL database that has been restored using a mysqldump dump file.

Disclaimer

Disclaimer: All sample code and MySQL parameters provided below is for illustrative purposes only. These examples have not been thoroughly tested under all conditions and I cannot guarantee or imply reliability, serviceability, or function of the programs parameter settings listed below.

Setting Up Binary Logging

The first step when taking backups of a MySQL database is to make sure that binary logging is turned on in the configuration file (my.cnf) of the database by setting the log-bin parameter:

log-bin=/var/log/mysql/mysql-bin.log

Backing Up MySQL with mysqldump

The next step is to take a mysqldump database backup of the database environment. To make sure that you are able to do a full recovery of your database using the binary logs, it is very important that you include the –master-data and –single-transaction options. An example of a  mysqldump with these parameters set correctly would be:

mysqldump -u$DB_USER -p=$DB_PASSWD --master-data=2 --single-transaction > mysql_db.dmp

The master-data and single-transaction options of mysqldump

The master-data option will provide the binary log position output that the database administrator will need when applying binary logs to a database restored using a mysqldump. Without this parameter set, you may miss some of the transactions between the time that the mysqldump was completed and the time that the binary logs started writing information.

The single-transaction option dumps the entire database as part of one transaction so that you get a consistent view of mysql so that you can begin applying transactions with the database objects all consistent at the same change point.

MySQL Locking during mysqldump

The requirement of dumping the entire database in one consistent-view transaction can be problematic when it comes to locking for very large databases so the MySQL DBA may want to consider setting up replication for the purposes of backing up the production environment and taking the mysqldump backups against the MySQL slave.

MySQL Sample Backup Script

Below is a sample Bash Script that you can test and utilize at your own discretion to backup MySQL:

##############
#!/bin/bash

DB_BACKUP=”/backups/mysql_backup/hourly/`date +%Y-%m-%d-%k`”
DB_USER=”root”
DB_PASSWD=”password”
HN=`hostname | awk -F. ‘{print $1}’`

# Create the backup directory
mkdir -p $DB_BACKUP

# Option 1: Backup each database on the system using a root username and password
for db in $(mysql –user=$DB_USER –password=$DB_PASSWD -e ‘show databases’ -s –skip-column-names|grep -vi information_schema);
do mysqldump –user=$DB_USER –password=$DB_PASSWD –master-data=2 –single-transaction –opt $db | gzip > “$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d-%k).gz”;
done
##############

Recovering a MySQL Database using a mysqldump and Binary Logs

Import mysqldump file

To restore and recover a MySQL database, you will need to import the mysqldump file using a command like the following (warning: this will overwrite your existing database):

mysql -u user -ppassword mysql_database < dump_file.sql

Determine Binary Log Position

Open up the mysqldump file that you just imported into your MySQL environment using a text editor (ie. vi; more) and towards the very top of the dump file will be the binary log and start position that you will need to use to being applying the binary logs. It will look like the example below:

#############

--
-- Position to start replication or point-in-time recovery from
--

– CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000043′, MASTER_LOG_POS=1050370582;

#############

The MASTER_LOG_FILE is the binary log that you will need to apply first to MySQL and the MASTER_LOG_POS parameter is the start position that you will need to apply to your MySQL database server.

Apply Binary Logs to your MySQL Database Server

The final step is to apply the binary logs to your MySQL database server using the mysqlbinlog utility along with the –start-position option where you will take the numerical value returned in the MASTER_LOG_POS section of your mysqldump and use that value as the –start-position of your binary log recovery.

mysqlbinlog --start-position=1050370582 mysql-bin.000043 | mysql -uuser -ppassword database_name

The example above will take the contents of the mysql-bin.000043 binary log and apply them to the restored mysql database beginning with position 1050370582.

Multiple Log Files in MySQL Database Recovery

If you have multiple binary log files that you need to use to recover your database, you can specify them all on the same line. For our example above, if there were two additional binary log files written after the mysql-bin.000043 file, we could include them as:

mysqlbinlog --start-position=1050370582 mysql-bin.000043 mysql-bin.000044 mysql-bin.000045| mysql -uuser -ppassword database_name

MySQL Point-In-Time Recovery

If you would like to stop the recovery at a certain point, you can either specify a stop position that you have identified within the mysqldump file or use a date and time. This could occur if there were some bad transactions against your database that you wanted to eliminate from being applied.

The example syntax for specifying an end point using the –stop-position option for mysqlbinlog using our example above is:

mysqlbinlog --start-position=1050370582 --stop-position=2170789743 mysql-bin.000043 mysql-bin.000044 mysql-bin.000045| mysql -uuser -ppassword database_name

To specify an end time in your point-in-time recovery, you would specify the –stop-datetime option in mysqlbinlog:

mysqlbinlog --start-position=1050370582 --stop-datetime="2013-07-31 3:45:00" mysql-bin.000043 mysql-bin.000044 mysql-bin.000045| mysql -uuser -ppassword database_name

Tags: , , ,

One Response to “Creating MySQL Full Database Backups for Recovery Operations using mysqldump and Binary Logs”

  1. Akash June 29, 2015 at 9:01 pm #

    This worked great thnaks! Just one note: I had to unzip the .sql.tar.gz for it to work with no errors. Maybe obvious to experienced users but figured I’d throw it in just in case.

Leave a Reply