1, Introduction to Xtrabackup

MySQL cold standby, mysqldump and MySQL hot copy cannot realize incremental backup of the database. In the actual production environment, incremental backup is very practical. If the data is greater than 50G or 100G and the storage space is sufficient, full backup can be carried out every day. If the amount of data generated every day is large, the data backup strategy needs to be customized. For example, weekly full backup and Monday to Saturday incremental backup are available. Percona xtrabackup is a mainstream backup tool for incremental backup. xtrabakackup has two tools, xtrabakup and innobakupe.

Percona xtrabackup is a backup tool developed by percona company for physical hot backup of MySQL database. It supports mysql, Percona server and MariaDB. It is open source and free. It is a popular mainstream backup tool at present. Xtrabackup can only back up the tables of innoDB and xtraDB data engines, not MyISAM data tables.

2, Xtrabackup benefits

(1) Fast backup speed and reliable physical backup

(2) The backup process does not interrupt ongoing transactions (no table locking is required)

(3) It can save disk space and traffic based on compression and other functions

(4) Automatic backup verification

(5) Fast restore speed

(6) Backups can be transferred to another machine

(7) Backup data without increasing server load

3, Xtrabackup backup principle

Xtrabackup backup flowchart:

(1) After innobackup ex is started, it will fork a process to start xtrabackup, and then wait for xtrabackup to back up ibd data files;

(2) Xtrabackup backs up innoDB data. There are two kinds of threads: redo copy thread and ibd data copy thread. After xtrabackup starts executing, it will start a redo copy thread to copy redo.log sequentially from the latest checkpoint point, and then start the ibd data copy thread to copy ibd data. Here, start the redo copy thread first. At this stage, inn Obackupex is in a waiting state (waiting for the file to be created)

(4) After xtrabackup copies the ibd data file, it will notify innobackupex (by creating the file), and xtrabackup will enter the waiting state (redo thread is still copying redo.log)

(5) After innobackupex receives the xtrabackup notification, execute FLUSH TABLES WITH READ LOCK (FTWRL) to obtain the consistency point, and then start backing up non InnoDB files (such as files in frm, MYD, MYI, CSV, opt, par and other formats). During the process of copying non InnoDB files, the database is in a global read-only state.

(6) When innobackup copies all non InnoDB files, it will notify xtrabackup. After the notification is completed, it will enter the waiting state;

(7) xtrabackup will stop the redo copy thread after receiving the notification that innobackupex backup is complete, and then notify innobackupex that redo.log file copy is complete;

(8) After innobackupex receives the redo.log backup, it unlocks it and executes: UNLOCK TABLES;

(9) Finally, innbackupex and xtrabackup processes release resources respectively, write backup metadata information, etc., and exit after innobackupex and other xtrabackup sub processes are completed.

4, The installation and deployment of xtrabackup and the implementation of backup and recovery

1. xtrabackup installation

Download address: https://www.percona.com/downloads/XtraBackup/LATEST/

You can choose the rpm package to install, or you can download the source package to compile and install. Here, the rpm package is directly used for installation

[root@master tools]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
[root@master tools]# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 
[root@master ~]# rpm -qa |grep xtrabackup
percona-xtrabackup-24-2.4.9-1.el7.x86_64

Xtrabackup It mainly contains two tools:
xtrabackup: It is used for hot standby innodb，xtradb The tool for data in tables cannot back up other types of tables or data table structures;
innobackupex: Will be xtrabackup Encapsulated perl Script, providing backup myisam The ability of the table.
Common options:  
   --host     Specify host
   --user     Specify user name
   --password    Specify password
   --port     Specify port
   --databases     Specify database
   --incremental    Create incremental backup
   --incremental-basedir   Specify the directory that contains the full backup
   --incremental-dir      Specify the directory that contains the incremental backup   
   --apply-log        Preprocessing backup             
     Generally, after the backup is completed, the data can not be used for recovery operation, because the backed up data may contain uncommitted transactions or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The main function of "preparation" is to roll back uncommitted transactions and synchronize committed transactions to the data file, so as to make the data file in a consistent state.
   --redo-only      Do not roll back uncommitted transactions
   --copy-back     Restore backup directory

When using innobackupex backup, it will call xtrabackup to back up all InnoDB tables, copy all relevant files (. frm) about table structure definition, MyISAM, MERGE, CSV and ARCHIVE tables, and back up files related to trigger and database configuration information. These files will be saved to a directory named by time. While backing up, innobackupex also creates the following files in the backup directory:

(1)xtrabackup_checkpoints -- Backup type(Such as full or incremental),Backup status(If yes prepared state)and LSN(Log serial number)Scope information:

