MySQL 5.6 Using transport table space to migrate tables or recover mistakenly deleted tables

MySQL 5.6 Using transport table space to migrate tables or recover mistakenly deleted tables

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

Before on the internet have seen some of the 5.6 transmission table space Introduction, but not enough to write specific details, today there is a specific operation, and detailed records of the left a file

One, simple description:
1), Transport tablespace restrictions: 1,mysql version 5.6.6 and above, and the version is recommended that the source and target versions are GA version and the same as the large version 2, the table engine is INNODB and open the independent tablespace innodb_file_per_table 3, in the export state The table is only allowed to read and that is the "read_only" state 4,discard tablespace does not support partitioned tables [5.6.17 is supported] and when there is a foreign key relationship on the table you need to set Foreign_key_check to 0 5, the source and destination instances of the page Size must be uniform 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, and 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 done meta-data check 7, on Windows, Inn       The ODB engine internally 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 at the same time under the [mysqld] key added [mysqld] Lower_case_table_names=1 2), the advantages of the transmission table space: 1, do not need to consume too much resources 2, it is convenient to migrate the table from one instance to another 3, relative to mysqldump you do not need to export and then import the same maintenance index
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 (Ten);       3, target database unload tablespace:     &NB Sp    alter TABLE Lidan DISCARD tablespace;           LIDAN.IBD file and table space are detached and an exclusive lock is added to the table after the table space is unloaded, and if DML is executed then it will be immediately error                  4, source Data Execution table space export:          use test;          flush TABLES Lidan for EXPORT;         Flush operation will touch the dirty page of the published Lidan to disk, and stop purge thread while writing metadata information to lidan.cfg       &NB Sp The following information is stored in the           metadata file:                     is primarily a database and table name, ROW_ID, rollback pointer, object ID, etc.     &N Bsp     The session will always hold the metadata lock on the table Lidan, so when other sessions can only make a select two update and the insert operation will be blocked.                               5 to export Table (source database) is located in the data file directory corresponding to the Lidan.ibd file and lidan.cfg copy 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 be released before the next release of metadata lock      6, releasing the metadata lock          use test of the source database;          unlock table;         Unlock This step removes the previously generated lidan.cfg file, releasing metadata lock and restarting the purge thread      7, the target database will guide the table Enter tablespace         ALTER TABLE Lidan import tablespace;          Then view import success   & nbsp             &nbsp The process of importing MySQL internally will do the following things:         A, check the table space for each page consistency         B, update the space ID and LSN information for each page & nbsp       C, enable header page tagging and update LSN to header page         d,page status information is set to dirty, so page will be flushed to disk soon     & nbsp   Specific                   III, recovery in the case of accidental deletion     only the recovery of the deleted copy is discussed here, if the main library mistakenly deletes the table it can only be restored by backup       1, first the drop operation on the standby, analog delete         (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 done in (ii) from 3 onwards, please test yourself.           reference: http://dev.mysql.com/doc/refman/5.6/en/flush.html         &NBS P                          ,         &NB Sp           http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html