数据库极客时间-mysql
Source
Edit
History

快速复制一张表

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

一、mysqldump 方法

使用 mysqldump 命令将数据导出成一组 insert 语句。可以使用如下命令：

1
mysqldump -h$host -P$port -u$user -p --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 设置为 0，表示在输出的文件结果中，不增加 LOCK TABLES t write; 语句
  • --no-create-info 的意思是，不需要导出表结构
  • --set-gtid-purged=OFF 表示不输出和 GTID 相关的信息
  • --result-file=/client_tmp/t.sql 指定流输出文件的路径

通过这条 mysqldump 命令，生成的 sql 文件中包含了符合条件下的 INSERT 语句（包含了所有数据）。

如果希望生成的文件中一条 INSERT 语句，只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数 --skip-extended-insert。

然后我们可以将生成的 sql 文件，将数据导入到目标数据库中：

1
mysql -hxxx -Pxxx -uxxx db2 -e "source /client_tmp/t.sql"

其中，source 是一个客户端命令。相当于打开文件，读取一条条以分号结尾的 SQL 语句，然后将 SQL 语句发送到服务端执行。

二、导出 CSV 文件

直接将结果导出成 .csv 文件。如下将查询结果导出到服务端本地目录：

1
select * from db1.t where a>900 into outfile '/var/lib/mysql-files/db1_t.csv'

我们需要注意以下几点：

  • 这条语句将结果保存在服务端机器上

  • into outfile 指定了文件的生成位置，这个位置必须受参数 secure_file_priv 的限制。

    可以使用 show variables like 'secure_file_priv'; 来查看。 参数 secure_file_priv 的可选值和作用分别是：

    • 如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置
    • 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者他的子目录
    • 如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select ... into outfile 操作

  • 这条命令不会帮我们覆盖文件，因为需要确保 /var/lib/mysql-files/db1_t.csv 这个文件不存在，否则会报错退出

  • 这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上 \ 这个转义符，这样就可以和字段之间、数据行之间的分隔符分开。

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

1
load data infile '/var/lib/mysql-files/db1_t.csv' into table db2.t;

这条语句的执行流程如下：

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

如果 binlog_format=statement，这个 load 语句记录到 binlog 中后，如何在备库重放？因为备库的本地机器上没有这个文件。

  • 主库执行完成后，将 /var/lib/mysql-files/db1_t.csv 文件的内容直接写到 binlog 文件中
  • 往 binlog 文件中写入语句 load data local infile '/tmp/SQL_LOAD_MB-1-0' into table db2.t;
  • 把这个 binlog 日志传到备库
  • 备库的 apply 线程在执行这个事务日志时，先将 binlog 中的 t.csv 文件的内容读出来，写入到本地临时目录 /tmp/SQL_LOAD_MB-1-0 中；然后再执行 load data 语句，往备库的 db2.t 表中插入和主库相同的数据

这里，备库执行的 load data 语句中，这条命令中多了一个 local，表示将执行这条命令的客户端所在机器的本地文件 /tmp/SQL_LOAD_MB-1-0 的内容，加载到 db2.t 中。也就是说，load data 命令有两种用法：

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

需要注意，select ... into outfile 方法不会生成表结构文件，所以我们导出数据时还需要单独的命令得到表结构定义。mysqldump 提供了一个 -tab 参数，可以同时导出表结构定义文件和 csv 数据文件。这条命令的使用方法如下：

1
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 文件。

三、物理拷贝方法

上面两种办法都是逻辑导数据的方法，也就是从表 db1.t 中读出来，生成文本，然后再写入目标表 db2.t 中。这里，我们介绍下物理导数据的方式。

一个 InnoDB 表，除了包含两个物理文件（.frm 文件和 .ibd 文件）外，还需要在数据字典中注册。

从 MySQL 5.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.t 整个表处于只读状态，直到执行 unlock tables; 命令后才释放读锁。
  • 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd; 这这两个命令。（注意：Linux 中 db1 目录的位置一般在： /var/lib/mysql/db1 下。然后拷贝文件，注意查看文件的权限，MySQL 进程要有读写权限）
  • 执行 unlock tables; 这时候 t.cfg 文件会被删除
  • 执行 alter table r import tablespace; 将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。在执行 import tablespace 的时候，为了让文件中的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件，每个数据页都需要修改，所以会看到这个 import 语句的执行是需要一些时间的。当然，相比于逻辑导入的方式，import 语句的耗时是非常短的。

四、三种方法的优缺点

我们来对比一下三种方法的优缺点。

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

后两种都是逻辑备份方式，是可以跨引擎使用的。