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-f --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 怎么恢复指定表了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。