1、源库查询表结构mysql> show create table s_show\G
*************************** 1. row ***************************
Table: study_show
Create Table: CREATE TABLE `study_show` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT ‘0‘ ,
`course_id` int(11) DEFAULT ‘0‘ ,
`ishow` tinyint(1) DEFAULT ‘0‘ ,
`album_id` int(10) DEFAULT ‘0‘ ,
`group_id` int(11) DEFAULT ‘0‘ ,
`task_id` int(11) DEFAULT ‘0‘ ,
`video` varchar(40) DEFAULT ‘‘ ,
`show_pic` int(11) DEFAULT ‘0‘ ,
`create_time` int(11) DEFAULT ‘0‘ ,
PRIMARY KEY (`id`),
KEY `uid` (`uid`) USING BTREE,
KEY `course_id` (`course_id`) USING BTREE,
KEY `create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=35230183 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
2、目标库创建表mysql> CREATE TABLE `s_show` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT ,
-> `uid` int(11) DEFAULT ‘0‘ ,
-> `course_id` int(11) DEFAULT ‘0‘ ,
-> `ishow` tinyint(1) DEFAULT ‘0‘ ,
-> `album_id` int(10) DEFAULT ‘0‘ ,
-> `group_id` int(11) DEFAULT ‘0‘ ,
-> `task_id` int(11) DEFAULT ‘0‘ ,
-> `video` varchar(40) DEFAULT ‘‘ ,
-> `show_pic` int(11) DEFAULT ‘0‘ ,
-> `create_time` int(11) DEFAULT ‘0‘ ,
-> PRIMARY KEY (`id`),
-> KEY `uid` (`uid`) USING BTREE,
-> KEY `course_id` (`course_id`) USING BTREE,
-> KEY `create_time` (`create_time`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.32 sec)
mysql> desc s_show;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| uid | int(11) | YES | MUL | 0 | |
| course_id | int(11) | YES | MUL | 0 | |
| ishow | tinyint(1) | YES | | 0 | |
| album_id | int(10) | YES | | 0 | |
| group_id | int(11) | YES | | 0 | |
| task_id | int(11) | YES | | 0 | |
| video | varchar(40) | YES | | | |
| show_pic | int(11) | YES | | 0 | |
| create_time | int(11) | YES | MUL | 0 | |
+-------------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
mysql> select * from s_show;
Empty set (0.00 sec)
3、目标库禁用表空间mysql> alter table s_show discard tablespace;
Query OK, 0 rows affected (0.05 sec)
4、源库锁定表mysql> flush table s_show for export;
Query OK, 0 rows affected (0.05 sec)
5、复制文件到目标库[root@node01 mydb_1]# ls -alh|grep "s_show\."
-rw-r----- 1 mysql mysql 1.2K Aug 10 11:52 s_show.cfg
-rw-r----- 1 mysql mysql 8.9K Aug 9 14:02 s_show.frm
-rw-r----- 1 mysql mysql 5.0G Aug 9 14:46 s_show.ibd
[root@node01 mydb_1]# scp s_show.{cfg,ibd} root@10.10.1.103:/u01/mydata/mydb_1/
root@10.10.1.103‘s password:
s_show.cfg 100% 1152 1.1KB/s 00:00
s_show.ibd 100% 5024MB 19.9MB/s 04:13
6、目标库导入表空间修改文件权限
[root@node02 mydb_1]# chown -R mysql:mysql /u01/mydata/mydb_1/s_show.{cfg,ibd}导入表空间(如果表很大会有很高的IO)
mysql> alter table s_show import tablespace;
Query OK, 0 rows affected (6 min 32.76 sec)
7、源库解除锁表mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.05 sec)