MySQL Table Space Transfer

1. Source Library query Table structure

mysql> show create table s_show\g*************************** 1. row ****        table: study_showcreate table:  CREATE TABLE  ' Study_show '   (   ' id '  int (one)  unsigned not null  AUTO_INCREMENT,   ' uid '  int (one)  DEFAULT  ' 0 '  ,   ' course_id '   Int (one)  DEFAULT  ' 0 '   ,   ' ishow '  tinyint (1)  DEFAULT  ' 0 '      ' album_id '  int (Ten)  DEFAULT  ' 0 '  ,   ' group_id '  int (one)   default  ' 0 '  ,   ' task_id '  int (one)  DEFAULT  ' 0 '  ,   ' video '  varchar (+)  DEFAULT  '  ,   ' show_pic '  int (one)  DEFAULT  ' 0 '      ' Create_time '  int (one)  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. Create a table for the target library

mysql> create table  ' S_show '   (    ->    ' id '   Int (one)  unsigned NOT NULL AUTO_INCREMENT ,    ->     ' UID '  int (one)  DEFAULT  ' 0 '  ,    ->    ' course_id '  int (one)  DEFAULT  ' 0 '  ,    ->    ' ishow '  tinyint (1)  DEFAULT  ' 0 '  ,    ->    ' album_id '  int (Ten)   default  ' 0 '  ,    ->    ' group_id '  int (one)  default   ' 0 '  ,    ->    ' task_id '  int (one)  DEFAULT  ' 0 '  ,    ->    ' video '  varchar (+)  DEFAULT  '       ->    ' Show_pic '  int (one)  DEFAULT  ' 0 '  ,     ->    ' Create_time '  int (one)  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 (one)  unsigned | NO   | PRI | NULL     | auto_increment | |  uid         | int (one)            | YES  | MUL | 0        |                | |  course_id   | int (one)           |  YES  | MUL | 0       |                 | |  ishow       | tinyint (1)         | yes  |     | 0       |                 | |  album_id    | int (Ten)            | yes  |     | 0       |                 | |  group_id    | int (one)            | yes  |     | 0       |                 | |  task_id     | int (one)            | YES  |     | 0       |                 | |  video       | varchar (+)       |  yes  |     |         |                 | |  show_pic    | int (one)            | yes  |     | 0       |                 | |  create_time | int (one)           | yes   | MUL | 0       |                 |+-------------+------------------+------+-----+---------+------- ---------+10 rows in set  (0.00 sec) mysql> select * from s_show ; empty set  (0.00 SEC)

3. The Target library disables table space

Mysql> ALTER TABLE s_show discard tablespace; Query OK, 0 rows affected (0.05 sec)

4, Source Library lock table

Mysql> Flush table s_show for export; Query OK, 0 rows affected (0.05 sec)

5. Copying files to the target library

[[email protected] 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[[email protected ] mydb_1]# scp s_show. {cfg,ibd} [email protected]:/u01/mydata/mydb_1/[email protected] ' S password: s_ show.cfg                                                                                                     100% 1152     1.1KB/s   00:00     s_show.ibd                                                                                                     100% 5024MB  19.9MB/s    04:13

6. Target Library Import table space

Modify file permissions [[email protected] mydb_1]# chown-r mysql:mysql/u01/mydata/mydb_1/s_show. {CFG,IBD}

Import table Space (if the table has a very high IO) mysql> ALTER TABLE s_show import tablespace; Query OK, 0 rows affected (6 min 32.76 sec)

7, the Source Library unlocks the table

Mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.05 sec)

This article is from the "Dead Leaf" blog, please make sure to keep this source http://mydbs.blog.51cto.com/513727/1836533

MySQL Table Space Transfer