Percona Xtrabackup 2.4 恢复指定表

清疚 2024-04-18 00:04 92阅读 0赞

在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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2123281/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2123281/