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

There are many ways to move or copy large InnoDB tables from one instance to another. Prior to 5.6, physical or logical backups were commonly used.

In version 5.6.6 +, a fast method based on tablespace migration, similar to Oracle TTS, is used.

Because it is used, so collate the records so far.

Two machines are used in the experiment, single machine and single instance, MySQL 5.6.30.

The two shell environments and mysql client environments will be distinguished by vm1 > mysql1 > vm2 > mysql2 >.

0 process:

① first creates test data on mysql1: mysql >\ R mysql1 >

PROMPT set to 'mysql1 >'

Mysql1 > USE test

Database changed

Mysql1 > CREATE TABLE tts (id int PRIMARY KEY AUTO_INCREMENT, name char)

Query OK, 0 rows affected (0.01 sec)

Mysql1 > INSERT INTO tts (name) VALUES (REPEAT)

Query OK, 1 row affected (0.00 sec)

Mysql1 > INSERT INTO tts (name) SELECT name FROM tts

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql1 > INSERT INTO tts (name) SELECT name FROM tts

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

...

Mysql1 > INSERT INTO tts (name) SELECT name FROM tts

Query OK, 131072 rows affected (0.79 sec)

Records: 131072 Duplicates: 0 Warnings: 0

Mysql1 > INSERT INTO tts (name) SELECT name FROM tts

Query OK, 262144 rows affected (2.15 sec)

Records: 262144 Duplicates: 0 Warnings: 0

Mysql1 >\! Du-sh / data/mysql/test/tts*

12K / data/mysql/test/tts.frm

92M / data/mysql/test/tts.ibd

② ensures that there is the same database table structure on mysql2, which is newly created here, and discard the newly created test.tts table on mysql2 to the ibd file:

Mysql >\ R mysql2 >

PROMPT set to 'mysql2 >'

Mysql2 > USE test

Database changed

Mysql2 > CREATE TABLE tts (id int PRIMARY KEY AUTO_INCREMENT, name char)

Query OK, 0 rows affected (0.01 sec)

Mysql2 >\! Du-sh / data/mysql/test/tts*

12K / data/mysql/test/tts.frm

96K / data/mysql/test/tts.ibd

Be careful! The alter table... The discard tablespace operation will record the binlog and affect the replication structure, use with caution, or set sql_log_bin=0;mysql2 > ALTER TABLE tts DISCARD TABLESPACE

Query OK, 0 rows affected (0.01 sec)

Mysql2 >\! Du-sh / data/mysql/test/tts*

12K / data/mysql/test/tts.frm

③ does the FLUSH TABLES operation on mysql1's test.tts table, and there will be one more cfg file: mysql1 > FLUSH TABLE tts FOR EXPORT

Query OK, 0 rows affected (0.05 sec)

Mysql1 >\! Du-sh / data/mysql/test/tts*

4.0K / data/mysql/test/tts.cfg

12K / data/mysql/test/tts.frm

92M / data/mysql/test/tts.ibd

④ opens one more terminal and scp the ibd and cfg files to vm2 on vm1: vm1 > scp / data/mysql/test/tts. {ibd,cfg} user@vm2:/data/mysql/test

User@vm2's password:

Tts.ibd 100% 92MB 46.0MB/s 00:02

Tts.cfg 100% 380 0.4KB/s 00:00

⑤ UNLOCK the test.tts table of mysql1 (at this time, it can be found that the cfg file has been deleted): mysql1 > UNLOCK TABLES

Query OK, 0 rows affected (0.00 sec)

Mysql1 >\! Du-sh / data/mysql/test/tts*

12K / data/mysql/test/tts.frm

92M / data/mysql/test/tts.ibd

⑥ modifies the passed ibd and cfg files on vm2: vm2 > chown mysql:mysql / data/mysql/test/tts. {ibd,cfg}

⑦ IMPORT the above ibd file into the tts table: mysql2 > ALTER TABLE tts IMPORT TABLESPACE

Query OK, 0 rows affected (0.93 sec)

Mysql2 > SELECT count (*) FROM tts

+-+

| | count (*) |

+-+

| | 524288 |

+-+

1 row in set (0.94 sec)

At this point, the data in the tts table on the mysql1 instance has been quickly migrated to the mysql2 instance.

0 explanation of the above steps:

Operation discard tablespace in ② will add MDL lock on the table, delete all relevant cache items of change buffer, set table metadata information, mark tablespace for deletion status, regenerate table id, ensure that subsequent operations based on table id will fail, and then kill the idb file. You can see that .idb file has been deleted in two du in ②. This is a very dangerous operation, careful; this operation will also be recorded in binlog, if the replication structure may have a great impact, be sure to temporarily close binlog.

Manipulate flush table in ③... For export puts a shared lock on the test.tts table, stops purge coordinator thread (which is similar to sql thread in parallel replication), forces dirty pages to be synchronized to disk, creates and writes the metadata of the test.tts table to the .cfg file

FLUSH TABLES... FOR EXPORT embodies this process in error log:

[Note] InnoDB: Sync to disk of'"test". "tts" 'started.

[Note] InnoDB: Stopping purge

[Note] InnoDB: Writing table metadata to'. / test/tts.cfg'

[Note] InnoDB: Table'"test". "tts" 'flushed to disk

Operate ⑤ to execute unlock tables to release the lock in ③, and the .cfg file is deleted and purge coordinator thread will restart. (doing flush table. Session cannot be closed during for export to avoid the deletion of .cfg files caused by lock release)

UNLOCK TABLES is recorded in error log as:

[Note] InnoDB: Deleting the meta-data file'. / test/tts.cfg'

[Note] InnoDB: Resuming purge

The operation ⑦ is to import the .ibd file transferred from the vm1 into the tts table through the import tablespace operation, and the .cfg file must also exist.

ALTER TABLE... IMPORT TABLESPACE is recorded in error log as:

[Note] InnoDB: Importing tablespace for table 'test/tts' that was exported from host' vm01'

[Note] InnoDB: Phase I-Update all pages

[Note] InnoDB: Sync to disk

[Note] InnoDB: Sync to disk-done!

[Note] InnoDB: Phase III-Flush changes to disk

[Note] InnoDB: Phase IV-Flush complete

[Note] InnoDB: "test". "tts" autoinc value set to 786406

The process is to read the cfg file: table definition, index definition, index RootPage, column definition, and so on. Then read each page of the import file, check the integrity, and reset the metadata information of the current table according to the read cfg file.

To sum up, the whole process is:

Create table $new_table...

Alter table $new_table discard tablespace; (delete the tablespace file for the new table and keep the frm file)

Flush table $old_table for export; (close the table and generate the cfg file)

Copy the ibd file, the corresponding cfg file.

Unlock tables

Copy the ibd file and cfg file to the new address and modify the permissions

Alter table $new_table import tablespace

0 limit:

Independent tablespaces must be enabled for both instances, innodb_file_per_table

The innodb_page_size of the two migrated instances must be the same, and the mysql server version recommendations must be the same.

Discard tablespace execution on partitioned tables is not supported

Discard tablespace is not supported on tables with primary foreign key relationships unless foregin_key_checks=0 is set

0 reference documentation:

MySQL 5.6 Reference Manual-14.5.5 Copying Tablespaces to Another Server (Transportable Tablespaces)

Author's official account on Wechat (continuously updated)

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.