快速复制一张表

insert...select语句
利用mysqldump工具
利用mysqlimport工具
load data infile语句
物理拷贝方法

准备

create database db1;
use db1;

create table t(id int primary key, a int, b int, index(a) ) engine=innodb;
delimiter ;;
  create procedure idata()
  begin
    declare i int;
    set i=1;
    while(i<=1000)do
      insert into t values(i,i,i);
      set i=i+1;
    end while;
  end;;
delimiter ;
call idata();

create database db2;
create table db2.t like db1.t

insert...select语句

如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用 insert … select 语句即可实现。

INSERT INTO new_table SELECT * FROM old_table;

这种方法的缺点是，如果源表数据量很大，那么复制过程会消耗大量的资源，甚至会导致数据库服务器的性能下降。

利用mysqldump工具

mysqldump 工具可以用来导出和导入数据库中的数据。我们可以利用 mysqldump 工具导出源表的数据，然后导入到新表中。

mysqldump -u root -p old_db old_table > old_table.sql

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

然后导入到新表中：

mysql -u root -p new_db < old_table.sql

mysql -u root -p new_db < t.sql

利用mysqlimport工具

mysqlimport 工具可以用来导入数据到数据库中。我们可以利用 mysqlimport 工具导入源表的数据到新表中。

mysqlimport -u root -p new_db old_table.sql

load data infile语句

load data infile 语句可以用来导入数据到数据库中。我们可以利用 load data infile 语句导入源表的数据到新表中。

-- export data to csv file
select * from db1 where a > 900 into outfile '/client_tmp/old_table.csv' fields terminated by ',' lines terminated by '\n';


load data infile '/client_tmp/old_table.txt' into table new_db.t fields terminated by ',' lines terminated by '\n' (id, a, b);

物理拷贝方法

物理拷贝方法是指利用系统提供的拷贝功能，将源表的数据直接拷贝到新表中。

在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r：

执行 create table r like t，创建一个相同表结构的空表；
执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令；
执行 unlock tables，这时候 t.cfg 文件会被删除；
执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由1. 于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。