Percona Xtrabackup 2.4 如何恢复指定表
|
这期内容当中小编将会给大家带来有关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 怎么恢复指定表了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
