Incremental MySQL Server Backup via Binary Log

Making backups is important and necessary. However, if your database is large, then creating a backup will load the server, and the backup itself will consume storage space. In light of these considerations, many make backups once a day at night, or even worse, once a week on weekend! However, a failure can lead to losing data for a period of many hours. What if you want to make backups more often without overloading the server? Incremental backups can help you with this.

An incremental backup 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. In PostgreSQL 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 MySQL Server are sequentially written to binary log files. Hence, you can always restore the original database from these files.

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

Creating Incremental Backups with Shell Command

  1. Find the MySQL Server configuration file

The first thing to do is enable binary logs on 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 mysql.cnf file, then use the find command:

sudo find / -type f -name mysql.cnf

  1. 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 instead the entire server you want to backup only some databases, then binlog_do_db and binlog_ignore_db parameters can come in handy. Uncomment them if necessary.

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

sudo service mysql restart
  1. 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 --lock-all-tables --all-databases | gzip > /var/backups/mysql/$(date +%d-%m-%Y_%H-%M-%S)-inc.gz
  1. Copying accumulated binary log files

You can use cp command to copy binary log files, but copying 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 logs 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, 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.

  1. Regularly performing incremental backups

Creating a manual backup is crazy, one day you will forget about it and it is on this very day that you will need data. 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. As a rule, 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 --lock-all-tables --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, for example, to another server.

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 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 mysqlbinlog utility for restoration. It comes with MySQL Server.

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

  1. Preparing the server for restorationa.
    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 backupRestoring a full backup is as easy as creating a full backup. This can be done directly from the archive using MySQL utility:
    sudo gunzip < 01-10-2020_20-08-41-full.gz | sudo mysql -u root
  3. Restoring incremental backupsUnzip 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 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 demon to server with 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 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 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 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 MySQL server using binary log files is extremely useful but has many fine points. Be careful and be sure to test 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 job done successfully
  • Encrypting the data
  • Deleting old backups from time to time
  • Saving 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.

Leave a Reply

Your email address will not be published. Required fields are marked *