Percona Xtrabackup 2.4 怎么恢復指定表

這期內(nèi)容當中小編將會給大家?guī)碛嘘P(guān)Percona Xtrabackup 2.4 怎么恢復指定表，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

在5.6之前的服務(wù)版本，在不同的MySQL服務(wù)中通過復制表的文件來拷貝表是不可能的，即使啟用了innodb_file_per_table。然而，通過Percona XtraBackup，可以從任意的InnoDB數(shù)據(jù)庫中導出指定的表，并將它們導入到使用XtraDB的Percona服務(wù)中或MySQL 5.6。這只對.ibd文件有效。
 
創(chuàng)建測試表
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)
--執(zhí)行備份
[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 格式創(chuàng)建，在備份目錄中以.bd文件格式存在。
[root@localhost /]# find /backup/20160810 -name export_test.*
/backup/20160810/test/export_test.frm
/backup/20160810/test/export_test.ibd

 
當準備備份的時候，增加xtrabackup --export參數(shù)到命令中。
[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這三個文件用于數(shù)據(jù)庫導入中

 
導入表
刪除表
mysql> drop table export_test;
Query OK, 0 rows affected (1.45 sec)
在目標MySQL服務(wù)器上，創(chuàng)建一張具有相同結(jié)構(gòu)的空表。
mysql> CREATE TABLE export_test (
->      a int(11) DEFAULT NULL
->      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)
mysql> ALTER TABLE test.export_test DISCARD TABLESPACE;
Query OK, 0 rows affected (0.10 sec)
拷貝導出文件到數(shù)據(jù)目錄中
[root@localhost test]# cp export_test.ibd export_test.exp export_test.cfg /var/lib/mysql/test
mysql> ALTER TABLE test.export_test IMPORT TABLESPACE;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table '"test"."export_test"' : Tablespace not found
更改文件權(quán)限為mysql
[root@localhost ~]# cd /var/lib/mysql/test/
[root@localhost test]# ls
export_test.frm  test.frm  test.ibd
[root@localhost test]# ls
export_test.cfg  export_test.exp  export_test.frm  export_test.ibd  test.frm  test.ibd
[root@localhost test]# ls -trl
total 236
-rw-r-----. 1 mysql mysql  8556 Aug  8 17:17 test.frm
-rw-r-----. 1 mysql mysql 98304 Aug  8 17:17 test.ibd
-rw-rw----. 1 mysql mysql  8554 Aug 10 15:30 export_test.frm
-rw-r-----. 1 root  root  98304 Aug 10 15:34 export_test.ibd
-rw-r-----. 1 root  root  16384 Aug 10 15:34 export_test.exp
-rw-r--r--. 1 root  root    374 Aug 10 15:34 export_test.cfg
[root@localhost test]# chown -R mysql:mysql .
[root@localhost test]# ls -trl
total 236
-rw-r-----. 1 mysql mysql  8556 Aug  8 17:17 test.frm
-rw-r-----. 1 mysql mysql 98304 Aug  8 17:17 test.ibd
-rw-rw----. 1 mysql mysql  8554 Aug 10 15:30 export_test.frm
-rw-r-----. 1 mysql mysql 98304 Aug 10 15:34 export_test.ibd
-rw-r-----. 1 mysql mysql 16384 Aug 10 15:34 export_test.exp
-rw-r--r--. 1 mysql mysql   374 Aug 10 15:34 export_test.cfg
mysql> ALTER TABLE test.export_test IMPORT TABLESPACE;
Query OK, 0 rows affected (0.11 sec)
驗證表中的數(shù)據(jù)
mysql> select * from export_test;
+------+
| a    |
+------+
|  100 |
|  200 |
+------+
2 rows in set (0.00 sec)
需要注意的是，導入表后，表的永久統(tǒng)計信息是空的，需要重新進行收集
mysql> select * from innodb_index_stats where table_name='export_test';
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name  | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | export_test | GEN_CLUST_INDEX | 2016-08-10 15:36:50 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| test          | export_test | GEN_CLUST_INDEX | 2016-08-10 15:36:50 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | export_test | GEN_CLUST_INDEX | 2016-08-10 15:36:50 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
mysql> select * from innodb_table_stats where table_name='export_test';
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name  | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
| test          | export_test | 2016-08-10 15:36:50 |      2 |                    1 |                        0 |
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)
mysql> analyze table test.export_test;
+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| test.export_test | analyze | status   | OK       |
+------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> select * from innodb_index_stats where table_name='export_test';
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name  | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | export_test | GEN_CLUST_INDEX | 2016-08-10 15:48:32 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| test          | export_test | GEN_CLUST_INDEX | 2016-08-10 15:48:32 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | export_test | GEN_CLUST_INDEX | 2016-08-10 15:48:32 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
mysql> select * from innodb_table_stats where table_name='export_test';
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name  | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
| test          | export_test | 2016-08-10 15:48:32 |      2 |                    1 |                        0 |
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

上述就是小編為大家分享的Percona Xtrabackup 2.4 怎么恢復指定表了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。