一、本节概述
我在上一篇文章最后给你留下的问题是怎么在两张表中拷贝数据。如果可以控制对源表的扫描行数和加锁范围很小的话我们简单地使用 insert … select 语句即可实现。
当然为了避免对源表加读锁更稳妥的方案是先将数据写到外部文本文件然后再写回目标表。这时有两种常用的方法。接下来的内容我会和你详细展开一下这两种方法。
为了便于说明我还是先创建一个表 db1.t并插入 1000 行数据同时创建一个相同结构的表 db2.t。
create database db1;
use db1;
create table t(id int primary key, a int, b int, index(a))engineinnodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i1;
while(i<1000)do
insert into t values(i,i,i);
set ii1;
end while;
end;;
delimiter ;
call idata();
create database db2;
create table db2.t like db1.t
假设我们要把 db1.t 里面 a>900 的数据行导出来插入到 db2.t 中。
二、mysqldump 方法
1、mysqldump 主要参数含义
一种方法是使用 mysqldump 命令将数据导出成一组 INSERT 语句。你可以使用下面的命令
mysqldump -h$host -P$port -u$user --add-locks0 --no-create-info --single-transaction --set-gtid-purgedOFF 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-purgedoff 表示的是不输出跟 GTID 相关的信息
5. –result-file 指定了输出文件的路径其中 client 表示生成的文件是在客户端机器上的。
通过这条 mysqldump 命令生成的 t.sql 文件中就包含了如图 1 所示的 INSERT 语句。
图 1 mysqldump 输出文件的部分结果
可以看到一条 INSERT 语句里面会包含多个 value 对这是为了后续用这个文件来写入数据的时候执行速度可以更快。
如果你希望生成的文件中一条 INSERT 语句只插入一行数据的话可以在执行mysqldump 命令时加上参数–skip-extended-insert。
然后你可以通过下面这条命令将这些 INSERT 语句放到 db2 库里去执行。
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
2、source 并不是一条 SQL 语句而是一个客户端命令
需要说明的是source 并不是一条 SQL 语句而是一个客户端命令。mysql 客户端执行这个命令的流程是这样的
1. 打开文件默认以分号为结尾读取一条条的 SQL 语句
2. 将 SQL 语句发送到服务端执行。
也就是说服务端执行的并不是这个“source t.sql"语句而是 INSERT 语句。所以不论是在慢查询日志(slow log)还是在 binlog记录的都是这些要被真正执行的
INSERT 语句。
三、导出 CSV 文件
1、导出 CSV 文件注意事项
另一种方法是直接将结果导出成.csv 文件。MySQL 提供了下面的语法用来将查询结果
导出到服务端本地目录
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. 这条命令生成的文本文件中原则上一个数据行对应文本文件的一行。但是如果字段中包含换行符在生成的文本中也会有换行符。不过类似换行符、制表符这类符号前
面都会跟上“\”这个转义符这样就可以跟字段之间、数据行之间的分隔符区分开。
得到.csv 导出文件后你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中。
load data infile /server_tmp/t.csv into table db2.t;
2、load data 语句执行流程
得到.csv 导出文件后你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中。
这条语句的执行流程如下所示。
1. 打开文件 /server_tmp/t.csv以制表符 (\t) 作为字段间的分隔符以换行符(\n)作为记录之间的分隔符进行数据读取
2. 启动事务。
3. 判断每一行的字段数与表 db2.t 是否相同
若不相同则直接报错事务回滚
若相同则构造成一行调用 InnoDB 引擎接口写入到表中。
4. 重复步骤 3直到 /server_tmp/t.csv 整个文件读入完成提交事务。
3、如果 binlog_formatstatement这个 load 语句记录到 binlog里以后怎么在备库重放呢
你可能有一个疑问如果 binlog_formatstatement这个 load 语句记录到 binlog里以后怎么在备库重放呢
由于 /server_tmp/t.csv 文件只保存在主库所在的主机上如果只是把这条语句原文写到binlog 中在备库执行的时候备库的本地机器上没有这个文件就会导致主备同步停止步
4、load data 的完整执行流程
所以这条语句执行的完整流程其实是下面这样的。
1. 主库执行完成后将 /server_tmp/t.csv 文件的内容直接写到 binlog 文件中。
2. 往 binlog 文件中写入语句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0’INTO TABLE db2.t。
3. 把这个 binlog 日志传到备库。
4. 备库的 apply 线程在执行这个事务日志时
a. 先将 binlog 中 t.csv 文件的内容读出来写入到本地临时目录/tmp/SQL_LOAD_MB-1-0 中
b. 再执行 load data 语句往备库的 db2.t 表中插入跟主库相同的数据。
执行流程如图 2 所示
图 2 load data 的同步流程
注意这里备库执行的 load data 语句里面多了一个“local”。它的意思是“将执行这条命令的客户端所在机器的本地文件 /tmp/SQL_LOAD_MB-1-0 的内容加载到目标表db2.t 中”。
5、load data 命令有两种用法
1. 不加“local”是读取服务端的文件这个文件必须在 secure_file_priv 指定的目录或子目录下
2. 加上“local”读取的是客户端的文件只要 mysql 客户端有访问这个文件的权限即可。这时候MySQL 客户端会先把本地文件传给服务端然后执行上述的 load data流程。
另外需要注意的是select …into outfile 方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义。mysqldump 提供了一个–tab 参数可以同时导出表结
构定义文件和 csv 数据文件。这条命令的使用方法如下
mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purgedOFF db1 t --where"a>900" --tab$secure_file_priv
这条命令会在 $secure_file_priv 定义的目录下创建一个 t.sql 文件保存建表语句同时创建一个 t.txt 文件保存 CSV 数据。
四、物理拷贝方法
1、直接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下是否可行呢
前面我们提到的 mysqldump 方法和导出 CSV 文件的方法都是逻辑导数据的方法也就是将数据从表 db1.t 中读出来生成文本然后再写入目标表 db2.t 中。
你可能会问有物理导数据的方法吗比如直接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下是否可行呢
答案是不行的。
因为一个 InnoDB 表除了包含这两个物理文件外还需要在数据字典中注册。直接拷贝这两个文件的话因为数据字典中没有 db2.t 这个表系统是不会识别和接受它们的
2、MySQL 5.6实现物理拷贝表的步骤
不过在 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这两个命令(这里需要注意的是拷贝得到的两个文件MySQL 进程要有读写权限)
5. 执行 unlock tables这时候 t.cfg 文件会被删除
6. 执行 alter table r import tablespace将这个 r.ibd 文件作为表 r 的新的表空间由于这个文件的数据内容和 t.ibd 是相同的所以表 r 中就有了和表 t 相同的数据。
至此拷贝表数据的操作就完成了。这个流程的执行过程图如下
图 3 物理拷贝表
3、物理拷贝方法注意事项
关于拷贝表的这个流程有以下几个注意点
1. 在第 3 步执行完 flsuh table 命令之后db1.t 整个表处于只读状态直到执行 unlocktables 命令后才释放读锁
2. 在执行 import tablespace 的时候为了让文件里的表空间 id 和数据字典中的一致会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此如果是一
个很大的文件(比如 TB 级别)每个数据页都需要修改所以你会看到这个 import语句的执行是需要一些时间的。当然如果是相比于逻辑导入的方法import 语句的耗时是非常短的。
五、小结
今天这篇文章我和你介绍了三种将一个表的数据导入到另外一个表中的方法。
我们来对比一下这三种方法的优缺点。
1. 物理拷贝的方式速度最快尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况用备份恢复出误删之前的临时库然后再把临时库中的表拷贝到生产库上是恢复
数据最快的方法。但是这种方法的使用也有一定的局限性
必须是全表拷贝不能只拷贝部分数据
需要到服务器上拷贝数据在用户无法登录数据库主机的场景下无法使用
由于是通过拷贝物理文件实现的源表和目标表都是使用 InnoDB 引擎时才能使用
2. 用 mysqldump 生成包含 INSERT 语句文件的方法可以在 where 参数增加过滤条件来实现只导出部分数据。这个方式的不足之一是不能使用 join 这种比较复杂的
where 条件写法。
3. 用 select … into outfile 的方法是最灵活的支持所有的 SQL 写法。但这个方法的缺点之一就是每次只能导出一张表的数据而且表结构也需要另外的语句单独备份。
后两种方式都是逻辑备份方式是可以跨引擎使用的。
最后我给你留下一个思考题吧。
我们前面介绍 binlog_formatstatement 的时候binlog 记录的 load data 命令是带local 的。既然这条命令是发送到备库去执行的那么备库执行的时候也是本地执行为什
么需要这个 local 呢如果写到 binlog 中的命令不带 local又会出现什么问题呢
你可以把你的分析写在评论区我会在下一篇文章的末尾和你讨论这个问题。感谢你的收听也欢迎你把这篇文章分享给更多的朋友一起阅读。
六、上期问题时间
我在上篇文章最后给你留下的思考题已经在今天这篇文章的正文部分做了回答。上篇文章的评论区有几个非常好的留言我在这里和你分享一下。
huolang 同学提了一个问题如果 sessionA 拿到 c5 的记录锁是写锁那为什么sessionB 和 sessionC 还能加 c5 的读锁呢
这是因为 next-key lock 是先加间隙锁再加记录锁的。加间隙锁成功了加记录锁就会被堵住。如果你对这个过程有疑问的话可以再复习一下第 30 篇文章中的相关内容。
一大只 同学做了一个实验验证了主键冲突以后insert 语句加间隙锁的效果。比我在上篇文章正文中提的那个回滚导致死锁的例子更直观体现了他对这个知识点非常好的理
解和思考很赞。
roaming 同学验证了在 MySQL 8.0 版本中已经能够用临时表处理 insert … select 写入原表的语句了。
老杨同志 的回答提到了我们本文中说到的几个方法。