MySQL Incremental Backups

by

There are two types of people: people who do back up their files, and people who need to start doing it.

If you are not backing up your MySQL databases yet, you should start before you lose your precious data.

Some people think that they don’t need a backup because they have replication. However, bear in mind that replication protects against hardware failures, while backups protects against human errors.

What do we mean by this? Imagine your disgruntled colleague decides to delete the entire database. But even if you are in a dream team, and anyone who has access to the MySQL Server is always in a good mood, you are still not protected from unintentional errors. Say you want to delete old data, but instead you accidentally delete your current data. Or a bug pops up in the program that connects to your MySQL Server and corrupts the data in the database. In the case of replication, this action will simply spread to all slave servers.

Backups allow you to avoid such issues. However, there’s a catch. Backing up a large MySQL database can overburden the server. Therefore, such a backup is usually best done at night, which, unfortunately, means that in the event of a failure, you lose data for the entire day.

So what is the solution? Point-in-time backup, sometimes known as incremental backup, comes to the rescue. Incremental backups are backups that copy only the data that has changed since the previous backup. They can be done frequently, as they are small in size and do not overload the server.

Different databases offer different implementations of incremental backups. In SQL Server, this is a differential and transaction log backup. In Oracle databases, this can be done through RMAN. In PostgreSQL, it is implemented through a backup of WAL files.

In MySQL, there are multiple ways to create an incremental backup. In addition, there are turnkey solutions that allow you to automate the process. Before moving on to considering how to make incremental backups, remember that incremental backups do not eliminate the need for full backups.

Full and Incremental Backups

An incremental backup contains all the changes that have occurred to the database since the last backup. That is, to restore an incremental backup for a specific date, you will need not only the incremental backup itself but also the previous backup, which in turn can also be an incremental backup, and so on. Although this list can be very large, the very first backup should be a full backup, which does not depend on other backups.

Indeed, there is a temptation to do one full backup, and then make only incremental backups. However, this is very dangerous. If at least one file in the backup set is damaged, then the entire recovery chain is lost. Also, you need to factor in the fact that the bigger the backup set the more time it will take to recover.

Therefore, even if you make incremental backups, you will need to make full backups from time to time.

Utilities and Tools for Incremental Backups

Ultimately, a backup is a file or set of files. There are three ways in MySQL to create incremental backups. Below is an overview of each of them.

Binary logs

MySQL Server sequentially writes all changes that have occurred in the database to binary log files. Binary logs are included across the entire server in mysql.cnf

After activating binary logs, files of the type mysql-bin.<Bin log name> will appear in the directory specified in the configuration. You can extract SQL statements from binary logs files using the mysqlbinlog utility.

If you apply the extracted SQL statements sequentially to the database, you can restore the database to any specific date.

To restore you need a full backup created using mysqldump with the ––flush-logs parameter and all binary logs files that were created after that.

Binary log backup is essentially just copying files. However, problems may arise when copying the active binary log file (into which data is being written at the moment). More details on how to set up a regular binary log backup on Linux can be found here. To enable the bin logs on Windows please check the following article.

Binary log backup has a number of advantages over other types of backups:

  1. Compressed binary log files take up very little space
  2. Using the ––verbose parameter, you can even extract SQL statements and edit them before restoring
  3. Binary log backup is very fast because it’s just copying files. However, this does not apply to full backups.

All this makes binary log backup a good solution. However, this method has a few disadvantages:

  1. Complex management for copying binary log files. It is necessary to copy only new files and you cannot copy the file to which data is being written at the moment. In addition, there are many unobvious nuances, for example when restoring a database all instructions for creation will get into the binary log file.
  2. Incremental backup restore time. Sequential execution of SQL statements from binary logs takes time. To reduce the potential restore time, it is necessary to perform a full backup more often. In this case, the backup set will be smaller.
  3. If the server uses replication, the process performing the backup can delete the binary log that is needed for replication.
  4. For large databases, creating a full backup via mysqldump takes a lot of time.

An alternative to manual binary log file backup is the SqlBak service that monitors the correctness of file copying and does not interfere with replication.

Xtrabackup

Xtrabackup is an open-source utility from Percona for MySQL Server backups. Unlike mysqldump and mysqlbinlog, which create an sql script, Xtrabackup makes a physical backup by copying the database files. Usually, to copy database files, you need to turn off MySQL Server, but the peculiarity of Xtrabackup is that it can copy live database files.

On top of full backups, Xtrabackup allows you to make incremental backups. An incremental backup is created on the basis of another backup (which must be physically stored somewhere in the file system).

The backup, depending on which incremental backup is created, can be either full or another incremental backup. This allows you to make not only incremental backups but also a differential backup, whose restoration will always require two files: an incremental backup and a full backup on the basis of which it was made.

