mysqldump | mysql 跨库导数据“假死锁“现象浅析

介绍

在日常业务开发中，经常可能需要对MySQL
中数据进行导出、导入，最常使用的工具便是mysqldump
。

mysqldump
功能很丰富，可以支持导出表结构、导出整个库数据、导出指定表数据、甚至导出满足指定条件的数据到本地文件以及从一个库中将数据导入另一个库。

本文不打算介绍mysqldump
具体用法，网上也有很多相关介绍文档可以参考，本文中主要想介绍下mysqldump
跨库导数据时存在的一个“坑”——“假死锁”现象。这里“假死锁”是因为其表现结果和死锁一样，两个进程都互相阻塞，但其本质并非是两个进程互相锁死了对方导致的。

问题复现

mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2

上述命令在跨服务器的数据库之间(h1、h2
不同)导数据时没有任何问题，但是在同一个服务器的不同库之间(h1、h2
相同， db1、db2
不同)导数据时可能会发生死锁现象。

本地准备test、test1库，远程test库

首先尝试通过mysqldump
将本地test
库的数据导入到远程test
库中:

mysqldump -h127.0.0.1 -P3306 -uroot --databases test | mysql --host=cd-cdb-1ygx58w2.sql.tencentcdb.com -P3306 -uxxxx -p'test123!' test

切换至远程，可以看到test
库中已经有相关表及数据尝试通过mysqldump
将本地test
库中的表及数据导入到本地test1
库中

mysqldump -h127.0.0.1 -P3306 -uroot --databases test | mysql --host=127.0.0.1 -P3306 -uroot test1;

将上述命令稍微改下，只导某张表数据activity_info
则命令可以瞬间执行完成，然后切换到test
1数据库下也可以看到activity_info
表及其数据都成功从test
数据库导入过来了

mysqldump -h127.0.0.1 -P3306 -uroot --databases test --tables activity_info | mysql --host=127.0.0.1 -P3306 -uroot test1;

分析

首先我们可以分析下mysqldump | mysql
这个命令的执行方式：

• 这个命令本质上会产生两个进程，一个是mysqldump
  进程，它负责从数据库将表及数据导出，并将输出写入到管道中（写入到管道的数据即mysqldump
  导出到普通文本的数据，内容及格式如上图所示）
• 另一个是mysql
  进程，它的作用则是从管道读取（命令）数据，并发送到MySQL Server
  执行

并且这两个进程是并发执行的，通过ps
命令很容易查看到这两个进程的存在既然如此，那我们应该可以大胆的猜测“卡死”现象是由于发生了死锁。为了验证猜想，可以在mysql
中通过show processlist
命令查看当前连入服务器的进程执行情况如下:果不其然，此时总共有三个进程在执行，其中一个（id
为12301
）的进程为当前执行show processlis
t命令的进程，可以忽略。另外两个进程，id
为12407
的进程为mysqldump
数据导出进程，可以看到目前处理Sleep状态，并未正在执行数据导出操作；而另一个id
为12408
的进程，该进程为mysql
进程，并且该进程也处于一个等待锁的状态（waiting for table metadata lock
）, 并且可以看到该进程当前阻塞的命令为: DROP TABLE IF EXISTS account_map
，注意这条sql
正好是mysqldump
输出的数据的第3行。下面我们就分别分析下这两个进程都阻塞的原因。

mysql为何阻塞

• mysql
  想要尝试删除掉account_map
  这个表，要执行删除操作需要先获取一个table metadata lock
  (查了下，mysql
  对表执行DDL
  操作时都需要先获取表级元数据锁——metadata lock(MDL)
  ，DROP
  正好就是DDL
  操作)，不巧，这个锁正好被其它进程占用了，因此它只能阻塞等待锁；那么锁究竟是被那个进程占有了呢？
• 当然是mysqldump（id=12407）
  这个进程，这一点应该很好理解，mysqldump
  导出数据时本身就会对表加上MDL
  读锁，防止导出过程中表结构被篡改，而MySQL
  进程进行DDL
  操作会对表加MDL
  写锁，这两者是互斥的。

