mysql incremental backup xtrabackup (innobackupex) mode

mysql incremental backup xtrabackup (innobackupex) mode

1. First, make a full backup of the database.
Current database table data.


Database full backup

innobackupex --user=admin  --password=123456  /full_xtrabackup

2. Modify database content, incremental backup for the first time,
increase data id=3, id=4 for

Insert picture description here


the first incremental backup

 innobackupex --user=admin --password=123456  --incremental /incre_back --incremental-basedir=/full_xtrabackup/2021-06-02_12-00-52/

3. Modify the database content, incremental backup for the second time,
increase data id=5, id=6,

Insert picture description here


incremental backup for the second time

 innobackupex --user=admin --password=123456  --incremental /incre_back2 --incremental-basedir=/incre_back/2021-06-02_12-00-52/
Insert picture description here


4. Manufacturing failure

 rm -rf /usr/local/mysql/data/*

At this time, the database instance is started and the data can be accessed, but there are no more tables in the database.
Close mysql

pkill -9 mysqld

5. Backup file preprocessing
Preprocess the full backup file

innobackupex --apply-log --redo-only --user=admin  --password=123456 /full_xtrabackup/2021-06-02_12-00-52/

The first incremental backup is applied to the full backup

 innobackupex --user=admin --password=123456  --apply-log --redo-only  /full_xtrabackup/2021-06-02_13-08-27/  --incremental-dir=/incre_back/2021-06-02_13-10-03

The second incremental backup is applied to the full backup

 innobackupex --user=admin --password=123456  --apply-log     /full_xtrabackup/2021-06-02_13-08-27/  --incremental-            dir=/incre_back2/2021-06-02_13-10-03
Insert picture description here

6. Restore the backup
Restore the backup

innobackupex --copy-back  /full_xtrabackup/2021-06-02_10-37-32/

Modify file permissions

cd /usr/local/mysql
chown -R mysql.mysql .

7. Restart the MySQL service

service mysql start