mysql Innodb表空间卸载、迁移、装载的使用方法

电玩女神 2022-12-16 13:10 171阅读 0赞

条件：  
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