incremental backup in mysql

How to Do Incremental Backup in MySQL Using Binary Log

Database administrators need to regularly backup their database to ensure they can easily recover their data in case it is lost or corrupted. But it can be time-consuming to take full backup of database every time, especially if your database is large. Luckily, MySQL allows you to do incremental backup, whereby it takes a backup of the incremental changes since your last backup. It saves lot of space and system resources to do an incremental backup in MySQL. In this article, we will learn how to do incremental backup in MySQL using binary log.


How to Do Incremental Backup in MySQL Using Binary Log

Here are the steps to do incremental backup in MySQL. If you get ‘permission denied’ message for any of the commands below, add sudo keyword at its beginning and try again.


1. Install MySQL

Open terminal and run the following command to install latest MySQL version. If it is already installed on your system, you can skip this step.

$ apt-get install mysql-server -y

During installation, you will be asked to set root password, and may also see some prompts. Enter password of your choice, and enter yes for prompts. After you install MySQL, run the following commands to start server and enable it to autostart during system reboot.

$ systemctl start mysql
$ systemctl enable mysql


2. Enable Binary Logging

Next, you need to enable binary logging to take incremental backup. Open MySQL configuration file in a text editor.

$ vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add/modify the following lines to look as below.

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10

Here we specify log location for binary log and also the number of days after which it expires. Save and close the file.

Restart MySQL server to apply changes.

$ systemctl restart mysql

You can check if the binary logs are being created using the following command.

$ ls -l /var/log/mysql/

You will see the following output.

-rw-r----- 1 mysql adm   6117 Jan 20 09:13 error.log
-rw-r----- 1 mysql mysql  156 Jan 20 09:13 mysql-bin.000001
-rw-r----- 1 mysql mysql   32 Jan 20 09:13 mysql-bin.index

All changes in your MySQL database is stored in file mysql-bin.000001 binary log file.


3. Take Full Backup of Database

Next, run the following command to take full backup of your database.

$ mysqldump -uroot -p --all-databases --single-transaction --flush-logs --master-data=2 > full_backup.sql

Now if you check out your log file location

$ ls -l /var/log/mysql/

You will see the following output.

-rw-r----- 1 mysql adm    6117 Jan 20 09:13 error.log
-rw-r----- 1 mysql mysql  2036 Jan 20 09:25 mysql-bin.000001
-rw-r----- 1 mysql mysql   156 Jan 20 09:25 mysql-bin.000002
-rw-r----- 1 mysql mysql    64 Jan 20 09:25 mysql-bin.index

Here mysql-bin.000002 is a binary log file where all database changes are saved.


4. Take Incremental Backup

To take incremental backup, you need to flush the binary logs and save binary logs created from last full backup.

$ mysqladmin -uroot -p flush-logs

It will close mysql-bin.000002 and create new file mysql-bin.000003. You can check it in the folder location containing binary logs.

ls -l /var/log/mysql/

-rw-r----- 1 mysql adm   6117 Jan 20 09:13 error.log
-rw-r----- 1 mysql mysql 2036 Jan 20 09:25 mysql-bin.000001
-rw-r----- 1 mysql mysql 1097 Jan 20 09:27 mysql-bin.000002
-rw-r----- 1 mysql mysql  156 Jan 20 09:27 mysql-bin.000003
-rw-r----- 1 mysql mysql   96 Jan 20 09:27 mysql-bin.index


5. Restore Database from Incremental Backup

Now to restore database from incremental backup, we create a new database.

mysql> create database mydb;
mysql> exit;

Next, we populate the new database using the full backup file.

$ mysql -u root -p mydb < full_backup.sql

At this point if you view the records in your database, it will not contain the incremental changes made after full backup taken earlier.

For this purpose, run the following command that will analyze the binary log for incremental changes and add them to your new database mydb.

$ mysqlbinlog /var/log/mysql/mysql-bin.000002 | mysql -uroot -p mydb

Now if you check your database, you will find it also includes incremental changes made after the full backup.

In this article, we have learnt how to create incremental backups in MySQL.

Also read:

How to Pass SSH Password in Shell Script
MySQL Change Storage Engine from InnoDB to MyISAM
How to Install Fonts in Ubuntu
How to Increment & Decrement Variable in MySQL
How to Get Current Directory of Shell Script

Leave a Reply

Your email address will not be published.