each InnoDB page(Usually 16 k size)
Will contain a log serial number, i.e LSN，LSN It is the system version number of the whole database system, and each page is related LSN Can indicate how this page has changed recently.

(2)xtrabackup_binlog_info  --  mysql The binary log file currently in use by the server and the location of the binary log time at the moment of backup.

(3)xtrabackup_binlog_pos_innodb  --  Binary log files and for InnoDB or XtraDB The current of the binary log file for the table position. 

(4)xtrabackup_binary  --  Used in backup xtrabackup Executable documents;

(5)backup-my.cnf  --  Configuration option information for backup command:

in use innobackupex You can also use the--no-timestamp Option to prevent the command from automatically creating a directory named after time: in this way, innobackupex The command will create a BACKUP-DIR Directory to store backup data.

If you want to use a user with the minimum permission for backup, you can create such a user based on the following command: if you want to use a user with the minimum permission for backup, you can create such a user based on the following command:

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';　　#Create user
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';　　#Reclaim all permissions for this user
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost';　　#Authorization refresh, locking table, user viewing server status
mysql> FLUSH PRIVILEGES;　　#Refresh authorization table

*Note: during backup, mysql needs to be started. During recovery, mysql needs to be closed, mysql data directory needs to be cleared and cannot be reinitialized. A full backup should be performed immediately after data recovery*

2. xtrabackup full backup and recovery

Backup:
innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf /path/to/BACKUP-DIR/

Recovery:
innobackupex --apply-log /backups/2018-07-30_11-04-55/
innobackupex --copy-back --defaults-file=/etc/my.cnf  /backups/2018-07-30_11-04-55/

(1) Prepare a full backup

Generally, after the backup is completed, the data can not be used for recovery operation, because the backed up data may contain uncommitted transactions or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to roll back uncommitted transactions and synchronize committed transactions to data files, so that the data files used are in a consistent state.

The -- apply log option of innobackupex command can be used to realize the above functions, such as the following command:

# innobackupex --apply-log /path/to/BACKUP-DIR
 If the execution is correct, the last lines of information output are usually as follows:

120407 09:01:04 innobackupex: completed OK!

In the process of "preparation", innobackupex can also use the -- user memory option to specify the size of memory it can use, which is 100M by default If enough memory space is available, more memory can be allocated to the prepare process to improve the speed of completing the backup.

(2) Restore data from a full backup

Note: MySQL does not need to be started for recovery

The -- copy back option of innobackupex command is used for recovery operation. It performs the recovery process by copying all data related files to the mysql server DATADIR directory. Innobackupex via backup my CNF to get information about the DATADIR directory.

# innobackupex --copy-back /path/to/BACKUP-DIR

After the data is restored to the DATADIR directory, you also need to ensure that the owners and groups of all data files are the correct users, such as mysql. Otherwise, you need to modify the owners and groups of data files before starting mysqld. For example:

# chown -R mysql.mysql /mydata/data/

(3) Actual combat practice

