源端:3306/test/t100w -----> 目标端:3307/test/t100w
1. 锁定源端t100w表
mysql> lock tables test.t100w read;
2.查询建表语句
mysql> show create table test.t100w;
CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3. 目标端创建test库和t100w空表
mysql> create database test charset=utf8mb4;
复制执行建表语句
CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
4.单独删除空的表空间文件(保留t100w的frm,ibdata1中关于t100w的系统数据.删除t100w.ibd)
mysql> alter table test.t100w discard tablespace;
5. 拷贝源端ibd文件到目标端目录,并设置权限
[root@db01 test]# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
[root@db01 test]# chown -R mysql.mysql /data/*
6. 导入表空间
#自动读取表名同名的ibd文件,导入表空间
mysql> alter table test.t100w import tablespace;
mysql> select count(*) from test.t100w;
+----------+
| count(*) |
+----------+
| 1000000 |
7. 解锁源端数据表
mysql> unlock tables;