快速复制一张表

在两表中拷贝数据。如果可以控制对源表的扫描函数和加锁范围很小的话，简单的使用insert…select语句即可实现。

为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。

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  

-- 把db1.t里面a>900的数据行导出来，插入到db2.t中。

mysqldump方法

使用mysqldump命令将数据导出成一组insert语句。把结果输出到临时文件。

1	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

-single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用start transaction with consistent snapshot的方法；
-add-locks设置为0，表示在输出文件结果里，不增加”lock tables t write;”；(等效于–skip-add-locks)
-no-create-info的意思是，不需要导出表结构；
-set-gtid-purged=off表示的是，不输出跟gtid相关的信息；
-result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。

通过这条mysqldump命令生成的t.sql文件中就包含了insert语句。一条insert语句里面包含多个value对，后续用这个文件来写入数据的时候，执行所读可以更快。

单条sql里的value值不会超过参数net_buffer_length，通过–net_buffer_length传给mysqldump工具

执行mysqldump命令时，加上参数-skip-extended-insert，生成的文件中一条insert语句只插入一行数据。

插入数据

1 2	mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql" // 将insert语句放到db2库里执行

source并不是一条sql语句，而是一个客户端命令。mysql客户端执行这个命令的流程：

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

服务端执行的并不是这个”source t.sql”语句，而是insert语句。不论是在慢查询日志(slow log)，还是在binlog，记录的都是这些要被真正执行的insert语句。

导出CSV文件

将结果导出成.csv文件。

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

这条语句会将结果保存在服务端。如果执行命令的客户端和MySQL服务端不在同一个机器上，客户端机器的临时目录下是不会生成t.csv文件的。
into outfile指定了文件的生成位置(/server_tmp)，这个位置必须受参数secure_file_priv的限制。参数secure_file_priv的可选值和作用分别是：
- 如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；
- 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
- 如果设置为null，就表示禁止在这个MySQL实例上执行select…into outfile操作。
这条命令不会覆盖文件，需要确保/server_tmp/t.csv这个文件不存在，否则执行语句时会因为有同名文件的存在而报错。
这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上”"这个转义符，可以跟字段之间、数据行之间的分隔符区分开。

导入数据

1 2	load data infile '/server_tmp/t.csv' into table db2.t; // 用load data命令将csv文件中的数据导入到目标表db2.t中。

执行流程：

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

备库重放

binlog_format=statement
由于/server_tmp/t.csv文件只保存在主库所在的主机上，如果只是把这条语句原文写到binlog中，在备库执行的时候，备库的本地机器上没有这个文件，就会导致主备同步停止。

完整流程：

主库执行完成后，将/server_tmp/t.csv文件的内容直接写到binlog文件中。
往binlog文件中写入语句load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ into table ‘db2’.’t’。

备库执行的load data语句里面，多了一个”local”。它的意思是”将执行这条命令的客户端所在机器的本地文件/tmp/SQL_LOAD_MB-1-0的内容，加载到目标表db2.t中”。
把这个binlog日志传到备库。
备库的apply线程在执行这个事务日志时；
- 先将binlog中t.csv文件的内容读出来，写入到本地临时目录/tmp/SQL_LOAD_MB-1-0中；
- 再执行load data语句，往备库的db2.t表中插入跟主库相同的数据。

load data命令两种用法：

不加“local”，是读取服务端的文件，这个文件必须在secure_file_priv指定的目录或子目录下；
加上“local”，读取的时候客户端的文件，只要mysql客户端有访问这个文件的权限即可。这时，MySQL客户端会先把本地文件传给服务端，然后执行上述的load data流程。

导出表结构定义

select…into outfile方法不会生成表结构文件，导数据时还需要单独的命令得到表结构定义。-tab参数，可以同时导出表结构定义文件和csv数据文。

1
2

mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_filr_priv;  
// 这条命令会在$secure_file_priv定义的目录下，创建一个t.sql文件保存建表语句，同时创建一个t.txt文件保存csv数据。

物理拷贝方法

mysqldump和csv都是逻辑导数据的方法，将数据从表db1.t中读出来，生成文本，然后再写入目标表db2.t中。

物理导数据，直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下不可行。
一个InnoDB表，除了包含这两个物理文件外，还需要再数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有db2.t这个表，系统不会识别和接受它们。

MySQL5.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;这两个命令(拷得到的两个文件，MySQL进程要有读写权限:root账号下命令行复制的文件，mysql用户启动的进程没有读写权限)；
执行unlock tables，这是t.cfg文件会被删除；
执行alter table r import tablespcace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的所以表r中就有了和表t相同的数据。

注意：

在第3步执行完flush table命令之后，db1.t整个表处于只读状态，直到执行unlock tables命令后才释放读锁；
在执行import tablespace的时候，为了让文件里的表空间id和数据字典中的一致，会修改r.ibd的表空间id。而这个表空间id存在于每一个数据页中。因此，如果是一个很大的文件(TB级别)，每个数据页都需要修改，所以会看到这个import语句的执行是需要一些时间的。但相比于逻辑导入的方法，import语句的耗时是非常短的。

总结

物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。局限性：
- 必须是全表拷贝，不能只拷贝部分数据；
- 需要到服务器上拷贝数据，在用户无法登陆数据库主机的场景下无法使用；
- 由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。
用mysqldump生成包含insert语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据。不足之一：不能使用join这种比较复杂的where条件写法。(mysqldump本身会保留一些环境信息、字符集等)
用select…into outfile的方法是最灵活的，支持所有的SQL写法。缺点之一：每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。