InnoDB data table space file migration

Preface

The InnoDB Storage engine meets the MVCC and acid features and is essential in environments that require transaction support. In some environments, InnoDB may be better than MyISAM. However, in the eyes of many people, InnoDB tablespace files are reluctant to be used because they cannot achieve smooth cross-server migration. Is the actual situation true? This article will discuss the possibility of smooth migration of InnoDB tablespace files.

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:

Suppose we have two dB hosts, one is a and the other is 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 for 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. 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

InnoDB: the directory.

InnoDB: Error: trying to open a table, but cocould 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. IBD' is 15, but in the InnoDB

InnoDB: data dictionary it is 13.

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.0/en/innodb-troubleshooting.html

InnoDB: For how to resolve the issue.

InnoDB: cannot find or open in the database Directory The. IBD file

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)

# At this time, the yejr ID is changed to 14

(Root@imysql.cn/17:52:47) [yejr]> alter table yejr1 Rename to yejr;

Query OK, 0 rows affected (0.00 Sec)

# At this time, the yejr ID is changed to 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. You can create a table by adding its ID, 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.

Http://ourmysql.com/archives/346? F = WB