InnoDB data table space file migration

How to migrate?

We learned from the MySQL documentation that InnoDB tablespaces can be shared or independent. For shared tablespaces, all tablespaces are placed in one file: ibdata1, ibdata2 .. ibdataN, in this case, there is no way to migrate tablespaces. Unless the table is fully migrated, it is not included in this discussion. We only discuss the situation of independent tablespaces.

Whether it is a shared or independent tablespace, the metadata (metadata) of each data table in InnoDB is always stored in the shared tablespace ibdata1. Therefore, this file is indispensable, it can also be used to store various data dictionaries and other information. In the data dictionary, the ID of each data table is saved. Each time a new data table space is added, the ID is automatically added with a value (++ 1). For example: create table xx ENGINE = InnoDB/alter table xx ENGINE = InnoDB will increase the ID value.

With the above understanding, it is easy to implement smooth migration of InnoDB tablespace files. The following are some examples:

Assume that we have two DB hosts, A and B. Now we want to migrate an InnoDB tablespace file on A to B for direct use.

1. Migration failure example

After the tablespace file yejr. ibd is directly copied from Table A to table B, the tablespace is imported. An error is returned and cannot be used. This is because the order of the table created on A and B is inconsistent, resulting in different table IDs and the table cannot be imported.

Note:The premise of copying a tablespace file is that the tablespace is in the "clean" state, that is, all data has been refreshed to the disk, otherwise, it may cause unavailability or some data loss.

1. discard the old tablespace on B

(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr DISCARD TABLESPACE; Query OK, 0 rows affected (0.00 sec)

2. Copy to target machine

scp yejr.ibd B:/home/mysql/yejr/yejr.ibd....

3. Enable the tablespace

(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr IMPORT TABLESPACE; ERROR 1030 (HY000): Got error -1 from storage engine

4. Viewing errors

InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './test/b.ibd'! InnoDB: Error: cannot reset lsn's in table `test/b` InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

5. Obviously, it is a permission issue. correct it and re-import it.

(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr DISCARD TABLESPACE; ERROR 1030 (HY000): Got error -1 from storage engine

6. Why or error? Continue viewing logs

InnoDB: Error: tablespace id in file './yejr/yejr.ibd' is 15, but in the InnoDB InnoDB: data dictionary it is 13. 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.0/en/innodb-troubleshooting.html InnoDB: for how to resolve the issue. InnoDB: cannot find or open in the database directory the .ibd file of InnoDB: table `yejr/yejr` InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

As shown in the preceding log, the ID of the yejr table is 15 on server A and that of the yejr table is 13 on server B. The two are inconsistent, so the migration fails.

Since the IDs are different and have the above theoretical basis, we can make them consistent manually. Please refer to the following 2nd attempts.

2. Successful migration with manual intervention

1. In the above example, the yejr table ID on B is 13, and that on A is 15. Therefore, you only need to increase the yejr table ID on B by 2.

(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr RENAME TO yejr1; Query OK, 0 rows affected (0.00 sec) #这个时候,yejr的ID变为14 (root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr1 RENAME TO yejr; Query OK, 0 rows affected (0.00 sec) #这个时候,yejr的ID变为15

2. Then, import

(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr IMPORT TABLESPACE; Query OK, 0 rows affected (0.00 sec) (root@imysql.cn/17:52:47)[yejr]>select count(*) from yejr; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec)

You can see it. It's successful. If you want to add other IDs, you can create tables again, depending on your actual situation or your preferences.

The above tests are all passed in MySQL 5.0.67, but the data is slightly processed.

1. Php xml class for MySQL
2. Introduction to three methods to optimize MySQL database queries
3. Test the replication feature of New MySQL features