Percona Xtrabackup 2.4 怎么恢復指定表

135次閱讀

共計 12036 個字符，預計需要花費 31 分鐘才能閱讀完成。

這期內容當中丸趣 TV 小編將會給大家帶來有關 Percona Xtrabackup 2.4 怎么恢復指定表，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

在 5.6 之前的服務版本，在不同的 MySQL 服務中通過復制表的文件來拷貝表是不可能的，即使啟用了 innodb_file_per_table。然而，通過 Percona XtraBackup，可以從任意的 InnoDB 數據庫中導出指定的表，并將它們導入到使用 XtraDB 的 Percona 服務中或 MySQL 5.6。這只對.ibd 文件有效。

創建測試表
mysql use test;
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_test |
+—————-+
| test |
+—————-+
1 row in set (0.00 sec)
mysql CREATE TABLE export_test (
– a int(11) DEFAULT NULL
– ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.31 sec)
mysql insert into export_test values(100),(200);
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql commit;
Query OK, 0 rows affected (0.00 sec)
mysql select * from export_test;
+——+
| a |
+——+
| 100 |
| 200 |
+——+
2 rows in set (0.03 sec)

導出表
mysql show variables like innodb_file_per_table
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| innodb_file_per_table | ON |
+———————–+——-+
1 row in set (0.00 sec)
– 執行備份
[root@localhost mysql]# /install/percona-xtrabackup-2.4.4-Linux-x86_64/bin/xtrabackup –defaults-file=/etc/my.cnf –backup –datadir=/var/lib/mysql/ –target-dir=/backup/20160810 –user root –password root
導出的表必須是以 innodb_file_per_table 格式創建，在備份目錄中以.bd 文件格式存在。
[root@localhost /]# find /backup/20160810 -name export_test.*
/backup/20160810/test/export_test.frm
/backup/20160810/test/export_test.ibd

當準備備份的時候，增加 xtrabackup –export 參數到命令中。
[root@localhost mysql]# /install/percona-xtrabackup-2.4.4-Linux-x86_64/bin/xtrabackup –prepare –export –target-dir=/backup/20160810/
/install/percona-xtrabackup-2.4.4-Linux-x86_64/bin/xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
xtrabackup: auto-enabling –innodb-file-per-table due to the –export option
xtrabackup: cd to /backup/20160810
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1639441)
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 = 1
xtrabackup: innodb_log_file_size = 8388608
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
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: The log sequence number 1633851 in the system tablespace does not match the log sequence number 1639441 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 1639441 (0%)
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.13 started; log sequence number 1639441
xtrabackup: export option is specified.
xtrabackup: export metadata of table mysql/innodb_index_stats to file `./mysql/innodb_index_stats.exp` (1 indexes)
xtrabackup: name=PRIMARY, id.low=18, page=3
xtrabackup: export metadata of table mysql/innodb_table_stats to file `./mysql/innodb_table_stats.exp` (1 indexes)
xtrabackup: name=PRIMARY, id.low=17, page=3
xtrabackup: export metadata of table mysql/slave_worker_info to file `./mysql/slave_worker_info.exp` (1 indexes)
xtrabackup: name=PRIMARY, id.low=21, page=3
xtrabackup: export metadata of table mysql/slave_relay_log_info to file `./mysql/slave_relay_log_info.exp` (1 indexes)
xtrabackup: name=PRIMARY, id.low=19, page=3
xtrabackup: export metadata of table mysql/slave_master_info to file `./mysql/slave_master_info.exp` (1 indexes)
xtrabackup: name=PRIMARY, id.low=20, page=3
xtrabackup: export metadata of table test/export_test to file `./test/export_test.exp` (1 indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=23, page=3
xtrabackup: export metadata of table test/test to file `./test/test.exp` (1 indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=22, page=3

xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 1639460
InnoDB: Number of pools: 1
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: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1639460
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1639948
InnoDB: Doing recovery: scanned up to log sequence number 1639957 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 1639957 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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.13 started; log sequence number 1639957
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 1639976
160810 15:14:42 completed OK!
在目標目錄下，可以看到.exp 文件
[root@localhost ~]# cd /backup/20160810/test/
[root@localhost test]# ls -trl
total 256
-rw-r—–. 1 root root 98304 Aug 10 15:06 export_test.ibd
-rw-r—–. 1 root root 98304 Aug 10 15:06 test.ibd
-rw-r—–. 1 root root 8554 Aug 10 15:06 export_test.frm
-rw-r—–. 1 root root 8556 Aug 10 15:06 test.frm
-rw-r—–. 1 root root 16384 Aug 10 15:14 export_test.exp
-rw-r–r–. 1 root root 374 Aug 10 15:14 export_test.cfg
-rw-r—–. 1 root root 16384 Aug 10 15:14 test.exp
-rw-r–r–. 1 root root 369 Aug 10 15:14 test.cfg
.exp、.ibd、.cfg 這三個文件用于數據庫導入中