How to implement MYSQL backups: Full and Incremental backup using Binary Logs ( bin-logging )

Terms

A full backup contains all the data of the database, therefore the very first backup should be a full backup of the database. An incremental backup contains all the changes that have occurred to the database since the last backup. Database changes are recorded/written on a binary log which we copy in the correct sequence to recover all the changes.

Importance of backups

1. Having periodic backups enables us to rewind the clock by reloading the previous database. If something breaks or fails, this acts as a lifeline for the system.
2. Data versioning. This is when different versions of the database are available to go back to, therefore critical changes that later prove to break the system can be undone by restoring older versions without the problem.

Let’s dive in ??

We will cover the following:

• Writing full and incremental backup scripts
• Configuring backup Cron Job
• Writing backups download scripts into your local machine
• Restore the backups

Prerequisites for this tutorial

This tutorial will be a hands-on demonstration therefore expected to be equipped with the following:

• Basic knowledge of Linux terminal commands
• Basic understanding of MYSQL commands

Enable binary logging

As discussed said earlier, Database changes (incremental) are recorded/written on a binary log, therefore will need to enable binary logging. By default it is disabled. It's a matter of uncommenting a few lines. Use your preferred editor, ( I will use nano) to open a mysqld.cnf file

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf        

When you open the file, scroll to the bottom and uncomment the key: value pairs as you see below: Just to point out a few ;

• log_bin: defines, the directory where the logs are dumped and the format of the logs.
• binlog_do_db: the database you what to enable logging for. To enable logging for all the databases, leave the line commented.

Restart mysql systemd service to enable binary logging.

$ sudo systemctl restart mysql.service         

Writing Full and incremental backup scripts

To remind you as discussed before, each incremental copy contains changes that have been created since the last backup, but the very first backup must be a full backup.

Now that you have the database configured and with test data, first create a full backup for the testdb using MySQL's inbuilt command: mysqldump with parameters; — delete-master-logs (deletes old binary log files ) and — flush-log ( initializes writing a new binary log file )

It is also a good idea to compress the output especially if the database size is very large, simply pipe the output to the gzip utility.

Since we hope to be running this regularly (at least once a week ) using a cron job, create a bash script with the mysql dump command as below. Will save the script as mysql_full_backup.sh First, create a directory where all the backups will be stored.

$ mkdir dbbackup        

#!/bin/bash 

mysqldump -u<user> -p<userpassword> --flush-logs --delete-master-logs testdb | gzip > /var/backups/mysql/$(date +%d-%m-%Y_%H-%M-%S)-full.gz        

as a root user:

#!/bin/bash

mysqldump --flush-logs --delete-master-logs testdb | gzip > dbbackup/$(date +%d-%m-%Y_%H-%M-%S)-full.sql.gz        

Now make the bash script executable using the command below;

$ sudo chmod +x mysql_full_backup.sh         

Now run the bash script to create the full backup and ensure the zipped file containing the backup has been created.

$ sudo ./mysql_full_backup.sh        

Create incremental backups from accumulated binary logs

Copy the script below and save it as mysql_inc_backup.sh Let me point out the command used in the script below;

• FLUSH BINARY LOGS: allows to safely copy all the accumulated binary log files. It allows copying the active binary log file (to which data is being written right now) by starting to write to a new binary log file.
• PURGE BINARY LOGS: deletes the old binary so that during the next copying they do not duplicate the already backed-up data.

NB: I assume you’ll run the script as a root user. To run it as another user you’ve created, remember to add username and password after the mysql command ie; sudo mysql -uadmin -padmin1234 -E …

#!/bin/bash

#path to directory with binary log files
binlogs_path=/var/log/mysql/

#path to backup storage directory
backup_folder=dbbackup/increment/

#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)-inc.zip $binlogs_fullPath

#delete saved binary log files
echo $binlog_Last | xargs -I % sudo mysql -E --execute='PURGE BINARY LOGS TO "%";' mysql        

Now make the bash script executable using the command below;

$ sudo chmod +x mysql_inc_backup.sh        

Now run the bash script to create the full backup and ensure the zipped file containing the backup has been created.

$ sudo ./mysql_inc_backup.sh        

Configuring backup Cron Job

To be able to create periodic backups means you will be logging into the server daily and manually running the script above. Doing such same task over and over again can be quite a chore, this is when a cron job becomes important. Scheduling cron jobs let users automate tasks on a Unix-like operating system. This saves precious time, allowing users to focus on other essential tasks. Run the first command provided below to open a text editor in the terminal as a root user. Use sudo so that the commands in the sudo’s cron table are executed as a root user.

$ sudo crontab -e        

Copy and paste the scripts below in the text, and exit to save. The cron jobs will be running the backup scripts weekly — for full backups, and daily — for incremental backups.

# Do full backup weekly
@weekly sudo bash /scripts/mysql_full_backup.sh

# Do Incremental daily
@daily sudo bash /scripts/mysql_inc_backup.sh        

Writing Backup download scripts.

Now that you have your backups you may need to download them to your local computer. Use scp — a Linux inbuilt command to copy the zipped backups to your local machine backup directory ( /dbbackup ).

$ mkdir dbbackup
$ gcloud compute scp <VM_NAME>:dbbackup/* dbbackup        

Unzip the downloaded file to check the contents. For full backup file, you should be able to see all the data. For incremental file, it comes as a bin log file which you can convert to a SQL to view the data. Remember it will be only the new data that was added.

Use mysqlbinlog utility which converts the binary log files containing binary data to SQL expressions. Below is the command below.

mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 > inc_backup.sql;        

NB: Running the above command on the local machine may throw some errors. Therefore, the other option is to do it on the remote server and then you download the converted sql file.

Restore Backups from the scripts.

To restore full backups, use the command below.

$ gunzip < 07-09-2022_13-15-58-full.gz | sudo mysql -u root        

Restore Incremental backups

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

$ unzip \*.zip -d logs 
$ cd logs        

Convert the binary log files to SQL expressions as done in previous section. The order in which binary log files are indicated is important. Indicate them in ascending order.

$ mysqlbinlog mysql-bin.000015 | sudo mysql -uadmin -padmin1234        

Use the command below, if you have a lot of incremental backups:

mysqlbinlog $(ls) | sudo mysql -uadmin -padmin1234        

Conclusion

To summarize below is what we have covered.

1. Writing full and incremental backup scripts
2. Configuring backup Cron Job for auto backups
3. Writing backups download scripts into your local machine
4. Restore the backups