Incremental backup and restore of MySQL via Innobackupex

Backup

Incremental backups are based on a full backup, so we need to do a full backup first:

Innobackupex--password=test/backup/

Note: Test is the password for the root user of my MySQL service,/backup/is the directory I used to store the backup files.

After executing the command, a timestamp directory is generated under the/backup/directory, which in my case is/backup/2016-09-20_14-45-26/.

Incremental backup for the first time:

Innobackupex--password=test--incremental/backup/--incremental-basedir=/backup/2016-09-20_14-45-26/

Note: The--incremental-basedir parameter of the first backup should point to the timestamp directory of the full backup.

After executing the command, a timestamp directory is generated under the/backup/directory, which in my case is/backup/2016-09-20_14-58-01/.

Second Incremental backup:

Innobackupex--password=test--incremental/backup/--incremental-basedir=/backup/2016-09-20_14-58-01/

Note: The--incremental-basedir parameter of the second backup should point to the timestamp directory of the first incremental backup.

After executing the command, a timestamp directory is generated under the/backup/directory, which in my case is/backup/2016-09-20_14-58-30/.

In order to achieve the experimental results, the reader is advised to make additions and deletions to the database before each incremental backup in order to observe the restore effect.

Restores

Stop the MySQL service and empty the data directory:

Service Mysqld Stop
rm-rf/mysql/*

Consolidate full and incremental backups:

Innobackupex--apply-log--redo-only/backup/2016-09-20_14-45-26/
Innobackupex--apply-log--redo-only/backup/2016-09-20_14-45-26/--incremental-dir=/backup/2016-09-20_14-58-01/
Innobackupex--apply-log/backup/2016-09-20_14-45-26/--incremental-dir=/backup/2016-09-20_14-58-30/
Innobackupex--apply-log/backup/2016-09-20_14-45-26/

Note: Be sure to consolidate in the order of full backups, first incremental backups, second incremental backups, and do not use the--redo-only parameter when consolidating the last incremental backup.

Start Restore:

Innobackupex--copy-back/backup/2016-09-20_14-45-26/

After the restore succeeds, you will see "Completed ok!" The words.

Modify the owner and host groups of the data directory and start the MySQL service:

Chown-r mysql.mysql/mysql/
Service mysqld Start

The restore of the incremental backup is completed with the above steps.

Warm tips

When restoring, it is recommended that you make a copy backup of the data directory and the backup directory if there is sufficient space.

Incremental backup and restore of MySQL via Innobackupex