怎么最快地复制一张表?
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 写法。但,这个方法的缺点之一就是,每次只能导出一张表的数据,而且表结构也需要另外的语句单独备份。