有cfg文件时表空间迁移
在源表上显示建表语句：
mysql> use test mysql> show create table t1; +-------+---------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
在目标表上创建一样的表：
mysql> CREATE TABLE `t1` ( -> `a` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; Query OK, 0 rows affected (0.07 sec)
在目标表上放弃此表对应的数据文件
mysql> alter table t1 discard tablespace; Query OK, 0 rows affected (0.03 sec) mysql> system ls -l /var/lib/mysql/test/t1.* -rw-r----- 1 mysql mysql 8554 Jan 10 02:06 /var/lib/mysql/test/t1.frm mysql>
在源表上输出表，注意加了一个读锁，产生了一个cfg结尾的文件。
mysql> flush tables t1 for export; Query OK, 0 rows affected (0.04 sec) mysql> select * from t1; +------+ | a | +------+ | 1 | +------+ mysql> update t1 set a=1; ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated mysql> 1 row in set (0.02 sec) root@scutech:/var/lib/mysql/test# ls -l /var/lib/mysql/test/t1.* -rw-r----- 1 mysql mysql 356 Jan 10 13:41 /var/lib/mysql/test/t1.cfg -rw-r----- 1 mysql mysql 8554 Jan 2 18:32 /var/lib/mysql/test/t1.frm -rw-r----- 1 mysql mysql 98304 Jan 2 18:32 /var/lib/mysql/test/t1.ibd
把文件拷贝过去：
root@scutech:/var/lib/mysql/test# scp t1.{cfg,ibd} scutech@192.168.??.???:/var/lib/mysql/test/ scutech@192.168.??.???'s password: t1.cfg 100% 356 212.9KB/s 00:00 t1.ibd 100% 96KB 15.6MB/s 00:00 root@scutech:/var/lib/mysql/test#
在源上解锁表：
mysql> unlock tables; Query OK, 0 rows affected (0.01 sec)
在目标上库导入：
mysql> select * from t1; ERROR 1814 (HY000): Tablespace has been discarded for table 't1' mysql> alter table t1 import tablespace; Query OK, 0 rows affected (0.03 sec) mysql> select * from t1; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql>
无cfg文件时表空间迁移
如果源数据库以及crash了，不可能导出cfg文件，我们怎么进行迁移呢？我们可以试一下没有cfg文件的加载，其它步骤一样：
mysql> \! chown mysql. /var/lib/mysql/test/a.ibd mysql> alter table a import tablespace; Query OK, 0 rows affected, 1 warning (0.07 sec) mysql> show warnings; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/a.cfg', will attempt to import without schema verification | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql>
我们看到提示只是没有进行schema的校验，顺利加载。
