Mysql innobackupex incremental backup

Note: innobackupex incremental backup is only for transactional engines such as InnoDB, but it is still complete for engines such as MyISAM.

Incremental backup:

Incremental backup needs to be based on full backup. Suppose we already have a complete backup (/ backup/2016-12-07 / 17-33-09), and we need to do incremental backup on this complete basis.

Fully prepared

Innobackupex-defaults-file=/home/mysql/mysql_5535/etc/my.cnf-user=root-password=123456 / backup/

Increment

Innobackupex-defaults-file=/home/mysql/mysql_5535/etc/my.cnf-user=root-password=123456-incremental-basedir=/backup/2016-12-07 July 17-33-09 /-- incremental / backup

Increment

Innobackupex-- defaults-file=/home/mysql/mysql_5535/etc/my.cnf-- user=root-- password=123456-- incremental-basedir=/backup/2016-12-08 / 11-01-43 /-- incremental / backup

Where-incremental-basedir points to the full directory and-- incremental points to the directory of incremental backups.

After the above statement is executed successfully, a timestamp subdirectory is created under the directory executed by-- incremental (in this case: / backup/mysql/data/2013-10-29backup 09-52-37), where all files for incremental backups are stored.

In the backup directory, there is a file xtrabackup_checkpoints that records the backup information. The complete information is as follows:

[root@bogon backup] # cd 2016-12-07 / 17-33-09 /

[root@bogon 2016-12-07 / 17-33-09] # cat xtrabackup_checkpoints

Backup_type = full-prepared

From_lsn = 0

To_lsn = 1595669

Last_lsn = 1595669

Compact = 0

Recover_binlog_info = 0

[root@bogon 2016-12-08 11-01-43] # cat xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 1595669

To_lsn = 1601979

Last_lsn = 1601979

Compact = 0

Recover_binlog_info = 0

[root@bogon backup] # cd 2016-12-08 / 11-03-28 /

[root@bogon 2016-12-08 11-03-28] # cat xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 1601979

To_lsn = 1604021

Last_lsn = 1604021

Compact = 0

Recover_binlog_info = 0

As you can see from the above, the from_lsn of the incremental backup is exactly the same as the full to_lsn.

So, can we do incremental backup on the basis of incremental backup? The answer is yes, just put-- incremental-basedir to the directory of the last incremental backup.

Restore:

The recovery of incremental backups is much more complex than complete, and the first step is to redo committed logs in all backup directories, such as:

Innobackupex-defaults-file=/home/mysql/mysql_5535/etc/my.cnf-user=root-password=123456-apply-log-redo-only / backup/2016-12-07 July 17-33-09 /

Innobackupex-- defaults-file=/home/mysql/mysql_5535/etc/my.cnf-- user=root-- password=123456-- apply-log-- redo-only / backup/2016-12-07 / 11-33-09 /-- incremental-dir=/backup/2016-12-08 11-01-43 /

Innobackupex-- defaults-file=/home/mysql/mysql_5535/etc/my.cnf-- user=root-- password=123456-- apply-log / backup/2016-12-07 / 17-33-09 /-- incremental-dir=/backup/2016-12-08 11-03-28 /

Innobackupex-defaults-file=/home/mysql/mysql_5535/etc/my.cnf-user=root-password=123456-apply-log / backup/2016-12-07 / 17-33-09 /

Where 2016-12-07 17-33-09 / refers to the full directory, 2016-12-08 11-01-43 / refers to the first incremental backup, / 2016-12-08 11-03-28 / refers to the second incremental backup, and so on. One thing to note here is that there is no redo-only option for the incremental backup in the last step! Also, you can use-- use_memory to improve performance. After the above statement is executed successfully, the final data is under BASE-DIR (that is, full directory).

After the first step is complete, let's move on to the second step: roll back the outstanding logs:

Innobackupex-defaults-file=/home/mysql/mysql_5535/etc/my.cnf-user=root-password=123456-apply-log / backup/2016-12-07 / 17-33-09 /

After the above execution, the backup files in BASE-DIR are fully ready, and the final step is to copy:

Innobackupex-defaults-file=/home/mysql/mysql_5535/etc/my.cnf-user=root-password=123456-copy-back / backup/2016-12-07 / 17-33-09 /

Modify permissions

[root@bogon var] # chown mysql.mysql / home/mysql/mysql_5535/var/-R

[root@bogon var] # / home/mysql/mysql_5535/bin/mysql.server start