MySQL 快速复制表

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;

mysqldump表

mysqldump -h 127.0.0.1 -P 3306 -u root --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/tmp/t.sql -p

• –single-transaction：在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
• --add-locks设置为0，表示输出的文件结果里，不增加"LOCK TABLES t WRITE;"
• --no-create-info：不导出表结构
• --set-gtid-purged=OFF：不输出跟GTID相关的信息
• --result-file：指定了输出文件的路径

mysql -h 127.0.0.1 -P 3306 -u root db2 -e "source /tmp/t.sql" -p

source命令的执行流程如下：

1. 打开文件，默认以分号为结尾读取一条一条的SQL语句
2. 将SQL语句发送到服务端执行

导出CSV文件

select * from db1.t where a > 900 into outfile '/tmp/t.csv';

• 上述语句会把结果保存在服务端
• into outfile指定文件的生成位置，该位置会受到secure_file_priv参数的限制。
• 上述命令不会覆盖文件

show global variables like 'secure_file_priv';

• 设置为NULL：禁止在mysql实例上执行select into outfile
• 设置为empty：不限制文件的生成为止
• 表示路径的字符串：只能在该目录下或其子目录下

load data infile '/tmp/t.csv' into table db2.t;

1. 打开文件/tmp/t.csv，以制表符\t作为字段间的间隔符，以换行符\n作为记录之间的分隔符进行数据读取
2. 启动事务
3. 判断每一行的字段数和表db2.t是否相同：如果不相同，报错，事务回滚；如果相同，则构造成一行，调用InnoDB引擎接口写入到表中
4. 重复步骤3，直至读取完整个文件

在binlog_format=statement的模式下，上述语句生成的binlog如下图：

物理拷贝方法

1. create table r like t，创建一个相同表结构空表
2. alter table r discard tablespace，此时r.ibd文件会被删除
3. flush table t for export(执行完以后，表t处于只读状态)，此时在db1目录下会生成一个t.cfg的文件
4. 在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd
5. 执行unlock tables(表t恢复可读写)，此时t.cfg会被删除
6. 执行alter table r import tablespace（修改r.ibd的表空间id, 表空间id存在于每一个数据页，需要修改为和数据字典中的一致），将r.ibd文件作为表r的新的表空间