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表空間遷移完畢。
