13518219792
这期内容当中小编将会给大家带来有关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这三个文件用于数据库导入中
导入表
删除表
mysql> drop table export_test;
Query OK, 0 rows affected (1.45 sec)
在目标MySQL服务器上，创建一张具有相同结构的空表。
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)
拷贝导出文件到数据目录中
[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
更改文件权限为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)
验证表中的数据
mysql> select * from export_test;
+------+
| a |
+------+
| 100 |
| 200 |
+------+
2 rows in set (0.00 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: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 怎么恢复指定表了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注创新互联行业资讯频道。
