MySQL InnoDB table space Uninstall, migration, load use method _mysql

Conditions:
2 servers: A and B, the tables on a server are required to migrate to the B server.
InnoDB table: Sysuser, record number: 351781.
The following tests are performed in the MySQL 5.5.34.
Start processing:
1: Establish the Sysuser table on the B server and execute:

Copy Code code as follows:

Zjy@b:db_test 09:50:30>alter table Sysuser discard tablespace;

2: Copy the table space (IBD) of a server table to the corresponding data directory of Server B.
3: Modify the copied over the IBD file permissions:

Copy Code code as follows:

Chown Mysql:mysql SYSUSER.IBD

4: Finally start loading:

Copy Code code as follows:

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

Error logging, view errors log:

Copy Code code as follows:

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 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

When this happens: The table Space ID on Server A is 2428, and the table space ID on Server B is 2430. So this error occurs, the solution is to have their table space IDs consistent, that is: B find a table with a table space ID of 2428 (CREATE table Innodb_monitor (a INT) engine=innodb;), Modify the table to be the same as the Sysuser table structure, and then import. Alternatively, increase the table space ID of Server A to a table space ID greater than or equal to B. (You need to create a new delete table to increase the ID)

If A's table space ID is greater than the table space ID of B, there will be:

Copy Code code as follows:

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 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

In this case: The table Space ID on Server A is 44132, and the table space ID on Server B is 2436. (Because A is a test machine, often do restore operations, so the table space ID is very large, under normal circumstances.) The table Space ID cannot be this large.

Since the table space ID does not cause this error to be reported, we manually let B's table space ID catch up with the table space ID of a.

Number of tables to be established: 44132-2436 = 41,696, to catch up. Because he himself needs to set up a target table, so the number of tables to be established is: 41695. However, for security reasons, it is best not to exceed 41695, in case B's table space ID exceeds a, such as setting a safe value: 41690, even if B does not reach a table space ID value, it should be almost, can be manually added. Run with a script (more tables to build), and you can do it yourself manually:

Copy Code code as follows:

#!/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)

can also open multithreading to deal with, speed up efficiency.
When you are done, repeat the 1-3 steps above and then reload again:

Copy Code code as follows:

Zjy@b:db_test 01:39:23>alter table Sysuser import tablespace;
Query OK, 0 rows Affected (0.00 sec)

If you are prompted a table space ID is greater than B table, and then manually according to the method in the script to increase the ID, this time only need to increase the number of digits can catch up a table space ID.
Summarize:
Above is only a method, although can migrate InnoDB, but after the problem may lead to its InnoDB page damage, so the safest or directly with mysqldump, Xtrabackup and so on to migrate.
5.6 You can import it directly without considering these tablespace IDs.

Copy Code code as follows:

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