Percona XtraBackup 2.4 innobackupex全量、增量備份恢復流程

innobackupex是xtrabackup的軟連線。
xtrabackup 使用C語言編譯的備份工具，可以完整備份MySQL資料庫，支援MyISAM、InnoDB和XtraDB儲存引擎。

在備份的時候，備份工具主要執行兩個任務來完成備份：
① 在後臺啟動一個日誌複製執行緒。這個執行緒會監視InnoDB日誌檔案，當日志檔案發生改變時，這個執行緒會將發生變化的資料塊複製到備份目錄下一個名為xtrabackup_logfile的檔案中。這個操作是必要的，因為備份可能會持續很長時間，在資料庫恢復時，需要所有從備份開始到結束的這些日誌檔案。
② 複製InnoDB資料檔案到指定備份目錄下。這不是一個簡單的複製，備份工具開啟並讀取檔案的方式類似InnoDB，透過讀取檔案目錄並以頁（page）為單位進行複製。

當資料檔案複製結束時，xtrabackup會停止日誌複製執行緒，並在指定備份目錄中建立一個名為xtrabackup_checkpoints的檔案，這個檔案包含備份的型別、開始備份的日誌序列號和結束備份的日誌序列號。

[root@localhost bin]# ls -trl /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin
total 224028
-rwxr-xr-x. 1 root root      3020 Mar 29 04:36 xbcloud_osenv
-rwxr-xr-x. 1 root root   5070569 Mar 29 04:36 xbstream
-rwxr-xr-x. 1 root root   4999056 Mar 29 04:36 xbcrypt
-rwxr-xr-x. 1 root root   5179178 Mar 29 04:36 xbcloud
-rwxr-xr-x. 1 root root 214139498 Mar 29 04:50 xtrabackup
lrwxrwxrwx. 1 root root        10 Apr 25 01:11 innobackupex -> xtrabackup

innobackupex常用引數

--compact        建立一個不包含第二索引（除了主鍵之外的索引）的備份

--decompress        解壓之前所有以–compress引數備份出來的帶有.qp格式的備份檔案，--parallel引數會允許同時解鎖或解壓多個檔案。需要安裝qpress軟體。

--defaults-file=[MY.CNF] 配置檔案的路徑

--incremental-basedir 以上一次全量或增量備份的路徑，作為增量備份的基礎。指定這個引數的同時，應該同樣指定--incremental引數

--incremental 建立增量備份，當指定這個引數的時候，應該指定--incremental-lsn或--incremental-basedir引數，否則將會備份到--incremental-basedir路徑

--apply-log     在備份目錄下，透過應用名稱為xtrabackup_logfile的交易日誌檔案來準備備份。同時，建立新的交易日誌。

--use-memory=# 指定資料庫恢復時使用的記憶體大小，需要搭配--apply-log引數。

--redo-only 當準備資料庫的全備或合併增量備份時，需要指定這個引數。這個引數實際上執行的是，會讓xtrabackup跳過回滾節點，只做“redo”步驟。當資料庫需要應用增量備份時，需要指定這個引數。

--incremental-dir=DIRECTORY 指定增量備份的目錄，需要搭配--incremental引數。

--no-timestamp        這個引數會讓xtrabackup在備份的時候不建立帶有時間格式的子資料夾。當指定了這個引數，備份會直接建立在指定的備份目錄下。

--stream=STREAMNAME 指定流備份的格式。備份將會以指定格式輸出到STDOUT。目前支援的格式有tar 和 xbstream。如果指定了這個引數，後面需要接tmpdir目錄作為處理流的一箇中間目錄。

--slave-info    當備份一個作為複製環境的伺服器時，這個引數會自動將CHANGE MASTER語句寫到備份中，在恢復備份後，不必執行CHANGE MASTER語句。

--tables-file=FILE        這個引數會接受一個字串，這個字串指定了一個檔案，這個檔案包含了要備份的表名，格式如database.table，一行一個。

