首页 > 代码库 > MySQL 5.6传输表空间
MySQL 5.6传输表空间
在MySQL 5.6 Oracle引入了一个可移动表空间的特征（复制的表空间到另一个服务器）和Percona Server采用部分备份，这意味着你现在可以备份单个数据库或表；由于Percona Server 5.6的出现，innodb_import_table_from_xtrabackup是过时的Percona Server实现Oracle MySQL的可移动表空间的功能，就是在服务器之间复制的表空间的能力（table.ibd）。让我展示通过一个例子，我将采取选择性表部分备份而不是一个完整的MySQL服务器并且在线恢复它，而不需要关闭MySQL服务器
该实验用到一个备份工具percona xtrabackup，是一个开源且免费的MySQL数据库热备份软件,执行非阻塞InnoDB和xtradb数据库备份，想当好用， 最重要Percona支持部分备份模式，对应于特定的数据库或表格备份。
本实验环境：
服务器端版本：percona-5.6.21版本（下载地址：http://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.21-70.1/binary/tarball/Percona-Server-5.6.21-rel70.1-698.Linux.x86_64.tar.gz）
备份工具版本：percona-xtrabackup-2.2.5（下载地址：http://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.5/binary/tarball/percona-xtrabackup-2.2.5-5027-Linux-x86_64.tar.gz）
安装过程非常简单，这里不作说明
二、检查是否开启了独立表空间，在实验中，我发现（虽然MySQL5.6版本默认已经开启innodb_file_per_table选项，但在percona5.6的版本，我查看的时候是ON状态，但创建表的时候，只生成.frm文件而没有.ibd文件，所以创建表时，要查看下是独立表空间是否生效了：）
mysql> show global variables like ‘%innodb_file_pe%‘;+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+1 row in set (0.00 sec)mysql> system ls -l /data/percona5.6/irfan/ 总用量 112-rw-rw---- 1 percona5.6 percona5.6 65 12月 5 21:36 db.opt-rw-rw---- 1 percona5.6 percona5.6 8586 12月 5 21:37 test.frm-rw-rw---- 1 percona5.6 percona5.6 98304 12月 5 21:38 test.ibd
二、创建一个库，在库里创建一个test表，插入点数据，一会好验证。
mysql> create database irfan;Query OK, 1 row affected (0.07 sec)mysql> use irfan;Database changedmysql> CREATE TABLE `test` ( -> `id` int(11) DEFAULT NULL, -> `name` char(15) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.11 sec)mysql> insert into test select * from testdb.test;Query OK, 10 rows affected (0.05 sec)Records: 10 Duplicates: 0 Warnings: 0mysql> select * from test;+------+------+| id | name |+------+------+| 1 | aa || 2 | bb || 3 | cc || 4 | dd || 5 | ee || 6 | ff || 7 | gg || 8 | hh || 9 | ii || 10 | jj |+------+------+10 rows in set (0.00 sec)
三、把table_schema为irfan（就是刚刚创建的库）下的表test导出，保存为.txt的格式
[root ~]$ /usr/local/percona-5.6/bin/mysql -uroot -p123456 -S /data/percona5.6/mysql.sock --skip-column-names > -e "SELECT CONCAT(table_schema,‘.‘,table_name) FROM information_schema.tables tables \> WHERE table_schema IN (‘irfan‘) AND TABLE_NAME = ‘test‘;" > /root/tables_to_backup.txt[root ~]$ cat /root/tables_to_backup.txtirfan.test
四、我们用比较新版本的Percona xtrabackup，因为支持多种方式采取部分备份，我们用--tables-file选项备份特定的表。
[root ~]$ xtrabackup --versionxtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )[root ~]$ innobackupex --defaults-file=/usr/local/percona-5.6/my.cnf --user=root --password=123456 --port=3310 --sock=/data/percona5.6/mysql.sock > --no-timestamp --tables-file=/root/tables_to_backup.txt /root/partial_backup/ > /root/xtrabackup.log 2>&1
查看一下备份成功了没，若completed OK，那就OK了：
[root ~]$ cat xtrabackup.log ............
innobackupex: Backup created in directory ‘/root/partial_backup‘innobackupex: MySQL binlog position: filename ‘mysql-bin.000003‘, position 3398, GTID of the last change ‘‘141205 21:41:50 innobackupex: Connection to database server closed141205 21:41:50 innobackupex: completed OK!
下一步，我们需要准备备份，因为可能会有uncomitted事务需要回滚或事务日志重播备份， --export选项是为了创建 table.exp and table.cfg
[root ~]$ innobackupex --defaults-file=/usr/local/percona-5.6/my.cnf --user=root --password=123456 --port=3310 --sock=/data/percona5.6/mysql.sock > -apply-log --export /root/partial_backup/ > /root/xtrabackup-prepare.log 2>&1
查看最后是否是completed OK
[root ~]$ cat xtrabackup-prepare.log...........xtrabackup: starting shutdown with innodb_fast_shutdown = 0InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1659926141205 21:42:35 innobackupex: completed OK!
五、在另一台服务器进行还原操作：（我用的是percona5.6的沙箱环境，有关沙箱的安装，请查看我的博客：http://www.cnblogs.com/xuanzhi201111/p/3998604.html）
同样要确定独立表空间已经开启了，然后创建表，表结构要和原来备份的一致：
mysql [localhost] {msandbox} (none) > show variables like ‘innodb_file_per_table‘;+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+1 row in set (0.00 sec)mysql [localhost] {msandbox} ((none)) > create database irfan;Query OK, 1 row affected (0.02 sec)mysql [localhost] {msandbox} ((none)) > use irfan;Database changedmysql [localhost] {msandbox} (irfan) > CREATE TABLE `test` ( `id` int(11) DEFAULT NULL, `name` char(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.12 sec)
执行丢弃表空间命令
mysql [localhost] {msandbox} (irfan) > ALTER TABLE test DISCARD TABLESPACE;Query OK, 0 rows affected (0.02 sec)
查看发现.ibd表空间文件没了：
[root irfan]$ lsdb.opt test.frm
在备份的服务器上，把备份的表空间文件scp过来，注意：（要查看test.cfg文件和test.ibd文件权限是否给对）
[root irfan]$ pwd/root/partial_backup/irfan[root irfan]$ scp test.cfg test.ibd192.168.1.128:/root/sandboxes/msb_5_6_21/data/irfan
执行导入表空间命令，然后查看表数据：
mysql [localhost] {msandbox} (irfan) > ALTER TABLE test IMPORT TABLESPACE;Query OK, 0 rows affected (0.04 sec)mysql [localhost] {msandbox} (irfan) > select * from test;+------+------+| id | name |+------+------+| 1 | aa || 2 | bb || 3 | cc || 4 | dd || 5 | ee || 6 | ff || 7 | gg || 8 | hh || 9 | ii || 10 | jj |+------+------+10 rows in set (0.00 sec)
数据已经导回来，但为了安全起见，最好看看错误日志是否有报错，正常的应该没warning和error字眼的：
[root data]$ tail -f msandbox.err 2014-12-11 16:16:27 34035 [Note] InnoDB: Waiting for purge to start2014-12-11 16:16:27 34035 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.21-rel70.1 started; log sequence number 17521562014-12-11 16:16:27 34035 [Note] RSA private key file not found: /root/sandboxes/msb_5_6_21/data//private_key.pem. Some authentication plugins will not work.2014-12-11 16:16:27 34035 [Note] RSA public key file not found: /root/sandboxes/msb_5_6_21/data//public_key.pem. Some authentication plugins will not work.2014-12-11 16:16:27 34035 [Note] Server hostname (bind-address): ‘127.0.0.1‘; port: 56212014-12-11 16:16:27 34035 [Note] - ‘127.0.0.1‘ resolves to ‘127.0.0.1‘;2014-12-11 16:16:27 34035 [Note] Server socket created on IP: ‘127.0.0.1‘.2014-12-11 16:16:27 34035 [Note] Event Scheduler: Loaded 0 events2014-12-11 16:16:27 34035 [Note] /usr/local/src/5.6.21/bin/mysqld: ready for connections.Version: ‘5.6.21-70.1‘ socket: ‘/tmp/mysql_sandbox5621.sock‘ port: 5621 Percona Server (GPL), Release 70.1, Revision 6982014-12-11 16:19:05 34035 [Note] InnoDB: Importing tablespace for table ‘irfan/test‘ that was exported from host ‘Hostname unknown‘2014-12-11 16:19:05 34035 [Note] InnoDB: Phase I - Update all pages2014-12-11 16:19:05 34035 [Note] InnoDB: Sync to disk2014-12-11 16:19:05 34035 [Note] InnoDB: Sync to disk - done!2014-12-11 16:19:05 34035 [Note] InnoDB: Phase III - Flush changes to disk2014-12-11 16:19:05 34035 [Note] InnoDB: Phase IV - Flush complete
哈哈，已经成功还原表数据了^.^
参考资料：http://www.percona.com/blog/2014/12/09/mysql-5-6-transportable-tablespaces-best-practices/
MySQL 5.6传输表空间