Physical backup (full backup, incremental backup, differential backup)

  • Full backup: refers to backing up the data and structure of the entire database and generating a full backup file. In subsequent backups, the full backup can be used as a baseline backup for incremental or differential backups.

  • Incremental backup: refers to backing up changes that have occurred since the last backup, so that only these changes are backed up, not all data and structures. Incremental backup must be based on the last full backup or incremental backup, and cannot be directly based on the last incremental backup. Multiple incremental backups can form a backup chain, where each incremental backup depends on the previous backup. If any backup in the backup chain is lost or corrupted, the latest data cannot be restored.

  • Differential backup: It is a special incremental backup method, which refers to backing up changes that have occurred since the last full backup. Unlike incremental backup, differential backup only needs to be based on the last full backup and does not need to rely on previous backups. Differential backup is faster to restore than incremental backup, but it requires more hard drive space.

1. Install xtrabackup

Official website: www.percona.com

2. Full backup

Create backup directory: mkdir /xtrabackup/full -p

Backup: innobackupex –user=root –password=123’ /xtrabackup/full

Full backup and recovery process:

1. Stop the database: systemctl stop mysqld

2. Clean the environment: rm -rf /var/lib/mysql/* or rm -rf /var/log/mysqld.log or rm -rf /var/log/mysql-slow/slow.log

3. Repeat the rollback and restore data: innobackupex -apply-log /xtrabackup/full/2023-10-07_11-47-49

Before restoring, you need to confirm that the database directory is specified in the configuration file: /etc/my.cnf datadir=/var/lib/mysql

Restore data: innobackupex –copy-back /xtrabackup/full/2023-10-07_11-47-49

4. Modify permissions: chown -R mysql.mysql /var/lib/mysql

5. Start the database: systemctl start mysqld

3. Incremental backup process

Principle: Each time you back up the new data generated from the last backup to now

1. Full backup: Monday

[root@mysql-server ~]# rm -rf /xtrabackup/*
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2023-08-20_14-51-35
[root@mysql-server xtrabackup]# cd 2019-08-20_14-51-35/
[root@mysql-server 2023-08-20_14-51-35]# ls
backup-my.cnf ib_buffer_pool mysql sys testdb xtrabackup_info
company ibdata1 performance_schema test xtrabackup_checkpoints xtrabackup_logfile

2. Incremental backup: Tuesday — Wednesday

Insert Tuesday’s data into the database:
mysql> insert into testdb.t1 values(2); #Simulating Tuesday
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2023-08-20_14-51-35/
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2023-08-20_14-51-35 2023-08-20_15-04-29 ---Equivalent to Tuesday’s incremental backup
Insert Wednesday’s data into the database:
mysql> insert into testdb.t1 values(3); #Simulation Wednesday
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2023-08-20_15-04-29/ # Directory based on previous day's backup
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2023-08-20_14-51-35 2023-08-20_15-04-29 2023-08-20_15-10-56 ---Equivalent to Wednesday’s incremental backup
Check the backup directory:
[root@mysql-server ~]# ls /xtrabackup/
2023-08-20_14-51-35 2023-08-20_15-04-29 2023-08-20_15-10-56
    Full Monday, Incremental Tuesday, Incremental Wednesday

Incremental backup and recovery process
1. Stop the database
2. Clean up the environment
3. Repeat the rollback of redo log in sequence–>restore data
4. Modify permissions
5. Start the database
6. Binlog recovery

[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
Repeat the rollback redo log in sequence:
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-08-20_14-51-35
Tuesday Wednesday
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-08-20_14-51-35 --incremental-dir=/xtrabackup/2023-08-20_15-04-29
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-08-20_14-51-35 --incremental-dir=/xtrabackup/2023-08-20_15-10-56 /
Data recovery:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2023-08-20_14-51-35/
Modify permissions
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld
Log in and take a look:

4. Differential backup

Create a differential backup directory

Monday full backup: innobackupex –user=root –password=123’ /home/wz

Tuesday incremental backup: innobackupex –user=root –password=’123′ –incremental /home/zl/ –incremental-basedir=/home/wz/2023-08-20_14-51- 35/

Wednesday incremental backup: innobackupex –user=root –password=’123′ –incremental /home/zl/ –incremental-basedir=/home/zl/2023-08-20_15-04- 29/

Thursday differential backup: innobackupex –user=root –password=’123′ –incremental /home/cy –incremental-basedir=/home/wz/2023-08-20_14-51-35 /

Differential backup and recovery process

1. Stop the database
2. Clean up the environment
3. Replay the rollback redo log (Monday, a certain difference) –> restore data
4. Modify permissions
5. Start the database
6. Binlog recovery

Stop the database and clean up the environment

1. Restore the full redo log: innobackupex –apply-log –redo-only /home/wz/full backup directory (Monday)

2. Restore differential redo log: innobackupex –apply-log –redo-only /home/wz/full backup directory (Monday) –incremental-dir=/home/cy/Thursday differential backup

3. Restore data: innobackupex –copy-back /home/wz/full backup directory (Monday)

4. Modify permissions: chown -R mysql.mysql /var/lib/mysql

syntaxbug.com © 2021 All Rights Reserved.