Backup made with Xtrabackup has the following pros:

  1. Fast restore time
  2. Fast full backup even for a large database

Creating a backup via Xtrabackup is easier, and restoration is faster than via MySQL binary logs.

However, there are some cons too:

  1. To create a backup, you need to have a previous backup on the server, which takes up disk space.
  2. You cannot make changes to backup before data restoration.
  3. You need to disable MySQL Server before backup restoration.
  4. Xtrabackup creates a physical backup that does not protect database files from corruption (corrupted files will be copied into backup).
  5. Difficulties in creating a partition backup.

If you are working not with classic MySQL, but with MariaDB, you can use the mariabackup fork.

Note, that Percona XtraBackup can only be installed on Linux. However, there is a workaround for using it with Windows. To achieve this you have to use Docker. To perform a backup, you need to run the xtrabackup container and map the directory with the database files to a directory inside the container /opt/mysql/data. Please find more details on how to do it in the following blog post.

MySQL Enterprise Backup

MySQL Enterprise Backup is a solution offered by Oracle which is part of MySQL Enterprise Edition. This edition comes with the mysqlbackup utility that allows you to perform backup and restore operations.

When creating an incremental backup using mysqlbackup, you need to specify values for the ––incremental-base parameter. LSN (Log Sequence Number) must be specified in this parameter. This is the starting point for backup. The LSN value is returned after the backup is done. Alternatively, you can specify the value history: last_backup, and mysqlbackup will insert END LSN of the previous backup.

If you specify LSN of full backup, you will actually create a differential backup.

MySQL Enterprise backup is very similar to Xtrabackup, it can be run both on Linux and Windows. In addition, there are other differences – for example, for mysqlbackup, you just need to specify the LSN number, while for Xtrabackup, you need to save the previous backup on disk. More detailed comparison is here.

For many, the disadvantage may be that this product is not free. And if you only need incremental backups, then buying MySQL Enterprise Edition for mysqlbackup utility may be overkill.

SqlBak

Whichever way you create incremental backups, the main challenge is the management of incremental backups and not the command that creates the backups.

If you create a backup via binary logs, then you need to make sure that binary log files are copied in the correct sequence. For Xtrabackup, you need to keep the previous backup close at hand, and then replace it with another. To backup using MySQL Enterprise Backup, you need to track LSN.

Moreover, creating a backup is not even half the battle. You need to send it to cloud storage and delete outdated backups in time so that the space in backup storage does not run out.

All this can be handled by SqlBak. SqlBak is a service that creates SQL instance backups and sends them to cloud storage. One of the features of the service is creation of incremental MySQL Server backups based on binary logs.

In SqlBak you can create a backup job consisting of the following steps:

  1. Backup creation
  2. Compression
  3. Encryption
  4. Sending to cloud storage (like Amazon S3, S3 Compatible, Dropbox, Google Drive, OneDrive, OneDrive for Business, Azure Storage, Box, Backblaze B2, Yandex.Disk).
  5. Backup testing, such as test restore
  6. Email notification
  7. Removal of old backups

SqlBak is available on both Windows and Linux. To get started with SqlBak you need to install and connect the SqlBak daemon to the server using the instruction from the “Download” page. If you use SqlBak on Linux, then it can be done with one command:

If you use a Windows version, then just install a tiny SqlBak app on your server and set the connection to sqlbak.com and your MySQL Server:

Once this is done, you can create a job and indicate the steps that must be performed.

The incremental backup option can be enabled while editing your backup job schedule settings:

Then you can save and run the job.

If you have multiple servers, SqlBak also helps manage backups on all servers from one panel.

SQLBackupAndFTP

SQLBackupAndFTP is our other backup tool that also allows to back up your databases according to the selected schedule and send the backup to the specified destination. To start backup MySQL Server with SQLBackupAndFTP you need to install the app and set a simple backup job. The latest release of SQLBackupAndFTP can be downloaded at the “Download” page. Once the app is downloaded and installed you can create a backup job following the simple steps one by one.

Firstly set a connection, choose databases that should be backed up and destination places:

The next step is to create a backup job schedule. During this setting, you can enable/disable incremental backups performing

Those are the main settings you’ll need to create an automatically backed up job. Below you can find other useful options such as email notifications, encryption and compression:

Conclusion

Regardless of how you do your backups, make sure to test your restore script regularly. The point is not to make a backup, but to perform a restore when everything goes south. Incremental backups are more complex than classic full backups and therefore require thorough testing. Whether you are using SqlBak or writing your own scripts, make sure to check your restore script from time to time.

1 thought on “MySQL Incremental Backups”

Leave a Comment