Smarticen Notes

最快复制一张表的方法

5 min read

mysqldump方法

使用mysqldump命令将数据到处一组INSERT语句：

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;”。
  • -no-create-info：不需要导出表结构
  • -set-gtid-purged=off，不输出跟GTID相关的信息
  • -result-file：指定输出的文件路径，client表示生成的文件是在客户端机器上的。

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

然后通过执行下面的命令，将INSERT语句放到db2库里取执行：

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

这条命令是客户端命令，负责取出文件中以分号结尾的一条条SQL语句，将SQL发送到服务端执行。

到处CVS文件

直接将结果导出成.csv文件。mysql提供了下面的语法，用来将查询结果导出到服务端本地目录：

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

导出文件之后，就可以使用load data命令将数据导入到目标表db2.t中

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

因为csv文件是保存在本地，所以为了能够备库也成功执行，主库执行完成之后，会将csv文件的内容直接写到binlog文件中。

备库会先将binlog中的临时文件内存读取出来写入到本地临时目录，然后再执行load data语句。

物理拷贝方法

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

拷贝一个与t相同的表r。

  1. 执行create table like t，创建一个表结构相同的空表。
  2. 执行alter table r discard tablespace，r.idb文件会被删除
  3. 执行flush table t for export，db1目录下会生成一个t.cfg文件。
  4. 到db1目录下执行cp t.cfg r.cfg; cp t.ib r.ibd。就是拷贝表t的两个文件。
  5. 执行Unlock tables,这样 t.cfg文件会被删除。
  6. 执行alter table r import tablespace，将r.ibd文件作为表r的新的表空间，这样表r就有了和表t相同的数据。

小结

  1. 物理拷贝方式速度最快，尤其对于大表拷贝来说是最快的方法。不过有一定的局限性：
    1. 必须是全表拷贝，不能只拷贝部分数据
    2. 需要到服务器上拷贝数据，再用户无法登录数据库主机的场景下无法使用
    3. 由于是拷贝物理文件实现的，源表和目标表都使用InnoDB引擎才能使用。
    4. 用mysqldump生成包含INSER语句，可以在where参数增加过滤条件。不过不能使用join这种比较负责的where条件写法。
    5. 用select … into outfiile的方法是最灵活的，支持所有SQL写法，但是这个方法每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

Graph View