mysql Innodb資料表空間卸載、遷移、裝載的使用方法_Mysql

條件：
2台伺服器：A和B，需要A伺服器上的表遷移到B伺服器。
Innodb表：sysUser，記錄數：351781。
以下測試在MySQL 5.5.34中進行。
開始處理：
1：在B伺服器上建立sysUser表，並且執行：

複製代碼 代碼如下:

zjy@B : db_test 09:50:30>alter table sysUser discard tablespace;

2：把A伺服器表的資料表空間（ibd）複製到B伺服器的相應資料目錄。
3：修改複製過來的ibd檔案許可權：

複製代碼 代碼如下:

chown mysql:mysql sysUser.ibd

4：最後就開始載入：

複製代碼 代碼如下:

zjy@B : db_test 10:00:03>alter table sysUser import tablespace;
ERROR 1030 (HY000): Got error -1 from storage engine

報錯了，查看錯誤記錄檔：

複製代碼 代碼如下:

10:05:44  InnoDB: Error: tablespace id and flags in file './db_test/sysUser.ibd' are 2428 and 0, but in the InnoDB
InnoDB: data dictionary they are 2430 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
10:05:44  InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `db_test`.`sysUser`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

當遇到這個的情況：A伺服器上的資料表空間ID 為2428，而B伺服器上的資料表空間ID為2430。所以導致這個錯誤發生，解決辦法是：讓他們的資料表空間ID一致，即：B找出資料表空間ID為2428的表（CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;），修改成和sysUser表結構一樣的的表，再import。要不就把A伺服器的資料表空間ID增加到大於等於B的資料表空間ID。（需要建立刪除表來增加ID）

要是A的資料表空間ID大於B的資料表空間ID，則會有：

複製代碼 代碼如下:

11:01:45  InnoDB: Error: tablespace id and flags in file './db_test/sysUser.ibd' are 44132 and 0, but in the InnoDB
InnoDB: data dictionary they are 2436 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
11:01:45  InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `db_test`.`sysUser`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

這時的情況：A伺服器上的資料表空間ID 為44132，而B伺服器上的資料表空間ID為2436。（因為A是測試機子，經常做還原作業，所以資料表空間ID已經很大了，正常情況下。資料表空間ID不可能這麼大。

既然資料表空間ID不對導致這個錯誤判出，那我們手動的讓B的資料表空間ID追上A的資料表空間ID。

需要建立的表數量：44132-2436 = 41696個，才能追上。因為他本身就需要再建立一個目標表，所以需要建立的表數量為：41695。不過安全起見，最好也不要超過41695，以防B的資料表空間ID超過了A，則比如設定安全的值：41690，即使B沒有到達A資料表空間ID的值，也應該差不多了，可以再手動的去增加。用一個指令碼跑（需要建立的表比較多），少的話完全可以自己手動去處理：

複製代碼 代碼如下:

#!/bin/env python
# -*- encoding: utf-8 -*-

import MySQLdb
import datetime

def create_table(conn):
    query = '''
create table tmp_1 (id int) engine =innodb
    '''
    cursor = conn.cursor()
    cursor.execute(query)
    conn.commit()
def drop_table(conn):
    query = '''
drop table tmp_1
    '''
    cursor = conn.cursor()
    cursor.execute(query)
    conn.commit()

if __name__ == '__main__':
    conn = MySQLdb.connect(host='B',user='zjy',passwd='123',db='db_test',port=3306,charset='utf8')
    for i in range(41690):
        print i
        create_table(conn)
        drop_table(conn)

也可以開啟多線程去處理，加快效率。
當執行完之後，再重新按照上面的1-3步驟進行一次，最後再裝載：

複製代碼 代碼如下:

zjy@B : db_test 01:39:23>alter table sysUser import tablespace;
Query OK, 0 rows affected (0.00 sec)

要是再提示A資料表空間ID大於B表的話，就再手動的按照指令碼裡面的方法來增加ID，這時候就只需要增加個位元就可以追上A的資料表空間ID了。
總結：
上面只是一個方法，雖然可以遷移Innodb，但是出問題之後可能會引其Innodb的頁損壞，所以最安全的還是直接用mysqldump、xtrabackup等進行遷移。
5.6 可以不用考慮這些tablespace id，可以直接import 進來。

複製代碼 代碼如下:

2013-11-12 15:25:09 2378 [Note] InnoDB: Sync to disk
2013-11-12 15:25:09 2378 [Note] InnoDB: Sync to disk - done!
2013-11-12 15:25:09 2378 [Note] InnoDB: Phase I - Update all pages
2013-11-12 15:25:09 2378 [Note] InnoDB: Sync to disk
2013-11-12 15:25:09 2378 [Note] InnoDB: Sync to disk - done!
2013-11-12 15:25:09 2378 [Note] InnoDB: Phase III - Flush changes to disk
2013-11-12 15:25:09 2378 [Note] InnoDB: Phase IV - Flush complete