怎么最快地复制一张表？
怎么最快地复制一张表？
mysqldump
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，导出数据不用锁表，使用
START TRANSACTION WITH CONSISTENT SNAPSHOT方法 - --add-locks 设置为0，不输出
LOCK TABLES t WRITE - --no-create-info，不导出表结果
- --set-gtid-purged=off 不输出GTID相关的信息
- --result-file 指定了输出文件的位置。其中client表示生成的文件在客户端机器上的
默认生成的insert语句是批量插入的，有多个value组成
通过--skip-extended-insert 让一个insert语句只插入一条记录。
导出CSV文件
select * from db1.t where a>999 into outfile '/server_tmp/t.csv'';
- 语句生成的文件在服务端
- into outfile 受
secure_file_priv限制，
- 设置为empty，表示不限制文件生成的位置
- 设置具体路径，表示生成的文件只能放在这个指定的目录，或者它的子目录。
- 设置为NULL，表示禁止在这个MySQL实例上执行select ... into outfile的操作。
- 如果指定路径有同名文件，会报错
- 一个数据行对应文本文件一行。
将csv文件导入
load data infile '/server_tmp/t.csv' into table db2.t;
如果binlog_format=statement, 这个load语句怎么在备库重放？
- 主库会将/server_tmp/t.csv文件内容直接写入binlog文件中
- 往binlog中写入
load data local infile '/tmp/SQL_LOAD_MB-1-0' into table db2.t - binlog传到备库
- 备库将binlog的csv文件读出后写入本地的/tmp/SQL_LOAD_MB-1-0中，再执行load data 语句
load data 命令有两种用法：
- 不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；
- 加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data 流程。
select …into outfile 方法不会生成表结构文件,
mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv
这条命令会在 $secure_file_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据。
物理拷贝方法
- 执行 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 进程要有读写权限）；
- 执行 unlock tables，这时候 t.cfg 文件会被删除；
- 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。
flusb table 后整个表处于只读状态，直到执行unlock tables 命令后才释放锁
总结
- 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
- 必须是全表拷贝，不能只拷贝部分数据；
- 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
- 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
- 用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。
- 用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。
