本地复制一张innodb表的数据

需求为： 要对一张数据表进行大的动作，在这之前需要进行数据备份。

常用的办法如 mysqldump ， select into outfile的备份效率都太低了。

本文介绍一种较为高效的（直接使用文件拷贝）备份方法：

假设原始数据表如下：

mysql> CREATE TABLE t1 (a INT);
mysql> INSERT INTO t1 VALUES (1),(2),(3);

用于备份的表如下：

mysql> CREATE TABLE t1_new (a INT) DATA DIRECTORY = '/tmp/mysql/';

操作步骤如下：

1. Now we can use ALTER TABLE ... DISCARD TABLESPACE to remove the
tables of t1_new

mysql> ALTER TABLE t1_new DISCARD TABLESPACE;

This will remove the data file of table t1_new (t1_new.ibd).

2. Before copying the data files of the source table, you have to use
FLUSH TABLES ... FOR EXPORT, and you have to do this in another mysql
connection, otherwise it will fail and report table locked or within a
transaction.

mysql> FLUSH TABLES t1 FOR EXPORT;

3. And then you can copy the t1.cfg and t1.ibd file to the destination.

$ cp -a /var/lib/mysql/test/t1.cfg /tmp/mysql/t1_new.cfg
$ cp -a /var/lib/mysql/test/t1.ibd /tmp/mysql/t1_new.ibd

4. After copying the files, we can release the locks obtained by FLUSH
TABLES t1 FOR EXPORT in the first mysql connection.

mysql> UNLOCK TABLES;

5. And now we can import the new table space we just copyied in:

mysql> ALTER TABLE t1_new IMPORT TABLESPACE;

6. Check if the table has the data

mysql> SELECT COUNT(*) FROM t1_new;



Please check the following pages for more information:

http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html
http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html


转载请注明转自高孝鑫的博客