Incremental MySQL Backup with Binary Log

Keeping your MySQL server backup as current as possible is important. I’ve seen (and have done this myself) people backup using mysqldump once a day, week, or month without any backups in-between. There are a few problems with backing up your database this way. The first problem is that running mysqldump is very slow on large databases and could lockup your website for a significant portion of time. Secondly, you can lose all data that occurred since the previous backup. The third problem is that you cannot restore data from a specific point in time. If your application starts corrupting data after an update, you would have to restore from the last known backup instead of being able to restore data right up until the problem started to occur.

What is the Binary Log

The binary log is a file that stores all queries performed by the database that alter or create new records. The binary log is used in database replication to keep the slave servers in sync with the master server. Binary logs can also be used as a backup method for your database that is more frequent and faster than performing large dumps on a schedule.

There is also an index file used to keep track of binary logs that is required for database replication. The index file tracks what binary logs exist. Slave servers use the indexes of the logs to know where to start syncing data from.

Configuring Binary Log

To start using the binary log you need to modify or add a few lines to your MySQL configuration. The MySQL configuration location can vary based on distribution, but you can generally find them in the following locations. These files may also import other configuration files and directories.

/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf

Find the configuration file or create a new one that will be included by the main configuration files and add the following lines. These lines must be inside the section [mysqld]. The server_id is likely to already be set by the configuration file.

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M

You need to restart your MySQL server before the changes take affect.

Initial Backup

The binary log only stores queries as they happen and will not contain past data inside of the database. It’s important to create an initial backup of your database and then flush logs to start the binary log after the backup to prevent backing up the same data twice.

You can choose the method used for the initial backup, such as performing a physical backup or to use mysqldump to create a logical backup. There are pros and cons to each method (read about them here), but in general mysqldump is safer, easier, but at the cost of being slower.

$ mysqldump --flush-logs --lock-all-tables --all-databases > backup.sql

The passed argument of --lock-all-tables will have the affect of dumping the database and flushing the logs at the same moment. This prevents the binary log from possibly having queries that already are present in the dump. The --flush-logs option flushes the database logs before (or at the same time) as the dump. Flushing the database logs is important to start a new log with the most recent database dump.

Backup Binary Log

Once the database has been configured with the log_bin option, the binary log will be stored by default in your data directory (somewhere around /var/lib/mysql) unless you specified an absolute path; in that case you know where the binary log is located. Every time the MySQL server flushes the server logs (timed events, log sizes, or manually), a new binary log will be created with an extension with an increased integer.

To backup the binary log, you want to save all of the binary logs that are generated. Using the command cp is fine for copying binary log files excluding the active binary log. It’s important to keep the files in the same order since they are stored sequentially and must be restored sequentially.

You can force MySQL to start a new binary log by running the command FLUSH BINARY LOGS. A new binary log will be created and the past one can now be copied and added to your backup.

Deleting old Binary Logs

Deleting binary logs can become complicated depending on if you have database replication configured. If you don’t have database replication configured you can safely delete old binary logs, preferably with the command mysqlbinlogpurge (available in the package mysql-utilities).

If you have database replication and slave servers, you need to ensure that all of the slave servers (online and offline) are up to date or you will have to spend additional time syncing slave servers off of backups. Going over the whole process is out of the scope of this tutorial but the MySQL documentation has a post on How to purge obsolute binary log files safely.

Restoring Backups

To restore from your backups, the first step is to restore the dump file created into an empty database. This is done by redirecting the contents of the sql dump to MySQL.

$ mysql -u username -p database < database.sql

After the dump is now inside the database, you want to import every binary log you have in order. Binary logs generally contain information from multiple databases and will require a higher level of permissions to import.

$ mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

You can get more information about restoring binary logs from the post Point in Time Recovery on the MySQL website.