表空间传输实验
1.限制:
- 不能并发做表空间传输
- 不能有外健键
2.操作
-查看待表并执行'flush table @table-name for export'root@localhost [wenyz]>show tables;+-----------------+| Tables_in_wenyz |+-----------------+| t2 |+-----------------+1 row in set (0.00 sec)root@localhost [wenyz]>flush table t2 for export;Query OK, 0 rows affected (0.01 sec)
- 复制t2.cfg和t2.cfg到tmp目录,并释放锁
#rm -rf t2.sql [root@db211_08:51:11 /tmp] #ll t2*-rw-r----- 1 root root 431 Aug 2 08:35 t2.cfg-rw-r----- 1 root root 14680064 Aug 2 08:35 t2.ibdfgroot@localhost [wenyz]>unlock tables; //注意flush table * for export 是会加X锁的,等复制完之后需要释放
- 在目标数据库里需要有相同表结构的表,如果没有则创建一个一样的表.
- 释放创建时产生的t2.ibd(DISCARD TABLESPACE)
root@localhost [wenyz]>CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ti` varchar(100) NOT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4079879 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.01 sec)root@localhost [wenyz]>alter table t2 DISCARD TABLESPACE; //高危命令,注意执行前一定确认是在目标库里,执行此命令后此表原有数据将丢失.
- 将tmp目录下的t2.ibd和t2.cfg文件复制到/data/57mysql/mysql3506/data/wenyz/目录下
#cp /tmp/t2* .[root@db211_08:58:36 /data/57mysql/mysql3506/data/wenyz] #lltotal 14356-rw-r----- 1 mysql mysql 61 Aug 1 19:25 db.opt-rw-r----- 1 root root 431 Aug 2 08:58 t2.cfg-rw-r----- 1 mysql mysql 8612 Aug 2 08:54 t2.frm-rw-r----- 1 root root 14680064 Aug 2 08:58 t2.ibd[root@db211_08:58:37 /data/57mysql/mysql3506/data/wenyz] #chown mysql:mysql * //记得修改权限[root@db211_09:00:53 /data/57mysql/mysql3506/data/wenyz] #lltotal 14356-rw-r----- 1 mysql mysql 61 Aug 1 19:25 db.opt-rw-r----- 1 mysql mysql 431 Aug 2 08:58 t2.cfg-rw-r----- 1 mysql mysql 8612 Aug 2 08:54 t2.frm-rw-r----- 1 mysql mysql 14680064 Aug 2 08:58 t2.ibd
- 在mysql中导入新表空间
- 查看表数据
root@localhost [wenyz]>alter table t2 import tablespace;Query OK, 0 rows affected (0.06 sec)root@localhost [wenyz]>select count(*) from t2;+----------+| count(*) |+----------+| 163818 |+----------+1 row in set (0.08 sec)