共计 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 这三个文件用于数据库导入中
导入表
删除表
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)
上述就是丸趣 TV 小编为大家分享的 Percona Xtrabackup 2.4 怎么恢复指定表了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。
