思享工具箱

MySQL 5.6表空间传输

摘要：
这个实验环境：服务器版本：percona-5.6.21版本添加：percona-5.5.40备份工具版本：perona-xtrabackup-2.2.5安装过程非常简单。这里不描述方法1：1.检查独立表空间mysql˃showglobalvariables（如“%innodb”）是否已启用_file_pe%；+-------------------------+---------+|变量名称|值|+----------------------------+|innodb_file_per_表|开|+---------------------------------------+----------------------------+行集II。创建库，在库中创建测试表，插入点数据，然后进行验证。

在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-5.5.40版本 (下载地址：http://www.percona.com/downloads/Percona-Server-5.5/Percona-Server-5.5.40-36.1/binary/tarball/Percona-Server-5.5.40-rel36.1-707.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）

安装过程非常简单，这里不作说明

方法一：

一、检查是否开启了独立表空间

mysql> show global variables like '%innodb_file_pe%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00sec)

二、创建一个库，在库里创建一个test表，插入点数据，一会好验证。在实验中，我发现（虽然MySQL5.6版本默认已经开启innodb_file_per_table选项，但在percona5.6的版本，我查看的时候是ON状态，但创建表的时候，只生成.frm文件而没有.ibd文件，所以创建表时，要查看下是否成功开启独立表空间了）：

mysql> create databaseirfan;
Query OK, 1 row affected (0.07sec)

mysql> useirfan;
Databasechanged
mysql> CREATE TABLE`test` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` char(15) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11sec)

mysql> insert into test select * fromtestdb.test;
Query OK, 10 rows affected (0.05sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> select * fromtest;
+------+------+
| 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)
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

三、把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.txt
irfan.test

四、我们用比较新版本的Percona xtrabackup，因为支持多种方式采取部分备份，我们用--tables-file选项备份特定的表。(/root/partial_backup/ 这目录不用创建，如果之前存在，会报错innobackupex: Error: Failed to create backup directory /root/partial_backup/: 文件已存在 at /usr/bin/innobackupex line 3949.)

[root ~]$ xtrabackup --version
xtrabackup 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[root irfan]$ pwd
/root/partial_backup/irfan
[root irfan]$ lstest.frm  test.ibd

查看一下备份成功了没，若completed OK，那就OK了：

[root ~]$ catxtrabackup.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:50innobackupex: Connection to database server closed
141205 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[root irfan]$ pwd
/root/partial_backup/irfan
[root irfan]$ lstest.cfg  test.exp  test.frm  test.ibd

查看日志最后是否为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 1659926
141205 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.00sec)
mysql [localhost] {msandbox} ((none)) > create databaseirfan;
Query OK, 1 row affected (0.02sec)
mysql [localhost] {msandbox} ((none)) > useirfan;
Databasechanged
mysql [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 TABLEtest 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]$ scptest.cfg test.ibd
192.168.1.128:/root/sandboxes/msb_5_6_21/data/irfan

（在还原机操作）执行导入表空间命令，然后查看表数据：

mysql [localhost] {msandbox} (irfan) > ALTER TABLEtest IMPORT TABLESPACE;
Query OK, 0 rows affected (0.04sec)

mysql [localhost] {msandbox} (irfan) > select * fromtest;
+------+------+
| 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 tostart
2014-12-11 16:16:27 34035 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.21-rel70.1 started; log sequence number 1752156
2014-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: 5621
2014-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 0events
2014-12-11 16:16:27 34035 [Note] /usr/local/src/5.6.21/bin/mysqld: ready forconnections.
Version: '5.6.21-70.1'  socket: '/tmp/mysql_sandbox5621.sock'  port: 5621  Percona Server (GPL), Release 70.1, Revision 698
2014-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 allpages
2014-12-11 16:19:05 34035 [Note] InnoDB: Sync to disk
2014-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 disk
2014-12-11 16:19:05 34035 [Note] InnoDB: Phase IV - Flush complete

哈哈，已经成功还原表数据了^.^

方法二：从运行MySQL实例复制表到另一个运行MySQL服务器是在MySQL手册描述了另一种方法:

mysql> show create table dummy;  
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| dummy | CREATE TABLE `dummy` (
  `id` int(11) DEFAULT NULL,
  `dummy` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00sec)

mysql> select * from dummy;    
+------+-------+
| id   | dummy |
+------+-------+
|    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.00sec)

mysql>

FLUSH TABLES FOR EXPORT只适用于Oracle MySQL 5.6/Percona Server 5.6，FLUSH TABLES FOR EXPORT会产生表的元数据文件（.cfg)和表空间文件(.ibd)

mysql> FLUSH TABLES dummy FOREXPORT;
Query OK, 0 rows affected (0.00sec)
mysql>

进到数据目录下查看已经产生了dummy.cfg和dummy.idb文件了：

[root irfan]$ pwd
/data/percona5.6/irfan
[root irfan]$ lsdb.opt  dummy.cfg  dummy.frm  dummy.ibd  test.frm  test.ibd

去到另一台服务器，创建表，表结构一定要和目标MySQL上的一样，再执行丢弃表空间命令：

mysql [localhost] {msandbox} (irfan) > useirfan;
Databasechanged
mysql [localhost] {msandbox} (irfan) > CREATE TABLE `dummy` ( `id` int(11) DEFAULT NULL, `dummy` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04sec)

mysql [localhost] {msandbox} (irfan) >  ALTER TABLE dummyDISCARD TABLESPACE;
Query OK, 0 rows affected (0.03 sec)

回到目标服务器,把dummy.cfg和dummy.ibd远程拷备到另一台服务器上：

[root irfan]$ scp  dummy.cfg  dummy.ibd 192.168.1.128:/root/sandboxes/msb_5_6_21/data/irfan

然后回到数据库执行解释操作，再查看，已经没有dummy.cfg文件了,所以一定确定scp完了后再解锁：

mysql>UNLOCK TABLES;
Query OK, 0 rows affected (0.06sec)

[root irfan]$ lsdb.opt  dummy.frm  dummy.ibd  test.frm  test.ibd

执行导入表空间命令，然后查看表数据：

mysql [localhost] {msandbox} (irfan) >ALTER TABLE dummy IMPORT TABLESPACE;
Query OK, 0 rows affected (0.03sec)

mysql [localhost] {msandbox} (irfan) >show tables;
+-----------------+
| Tables_in_irfan |
+-----------------+
| dummy           |
| test            |
+-----------------+
2 rows in set (0.00sec)

mysql [localhost] {msandbox} (irfan) > select *from dummy;
+------+-------+
| id   | dummy |
+------+-------+
|    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)

查看日志，已经成功导入了^。^

2014-12-12 12:48:21 34035 [Note] InnoDB: Importing tablespace for table 'irfan/dummy' that was exported from host 'Hostname unknown'
2014-12-12 12:48:21 34035 [Note] InnoDB: Phase I -Update all pages
2014-12-12 12:48:21 34035[Note] InnoDB: Sync to disk
2014-12-12 12:48:21 34035 [Note] InnoDB: Sync to disk - done!
2014-12-12 12:48:21 34035 [Note] InnoDB: Phase III -Flush changes to disk
2014-12-12 12:48:21 34035 [Note] InnoDB: Phase IV - Flush complete

总结：第一种方法步骤多一些，而且要借用开源的备份工具percona-xtrabackup，而且要是版本比较新的;

第二种方法，步骤简单，操作起来方便，但是要进行锁表操作，而且FLUSH TABLES FOR EXPORT只适用于Oracle MySQL 5.6/Percona Server 5.6，对于一个繁忙的OLTP系统，如果表比较大，锁住的时间比较长，对业务影响比较大

参考资料：http://www.percona.com/blog/2014/12/09/mysql-5-6-transportable-tablespaces-best-practices/

作者：陆炫志

出处：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111

您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。

标签：#表空间#mysql#percona#mysql创建数据库#root权限#innodb#xtrabackup#table | 浏览：127 | 发布日期：

免责声明：文章转载自《MySQL 5.6表空间传输》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇【LinearGradientBrush】线性渐变笔刷iOS地图及定位功能基本实现的详尽描述下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

MySQL触发器Trigger实例篇

定义： 何为MySQL触发器？ 在MySQL Server里面也就是对某一个表的一定的操作，触发某种条件（Insert,Update,Delete 等），从而自动执行的一段程序。从这种意义上讲触发器是一个特殊的存储过程。下面通过MySQL触发器实例，来了解一下触发器的工作过程吧！ 一、创建MySQL实例数据表： 在mysql的默认的测试test数据库下，...

【MySQL 组复制】1.组复制技术简介

组复制有两种模式 单主模式(single-primary/single-master)下自动选举出一个主节点，从而只允许在同一时刻只有该主节点可以更新数据。 对于MySQL的高级使用人员，可以通过复制组实现多主模型(multi-primary)，这种模型下，所有的主节点都可以在同一时刻接受更新操作，即并发写。 MySQL组复制有一个内置的组成员服务(gro...

Linux在没有root权限的情况下安装gcc环境

1. 安装gcc 首先下载新版本的gcc：http://ftp.gnu.org/gnu/gcc/ 安装GCC主要依赖三个库：GMP, MPFR 和MP）。之前的旧版本可能需要手动依次安装这些依赖库，但较新的版本其实只需要在GCC目录下运行下面这个命令就可以自动下载这几个组件，好像不用联网也可以。 ./contrib/download_prerequisit...

MySQL之alter语句用法总结

MySQL之alter语句用法总结 1：删除列 ALTER TABLE 【表名字】 DROP 【列名称】 2：增加列 ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL  COMMENT '注释说明' 3：修改列的类型信息 ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】...

MySQL 同时 delete 多张表的数据

三张表 article ，tag，article_tag article 文章表 create tablearticle( article_id int primary keyauto_increment, article_title varchar(100) ); insert into article(article_title) va...

MySQL 4.1/5.0/5.1/5.5各版本的主要区别

1、4.1 增加了子查询的支持，字符集增加UTF-8，GROUP BY语句增加了ROLLUP，mysql.user表采用了更好的加密算法。 2、5.0 增加了Stored procedures、Views、Cursors、Triggers、XA transactions的支持，增加了INFORATION_SCHEMA系统数据库。 3、5.1 增加了Even...

最新文章

随机推荐

思享工具箱导航