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全量備份資料庫
[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] ...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 20160716]# cd 2016-07-16_01-48-32/
[root@localhost 2016-07-16_01-48-32]# ls -trl
total 77876
-rw-r-----. 1 root root 79691776 Jul 16 01:48 ibdata1
drwxr-x---. 2 root root 4096 Jul 16 01:48 fire
drwxr-x---. 2 root root 12288 Jul 16 01:48 sys
drwxr-x---. 2 root root 4096 Jul 16 01:48 performance_schema
drwxr-x---. 2 root root 4096 Jul 16 01:48 mysql
-rw-r-----. 1 root root 68 Jul 16 01:48 xtrabackup_binlog_info
-rw-r-----. 1 root root 2560 Jul 16 01:48 xtrabackup_logfile
-rw-r-----. 1 root root 117 Jul 16 01:48 xtrabackup_checkpoints
-rw-r-----. 1 root root 6566 Jul 16 01:48 ib_buffer_pool
-rw-r-----. 1 root root 428 Jul 16 01:48 backup-my.cnf
-rw-r-----. 1 root root 577 Jul 16 01:48 xtrabackup_info
[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
[root@localhost 2016-07-19_02-07-35]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 186511470
last_lsn = 186511479
compact = 0
recover_binlog_info = 0
--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/
[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] ...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] ...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/
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] ...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: 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 *
--執行restore操作，將已經準備好的資料複製回my.cnf引數檔案中指定的資料目錄下。
[root@localhost backup]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/20160719/2016-07-19_07-24-18/
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
160719 08:01:32 [01] ...done
160719 08:01:32 completed OK!
--更改資料目錄的許可權
[root@localhost mysql]# chown -R mysql.mysql /var/lib/mysql
--啟動資料庫服務，驗證資料
[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)
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_info160716 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 20160716]# cd 2016-07-16_01-48-32/
[root@localhost 2016-07-16_01-48-32]# ls -trl
total 77876
-rw-r-----. 1 root root 79691776 Jul 16 01:48 ibdata1
drwxr-x---. 2 root root 4096 Jul 16 01:48 fire
drwxr-x---. 2 root root 12288 Jul 16 01:48 sys
drwxr-x---. 2 root root 4096 Jul 16 01:48 performance_schema
drwxr-x---. 2 root root 4096 Jul 16 01:48 mysql
-rw-r-----. 1 root root 68 Jul 16 01:48 xtrabackup_binlog_info
-rw-r-----. 1 root root 2560 Jul 16 01:48 xtrabackup_logfile
-rw-r-----. 1 root root 117 Jul 16 01:48 xtrabackup_checkpoints
-rw-r-----. 1 root root 6566 Jul 16 01:48 ib_buffer_pool
-rw-r-----. 1 root root 428 Jul 16 01:48 backup-my.cnf
-rw-r-----. 1 root root 577 Jul 16 01:48 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
[root@localhost 2016-07-19_02-07-35]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 186511470
last_lsn = 186511479
compact = 0
recover_binlog_info = 0
--在資料庫中建立測試表並插入資料
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!
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!
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.frm160719 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!
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.frm160719 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!
backup_type = incremental
from_lsn = 186528276
to_lsn = 186537693
last_lsn = 186537702
compact = 0
recover_binlog_info = 0
--準備基礎全量備份，同時避免回滾階段。
[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/
.....
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: Log scan progressed past the checkpoint lsn 186521799
InnoDB: Doing recovery: scanned up to log sequence number 186521808 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 186521808 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 864, file name production-bin.000003
InnoDB: xtrabackup: Last MySQL binlog file position 864, file name production-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186521817
InnoDB: Number of pools: 1
160719 07:47:19 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/inc1/2016-07-19_07-28-54/ --use-memory=100mInnoDB: 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: Log scan progressed past the checkpoint lsn 186521799
InnoDB: Doing recovery: scanned up to log sequence number 186521808 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 186521808 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 864, file name production-bin.000003
InnoDB: xtrabackup: Last MySQL binlog file position 864, file name production-bin.000003
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186521817
InnoDB: Number of pools: 1
160719 07:47:19 completed OK!
--準備增量備份一，同時避免回滾階段。
.....
160719 07:50:01 [01] Copying /backup/20160719/inc1/2016-07-19_07-28-54/mysql/user.frm to ./mysql/user.frm160719 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.MYD160719 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!
--當把所有全量備份和增量備份放在一起後，再次準備全部備份（基礎全量備份 + 增量備份）來回滾暫停的事務
.....
InnoDB: PUNCH HOLE support not availableInnoDB: 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 20160719]# cd /var/lib/mysql
[root@localhost mysql]# rm -rf *
--執行restore操作，將已經準備好的資料複製回my.cnf引數檔案中指定的資料目錄下。
[root@localhost backup]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/20160719/2016-07-19_07-24-18/
.....
160719 08:01:32 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
160719 08:01:32 [01] ...done
160719 08:01:32 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
160719 08:01:32 [01] ...done
160719 08:01:32 completed OK!
--更改資料目錄的許可權
[root@localhost mysql]# chown -R mysql.mysql /var/lib/mysql
--啟動資料庫服務，驗證資料
[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2088737/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Percona XtraBackup 2.4 xtrabackup全量、增量備份恢復流程
- Percona XtraBackup 實現全備&增量備份與恢復
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- mysql之 Innobackupex（全備+增量）備份恢復MySql
- MySQL innobackupex全量備份恢復MySql
- MySQL 5.6 xtrabackup 全量和增量的備份和恢復MySql
- mysql innobackupex增量備份恢復MySql
- 【MySql】innobackupex增量備份和恢復MySql
- 【MySql】innobackupex 增量備份和恢復MySql
- INNOBACKUPEX的全備和增量備份恢復學習筆記筆記
- Xtrabackup之innobackupex備份恢復詳解薦
- 【Mysql】xbackup全量與增量備份恢復MySql
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- 使用innobackupex線上增量備份和再增量備份及恢復mysql資料庫MySql資料庫
- innobackupex全備份流程圖流程圖
- MySQL運維實戰之備份和恢復（8.1）xtrabackup全量備份MySql運維
- innobackupex備份mysql大資料(全量+增量）操作記錄MySql大資料
- Xtrabackup備份恢復原理
- Mysql備份系列（3）--innobackupex備份mysql大資料(全量+增量）操作記錄MySql大資料
- innobackupex備份恢復實戰
- xtrabackup和innobackuppex的安裝和備份(含增量備份)已經恢復
- MySQL 之XtraBackup全量增量熱備實踐MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 【MySql】innobackupex 增量備份的bugMySql
- 利用innobackupex備份集恢復指定庫
- MySQL · 物理備份 · Percona XtraBackup 備份原理MySql
- Percona Xtrabackup 快速備份 MySQLMySql
- XtraBackup完整備份與增量備份的原理
- oracle 增量備份恢復驗證Oracle
- MySQL增量備份的指令碼（innobackupex）MySql指令碼
- 【MySQL】Xtrabackup備份及恢復指令碼MySql指令碼
- 【Mysql】xtrabackup 備份和恢復測試MySql
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- 如何用Percona XtraBackup進行MySQL從庫的單表備份和恢復MySql
- 基於percona xtrabackup之innobackupex實現基於時間點資料庫恢復資料庫
- windows 全量+增量備份指令碼batWindows指令碼BAT
- mysqldump 全量和增量備份指令碼MySql指令碼
- mysql 利用binlog增量備份、恢復MySql