但问题是: mysqldump
进程操作的是test
库，而mysql
进程写入的是test1
库，这两个进程都没有操作同一张表甚至是同一个库，怎么会竞争同一个表的元数据锁呢？

mysqldump
的输出结果第二行，恍然大悟，mysqldump --database
这个命令生成的sql
里面开头处会有USE test
这条, 这样虽然mysql
进程指定了往test1
库写数据，然而在执行了USE test
这条语句后mysql
进程当前执行环境实际上是切换到了test
库。因此在这条命令后mysql
进程和mysqldump
进程都在同时操作test
里的表，而mysqldump
开始导出数据前已经对表加了锁，所以会导致mysql
进程在执行DROP
操作时无法获取锁而被阻塞！

mysqldump进程为何阻塞

mysqldump
进程（id=12407
）处于Sleep
阻塞状态，并且从State
和Info
可以看出它阻塞的原因和前面mysql
进程阻塞的原因是不一样的，mysqldump
并不是在等待获取什么锁而进入阻塞状态。那我们想想，除了获取锁失败以外还有哪些原因可以导致一个进程阻塞的呢？不妨我们看看下面操作系统进程状态转换图:原因是不是很清晰了，当进程IO
阻塞的时候就会导致该进程进入Sleep
阻塞状态。那我们来看看mysqldump此时在做哪些IO
操作，无非是从数据库读数据以及往管道写数据。那么这两步究竟是哪一步发生了IO
阻塞呢？刚才已经知道mysqldump
已经获取了数据库中表的读锁，按理从数据库读数据应该不会发生阻塞的情况才对了，因此更大的可能就是往管道写数据时阻塞住了。

我们都知道shell
的管道缓冲区(pipe buffer)
容量是有限的, 如果缓冲区满了, 那么mysqldump
就不能再继续往里面写数据，因此只能阻塞。再结合前面mysql
进程因为无法获取锁，所以也就无法及时从管道把数据读取走，而mysqldump
一直往管道写数据，直到某一刻管道满了，mysqldump
无法继续写入而阻塞，而此时它还一直占着数据库锁。这样就导致了mysql
和mysqldump
互相“死锁”的问题。但之所以称之为"假死锁"，是因为mysql
进程和mysqldump
进程并不是因为互相占有了对方需要的锁导致的，mysql
进程确实是因为需要的锁被mysqldump
进程占用了，但mysqldump
阻塞是因为IO
的原因而不是因为需要的锁被mysql
占有了。

小结

• mysql
  进程阻塞是因为mysqldump -h127.0.0.1 -P3306 -uroot --databases
  生成的sql
  开头处带有USE
  命令将当前sql
  执行环境悄悄切换了源库上，导致mysql
  进程和mysqldump
  进程同时操作相同的表，而mysql
  进程在执行DDL
  操作时尝试去获取已经被mysqldump
  占有的表元数据锁时失败，从而阻塞在获取锁操作上。
• mysqldump
  阻塞是因为mysql
  因为获取表锁失败无法从管道读取数据，而mysqldump
  还在一直往管道写数据，当管道缓冲区满了以后，mysqldump
  也无法再继续往管道写数据，从而阻塞在IO
  上。
• 最终根本原因还是由于mysql
  进程获取锁失败导致的，如果mysql
  进程可以往下执行，则不会出现管道缓冲区一直处于满的情况，mysqldump
  进程也不会一直处于IO阻塞状态。

解决方案

方案1

同一个数据库实例的不同库间导数据时，要注意和跨数据库实例导数据的区别：

mysqldump -h127.0.0.1 -P3306 -uroot test | mysql --host=127.0.0.1 -P3306 -uroot test1;

方案2

先导表结构:

mysqldump -h127.0.0.1 -P3306 -uroot -d test | mysql --host=127.0.0.1 -P3306 -uroot test1

再导表数据:

mysqldump -h127.0.0.1 -P3306 -uroot -t test | mysql --host=127.0.0.1 -P3306 -uroot test1

如果这篇文章对你有所帮助，或者有所启发的话，帮忙 分享、收藏、点赞、在看，你的支持就是我坚持下去的最大动力！