Anti-forgery code: There can be no inner peace without considerable loneliness.

1. Overview

Percona XtraBackup (PXB) is a backup tool developed by Percona for the physical backup of MySQL databases. It supports MySQl (Oracle), Percona Server and MariDB, and is open source. Taobao's RDS for MySQL physical backup is based on XtraBackup.

Xtrabackup has two main tools: xtrabackup, innobackupex

(1) xtrabackup is a C/C++ compiled binary file that can only back up InnoDB and XtraDB data tables, not MyISAM data tables;

(2) innobackupex encapsulates xtrabackup, which is a perl script encapsulation, so it can backup both innodb and myisam, but it requires a read lock to process myisam;

1.1 Backup Principles

The entire backup process is as follows:

1. After the innobackupex starts, it fork s a process, starts the xtrabackup process, and then waits for the xtrabackup to back up the ibd data file.

2. Xtrabackup has two threads when backing up InnoDB-related data, one is the redo copy thread, which copies redo files, the other is the ibd copy thread, which copies ibd files; the redo copy thread has only one thread, which starts before the ibd copy thread and ends after the ibd thread ends.After the xtrabackup process starts executing, the redo copy thread is started, the redo log is copied sequentially from the latest checkpoint point, and then the ibd data copy thread is started. During the xtrabackup copy ibd process, the innobackupex process waits (waiting for the file to be created).

3. When the xtrabackup copy finishes the idb, notify innobackupex (by creating the file) and enter the wait yourself (the redo thread continues to copy);

4. After receiving the xtrabackup notification, innobackupex executes FLUSH TABLES WITH READ LOCK (FTWRL), obtains the consensus locus, and then starts backing up non-InnoDB files (including frm, MYD, MYI, CSV, opt, par, and so on).During the process of copying non-InnoDB files, because the database is in a global read-only state, be careful if the business's primary library is backed up. If there are more non-InnoDB tables (mainly MyISAM), the entire library will be read-only for a longer time, and this impact must be assessed.

5. When innobackupex has copied all non-InnoDB table files, notify xtrabackup (by deleting the file) and enter the waiting list yourself (by waiting for another file to be created);

6. xtrabackup stops the redo copy thread when it receives the innobackupex backup non-InnoDB notification, and then notifies innobackupex redo log that the copy is complete (by creating a file);

7. Once innobackupex receives the redo backup completion notification, it begins to unlock and execute UNLOCK TABLES;

8. Finally, the innobackupex and xtrabackup processes finish their respective work, such as resource release, writing backup metadata information, etc. Innobackupex waits for the xtrabackup subprocess to exit after it has finished.

1.2 Common parameters

--defaults-file: Indicates the server's configuration file, which must be the first parameter of innobackupex, otherwise an error will occur

--host:Indicates the host connecting to the database

--user:Indicates the user name to perform the database backup

--password:Indicates the password to perform the backup

--backup:indicated as backup, this parameter can be ignored

--apply-log: Redo log

--copy-back:Perform data recovery

--slave-info: Back up the show slave status information from the slave library, only for use when backing up the slave Library

--no-lock: unlock tables, only if the storage engine is innodb and does not care about backing up location points

2. Install XtraBackup

2.1 Environmental Preparation

MySQL installation steps are omitted.

2.2 Create backup users

mysql> create user 'backup'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.04 sec)
mysql> grant reload,lock tables,replication client,process on *.* to 'backup'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.30 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

2.3 Create backup directory

[root@MySQL-01 ~]#mkdir /backup

2.4 Download xtraBackup

[root@MySQL-01 ~]#cd /opt
# Download may be slow.
[root@MySQL-01 opt]#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm

2.4 Install XtraBackup

# An error occurred during installation because the dependent package was not installed.
[root@MySQL-01 opt]#rpm -ivh percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm
warning: percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
	libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.7-2.el6.x86_64
	perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.7-2.el6.x86_64
# Install dependent packages
[root@MySQL-01 opt]#yum -y install libev-devel perl-DBD-MySQL
[root@MySQL-01 opt]#rpm -ivh percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm
warning: percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                ########################################### [100%]
1:percona-xtrabackup-24  ########################################### [100%]
[root@MySQL-01 opt]#rpm -ql percona-xtrabackup-24-2.4.7-2.el6.x86_64
/usr/bin/innobackupex                #innobackupex script tool
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup                    #The main backup tool
/usr/share/doc/percona-xtrabackup-24-2.4.7
/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

3. Backup and Recovery Testing

3.1 Full Backup

# defaults-file:my.cnf configuration path
[root@MySQL-01 opt]#innobackupex --defaults-file=/app/mysql/my.cnf --user=backup --password=123456 /backup
# View that backup files already exist in the backup directory
[root@MySQL-01 opt]#ll /backup/
total 4
drwxr-x--- 5 root root 4096 Jul 14 17:25 2017-07-14_17-25-24
[root@MySQL-01 opt]#ls /backup/2017-07-14_17-25-24/
backup-my.cnf  performance_schema      xtrabackup_checkpoints
ibdata1        rep                     xtrabackup_info
mysql          xtrabackup_binlog_info  xtrabackup_logfile
[root@MySQL-01 opt]#cat /backup/2017-07-14_17-25-24/xtrabackup_checkpoints
backup_type = full-backuped        #Backup mode is full backup
from_lsn = 0                                  #As complete, LSN is actually 0
to_lsn = 13835894                        #Cutoff LSN Number
last_lsn = 13835894
compact = 0
recover_binlog_info = 0

3.2 Analog Recovery

Now that the full backup is complete, test it.

# Stop the database
[root@MySQL-01 ~]#service mysqld stop
Shutting down MySQL............ SUCCESS!
# Move the data file to the backup directory
[root@MySQL-01 ~]#mv /app/mysql/data /backup/data_bak
# mysqld could not be started
[root@MySQL-01 ~]#service mysqld start
Starting MySQL.Logging to '/app/mysql/data/MySQL-01.err'.
. ERROR! The server quit without updating PID file (/app/mysql/data/MySQL-01.pid).
# apply-log parameter: restore the redo log state of the database before actually restoring the database data.
[root@MySQL-01 ~]#innobackupex --defaults-file=/app/mysql/my.cnf --user=backup --password=123456  --apply-log /backup/2017-07-14_17-25-24
# Perform data recovery
[root@MySQL-01 ~]#innobackupex --defaults-file=/app/mysql/my.cnf --user=backup --password=123456  --copy-back /backup/2017-07-14_17-25-24
[root@MySQL-01 ~]#chown -R mysql.mysql /app/mysql/data
# Start the database again and show success
[root@MySQL-01 ~]#service mysqld start
Starting MySQL.Logging to '/app/mysql/data/MySQL-01.err'.
. SUCCESS!

4. Reference

http://mysql.taobao.org/monthly/2016/03/07/

http://www.cnblogs.com/olinux/p/5207887.html