主页 / dba / 问题 / 174957
Accepted
Matija Nalis
Asked: 2017-05-31 13:15:53 +0800 CST

如何安全地停止mysqldump？

  • 772

我（嗯，我的 cron 脚本）试过killall mysqldump了，但结果并不好——mysql 服务器在一段时间后停止接受连接。

这是 Debian Jessie 机器，带有mysql 5.5.55-0+deb8u1.

使用场景是：

  • 有一个长时间运行（几个小时）的SELECT查询，要么真的很慢，要么发送它的客户端有问题（查询状态是Sending data），但所有其他查询都愉快地来来去去（只有负载可能略高） .

  • 在夜间备份正在运行mysqldump --max_allowed_packet=2147483648 --hex-blob --single-transaction --master-data --routines --order-by-primary --databases db1 db2 db3... | pigz -p8 > backup.sql.gz。它从未完成，可能是因为它正在等待SELECT上面的首先完成（在这里猜测 - 这是唯一看起来不寻常的事情，并且相同的设置工作了几个月）。

  • cron作业在早上运行，它killall -q mysqldump应该安全地终止备份，以防它没有在设定的时间完成（通知管理员稍后检查并修复问题），从而允许人们继续正常使用mysql服务器。

  • 但是结果是完整的连接表，因此没有用户能够登录到 mysql 服务器。有FLUSH /*!40101 LOCAL */ TABLES查询卡住Waiting for table flush，数百个SELECT查询卡在同一Waiting for table flush状态。

  • 此外，管理员杀死LOCK TABLESmysql 查询并没有帮助，因为其他 SELECT 查询仍然存在Waiting for table flush（这似乎是预期的行为？）

重新启动 mysql 服务器终于“修复”了这个问题。但是，为了避免这种情况（和紧急管理员干预）重复，我想安全地终止 Debian Jessie mysql-5.5.55（或即将推出的 Debian Stretch mariadb-10.1.23-8）中的 mysqldump 备份。有办法吗？

如果没有，还有哪些其他选项可以完成 mysql 备份并避免早上的服务器负载（在这种情况下，这几乎与完全挂起的服务器一样糟糕）？

（如果可能的话，我想继续使用 Debian Stable 软件包）

mysql mysql-5.5

3 个回答

  1. 由于您使用--master_data的是主状态的一致值。

    mysqldump 的内部将向 mysql 服务器发出以下命令。

    2017-05-31T04:39:05.843130Z    48 Query /*!40100 SET @@SQL_MODE='' */
2017-05-31T04:39:05.843273Z    48 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-05-31T04:39:05.843411Z    48 Query FLUSH /*!40101 LOCAL */ TABLES
2017-05-31T04:39:05.846031Z    48 Query FLUSH TABLES WITH READ LOCK
2017-05-31T04:39:05.846166Z    48 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-05-31T04:39:05.846279Z    48 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-05-31T04:39:05.846413Z    48 Query SHOW MASTER STATUS
2017-05-31T04:39:05.846539Z    48 Query UNLOCK TABLES
..
..... Here it continues to take backup of data and structures .

    发生了什么事情？：

    您的备份刚刚开始，并且在 FLUSH TABLES 命令之前，有一个查询在特定表上运行了更长的时间，并且没有释放表上的锁，并且FLUSH TABLES必须一直在等待该线程完成或继续尝试刷新，直到该表的 revision_version 与所有表相同。

    因此，您也会为其他表阻塞其他线程。因为这是在刷新表进行时整个 DBs*.Tables* 级别的锁。最后，它会累积进程列表中的每个新连接并堆积起来，直到max_connections不允许任何人登录。

    假设您已成功登录终端并试图杀死刷新表，我认为没有办法拉回或回滚已完成的刷新表并释放其自己的线程连接。所以它可能会在KILLED STATE更长的时间内。因此，您可能已经达到了重新启动服务器的最后一个选项。

    如何解决？：

    在发布时，当管理员设法登录到 mysql 提示符时。

    而不是在 FLUSH TABLES 线程上发出 kill 命令，如果将 kill 给予运行 long SELECT 的线程。有可能 SELECT 会被删除并且表会被 FLUSH TABLES 获取和更新 revision_version 并释放新查询的锁。备份将继续进行。因为我认为没有人期待另一端的答案等待查询运行很长时间的结果。

    什么是长期解决方案？：

    • 您必须确保在备份时没有运行这么长时间的查询。

    • 看起来这可能是一个新部署，或者有人触发了错误的查询并且没有费心关闭会话。

    • 如果查询运行的时间超过 Xsecs（取决于您的要求），请尝试终止查询。或者

    • 与团队进行调整以调整查询或将其发送给管理员以给他们结果数据或给他们一个单独的从属。
    • 1
  2. Best Answer

    正如@Mannoj 所建议的，我已经用更智能的版本替换了简单的“killall -q mysqldump”，这应该可以解决问题。它在“等待表刷新”状态下查找所有超过 4 小时的查询并杀死它们（并生成进程列表，以便第二天可以调试问题）。

    我两次从 cron(8) 调用它：

    • 晚上 11 点 - 就在我开始备份之前（所以备份实际上开始了，即使当时一些长时间运行的 SELECT 仍在运行）
    • 早上 7 点，有“硬”论点——就在人们开始上班之前（重新启动 mysql 服务器，以防没有人可以连接到它）
    #!/bin/sh
killall -q mysqldump
睡觉 3
mysql -BNe '显示进程列表' | \
  perl -nE '($id,$u,$h,$db,$cmd,$time,$state,$info) = split /\t/;
  if (($time > 4*3600) and ($cmd =~ /Query/)) {
          $找到++；说“显示进程列表；”
          如果 ($found==1); 说“杀死$id；”
  };' | mysql -v

如果 [“$1”=“硬”]
然后
  睡觉 30
  mysql -BNe '显示进程列表' | grep -q '查询。*显示进程列表' || /etc/init.d/mysql 重启
菲
    • 1

  3. 因为评论的声誉限制，我想有 2 个选项可以测试，我认为安全地停止 mysqldump 并不是解决这个问题的好方法。

    max_allowed_packet

    这些天您的数据库突然变得更大了吗？

    它显示允许2G。

    如果你的数据超过2G就会挂掉进程。

    没有无锁表提示

    您可以尝试--lock-tables=false或禁用表锁定skip-add-locks。my.cnf

    • 0

相关问题