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

How to back up and restore a single innodb table in MySQL, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Usage restrictions and instructions

Innodb_file_per_table must be set to on, which is enabled by default in the MySQL5.6.6 version. Tables that reside in a shared system tablespace cannot be silent.

When the table is silent, only read-only transactions are allowed.

When importing tablespaces, the page size must match the page size of the imported instance.

DISCARD TABLESPACE does not support partitioned tables, which means that transportable tablespaces does not support partitioned tables either. If you execute ALTER TABLE on a partition table... DISCARD TABLESPACE will return the following error message: ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option.

When foreign_key_checks=1, DISCARD TABLESPACE does not support primary key foreign key constraints. These tables need to be set to foreign_key_checks when manipulating them.

ALTER TABLE... IMPORT TABLESPACE does not enforce foreign key constraints. If there are foreign key constraints between tables, all tables should be exported at the same point in time.

ALTER TABLE... .cfg metadata files are not required for IMPORT TABLESPACE to import tablespaces. However, the metadata check of the .cfg file cannot be completed without the .cfg file on import, or the following information is returned: InnoDB: IO Read error: (2, No such file or directory) Error opening'.\ test\ t.cfgcheck, will attempt to import without schema verification 1 row in set (0.00 sec).

When there are no mismatched table structures, it may be more convenient to import without a .cfg file. In addition, it may be more useful to import without .cfg when metadata cannot recover from failures collected from .ibd files.

The imported MySQL version for export needs to be the same. Otherwise, the file must be created on the imported server.

In a replicated architecture, the master and slave must set the innodb_file_per_table=1.

In windows, files are case-insensitive, while Linux and unix are case-sensitive, so you need to set lower_case_table_names=1 when importing and exporting across platforms.

Copy the tablespace to another

This procedure demonstrates how to copy tablespaces from one running MySQL server instance to another. Assume that the source instance is server_A and the destination instance is server_B.

On server_A

Mysql > use test

Mysql > CREATE TABLE ttlsa (id INT) engine=InnoDB

On server_B

Mysql > use test

Mysql > CREATE TABLE ttlsa (id INT) engine=InnoDB

On server_B

Discard existing tablespaces. Before tablespaces are imported, InnoDB must discard tablespaces that are connected to the accepted table.

1mysql > ALTER TABLE ttlsa DISCARD TABLESPACE

On server_A

Execute FLUSH TABLES... The FOR EXPORT statement silences the table and generates a .cfg metadata file. FLUSH TABLES... After the execution of FOR EXPORT, the session cannot be exited, otherwise the cfg will disappear automatically.

Mysql > use test

Mysql > FLUSH TABLES ttlsa FOR EXPORT

The file .cfg is created in the InnoDB data directory.

On server_A

Copy .ibd and .cfg files to server_B

Shell > scp / path/to/datadir/test/ttlsa. {ibd,cfg} destination-server:/path/to/datadir/test

The files .ibd and .cfg must be copied before releasing the shared lock.

On server_A

Release FLUSH TABLES... FOR EXPORT statement lock

Mysql > use test

Mysql > UNLOCK TABLES

On server_B

Import tablespace

Mysql > use test

Mysql > ALTER TABLE ttlsa IMPORT TABLESPACE

Transportable Tablespace inside story

The following describes the internal and error log information during tablespace transfer.

When executing ALTER TABLE on server_B... DISCARD TABLESPACE

The table is locked in X mode

The tablespace is separated from the table

When executing FLUSH TABLES on server_A... FOR EXPORT

The table is locked in shared mode

Purge coordinator thread stop

Dirty pages are synchronized to disk

Write table metadata to a binary .cfg file

The log information is as follows:

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

[Note] InnoDB: Stopping purge

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

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

When UNLOCK TABLES is executed on server_A

The binary .cfg file will be deleted

The shared lock will be released and the purge coordinator thread will restart

The log information is as follows:

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

[Note] InnoDB: Resuming purge

When executing ALTER TABLE on server_B... IMPORT TABLESPACE

Each tablespace page will check for corruption

Each space ID and log sequence number (LSN) will be updated

Flag valid and LSN update front page

The Btree page is updated

The page status is set to dirty and will be written to disk

The log information is as follows:

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

[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

After reading the above, have you mastered how to back up and restore a single innodb table in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.