Shulou(Shulou.com)06/01 Report--

In MySQL, if you want to migrate one table to another server / environment, the general practice is to back up using a backup tool, such as mysqldump, and then copy the backup to the target server or environment import. If a table has a large amount of data and the export dump file is very large, it will take a lot of time to use the export import tool.

How to improve efficiency, there can be an idea is to directly copy data files to the target environment, of course, in the earlier version, this is not desirable, because there will be a lot of associated data in ibdata, InnoDB data has the corresponding data dictionary information, is stored in the shared tablespace, can not be stripped out directly, and in 5.6max 5.7, introduced a very good feature, that is, migration tablespace This configuration information can be stripped out, simply by copying the data file directly to the target environment and mounting it on the target side.

A basic premise of such an operation is to use a separate tablespace and turn on innodb_file_per_table.

> show variables like'% per_table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_file_per_table | ON |

+-+ +

1 row in set (0.00 sec)

Cross-version transfer tablespace-source-side operation

We do a representative test, such as moving a table from the MySQL 5.6environment to the MySQL 5.7environment.

We choose a table users as the test, the amount of data is about 20, 000. Data file status:

-rw-rw---- 1 mysql mysql 8602 Feb 13 23:10 users.frm

-rw-rw---- 1 mysql mysql 11534336 Mar 12 22:55 users.ibd data:

> select count (*) from users

+-+

| | count (*) |

+-+

| | 20001 |

+-+

1 row in set (0. 01 sec) We start migrating data by generating a cfg file and exporting configuration information.

> flush tables users for export

Query OK, 0 rows affected (0.00 sec)

This command is worth mentioning, keep the current window, do not close, if closed, the cfg file will be automatically deleted, you can see that the command runs to generate the cfg file.

-rw-rw---- 1 mysql mysql 599 Mar 13 08:17 users.cfg

-rw-rw---- 1 mysql mysql 8602 Feb 13 23:10 users.frm

-rw-rw---- 1 mysql mysql 11534336 Mar 12 22:55 users.ibd after flush table, the table users is locked and the DML operation is blocked, which means that the data cannot be written directly during the migration.

> insert into users values (20234312310)

ERROR 1099 (HY000): the definition information of the Table 'users' was locked with a READ lock and can't be updated table users is as follows, which can be obtained using show create table users or mysqldump-- no-date test users.

DROP TABLE IF EXISTS `users`

CREATE TABLE `users` (

`userid` int (11) unsigned NOT NULL

`username` varchar (64) DEFAULT NULL

PRIMARY KEY (`userid`)

KEY `username` (`username`)

KEY `users` (`userid`, `username`)

ENGINE=InnoDB DEFAULT CHARSET=utf8; while the format of the cfg file has some obvious differences, you can take a peek at the summary information through strings.

# strings users.cfg

Mbionline.test.com

Test/users

Userid

Username

DB_ROW_ID

DB_TRX_ID

DB_ROLL_PTR

PRIMARY

Userid

DB_TRX_ID

DB_ROLL_PTR

Username

Username

Username

Userid

Idx_users

Userid

After the username is completed, launch the session and set the unlock tables.

Cross-version transfer tablespace-target-side operation

The operation of the target side is very critical, and the target side is the environment of MySQL 5.7.

First, you need to create a corresponding empty table on the target side. Then use the following statement to truncate the data file.

> alter table users discard tablespace

Query OK, 0 rows affected (0.02 sec) manually copy the data file .ibd and the configuration file .cfg, and copy it to the specified directory.

Cp / tmp/users.cfg / home/mysql/test

Cp / tmp/users.ibd / home/mysql/test needs to pay special attention to the permissions of the file at this time. After the copy is completed, we can mount the data file through import tablespace.

> alter table users import tablespace

ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1) or the following error:

> alter table users import tablespace

ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.) It can be found that it is related to an attribute of the table through the error message. Let's solve the problem first and add the attribute row_format

CREATE TABLE `users` (

`userid` int (11) unsigned NOT NULL

`username` varchar (64) DEFAULT NULL

PRIMARY KEY (`userid`)

KEY `username` (`username`)

KEY `users` (`userid`, `username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 row_format=compact; then continues to try the Import tablespace operation.

> alter table users discard tablespace

Query OK, 0 rows affected (0. 00 sec) shows that the whole process is very fast. After the execution is complete, let's check the table. [test] > check table users

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | test.users | check | status | OK | |

+-+

1 row in set (0.16 sec) view the data of the table for verification.

[test] > select count (*) from users

+-+

| | count (*) |

+-+

| | 20001 |

+-+

1 row in set (0.00 sec) the migration process is over, and we have successfully migrated a table from MySQL 5.6 to 5.7 environment.

Back to the question I just encountered, why there was an error in the migration from 5.6 to 5.7.

> alter table users import tablespace

ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.) The reason is that Innodb_file_format is Antelope in 5.6 and Barracuda in MySQL 5.7, mainly due to changes in table compression and row dynamic format. For more details, please refer to:

Https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html

Summary

In fact, there is already a familiar solution for this feature in Oracle, TTS, which supports cross-platform, conversion of byte order, and even supports migration scheme based on incremental backup. The migration mode in MySQL is similar to that of Oracle traditional TTS. Of course, the above operation can also be done using Percona's tool innobackupex, which we will demonstrate in the next article.

How can I vividly express this feeling of migration? I can even look at wine in the supermarket as read write.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.