MySQL Innodb表空间卸载和迁移案例

需求:
两台服务器A(192.168.1.1)和服务器B(192.168.1.2)
数据目录都为:/usr/local/mysql/data
现在想把A服务器上/usr/local/mysql/data下的chenfeng数据库迁移到B服务器上，只迁移chenfeng库，其他数据库不动。


步骤如下:
在服务器B上:
建库:
mysql> create database chenfeng;
Query OK, 1 row affected (0.02 sec)


创建和服务器A上一模一样的表:
mysql>use chenfeng
mysql> CREATE TABLE `accident` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `accidentContent` varchar(4000) DEFAULT NULL COMMENT '事故全称',
    ->   `accidentDate` datetime DEFAULT NULL COMMENT '事故发生时间',
    ->   `accidentType` int(2) DEFAULT NULL COMMENT '事故类型（1. 火灾事故 2. 爆炸事故 3. 中毒和窒息事故 4. 灼伤事故 5.  泄漏事故  6. 其它危化品事故）',
    ->   `accidentLevel` int(11) DEFAULT NULL COMMENT '事故等级（1. 一般事故 2. 较大事故 3. 重大事故 4. 特别重大事故）',
    ->   `companyId` int(11) DEFAULT NULL COMMENT '涉事企业id',
    ->   `accidentLocation` varchar(255) DEFAULT NULL COMMENT '事故发生位置（省、市、县）',
    ->   `accidentHarm` varchar(255) DEFAULT NULL COMMENT '事故伤亡（X人死亡，Y人受伤，Z人失踪）',
    ->   `accidentCause` varchar(255) DEFAULT NULL COMMENT '事故原因',
    ->   `lng` double(22,7) DEFAULT NULL COMMENT '经度',
    ->   `lat` double(22,7) DEFAULT NULL COMMENT '纬度',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8;


卸载chenfeng表空间文件:
mysql> alter table accident discard tablespace;
Query OK, 0 rows affected (0.15 sec)
警告：这个语句删除当前.ibd文件。 


在服务器A上：
锁表:
mysql(mdba@localhost:chenfeng 06:57:27)>flush tables accident for export;
Query OK, 0 rows affected (0.00 sec)
备注:此命令使.ibd文件保持一致的状态


复制数据文件:
把A服务器表的表空间（ibd）所有文件复制到B服务器的相应数据目录,只有文件处于一致的状态我们才可以复制它:
[root@localhost data]# scp -r chenfeng root@192.168.1.2:/usr/local/mysql/data
accident.frm                                                                                                           100%   21KB  24.1MB/s   00:00
db.opt                                                                                                                 100%   61   320.5KB/s   00:00
accident.ibd                                                                                                           100%  128KB  87.6MB/s   00:00


在服务器B上:
修改复制过来的文件权限：
[root@nginx data]# cd /usr/local/mysql/data
[root@nginx data]# chown -R mysql:mysql chenfeng


登录数据库并导入表空间:
mysql> alter table accident import tablespace;
Query OK, 0 rows affected, 1 warning (0.47 sec)


备注:当导入表空间时，目的库的页尺寸要和源库的页尺寸相匹配,另外需要注意两边的tablespace id得一致，否则会报错。


验证数据:
mysql> show tables;
+--------------------+
| Tables_in_chenfeng |
+--------------------+
| accident           |
+--------------------+
1 row in set (0.00 sec)


mysql> select * from accident;
+----+------------------------------------------------------------------+---------------------+--------------+---------------+-----------+-----------------------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+------------+
| id | accidentContent                                                  | accidentDate        | accidentType | accidentLevel | companyId | accidentLocation            | accidentHarm                         | accidentCause                                                                                                                                                                                                                                                                  | lng         | lat        |
+----+------------------------------------------------------------------+---------------------+--------------+---------------+-----------+-----------------------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+------------+
|  1 | 临近年关都注意安全 峄城棠阴东一轿车发生事故                      | 2017-01-27 00:00:00 |            2 |             1 |         1 | 山东省枣庄市峄城县          | 0人死亡，0人受伤，0人失踪            | 据市民潘先生反映，他在去台儿庄路上路过峄城区，棠阴东一辆车发生事故。所幸车上三名人员没大碍，两人被送医院救治。发生事故的车辆为黑色轿车，据潘先生介绍，导致此次车祸的原因是该轿车超车                                                                                           | 120.7725010 | 29.7305020 |
|  2 | 临近年关都注意安全 峄城棠阴东一轿车发生事故                      | 2017-02-27 00:00:00 |            2 |             1 |         1 | 山东省枣庄市峄城县          | 0人死亡，0人受伤，0人失踪            | 据市民潘先生反映，他在去台儿庄路上路过峄城区，棠阴东一辆车发生事故。所幸车上三名人员没大碍，两人被送医院救治。发生事故的车辆为黑色轿车，据潘先生介绍，导致此次车祸的原因是该轿车超车
......................................................................................................................................................................................................................................................
......................................................................................................................................................................................................................................................
......................................................................................................................................................................................................................................................

至此,Innodb表空间迁移完毕。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2142615/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2142615/