How to use mysqlInnodb tablespace for unmounting, migration, and loading _ MySQL

How to use mysqlInnodb tablespace unmounting, migration, and loading mysql

BitsCN.com

Condition:
Two servers: A and B. The table on server A needs to be migrated to server B.
Innodb table: sysUser, number of records: 351781.
The following tests are performed in MySQL 5.5.34.
Start processing:
1: Create a sysUser table on server B and execute:

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

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

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

An error is reported. view 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
InnoDB: commands discard tablespace and import tablespace?
InnoDB: Please refer
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
InnoDB: table 'DB _ Test'. 'sysuser'
InnoDB: in alter table... IMPORT TABLESPACE

In this case, the tablespace ID on server A is 2428, and the tablespace ID on server B is 2430. Therefore, this error occurs. the solution is to make their tablespace IDs consistent. that is, B finds the TABLE with the tablespace ID of 2428 (CREATE TABLE innodb_monitor (a INT) ENGINE = INNODB;), modify the table with the same structure as the sysUser table, and then import. Otherwise, add the tablespace ID of server A to the tablespace ID greater than or equal to that of server B. (You need to create and delete a table to add an ID)

If the tablespace ID of A is greater than the tablespace ID of B, 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
InnoDB: commands discard tablespace and import tablespace?
InnoDB: Please refer
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
InnoDB: table 'DB _ Test'. 'sysuser'
InnoDB: in alter table... IMPORT TABLESPACE

In this case, the tablespace ID on server A is 44132, and the tablespace ID on server B is 2436. (Because A is A test host and often performs restoration operations, the tablespace ID is large. normally. The tablespace ID cannot be so large.

Since this error is reported because the tablespace ID is incorrect, we manually set the tablespace ID of Table B to catch up with the tablespace ID of Table.

The number of tables to be created: 44132-2436 = 41696. Because he needs to create another target table, the number of tables to be created is 41695. But for security, it is best not to exceed 41695, in case the tablespace ID of B exceeds A, for example, set A safe value: 41690, even if B does not reach the value of the tablespace ID of, it should be almost the same. you can add it manually. Run with a script (many tables need to be created). if there are few tables, you can manually process them:

#! /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.exe cute (query)
Conn. commit ()
Def drop_table (conn ):
Query = '''
Drop table tmp_1
'''
Cursor = conn. cursor ()
Cursor.exe cute (query)
Conn. commit ()

If _ name _ = '_ main __':
Conn = MySQLdb. connect (host = 'B', user = 'zjy', passwd = '000000', db = 'DB _ test', port = 123, charset = 'utf8 ')
For I in range (41690 ):
Print I
Create_table (conn)
Drop_table (conn)

You can also enable multi-threaded processing to accelerate efficiency.
After the execution is complete, repeat the steps 1-3 above and load the file again:

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

If you are prompted that the ID of Table A is greater than that of Table B, manually add the ID according to the method in the script. in this case, you only need to add A single digit to catch up with the ID of Table.
Summary:
The above is just a method. although Innodb can be migrated, the page of Innodb may be corrupted after a problem occurs. Therefore, the most secure solution is to directly use mysqldump and xtrabackup for migration.
5.6 you do not need to consider these tablespace IDs. you can import them directly.

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

BitsCN.com