--use-memory=#        這個引數用於在準備備份時，xtrabackup執行crash recovery所使用的記憶體大小。這個引數僅和--apply-log搭配時才生效。

--建立備份賬戶
mysql> create user xtrabk@'localhost' identified by 'Myql#2015';
Query OK, 0 rows affected (0.18 sec)
mysql> grant reload,lock tables,replication client,super on *.* to xtrabk@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.44 sec)

需要注意的是，xtrabackup沒有提供日誌功能。如果要記錄日誌的話，需要在備份命令中新增下列命令。

innobackupex --defaults-file=/var/lib/mysql/main_my.cnf \

>  --user=${MYSQL_USER} --password=${MYSQL_PASS} \

>  --extra-lsndir=/database/backup/2016-08-09/checkpoints/ \

>  --compress --compress-threads=8 \

>  --stream=xbstream --parallel=4 /tmp \

>  2>> /database/backup/2016-08-09/xtra_full_bak_2016-08-09.log \

>  > /database/backup/2016-08-09/xtra_full_bak_2016-08-09.xbstream

--innobackupex全量備份資料庫
[root@localhost 20160719]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System#2013' /backup/20160716/
160716 01:48:48 [00] Writing xtrabackup_binlog_info
160716 01:48:48 [00]        ...done
160716 01:48:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186510528'
xtrabackup: Stopping log copying thread.
.160716 01:48:49 >> log scanned up to (186510537)

160716 01:48:49 Executing UNLOCK TABLES
160716 01:48:49 All tables unlocked
160716 01:48:49 [00] Copying ib_buffer_pool to /backup/20160716/2016-07-16_01-48-32/ib_buffer_pool
160716 01:48:49 [00]        ...done
160716 01:48:49 Backup created in directory '/backup/20160716/2016-07-16_01-48-32'
MySQL binlog position: filename 'production-bin.000006', position '194', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44'
160716 01:48:49 [00] Writing backup-my.cnf
160716 01:48:49 [00]        ...done
160716 01:48:49 [00] Writing xtrabackup_info
160716 01:48:49 [00]        ...done
xtrabackup: Transaction log of lsn (186510528) to (186510537) was copied.
160716 01:48:49 completed OK!
[root@localhost 2016-07-16_01-48-32]# cat xtrabackup_info 
uuid = 1d38a5f2-4b32-11e6-9859-000c29631605
name = 
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=root --password=... /backup/20160716/
tool_version = 2.4.2
ibbackup_version = 2.4.2
server_version = 5.7.12-log
start_time = 2016-07-16 01:48:32
end_time = 2016-07-16 01:48:49
lock_time = 0
binlog_pos = filename 'production-bin.000006', position '194', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44'
innodb_from_lsn = 0
innodb_to_lsn = 186510528
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
--innobackupex全量備份恢復
--在資料庫中建立測試表並插入資料
mysql> use fire
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_fire |
+----------------+
| t1             |
| t2             |
| test           |
| v_t1           |
| v_t1_myisam    |
+----------------+
5 rows in set (0.00 sec)

mysql> select * from t1;
+------+------+
| f1   | gc   |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
|    4 |    5 |
|    5 |    6 |
+------+------+
5 rows in set (0.00 sec)

mysql> delete from t1;
Query OK, 5 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
Empty set (0.00 sec)

--關閉資料庫
[root@localhost fire]# service mysqld stop

