Incremental MySQL Server Backup via Binary Log

Making backups is necessary, but doing this with large databases may involve high server load and using up storage space. And only creating a backup once a day or once a week could lead to losing hours of work. So how can you make backups more often without overloading the server? Incremental backups can help you with this.

An incremental backup only contains information that has changed after the previous backup was created. This substantially reduces the size of backups and allows you to make such backups very often. Creating incremental backups allows point-in-time recovery.

Various DBMSs offer different mechanisms for incremental backups. In Oracle, this can be done through RMAN, in SQL Server through a differential and transaction log backup and in PostgreSQL it can be done via WAL file backup.

In MySQL, you can implement the creation of incremental backups through a backup of binary log files. All transactions applied to the MySQL Server are sequentially written to binary log files. Hence, you can always restore the original database from these files.

Below you will find step-by-step instructions for setting up incremental backups, as well as a service that can do it for you.

Creating Incremental Backups with Shell Commands

1. Find the MySQL Server configuration file

The first thing to do is enable binary logs on the MySQL Server. To do this, you need to find the configuration file mysql.conf. It is usually located here:

vi /etc/mysql/mysql.cnf

It is possible that mysql.conf file itself will not contain configurations, but instead, it may contain links to directories containing specialized configuration files. For example, in version 5.7, the file with the binary log configurations is located here: /etc/mysql/mysql.conf.d/mysqld.cnf.

If you cannot locate the mysql.cnf file, then use the find command:

sudo find / -type f -name mysql.cnf

2. Enable binary log

