快速的复制一张表

mysqldump

你可以使用下面的命令：

mysqldump -h$host -P$port -u$user --add-locks --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

这条命令中，主要参数含义如下：

  1. –single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
  2. –add-locks设置为0，表示在输出的文件结果里，不增加” LOCK TABLES t WRITE;” ；
  3. –no-create-info的意思是，不需要导出表结构；
  4. –set-gtid-purged=off表示的是，不输出跟GTID相关的信息；
  5. –result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。

再使用这个命令加载：mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"

导出csv文件

导出命令：select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

需要注意如下几点：

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

导入命令：load data infile '/server_tmp/t.csv' into table db2.t;

另外需要注意的是，select …into outfile方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义。mysqldump提供了一个–tab参数，可以同时导出表结构定义文件和csv数据文件。这条命令的使用方法如下：mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

物理拷贝方法(不能跨引擎)

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

假设我们现在的目标是在db1库下，复制一个跟表t相同的表r，具体的执行步骤如下：

  1. 执行 create table r like t，创建一个相同表结构的空表；
  2. 执行alter table r discard tablespace，这时候r.ibd文件会被删除；
  3. 执行flush table t for export，这时候db1目录下会生成一个t.cfg文件；
  4. 在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令；
  5. 执行unlock tables，这时候t.cfg文件会被删除；
  6. 执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。