实践：mariadb进行表空间传输

需求：将源库中的表迁移到目标库中，通过传输表空间方式。

这个操作比导入导出方便多了。

环境：

MariaDB [lixf]> select version();
+---------------------+
| version()           |
+---------------------+
| 10.0.13-MariaDB-log |
+---------------------+
1 row in set (0.01 sec)

下面为具体的操作步骤：

传输表空间，针对于innodb：
在源库上创建表如在test库中创建lixf22：
MariaDB [test]> create table lixf22(id int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into lixf22 values(11);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into lixf22 values(11);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into lixf22 values(11);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into lixf22 values(11);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from lixf22;
+------+
| id   |
+------+
|   11 |
|   11 |
|   11 |
|   11 |
+------+
4 rows in set (0.00 sec)

MariaDB [test]> flush table lixf22 for export;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> unlock tables;
Query OK, 0 rows affected (0.01 sec)

下面为日志信息，注意export会生成.cfg文件。
141214 14:38:46 [Note] InnoDB: Sync to disk of '"test"."lixf22"' started.
141214 14:38:46 [Note] InnoDB: Stopping purge
141214 14:38:46 [Note] InnoDB: Writing table metadata to './test/lixf22.cfg'
141214 14:38:46 [Note] InnoDB: Table '"test"."lixf22"' flushed to disk
141214 14:39:05 [Note] InnoDB: Deleting the meta-data file './test/lixf22.cfg'
141214 14:39:05 [Note] InnoDB: Resuming purge




在新库上如lixf中，创建需要导入的表lixf22的表结构信息。
MariaDB [lixf]>  create table lixf22(id int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [lixf]> alter table lixf22 discard tablespace;      #######这步比较关键
Query OK, 0 rows affected (0.01 sec)


#######导入表空间
MariaDB [lixf]> alter table lixf22 import tablespace;
Query OK, 0 rows affected (0.04 sec)

MariaDB [lixf]>

####下面可以查询lixf22表中的信息了。
MariaDB [lixf]> select * from lixf22;
+------+
| id   |
+------+
|   11 |
|   11 |
|   11 |
|   11 |
+------+

下面为日志信息，

141214 14:40:13 [Note] InnoDB: Importing tablespace for table 'test/lixf22' that was exported from host 'mysql2'
141214 14:40:13 [Note] InnoDB: Phase I - Update all pages
141214 14:40:13 [Note] InnoDB: Sync to disk
141214 14:40:13 [Note] InnoDB: Sync to disk - done!
141214 14:40:13 [Note] InnoDB: Phase III - Flush changes to disk
141214 14:40:13 [Note] InnoDB: Phase IV - Flush complete

mysql 5.6新版本也具有上面的功能，具体大家可能测试一下。

Chapter 14 Storage Engines