The configuration file must contain a [mysqld] section, most of the options in this section will be commented out (starting with a # character). Find the parameters server_id, log_bin, max_binlog_size, expire_logs_days, and uncomment them.

When you add max_binlog_size, expire_logs_days parameter, you need to take into account the rate at which data is written to your database and how often you will make full backups. If you want to backup only some databases instead of the entire server, then the binlog_do_db and binlog_ignore_db parameters can come in handy. Uncomment them if necessary.

After saving the file, you need to restart the MySQL Server for the changes to take effect:

sudo service mysql restart

3. Initializations of binary logs

Each incremental copy contains changes that have been created since the last backup, but the very first backup must be a full backup. You need to create a full backup via mysqldump using the ––flush-log and ––delete-master-logs parameters. ––delete-master-logs will delete old binary log files, and ––flush-log will initialize writing a new binary log file. Also .sql file compresses very well, so it’s better to compress it on the go.

sudo mysqldump --flush-logs --delete-master-logs --single-transaction --all-databases | gzip > /var/backups/mysql/$(date +%d-%m-%Y_%H-%M-%S)-inc.gz

4. Copying accumulated binary log files

You can use the cp command to copy binary log files, but copying the active binary log file (to which data is being written right now) may cause some problems. Therefore, you need to run the FLUSH BINARY LOGS command, which will start writing to a new binary log file. In this case, all the accumulated binary log files can be safely copied. After copying the binary log files, they must be deleted so that during the next copying they do not duplicate the already backed up data. The PURGE BINARY LOGS command will help with this. Below is a small script that performs these actions and also puts binary log files in the archive. Specify the path to the directory of binary log files that you specified in mysql.conf and the path to the folder for storing incremental backups.

#path to directory with binary log files
binlogs_path=/var/log/mysql/
#path to backup storage directory
backup_folder=/var/backups/mysql/
#start writing to new binary log file
sudo mysql -E --execute='FLUSH BINARY LOGS;' mysql
#get list of binary log files
binlogs=$(sudo mysql -E --execute='SHOW BINARY LOGS;' mysql | grep Log_name | sed -e 's/Log_name://g' -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')
#get list of binary log for backup (all but the last one)
binlogs_without_Last=`echo "${binlogs}" | head -n -1`
#get the last active binary log file (which you do not have to copy)
binlog_Last=`echo "${binlogs}" | tail -n -1`
#form full path to binary log files
binlogs_fullPath=`echo "${binlogs_without_Last}" | xargs -I % echo $binlogs_path%`
#compress binary logs into archive
zip $backup_folder/$(date +%d-%m-%Y_%H-%M-%S).zip $binlogs_fullPath
#delete saved binary log files
echo $binlog_Last | xargs -I % sudo mysql -E --execute='PURGE BINARY LOGS TO "%";' mysql

This small script will create an archive of new binary log files at startup with a name containing the date.

5. Regularly performing incremental backups

Doing only manual backups is unwise, because if you forget one day to perform the backup, that may be the day that you needed it the most. You can schedule regular backups using cron:

crontab -e

You need to schedule both incremental backups and full backups. When choosing time intervals, remember that the less often you make full backups, the longer it will take to recover. A good solution would be to run a full backup every night and an incremental backup every hour.

0 0 * * * sudo mysqldump --flush-logs --delete-master-logs --single-transaction --all-databases | gzip > /var/backups/mysql/full_$(date +%d-%m-%Y_%H-%M-%S).gz 
*/60 * * * * sudo bash ~/scripts/mysql_inc_backup.sh

Restoring incremental backups

Note that even if you have set up regular backups according to the instructions above, you MUST test database restoration.

The above section describes how to create full and incremental backups. In the examples given, the backups are added to an archive with a name containing the date. This is useful when you need to restore the MySQL database as of a specific date. You need to find the incremental backup preceding this date, and the full backup preceding this date. These two files, and any incremental backups that were created between them, are needed for restoration.

We need the mysqlbinlog utility for restoration. It comes with the MySQL Server.

After you have copied the files that you need for restoration to a separate directory, you can proceed to restoration:

1. Prepare the server for restoration
   a. Make sure there are no databases on the server bearing same names as the backups
   b. Turn off binary log. If you do not do this, then all restored data will land in binary log files
2. Restore full backup. Restoring a full backup is as easy as creating a full backup. This can be done directly from the archive using the MySQL utility:

   sudo gunzip < 01-10-2020_20-08-41-full.gz | sudo mysql -u root

3. Restore incremental backups. Unzip all archives with binary log files into a separate directory and go to it

   unzip \*.zip -d logs 
   cd logs

Unlike a full backup created with mysqldump, binary log files contain binary data. Before restoring them, they need to be converted to sql expressions, and the mysqlbinlog utility is responsible for this. This utility receives binary log files as input, and returns sql statements.

You can simply list binary log files in command parameters and transfer them via piping to MySQL:

mysqlbinlog mysql-bin.000090 mysql-bin.000099  mysql-bin.000143 | sudo mysql -u root

Note that the order in which binary log files are indicated is important. Indicate them in ascending order.

You can use the following command if you have a lot of incremental backups:

mysqlbinlog $(ls) | sudo mysql -u root

SqlBak

SqlBak is a service that creates backups and sends them to cloud storage. SqlBak can also make incremental backups via MySQL binary logs.

To start making incremental backups using SqlBak, follow these steps:

1. Create an account at sqlbak.com
2. Install a daemon on the server with a MySQL instance:

   curl -sSL https://sqlbak.com/download/linux/latest | sudo bash -s <secret key>

   You can get a secret key from the download page after registration. Installation via the classic packet manager is also available. More details are here.

3. Add a connection to the MySQL Server

   sudo sqlbak -ac -dt mysql -u root

   This completes the work in the terminal. Further configuration is done on the website

4. Go to the Dashboard page and create a job
   
5. Choose databases you want to backup
   
6. Choose backup destination
   You can choose a local folder, FTP, DropBox, S3, Azure Storage, Google Drive, etc.
   
7. Schedule regular backups
   Here you can set times for full and incremental backups.
   
8. Start the job for the first time to make sure that everything works smoothly.

SqlBak not only allows you to perform incremental backups, but also contains a lot of other useful options: email notifications, encryption and automatic restoration to another server. To restore an incremental backup, you do not need to select a list of necessary files. You just select the date you want to restore and press the button.

Bottom Line

An incremental backup of a MySQL server using binary log files is extremely useful but requires care. Be careful and be sure to test the restore.

You can create incremental backups using the set of shell scripts described above, but a solution for protecting critical data must include many more points.

• Sending backup to cloud storage
• Emailing notification about a successful job done
• Encrypting the data
• Deleting old backups from time to time
• Saving the runtime log

This can also be implemented through shell scripts, but it will require significant development/testing/support. Instead, you can use SqlBak, which implements all of the above features and also boasts a number of additional options.