Use of mysql Innodb tablespace uninstall migrate and load

Condition:
Two servers: A and B, requiring tables on the A server to be migrated to the B server.
Innodb table: sysUser, number of records: 351781.
The following tests were performed in MySQL 5.5.34.
Start processing:
1: set up sysUser table on B server and execute:

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

2: copy the table space (ibd) of the A server table to the corresponding data directory of the B server.
3: modify the permissions of the copied ibd file:

chown mysql:mysql sysUser.ibd

4: start loading at last:

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

Error report, check the error log:

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

When this is encountered, the ID tablespace on the A server is 2428, while the ID tablespace on the B server is 2430. So the solution to this error is to have their tablespaces ID1, B, find the tablespaces ID, 2428 (CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;) , modified to the same table as sysUser table structure 1, and then import. Or increase the A server's ID tablespace to ID tablespace greater than or equal to B. (need to create a new delete table to add ID)

If A's table space ID is larger than B's table space ID, there will be:

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

Here's what happens: the table space ID on the A server is 44132, and the table space ID on the B server is 2436. (because A is a test machine and often does restore operations, the table space ID is already large under normal circumstances. The table space ID cannot be this big.

Since the table space ID is not causing this error to be reported, we manually have B's table space ID catch up with A's table space ID.

The number of tables to be set up: 44132-2436 = 41696 to catch up. Since he needs to set up another target table, the number of tables he needs to set up is 41695. To be safe, it is best not to exceed 41695, in case B's ID exceeds A. For example, set the safe value: 41690. Even if B does not reach A's ID, it should be about the same, and you can manually add it. Use 1 script to run (need to build more tables), if less, you can handle it manually:

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

You can also start multithreading to speed up efficiency.
After the execution, follow steps 1-3 above again once, and finally load:

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

If the A tablespace ID is larger than B tablespace B, then manually add ID according to the script. At this point, you only need to add a single digit to catch up with A tablespace ID.
Conclusion:
The above is only one method. Although it is possible to migrate Innodb, the page of Innodb may be damaged after the problem occurs, so the safest way is to migrate mysqldump, xtrabackup and so on.
5.6 you don't have to worry about tablespace id, you can just go in with 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