Xtrabackup full standby, incremental backup recovery

1, Xtrabackup principle

1 Xtrabackup can only back up the tables of InnoDB and xtradb two engines, but not the MyISAM engine's tables;

The 2 Innobackupex is a xtrabackup perl script that supports simultaneous backup of both InnoDB and MyISAM, but requires a global read lock for MyISAM backups. And there's the MyISAM. Incremental backups are not supported.

(1) Total preparation principle

1 will start a xtrabackup_log background detection process, real-time detection of MySQL redo changes, once found redo have a new log write, immediately write the log to the log file Xtrabackup_log

2 Copy InnoDB data files and system table space files idbdata1 to the corresponding default timestamp as backup directory

3 After the copy is finished, execute the Flush table with read lock operation

4 copy. frm. myd. myi file

5 and get the position of binary log at this moment

6 Unlocking the table unlock tables

7 Stop Xtrabackup_log Process

full-standby recovery principle

Full-ready + binary Log recovery

Starts the Xtrabackup embedded InnoDB instance, replays the Xtrabackup log Xtrabackup_log, applies the submitted transaction information changes to the INNODB data or tablespace, and rolls back uncommitted transactions

(2) Incremental backup principle

Incremental backups are mainly by copying pages with changes in the InnoDB (the LSN is greater than the LSN number in xtrabackup_checkpoints). Incremental backups are based on full provisioning, the data for the first incremental backup is based on the last full, and each incremental backup is based on the previous incremental backup, resulting in a consistent incremental backup, in the process of incremental backups, similar to the one in full, except for the second step

2. Soft software
[Root@dgt ~]# yum install libev-4.15-1.el6.rf.x86_64 rsync


[Root@dgt ~]# RPM-IVH percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm
Warning:percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm:header V4 dsa/sha1 Signature, key ID Cd2efd2a:nokey
Preparing ... ########################################### [100%]
1:percona-xtrabackup-24 ########################################### [100%]


Create a backup user
Grant Reload,lock tables, PROCESS, replication Client on *.* to ' backup ' @ ' localhost ' identified by ' az.123456 ';
Flush privileges;


Grant PROCESS on *.* to ' backup ' @ ' localhost ';


To create a backup directory:
Mkdir–p/backup/full
Mkdir-p/backup/inc



fully-prepared database
(1)-Create a fully prepared
Innobackupex--defaults-file=/etc/my.cnf--user=backup--password= ' az.123456 '--socket=/var/lib/mysql/mysql.sock/ backup/

(2) Analog error deletion
mysql> drop database test;

Restore Database

(1) Application log to full standby
/etc/init.d/mysqld stop
Innobackupex--apply-log/backup/2017-04-11_18-43-29

(2) original library change name
[Root@dgt lib]# Mv/var/lib/mysql/var/lib/mysql_bak

(3) Fully-prepared files restored to the target database
Innobackupex--defaults-file=/etc/my.cnf--copy-back--rsync/backup/2017-04-11_18-43-29


[Root@dgt lib]# chown mysql.mysql mysql-r
/etc/init.d/mysqld start




Incremental Backups
(1) Do the whole preparation first
Innobackupex--defaults-file=/etc/my.cnf--user=backup--password= ' az.123456 '--socket=/var/lib/mysql/mysql.sock/ Backup/full

---view checkpoints
[Root@dgt 2017-04-12_10-18-59]# Cat Xtrabackup_checkpoints
Backup_type = full-backuped
FROM_LSN = 0
TO_LSN = 274529749
LAST_LSN = 274529758
Compact = 0
Recover_binlog_info = 0

---do incremental data
Mysql> CREATE TABLE t2 (ID int,name char (10));
mysql> INSERT INTO T2 values (1, ' aaaa ');
mysql> INSERT INTO T2 values (2, ' CCCC ');

Mysql> select * from T2;
+------+------+
| ID | name |
+------+------+
| 1 | AAAA |
| 2 | CCCC |
+------+------+
2 rows in Set (0.00 sec)

(2) Incremental backup (the first incremental backup of the directory/backup/inc/one, the first backup is based on full)
Innobackupex--defaults-file=/etc/my.cnf--user=backup--password= ' az.123456 '--socket=/var/lib/mysql/mysql.sock-- Incremental/backup/inc/one--incremental-basedir=/backup/full/2017-04-12_10-18-59--parallel=2--no-timestamp


[Root@dgt one]# Cat Xtrabackup_checkpoints
Backup_type = Incremental
FROM_LSN = 274529749
TO_LSN = 274532033
LAST_LSN = 274532042
Compact = 0
Recover_binlog_info = 0

mysql> INSERT INTO T2 values (3, ' ddd ');
Query OK, 1 row affected (0.09 sec)


mysql> INSERT INTO T2 values (4, ' eeee ');
Query OK, 1 row affected (0.06 sec)


(2) Two incremental backups (two incremental backups are based on an incremental backup, so--incremental-basedir is the directory of the previous incremental backup/backup/inc/one)
Innobackupex--defaults-file=/etc/my.cnf--user=backup--password= ' az.123456 '--socket=/var/lib/mysql/mysql.sock-- Incremental/backup/inc/two--incremental-basedir=/backup/inc/one--parallel=2--no-timestamp


[Root@dgt two]# Cat Xtrabackup_checkpoints
Backup_type = Incremental
FROM_LSN = 274532033
TO_LSN = 274534286
LAST_LSN = 274534295
Compact = 0
Recover_binlog_info = 0



There are 3 steps required to restore an incremental backup
1 Restoring Full backups
2 Restore incremental backups to full backups (incremental backup to start recovery to add--redo-only parameters, to remove--redo-only for last incremental backup)
3 Recovery of a full backup of the whole, rollback of uncommitted data


(1) Restore full backup
Restore the underlying backup plus--redo-only, which means that only the data that has been submitted in the Xtabackup log is applied, and not the uncommitted data is rolled back
Innobackupex--apply-log--redo-only/backup/full/2017-04-12_10-18-59


(2) apply an increment of 1 to a full backup(Apply incremental backup/backup/inc/one to the base backup full)

Innobackupex--apply-log--redo-only/backup/full/2017-04-12_10-18-59--incremental-dir=/backup/inc/one

(3) If there is an increment of 2, then apply increment 2 to a full backup
(Apply incremental backup 2 to full standby, note that this is the last incremental backup restore, so remove the redo-only parameter and roll back uncommitted data in xtrabackup)
Innobackupex--apply-log/backup/full/2017-04-12_10-18-59--incremental-dir=/backup/inc/two


(4) All the combined full backup of the whole to perform a single apply operation, roll back the uncommitted data
Innobackupex--apply-log/backup/full/2017-04-12_10-18-59


Service Mysqld Stop
Mv/var/lib/mysql/var/lib/mysql_bak2

(5) Copy the restored backup to the data file directory, give permission, restart the database
Innobackupex--defaults-file=/etc/my.cnf--copy-back--rsync/backup/full/2017-04-12_10-18-59

Chown-r Mysql.mysql/var/lib/mysql
Service mysqld Restart


Mysql> select * from T2;
+------+------+
| ID | name |
+------+------+
| 1 | AAAA |
| 2 | CCCC |
| 3 | DDD |
| 4 | eeee |
+------+------+
4 rows in Set (0.00 sec)

To this incremental restore complete