mysql5.6 Transfer Table space Migration table or restore mistakenly deleted table

One, simple description:

1), the limit of the transmission table space:
1,mysql version 5.6.6 and above, and version recommendation source and target versions are recommended for GA version and large version
2, the table engine is InnoDB and opens a stand-alone tablespace innodb_file_per_table
3, the table in the export state is only allowed to read and that is the "read_only" state
4,discard Tablespace does not support partitioned tables [5.6.17 support] and you need to set the Foreign_key_check to 0 when there is a foreign key relationship on the table
5, the page size of the source and destination instances must be the same size
6,alter TABLE ... Import Tablespace does not enforce FOREIGN key constraint checking, so both the primary and child tables need to be exported and then imported in the target instance.
In addition, the operation does not require the. CFG metadata file, of course, if you do not change the file at the time of import is not to do meta-data check
7, on Windows, the InnoDB engine is stored in lowercase format database, table name and table space name, so in order to avoid the import because the case of file recommendations in case-sensitive, such as Linux, Unix operating systems are in lowercase format to create databases and tables in the [mysqld] Item is added under
[Mysqld]
Lower_case_table_names=1

2), the advantages of the transfer table space:
1, no need to consume too much resources
2, it is convenient to migrate a table from one instance to another
3, you do not need to export and then import the same maintenance index as compared to mysqldump


Second, normal table space migration
1, source database:
Use test;
CREATE TABLE Lidan (id int) Engine=innodb;
INSERT into Lidan values (10);
2, Target database:
Use test;
CREATE TABLE Lidan (id int) Engine=innodb;
INSERT into Lidan values (10);
3, the target database unloads the table space:
ALTER TABLE Lidan DISCARD tablespace;
The lidan.ibd file and tablespace are detached after the table space is unloaded and an exclusive lock is added to the table, and if DML is executed, the error is directly

4, the source data performs table space export:
Use test;
FLUSH TABLES Lidan for EXPORT;
The flush operation will touch the dirty page of the published Lidan to disk, and the stop purge thread while writing metadata information to the LIDAN.CFG

The metadata file stores the following information:

It's mostly database and table names, row_id, rollback pointers, object IDs, etc.
The session will always hold the metadata lock on the table Lidan, so when other sessions can only select two updates and insert operations will be blocked.



5, Copy the exported table (source database) in the same data file directory as the corresponding LIDAN.IBD file and lidan.cfg to the corresponding folder of the target database
Cp-p lidan.cfg lidan.ibd/data/percona-data-3307/test/

Note: A copy of the file must precede the next release of metadata lock
6. Release the metadata lock of the source database
Use test;
Unlock table;
Unlock This step deletes the previously generated lidan.cfg file, releasing metadata lock and restarting the purge thread
7, the target database imports the table into the table space
ALTER TABLE Lidan import tablespace;
Then view the import success

The process of importing MySQL internally will do the following things:
A, check the consistency of each page in the Tablespace
b, update the space ID and LSN information for each page
C, Enable header page markup and update LSN to header page
The D,page status information is set to dirty so that the page will be flushed to disk soon
Specific

Third, the recovery in case of accidental deletion
This is only to discuss the recovery of deleted by the repository, if it is the main library mistakenly delete the table that can only be restored by backup
1, start the drop operation on the standby, simulate the accidental deletion
(User:root time:16:37 port:3307) [Db:test]drop table Lidan;
2, the repository recovery must have a. frm file, so here you need to view the table structure from the source library and create it in the target library
CREATE TABLE Lidan (id int) Engine=innodb;
The next steps are as follows (b) from 3 onwards, please test yourself.


Reference:
Http://dev.mysql.com/doc/refman/5.6/en/flush.html
Http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

Ext.: http://blog.csdn.net/lidan3959/article/details/25152623

mysql5.6 Transfer Table space Migration table or restore mistakenly deleted table