--刪除資料檔案目錄下的所有檔案
[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# ls
auto.cnf         client-key.pem  ib_logfile0                performance_schema     production-bin.000003  production-bin.000007  server-cert.pem
ca-key.pem       fire            ib_logfile1                private_key.pem        production-bin.000004  production-bin.000008  server-key.pem
ca.pem           ib_buffer_pool  localhost.localdomain.err  production-bin.000001  production-bin.000005  production-bin.index   sys
client-cert.pem  ibdata1         mysql                      production-bin.000002  production-bin.000006  public_key.pem
[root@localhost mysql]# rm -rf *

--備份出來的資料檔案由於複製時間不同，在複製資料的過程中，資料可能會發生變化，直接使用這些資料檔案，會導致恢復出來的資料檔案可能會有資料損壞。準備備份檔案的目的，是讓資料檔案在某時間點上保持一致性。可以在任意機器上執行準備備份檔案這一動作，不需要僅在備份的主機上面執行這一動作。準備好備份，使用--apply-log引數並指定帶時間的備份子目錄。想要加快apply-log的程式，推薦使用--use-memory引數。
[root@localhost 2016-07-16_01-48-32]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf  --apply-log /backup/20160716/2016-07-16_01-48-32/
.....
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support not available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.11 started; log sequence number 186511139
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
--當準備備份的操作完成後，會看到下面資訊
InnoDB: Shutdown completed; log sequence number 186511158
160716 03:07:07 completed OK!

--恢復已經準備好的備份，停止MySQL服務並在innobackupex命令中附帶--copy-back引數，這會將已經準備好的資料複製回my.cnf引數檔案中指定的資料目錄下。
[root@localhost 2016-07-16_01-48-32]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf  --copy-back /backup/20160716/2016-07-16_01-48-32/
.....
160716 03:12:30 [01] Copying ./mysql/engine_cost.frm to /var/lib/mysql/mysql/engine_cost.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/server_cost.frm to /var/lib/mysql/mysql/server_cost.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/time_zone_name.ibd to /var/lib/mysql/mysql/time_zone_name.ibd
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/innodb_table_stats.frm to /var/lib/mysql/mysql/innodb_table_stats.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/slave_master_info.frm to /var/lib/mysql/mysql/slave_master_info.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
160716 03:12:30 [01]        ...done
160716 03:12:30 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
160716 03:12:30 [01]        ...done
160716 03:12:30 completed OK!

--資料恢復完成之後，需要修改相關檔案的許可權
[root@localhost 2016-07-16_01-48-32]# chown -R mysql.mysql /var/lib/mysql

--啟動資料庫
[root@localhost fire]# service mysqld start

--檢視刪除的資料，已經恢復
mysql> select * from fire.t1;
+------+------+
| f1   | gc   |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
|    4 |    5 |
|    5 |    6 |
+------+------+
5 rows in set (0.00 sec)

--使用流的格式壓縮備份全庫
[root@localhost bin]# ./innobackupex  --defaults-file=/etc/my.cnf --stream=tar /tmp --user system --password 'Mysql#2015' | gzip -> /backup/xtra/xtra_fullbackup.tar.gz

--innobackupex增量備份

--進行全量備份
[root@localhost backup]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System#2013' /backup/20160719/

.....
160719 07:24:33 [01] Copying ./mysql/help_relation.frm to /backup/20160719/2016-07-19_07-24-18/mysql/help_relation.frm
160719 07:24:33 [01]        ...done
160719 07:24:33 Finished backing up non-InnoDB tables and files
160719 07:24:33 [00] Writing xtrabackup_binlog_info
160719 07:24:33 [00]        ...done
160719 07:24:33 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186521799'
xtrabackup: Stopping log copying thread.
.160719 07:24:33 >> log scanned up to (186521808)

160719 07:24:33 Executing UNLOCK TABLES
160719 07:24:33 All tables unlocked
160719 07:24:33 [00] Copying ib_buffer_pool to /backup/20160719/2016-07-19_07-24-18/ib_buffer_pool
160719 07:24:33 [00]        ...done
160719 07:24:33 Backup created in directory '/backup/20160719/2016-07-19_07-24-18'
MySQL binlog position: filename 'production-bin.000001', position '335', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
e240ea67-4dbb-11e6-a7d5-000c29631605:1'
160719 07:24:33 [00] Writing backup-my.cnf
160719 07:24:33 [00]        ...done
160719 07:24:34 [00] Writing xtrabackup_info
160719 07:24:34 [00]        ...done
xtrabackup: Transaction log of lsn (186521799) to (186521808) was copied.
160719 07:24:34 completed OK!

[root@localhost 2016-07-19_07-24-18]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 186521799
last_lsn = 186521808
compact = 0
recover_binlog_info = 0
--建立測試表一併插入資料
mysql> use fire
Database changed
mysql> create table emp(id int(7), name varchar(15));
Query OK, 0 rows affected (0.53 sec)

mysql> insert into emp values(10, 'Neo');
Query OK, 1 row affected (0.15 sec)
mysql> insert into emp values(20, 'Trinity');
Query OK, 1 row affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from emp;
+------+---------+
| id   | name    |
+------+---------+
|   10 | Neo     |
|   20 | Trinity |
+------+---------+
2 rows in set (0.06 sec)

--進行增量備份一，以上面的全量備份目錄為基礎
[root@localhost 2016-07-19_07-24-18]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System#2013' --incremental /backup/20160719/inc1 --incremental-basedir=/backup/20160719/2016-07-19_07-24-18/
.....
160719 07:29:04 [01] Copying ./mysql/proc.MYD to /backup/20160719/inc1/2016-07-19_07-28-54/mysql/proc.MYD
160719 07:29:04 [01]        ...done
160719 07:29:04 [01] Copying ./mysql/help_relation.frm to /backup/20160719/inc1/2016-07-19_07-28-54/mysql/help_relation.frm
160719 07:29:04 [01]        ...done
160719 07:29:04 Finished backing up non-InnoDB tables and files
160719 07:29:04 [00] Writing xtrabackup_binlog_info
160719 07:29:04 [00]        ...done
160719 07:29:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186528276'
xtrabackup: Stopping log copying thread.
.160719 07:29:04 >> log scanned up to (186528285)

160719 07:29:04 Executing UNLOCK TABLES
160719 07:29:04 All tables unlocked
160719 07:29:04 [00] Copying ib_buffer_pool to /backup/20160719/inc1/2016-07-19_07-28-54/ib_buffer_pool
160719 07:29:04 [00]        ...done
160719 07:29:04 Backup created in directory '/backup/20160719/inc1/2016-07-19_07-28-54'
MySQL binlog position: filename 'production-bin.000001', position '1045', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
e240ea67-4dbb-11e6-a7d5-000c29631605:1-4'
160719 07:29:04 [00] Writing backup-my.cnf
160719 07:29:04 [00]        ...done
160719 07:29:04 [00] Writing xtrabackup_info
160719 07:29:04 [00]        ...done
xtrabackup: Transaction log of lsn (186528276) to (186528285) was copied.
160719 07:29:04 completed OK!
[root@localhost 2016-07-19_07-28-54]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 186521799
to_lsn = 186528276
last_lsn = 186528285
compact = 0
recover_binlog_info = 0

--建立測試表二並插入資料
mysql> create table emp2 like emp;
Query OK, 0 rows affected (0.12 sec)
mysql> insert into emp2 select * from emp;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> insert into emp2 select * from emp;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> insert into emp2 select * from emp;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select count(*) from emp2;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--進行增量備份二，以上面的全量備份目錄為基礎
[root@localhost 2016-07-19_07-28-54]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System#2013' --incremental /backup/20160719/inc2 --incremental-basedir=/backup/20160719/2016-07-19_07-24-18/
160719 07:35:19 [01] Copying ./mysql/help_relation.frm to /backup/20160719/inc2/2016-07-19_07-35-08/mysql/help_relation.frm
160719 07:35:19 [01]        ...done
160719 07:35:19 Finished backing up non-InnoDB tables and files
160719 07:35:19 [00] Writing xtrabackup_binlog_info
160719 07:35:19 [00]        ...done
160719 07:35:19 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186537693'
xtrabackup: Stopping log copying thread.
.160719 07:35:19 >> log scanned up to (186537702)

160719 07:35:19 Executing UNLOCK TABLES
160719 07:35:19 All tables unlocked
160719 07:35:19 [00] Copying ib_buffer_pool to /backup/20160719/inc2/2016-07-19_07-35-08/ib_buffer_pool
160719 07:35:19 [00]        ...done
160719 07:35:19 Backup created in directory '/backup/20160719/inc2/2016-07-19_07-35-08'
MySQL binlog position: filename 'production-bin.000001', position '2028', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
e240ea67-4dbb-11e6-a7d5-000c29631605:1-8'
160719 07:35:19 [00] Writing backup-my.cnf
160719 07:35:19 [00]        ...done
160719 07:35:19 [00] Writing xtrabackup_info
160719 07:35:19 [00]        ...done
xtrabackup: Transaction log of lsn (186537693) to (186537702) was copied.
160719 07:35:19 completed OK!
[root@localhost 2016-07-19_07-35-08]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 186521799
to_lsn = 186537693
last_lsn = 186537702
compact = 0
recover_binlog_info = 0

--進行增量備份三，以上面的增量備份一為基礎
[root@localhost 2016-07-19_07-35-08]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System#2013' --incremental /backup/20160719/inc3 --incremental-basedir=/backup/20160719/inc1/2016-07-19_07-28-54/
160719 07:40:27 [01] Copying ./mysql/help_relation.frm to /backup/20160719/inc3/2016-07-19_07-40-11/mysql/help_relation.frm
160719 07:40:27 [01]        ...done
160719 07:40:27 Finished backing up non-InnoDB tables and files
160719 07:40:27 [00] Writing xtrabackup_binlog_info
160719 07:40:27 [00]        ...done
160719 07:40:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186537693'
xtrabackup: Stopping log copying thread.
.160719 07:40:27 >> log scanned up to (186537702)

160719 07:40:27 Executing UNLOCK TABLES
160719 07:40:27 All tables unlocked
160719 07:40:27 [00] Copying ib_buffer_pool to /backup/20160719/inc3/2016-07-19_07-40-11/ib_buffer_pool
160719 07:40:27 [00]        ...done
160719 07:40:27 Backup created in directory '/backup/20160719/inc3/2016-07-19_07-40-11'
MySQL binlog position: filename 'production-bin.000001', position '2028', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
e240ea67-4dbb-11e6-a7d5-000c29631605:1-8'
160719 07:40:27 [00] Writing backup-my.cnf
160719 07:40:27 [00]        ...done
160719 07:40:27 [00] Writing xtrabackup_info
160719 07:40:27 [00]        ...done
xtrabackup: Transaction log of lsn (186537693) to (186537702) was copied.
160719 07:40:27 completed OK!
[root@localhost 2016-07-19_07-40-11]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 186528276
to_lsn = 186537693
last_lsn = 186537702
compact = 0
recover_binlog_info = 0
--innobackupex增量恢復
--準備基礎全量備份，同時避免回滾階段。
[root@localhost backup]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/20160719/2016-07-19_07-24-18/
[root@localhost backup]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/20160719/2016-07-19_07-24-18/ --incremental-dir=/backup/20160719/inc1/2016-07-19_07-28-54/ --use-memory=100m
160719 07:50:01 [01] Copying /backup/20160719/inc1/2016-07-19_07-28-54/mysql/user.frm to ./mysql/user.frm
160719 07:50:01 [01]        ...done
160719 07:50:01 [01] Copying /backup/20160719/inc1/2016-07-19_07-28-54/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm
160719 07:50:01 [01]        ...done
160719 07:50:01 [01] Copying /backup/20160719/inc1/2016-07-19_07-28-54/mysql/proc.MYD to ./mysql/proc.MYD
160719 07:50:01 [01]        ...done
160719 07:50:01 [01] Copying /backup/20160719/inc1/2016-07-19_07-28-54/mysql/help_relation.frm to ./mysql/help_relation.frm
160719 07:50:01 [01]        ...done
160719 07:50:01 [00] Copying /backup/20160719/inc1/2016-07-19_07-28-54//xtrabackup_binlog_info to ./xtrabackup_binlog_info
160719 07:50:01 [00]        ...done
160719 07:50:01 [00] Copying /backup/20160719/inc1/2016-07-19_07-28-54//xtrabackup_info to ./xtrabackup_info
160719 07:50:01 [00]        ...done
160719 07:50:01 completed OK!
--準備增量備份三，同時避免回滾階段。
[root@localhost backup]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/20160719/2016-07-19_07-24-18/ --incremental-dir=/backup/20160719/inc3/2016-07-19_07-40-11/ --use-memory=100m
160719 07:55:15 [01] Copying /backup/20160719/inc3/2016-07-19_07-40-11/mysql/func.MYD to ./mysql/func.MYD
160719 07:55:15 [01]        ...done
160719 07:55:15 [01] Copying /backup/20160719/inc3/2016-07-19_07-40-11/mysql/user.frm to ./mysql/user.frm
160719 07:55:15 [01]        ...done
160719 07:55:15 [01] Copying /backup/20160719/inc3/2016-07-19_07-40-11/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm
160719 07:55:15 [01]        ...done
160719 07:55:15 [01] Copying /backup/20160719/inc3/2016-07-19_07-40-11/mysql/proc.MYD to ./mysql/proc.MYD
160719 07:55:15 [01]        ...done
160719 07:55:15 [01] Copying /backup/20160719/inc3/2016-07-19_07-40-11/mysql/help_relation.frm to ./mysql/help_relation.frm
160719 07:55:15 [01]        ...done
160719 07:55:15 [00] Copying /backup/20160719/inc3/2016-07-19_07-40-11//xtrabackup_binlog_info to ./xtrabackup_binlog_info
160719 07:55:15 [00]        ...done
160719 07:55:15 [00] Copying /backup/20160719/inc3/2016-07-19_07-40-11//xtrabackup_info to ./xtrabackup_info
160719 07:55:15 [00]        ...done
160719 07:55:15 completed OK!
[root@localhost backup]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log /backup/20160719/2016-07-19_07-24-18/
InnoDB: PUNCH HOLE support not available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.11 started; log sequence number 186538279
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186538298
160719 07:59:03 completed OK!
[root@localhost fire]# service mysqld stop
root@localhost 20160719]# cd /var/lib/mysql
[root@localhost mysql]# rm -rf *

--啟動資料庫服務，驗證資料
[root@localhost fire]# service mysqld start
mysql> select * from emp;
+------+---------+
| id   | name    |
+------+---------+
|   10 | Neo     |
|   20 | Trinity |
+------+---------+
2 rows in set (0.00 sec)

mysql> select * from emp2;
+------+---------+
| id   | name    |
+------+---------+
|   10 | Neo     |
|   20 | Trinity |
|   10 | Neo     |
|   20 | Trinity |
|   10 | Neo     |
|   20 | Trinity |
+------+---------+
6 rows in set (0.00 sec)

建立一個不包含第二索引（除了主鍵之外的索引）的備份

innobackupex --defaults-file=/var/lib/mysql/main_my.cnf \

>  --user=${MYSQL_USER} --password=${MYSQL_PASS} --compact\

>  --extra-lsndir=/database/backup/2016-08-09/checkpoints/ \

>  --compress --compress-threads=8 \

>  --stream=xbstream --parallel=4 /tmp \

>  2>> /database/backup/2016-08-09/xtra_full_bak_2016-08-09.log \

>  > /database/backup/2016-08-09/xtra_full_bak_2016-08-09.xbstream