(1)Full backup
[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/　　#stay master Full library backup on#Syntax explanation:#--user=root Specify backup user#--password=123456  Specify backup user password#--host　　Specify host#/backups specifies the backup directory
[root@master backups]# ll
total 0
drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37
[root@master backups]# ll 2018-07-30_11-01-37/　　#View backup data
total 77856
-rw-r----- 1 root root      418 Jul 30 11:01 backup-my.cnf　　#Backup the configuration option information file used
-rw-r----- 1 root root 79691776 Jul 30 11:01 ibdata1　　#data file
drwxr-x--- 2 root root       20 Jul 30 11:01 kim
drwxr-x--- 2 root root     4096 Jul 30 11:01 mysql
drwxr-x--- 2 root root     4096 Jul 30 11:01 performance_schema
drwxr-x--- 2 root root       20 Jul 30 11:01 repppp
drwxr-x--- 2 root root     4096 Jul 30 11:01 wordpress
-rw-r----- 1 root root       21 Jul 30 11:01 xtrabackup_binlog_info　　#The binary log file currently used by the mysql server and the location information file of the binary log time at this time
-rw-r----- 1 root root      113 Jul 30 11:01 xtrabackup_checkpoints　　#Backup type, status, and LSN status information file
-rw-r----- 1 root root      482 Jul 30 11:01 xtrabackup_info
-rw-r----- 1 root root     2560 Jul 30 11:01 xtrabackup_logfile　　　　#Log files backed up

(2)recovery
[root@slave ~]# /etc/init.d/mysqld stop　　#Stop mysql on slave
Shutting down MySQL.. SUCCESS! 

[root@slave tools]# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 　　#Installing xtrabackup
[root@master backups]# scp -r 2018-07-30_11-01-37/ root@192.168.56.12:/backups/　　 #Copy backup data from master
[root@slave tools]# innobackupex --apply-log /backups/2018-07-30_11-01-37/　　　　　 #Merge data so that the data file is in a consistent state
180729 23:18:23 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /backups/2018-07-30_11-01-37/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3127097)
......
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3129915
180729 23:18:30 completed OK!
[root@slave ~]# rm -rf /usr/local/mysql/data/　　#Delete the original data on the slave
[root@slave ~]# vim /etc/my.cnf　　#Configure my CNF's data directory path, otherwise an error will be reported, which should be consistent with the master
datadir=/usr/local/mysql/data
[root@slave ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/　　#Data recovery on slave
180729 23:32:03 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".
......
180729 23:32:08 completed OK!　　#When you see completed OK, you are back to normal
[root@slave ~]# ll /usr/local/mysql/data/　　#Viewing the data directory on the slave, you can see that the data has been recovered, but the owner will have problems and need to be modified. Therefore, the running user of mysql is generally used for recovery, otherwise the owner and group information need to be modified
total 188432
-rw-r----- 1 root root 79691776 Jul 29 23:32 ibdata1
-rw-r----- 1 root root 50331648 Jul 29 23:32 ib_logfile0
-rw-r----- 1 root root 50331648 Jul 29 23:32 ib_logfile1
-rw-r----- 1 root root 12582912 Jul 29 23:32 ibtmp1
drwxr-x--- 2 root root       20 Jul 29 23:32 kim
drwxr-x--- 2 root root     4096 Jul 29 23:32 mysql
drwxr-x--- 2 root root     4096 Jul 29 23:32 performance_schema
drwxr-x--- 2 root root       20 Jul 29 23:32 repppp
drwxr-x--- 2 root root     4096 Jul 29 23:32 wordpress
-rw-r----- 1 root root      482 Jul 29 23:32 xtrabackup_info
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql/data/　　#Modify primary group
[root@slave ~]# /etc/init.d/mysqld start　　#mysql start 
Starting MySQL. SUCCESS! 
[root@slave ~]# mysql -uroot -p -e "show databases;"　　#Check the data and whether to restore it
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kim                |
| mysql              |
| performance_schema |
| repppp             |
| wordpress          |
+--------------------+

Summarize the three steps of full library backup and recovery:

a. innobackupex full backup and specify the backup directory path;

b. Before recovery, you need to use the -- apply log parameter to merge data files to ensure data consistency requirements;

c. During recovery, directly use the -- copy back parameter to recover. Note that in my The path of the data file directory to be specified in CNF.

3. xtrabackup incremental backup and recovery

For incremental backup using innobackupex, each InnoDB page will contain an LSN information. Whenever the relevant data changes, the LSN of the relevant page will grow automatically. This is the basis for incremental backup of InnoDB tables, that is, innobackupex is realized by backing up the pages that have changed since the last full backup. When performing incremental backup, first perform a full backup. The first incremental backup is based on the full backup, and the subsequent incremental backups are based on the previous incremental backup, and so on.

To achieve the first incremental backup, you can use the following command:

Incremental backup and recovery based on full backup
 Make an incremental backup (based on the latest full backup)
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37
1. Preparation is based on full volume
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37
2. Preparation based on increment
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37 --incremental-dir=/backups/2018-07-30_13-51-47/3. recovery
innobackupex --copy-back --defaults-file=/etc/my.cnf /opt/2017-01-05_11-04-55/
Explanation:
1. 2018-07-30_11-01-37 Refers to the directory where the full backup is located. two. 2018-07-30_13-51-47 The assignment is based on 2018 for the first time-07-30_11-01-37 The directory of incremental backup, and so on, that is, if there are multiple incremental backups. Perform the above operation every time.

It should be noted that incremental backup can only be applied to InnoDB or XtraDB tables. For MyISAM tables, incremental backup is actually a full backup.

There are some differences between prepare incremental backup and organize full backup, especially: ① You need to "replay" the committed transactions on each backup (including full and incremental backups). After "replay", all backup data will be merged into a full backup. ② "Roll back" uncommitted transactions based on all backups

(1) Incremental backup demo

[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/   #Full data[root@master ~]# mysql -uroot -p　　#Create a student library on the master, create a testtb table, and insert some data. Enter password: 
mysql> create database student;
Query OK, 1 row affected (0.03 sec)

mysql> use student;
Database changed
mysql> create table testtb(id int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into testtb values(1),(10),(99);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testtb;
+------+
| id   |
+------+
|    1 |
|   10 |
|   99 |
+------+
3 rows in set (0.00 sec)

mysql> quit;
Bye
#use innobackupex Perform incremental backup[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37/
......
180730 13:51:50 Executing UNLOCK TABLES
180730 13:51:50 All tables unlocked
180730 13:51:50 Backup created in directory '/backups/2018-07-30_13-51-47/'
MySQL binlog position: filename 'mysql-bin.000005', position '664'
180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/backup-my.cnf
180730 13:51:50 [00]        ...done
180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/xtrabackup_info
180730 13:51:50 [00]        ...done
xtrabackup: Transaction log of lsn (3158741) to (3158741) was copied.
180730 13:51:50 completed OK!
[root@master backups]# ll　　#View backup data
total 0
drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37　　#Full backup data directory
drwxr-x--- 8 root root 273 Jul 30 13:51 2018-07-30_13-51-47　　#Incremental backup data directory
[root@master 2018-07-30_11-01-37]# cat xtrabackup_checkpoints #View xtrabackup for full backup_ checkpoints
backup_type = full-backuped　　#The backup type is full backup
from_lsn = 0　　#lsn starts at 0
to_lsn = 3127097　　#lsn ends at 3127097
last_lsn = 3127097
compact = 0
recover_binlog_info = 0

[root@master 2018-07-30_13-51-47]# cat xtrabackup_checkpoints 　　#View xtrabackup for incremental backups_ checkpoints
backup_type = incremental　　#The backup type is incremental
from_lsn = 3127097　　#lsn starts at 3127097
to_lsn = 3158741　　  #lsn to ah 3158741 end
last_lsn = 3158741　　
compact = 0
recover_binlog_info = 0

(2) Demonstration of data recovery after incremental backup

(1)simulation mysql Fault, delete all data in the data directory[root@master ~]# /etc/init.d/mysqld stop　　#Simulate MySQL failure and stop mysql
Shutting down MySQL.. SUCCESS! 
[root@master ~]# rm -rf /usr/local/mysql/data/*　　#Delete all data in the data directory (2) merge the full data directory to ensure data consistency
[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/
180730 14:05:27 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /backups/2018-07-30_11-01-37/
......
......
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3127106
InnoDB: Number of pools: 1
180730 14:05:29 completed OK!(3)Merge the incremental backup data into the full backup data directory
[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/ --incremental-dir=/backups/2018-07-30_13-51-47/
180730 14:06:42 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".
......
......
180730 14:06:44 [00]        ...done
180730 14:06:44 completed OK!
[root@master ~]# cat /backups/2018-07-30_11-01-37/xtrabackup_checkpoints 
backup_type = log-applied　　#It is found that the data backup type is increased
from_lsn = 0　　#lsn starts at 0
to_lsn = 3158741　　#The lsn end number is the latest lsn
last_lsn = 3158741
compact = 0
recover_binlog_info = 0(4)Recover data
[root@master ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/
180730 14:07:51 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".
.......
.......
180730 14:08:17 [01]        ...done
180730 14:08:17 completed OK!
[root@master ~]# ll /usr/local/mysql/data/
total 77844
-rw-r----- 1 root root 79691776 Jul 30 14:08 ibdata1
drwxr-x--- 2 root root       20 Jul 30 14:08 kim
drwxr-x--- 2 root root     4096 Jul 30 14:08 mysql
drwxr-x--- 2 root root     4096 Jul 30 14:08 performance_schema
drwxr-x--- 2 root root       20 Jul 30 14:08 repppp
drwxr-x--- 2 root root       56 Jul 30 14:08 student
drwxr-x--- 2 root root     4096 Jul 30 14:08 wordpress
-rw-r----- 1 root root       21 Jul 30 14:08 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      554 Jul 30 14:08 xtrabackup_info
[root@master ~]# chown -R mysql.mysql /usr/local/mysql/data　　#Change the primary group of data
[root@master ~]# /etc/init.d/mysqld start　　#mysql start 
Starting MySQL.Logging to '/usr/local/mysql/data/master.err'.
.. SUCCESS! 
[root@master ~]# mysql -uroot -p -e "show databases;"　　#Check whether the data is recovered
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kim                |
| mysql              |
| performance_schema |
| repppp             |
| student            |
| wordpress          |
+--------------------+

Summary:

(1) For incremental backup, you need to use the parameter -- incremental to specify which directory to back up, and incremental dir to specify the full backup directory;

(2) During data backup, you need to use the parameter -- apply log redo only to merge the full backup data directory data first to ensure the consistency of the full backup data directory data;

(3) Then merge the incremental backup data use parameter -- incremental dir into the full backup data;

(4) Finally, recover the data through the last full backup data. Note that if there are multiple incremental backups, they need to be merged into the full backup data one by one before recovery.

#1. --user=root specifies the backup user

#2. --password=root specifies the password of the backup user

#3. --defaults-file=/etc/my.cnf specifies the configuration file for the backup data

#4. /opt / specify the data saving path after backup

Next: Backup and recovery of MySql database Mysqldump →