mysql5.6新功能通过flush table with read lock时将meta-data记录到一个单独的文件中,从而使得可以将ibd文件copy到别处进行import,这样可以使用xtrabackup的备份的表可以进行单表还原,但是还得先把xtrabackup备份启动起来生成cfg文件;还是比较麻烦,Percona可以直接import(可以从官方的innodb引擎备份的文件中进行import),注意Percona这个功能(通过参数[innodb_import_table_from_xtrabackup]( "innodb_import_table_from_xtrabackup").
控制)默认是关闭的,不过是dynamic的。**_ _[]( The Problem**
In the past users were unable to take full advantage of the [FLUSH TABLES WITH READ LOCK]( "FLUSH TABLES WITH READ LOCK") statement. InnoDB simply ignored the flush to disk part. If the table did not have any dirty pages in the InnoDB buffer that weren't synced to disk (due to sheer luck) then it was safe to copy the .ibd file to another location. Also, the restore was not without its limitations and complications. The .ibd file could not be copied over to another server because InnoDB during [import]( "ALTER TABLE T IMPORT TABLESPACE") did not do any fix ups required proper import. The main problems during import were :
当执行flush tables withe read lock之后,innodb会将table的所有脏页刷新到磁盘上,这个时候.ibd文件将不会发生很大的改变,但是因为下面的原因导致将idb文件copy到别的实例上时会发生错误:
* If the tablespace id of the IMPORTing instance had changed then the import would fail. * If the table and index ids in the tablespace were different than the importing server.因为两个实例表创建的顺序可能不一致,导致tablespace id会不一致。[参照]( * InnoDB uses a global row id, this is used for tables without an explicit primary key, the row id if present in a to be imported tablespace had to be lower than importing server current maximum.当表没有显示的制定主键时,innodb使用隐藏的row id来表示主键,这个保存在共享表空间中会存在不一致。 * Maximum LSN of the tablespace had to be lower than the server current max LSN * The maximum trx id of the tablespace had to be lower than the server current max trx id. * Purge and change buffer issues
In short, if it worked you were lucky that the above constraints were satisfied or your tables were probably read-only.
**The Solution** Do the flush (export) properly and also the import. During flushing we disable purge, merge all pending change buffer entries to the tablespace and then flush all the dirty pages to disk. **Note: Purge will remain disabled until UNLOCK TABLES; is issued**. Write out a meta-data file that contains the table schema, hostname of the exporting server, page size of the exporting instance and the highest autoinc value in memory etc.. So that when we do an import we can check if the table schema matches (currently ignores foreign key relationships) and also restore the autoinc value. This meta-data file is created in the same directory as the tablespace, it has a suffix of .cfg. If the tables is named _customer_ then the meta-data file will be named _customer.cfg_ and the tablespace will be named _customer.ibd._ Note: FLUSH TABLES and IMPORT only work for tables that are stored outside the system tablespace, in their own tablespace. The InnoDB config parameter [innodb_file_per_table]( "innodb_file_per_table") must be set when creating the table.
Because InnoDB stores data in [big-endian format]( "Endianess"), this allows copying data between different architectures possible. Copying between different operating systems will also work.
5.6的解决方法就是在执行flush table with read lock时将共享表空间中保存的关于表的meta-data保存在一个外部的cfg文件中,等import时可以读取这个cfg文件进行修正。
**Example** If you want to backup a single table: CREATE DATABASE ftwrl; CREATE TABLE ftwrl.t (C INT) ENGINE=InnoDB; INSERT INTO ftwrl.t VALUES(1),(2),(3); SELECT * FROM ftwrl.t; FLUSH TABLES ftwrl.t WITH READ LOCK; From the command line, copy the t.ibd and t.cfg to your backup directory. The files should in the ftwrl/ sub-directory under [datadir]( "--datadir"). UNLOCK TABLES; - **Note: This will remove the t.cfg file.** DROP TABLE ftwrl.t; CREATE TABLE ftwrl.t (C INT) ENGINE=InnoDB; ALTER TABLE ftwrl.t DISCARD TABLESPACE; The DISCARD tablespace will rename the t.ibd file to t.ibt in the same directory, you have to remove this file manually for now. Once you've removed this file, copy the backed up tablespace t.ibd and the meta-data file t.cfg to the ftwrl/ sub-directory. Then issue the folowing: ALTER TABLE ftwrl.t IMPORT TABLESPACE; CHECK TABLE ftwrl.t; SELECT * FROM t; You should be good to go. If you want to backup multiple tables together then the syntax for that is: FLUSH TABLES t1, t2, ..., tn WITH READ LOCK; Copy as before but this time the multiple tablespaces and meta-data files UNLOCK TABLES; Then first discard the tablespace as above followed by an import as above. **Other changes** Errors and warnings will be pushed to the client so that the user doesn't need to poke around in the server error log files to check why an operation failed. The intention is to send enough information to the user so that the problem can be clearly identified and understood. **Conclusion** The syntax for "export" is not finalised yet and may change, for now the above examples using FLUSH TABLES WITH READ LOCK should work. To export tables that are in a foreign key relationship you must export and import all the tables together, otherwise you may end up with inconsistencies. The export/import doesn't work on partitioned tables, this limitation will be addressed later. A matching table definition must exist in the importing server instance, currently we don't automagically create the table using the saved meta-data during import. The .cfg format is not written in stone yet, we may add more data to this file, e.g., when we add partition support.
Your feedback is important to us, so that we can improve this feature further.