How to Set Up MySQL Incremental Backups?

• EndNote

1. Install MySQL Server 8

Install the MySQL database server version 8 by running the following command -

apt-get install mysql-server -y

Now that the MySQL server is installed, you can start the MySQL service. Enable it to start at system reboot usign these commands -

systemctl start mysql

systemctl enable mysql

2. Enable Binary Logging

To perform incremental backups, you have to enable binary logging. You can enable it by editing the MySQL default configuration file -

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

Add or modify the following lines based on your preference.

• Log_bin = /var/log/mysql/mysql-bin.log

• expire_logs_days = 10

Save and close the file. You can now restart the MySQL service to apply the changes. Use the following command -

systemctl restart mysql

Check the MySQL binary log directory path with the following command.

ls -l /var/log/mysql/

Check the MySQL binary log file in the following output.

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

In the output, you can see the mysql-bin.000001 is a MySQL binary log file. All changes in the MySQL databases will be stored in this file.

3. Create a Database and Table

We now create a test database and table. We will insert some rows in the table.

Connect to MySQL with the following command -

mysql

After you are connected, create a database with the name mydb. Use the command shown below -

mysql> CREATE DATABASE mydb;

Change the database to mydb and create a new table with the name my_tbl

mysql> USE mydb;
mysql> create table my_tbl(
my_id INT NOT NULL AUTO_INCREMENT,
my_field VARCHAR(100) NOT NULL,
submission_date DATE,
time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ( my_id )
);

You can now add some rows using the following command -

mysql> INSERT into my_tbl (my_field) VALUES ('val1');
mysql> INSERT into my_tbl (my_field) VALUES ('val2');
mysql> INSERT into my_tbl (my_field) VALUES ('val3');

Exit from the MySQL shell with the command below -

mysql> exit;

4. Take a Full MySQL MySQL Backup

take a full backup of the current MySQL database. You can do it with the following command:

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

• --flush-logs will close current logs (mysql-bin.000001). It creates a new one (mysql-bin.000002).

You can check the new MySQL binary log file with the following command:

ls -l /var/log/mysql/

You should see the following output -

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

Now, all database changes will be writen in mysql-bin.000002 file.

Next, login to MySQL again and insert more rows:

mysql> USE mydb;
mysql> INSERT into my_tbl (my_field) VALUES ('val4');
mysql> INSERT into my_tbl (my_field) VALUES ('val5');
mysql> INSERT into my_tbl (my_field) VALUES ('val6');
mysql> exit;

We now have new database changes saved in the file mysql-bin.000002 after the full backup.

5. Take an Incremental MySQL Backup

For incremental backups only, flush the binary log again and save binary logs created from the last full backup.

To flush the binary log, use the following command -

mysqladmin -uroot -p flush-logs

This will close the mysql-bin.000002 file and create a new one. You can check the result with the following command -

ls -l /var/log/mysql/

You will see the following output -

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

You can also check the current state of the table. Use the following command -

mysql
mysql> use mydb;
mysql> select * from my_tbl;

You can see the following output -

6. Delete a MySQL Database

Login to MySQL again and delete a mydb database. You can perform this with the following command -

mysql> drop database mydb;

Create a mydb database again using the command shown below-

mysql> create database mydb;
mysql> exit;

Restore the database from the full_backup.sql. Use the following command -

mysql -u root -p mydb < full_backup.sql

Login to MySQL shell and check the content of the table -

mysql
mysql> use mydb;
mysql> select * from my_tbl;

You should see just three rows -

mysql> exit;

Next, you have to restore data from the binary log saved in the mysql-bin.000002 file.

Run the following command to restore the incremental backup.

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

Login to MySQL again and check the content of the table:

mysql
mysql> use mydb;
mysql> select * from my_tbl;

You should see that all rows are restored -

Exit from the MySQL using the following command -

mysql> exit;

EndNote

Database backups prevent data loss in case of system failures or application bugs.

Performing incremental backups everyday save storage space as it only stores the updated changes.

It uses fewer resources and is a recommended solution for cloud backups.

We have covered how to set up MySQL incremental backups.

To learn more about backups and cloud hosting, check out CloudPanel Tutorials.