Full and incremental backups are the two types of backups.
·
A full backup
contain all the data.
·
Data that has
changed since the last backup is saved using an incremental backup process.
This article
will explain how to use Mysqldump and Binary log to take incremental backups of
MySQL.
1. Install MySQL Server 8
Use the following
command to install MySQL database server version 8.
apt-get install mysql-server
You can start the MySQL
service at this point after installing the MySQL server. Use these instructions
to enable it to start when the system reboots.
systemctl start mysql
systemctl enable mysql
2. Enable Binary Logging
You must enable binary
logging in order to execute incremental backups. Editing the default
configuration file for MySQL will enable it.
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 -
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;
7. Restore the database from the full_backup
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;
- Restore data from the binary log
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;
0 Comments