brief introduction

MySQL database backup can be divided into logical backup and physical backup. The logical backup tools are mysqldump and the physical backup tools are xtrabackup. The two backup methods have their own advantages and disadvantages

Use scenario
1) When the data volume is large and the business impact is small, if there is enough space to save the backup data, select xtrabackup
2) When the data volume is small and there is no large amount of disk space to store backup data, mysqldump is selected
3) When the downtime is short, use xtrabackup for backup

preparation in advance

Prepare two Centos7 virtual machines, configure IP address and hostname, synchronize system time, turn off firewall and selinux, configure IP address and hostname mapping

Install MySQL database
Note: the MySQL version installed on this machine is 8.0.17

#Get the yum source of mysql from the official website
[root@master ~]# yum install mysql mysql-server -y
[root@bak ~]# yum install mysql mysql-server -y

master node creates database and adds data

mysql> create database test_db;
mysql> use test_db;
mysql> create table test(id int primary key);
mysql> insert into test values(1);
mysql> insert into test values(2);
mysql> insert into test values(3);
mysql> insert into test values(4);
mysql> insert into test values(5);
mysql> insert into test values(6);
mysql> insert into test values(7);

MySQL tools

master backup data

[root@master ~]# mysqldump -uroot -p test_db > /tmp/a.sql

#Transfer sql file to bak node machine
[root@master ~]# scp /tmp/a.sql  root@192.168.29.138:/tmp/

bak node recovery data

mysql> create database test_db;
mysql> use test_db;
mysql> source /tmp/a.sql

#View recovery
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

xtrabackup tool

install

[root@master ~]# yum install epel -y
[root@master ~]# yum install xtrabackup -y

Full backup

master for full backup

[root@master ~]# xtrabackup --backup --user='root' --password='your_password'  --target-dir=/backup/full

#Transfer to bak machine
[root@master ~]# scp -r /backup/full/ root@192.168.29.138:/tmp/

bak for backup recovery

#You need to clear the datadir first
[root@bak ~]# rm -rf /var/lib/mysql/*
#Backup recovery
[root@bak ~]# xtrabackup --copy-back  --target-dir=/tmp/full/ --datadir=/var/lib/mysql/
#Modify datadir permissions
[root@bak ~]# chown -R mysql.mysql /var/lib/mysql
#Restart mysql service
[root@bak ~]# systemctl restart mysqld

Verify backup recovery results

mysql> select * from test_db.test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.03 sec)

Incremental backup

Add data as appropriate

mysql> insert into test values(8);
mysql> insert into test values(9);

Incremental backup

[root@master ~]# xtrabackup --backup --user='root' --password='your_password' --target-dir=/backup/inc1/  --incremental-basedir=/backup/full/

Compare data sizes for full and incremental backups

[root@master ~]# du -h /backup/full
231M    full
[root@master ~]# du -h /backup/inc1/
14M /backup/inc1/

Restore backup

#Delivery folder
[root@master ~]# scp -r /backup/inc1/ root@192.168.29.138:/tmp/
#Clean up datadir
[root@bak ~]# rm -rf /var/lib/mysql/*

#Prepare to restore full backup
[root@bak ~]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/full/
#Prepare incremental backup
[root@bak ~]# xtrabackup --prepare --target-dir=/tmp/full/ --incremental-dir=/tmp/inc1/
#Restore backup
[root@bak ~]# xtrabackup --copy-back  --target-dir=/tmp/full/ --datadir=/var/lib/mysql/
#Modify permission
[root@bak ~]# chown -R mysql.mysql /var/lib/mysql
#Restart service
[root@bak ~]# systemctl restart mysqld

Test verification

mysql> select * from test_db.test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
9 rows in set (0.00 sec)