Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of tablespace transmission in mysql, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Description: MySQL (5.6.6 or above), innodb_file_per_table enabled.

1.1. Procedure:

0. The target server creates the same table structure

1. Destination server: ALTER TABLE t DISCARD TABLESPACE

two。 Source server: FLUSH TABLES t FOR EXPORT

3. Copy t.ibd, t.cfg files from the source server to the destination server

4. Source server: UNLOCK TABLES

5. Destination server: ALTER TABLE t IMPORT TABLESPACE

1.2. Demo

Transfer the test_ purge table under the burn_test library in the multi-instance [mysql5711] to the test_ purge table under the burn_test2 library in [mysql57112].

1.2.1. Preparatory work

1. Create a tablespace on the target server

-- Source server [mysql5711]

Mysql > select * from burn_test.test_purge

+-+ +

| | a | b | |

+-+ +

| | 1 | 10 |

| | 3 | 30 |

| | 4 | 40 |

| | 5 | 50 |

| | 6 | 60 |

| | 7 | 70 |

| | 8 | 80 |

| | 10 | 100 | |

+-+ +

8 rows in set (0.01 sec)

-- Target server [mysql57112]

--

-- test_purge does not exist on the target server. Create the table first

Mysql > CREATE TABLE `test_ purge` (

`a`int (11) NOT NULL AUTO_INCREMENT

`b` int (11) DEFAULT NULL

PRIMARY KEY (`a`)

UNIQUE KEY `b` (`b`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

Query OK, 0 rows affected (0.16 sec)

two。 Check after the creation is completed

#

# Target server

#

[root@MyServer burn_test_2] > ll | grep test_purge

-rw-r-. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm # table structure

-rw-r-. 1 mysql mysql 57344 Mar 21 10:31 test_purge.ibd # tablespace, the tablespace file needs to be deleted through DISCARD

ALTER TABLE test_purge DISCARD TABLESPACE; means to keep the test_purge.frm file and delete the test_purge.ibd.

3. Open discard to delete ibd files

-- Target server

Mysql > alter table test_purge discard tablespace

Query OK, 0 rows affected (0.04 sec)

Mysql > show tables

+-- +

| | Tables_in_burn_test_2 |

+-- +

| | test_backup1 |

| | test_purge |

+-- +

2 rows in set (0.00 sec)

Mysql > select * from test_purge

ERROR 1814 (HY000): Tablespace has been discarded for table 'test_purge'

[root@MyServer burn_test_2] > ll | grep test_purge

-rw-r-. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm

1.2.2. Export tablespace

1. On the source server, use the export command to export the tablespace (with read locks)

-- Source server

Mysql > flush table test_purge for export;-- actually add a read lock to the table

Query OK, 0 rows affected (0.00 sec)

two。 Copy the exported cfg file and ibd file to the database of the target server

#

# Source server

#

[root@MyServer burn_test] > ll | grep test_purge

-rw-r-. 1 mysql mysql 462 Mar 21 10:58 test_purge.cfg # export, extra files containing some metadata information

-rw-r-. 1 mysql mysql 8578 Mar 4 15:41 test_purge.frm

-rw-r-. 1 mysql mysql 57344 Mar 5 15:28 test_purge.ibd

[root@MyServer burn_test] > cp test_purge.cfg test_purge.ibd / data/mysql_data/5.7.11_2/burn_test_2/ # copy tablespaces and cfg files. Use scp remotely (local multi-instance demonstration, where the library name is different)

3. After exporting the tablespace, unlock it as soon as possible

-- Source server

Mysql > unlock tables;-unlock as soon as possible

Query OK, 0 rows affected (0.00 sec)

Note: be sure to copy the cfg and ibd files before unlock, because when you unlock, the cfg file will be deleted

# logs on the source server

[Note] InnoDB: Stopping purge # actually stop purge, just find a test table for export

[Note] InnoDB: Writing table metadata to'. / burn_test/test_purge.cfg'

[Note] InnoDB: Table `burn_ test`.`test _ purge`flushed to disk

[Note] InnoDB: Deleting the meta-data file'. / burn_test/test_purge.cfg' # unlock table, the file is automatically deleted

[Note] InnoDB: restore the purge thread after Resuming purge # unlock

4. Modify permissions for cfg and ibd files on the target server

#

# Target server

#

[root@MyServer burn_test_2] > chown mysql.mysql test_purge.cfg test_purge.ibd

5. Import tablespaces through the import command on the target server

-- Target server

--

Mysql > alter table test_purge import tablespace;-- Import tablespaces

Query OK, 0 rows affected (0.24 sec)

Mysql > select * from test_purge;-data copied from the source server can be read

+-+ +

| | a | b | |

+-+ +

| | 1 | 10 |

| | 3 | 30 |

| | 4 | 40 |

| | 5 | 50 |

| | 6 | 60 |

| | 7 | 70 |

| | 8 | 80 |

| | 10 | 100 | |

+-+ +

8 rows in set (0.00 sec)

# Information that appears in error.log

InnoDB: Importing tablespace for table 'burn_test/test_purge' that was exported from host' MyServer'

Note:

The name of the table must be the same. After the above test, the library name can be different.

This method can also be used for backup and recovery of partition tables.

Thank you for reading this article carefully. I hope the article "sample Analysis of tablespace Transmission in mysql" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.