不中断生产环境INSERT的情况下使用mysqldump

Question

我将要把我们的生产数据库迁移到另一个服务器。它大约有38GB大小，使用的是MYISAM表。由于我无法物理访问新服务器文件系统，我们只能使用mysqldump。

我查看了这个网站，想知道mysqldump在线备份是否会导致我们的生产网站崩溃。从这篇文章中：Run MySQLDump without Locking Tables，显然它说mysqldump会锁定数据库并阻止插入。但经过几次测试，我好奇地发现情况正好相反。

如果我使用

mysqldump -u root -ppassword --flush-logs testDB > /tmp/backup.sql

mysqldump默认会使用'--lock-tables'，这是一种READ LOCAL锁（参考mysql 5.1文档），在此期间并发插入仍然可用。我已经做了一个for循环来每秒插入到其中一个表中，而mysqldump需要一分钟才能完成。在那段时间内，每秒钟都会插入记录。这意味着，mysqldump不会中断生产服务器，INSERT仍然可以继续进行。

是否有人有不同的经验？在继续在我的生产服务器上操作之前，我想确保这一点，因此很高兴知道是否有任何错误导致我的测试不正确。

[我的mysql-server版本是5.1.52，mysqldump是10.13]

- sylye

---

你应该首先获得新服务器的访问权限，可能需要使用mysql复制来解决迁移问题。请记住：你可以将数据导出并安装到新服务器上，但是在导出数据之后，会有更多的新写入操作，你要如何将这些新写入操作同步到新服务器上呢？ - ajreal

对于随后的新数据写入，我们将使用mysqlbinlog来解决，这应该会很好地得到处理。现在我的主要关注点将是使用mysqldump进行第一次备份。我只想确保mysqldump是否会阻止INSERT操作。根据我的测试，它没有表现出会这样做。只是想确定其他人的经验如何。 - sylye

Answer 1

现在，你可能拥有一个由不连续表组成的数据库，或者是一个数据仓库 - 其中一切都没有规范化，表之间也没有任何链接。在这种情况下，任何转储都可以使用。

我假设，一个包含38G数据的生产数据库以某种形式包含图形（BLOB），然后 - 无处不在的 - 你从其他表中获得链接。对吗？

因此，就我所看到的而言，您有可能丢失表之间的重要链接（通常是主键/外键对），因此，在某个表被更新/插入时捕获它，而其依赖表（将该表用作其主要来源）尚未更新，则会丢失您的数据库的完整性。

往往非常麻烦重新建立完整性，往往是因为使用/生成/维护数据库系统的系统未被制作为事务导向系统，因此，除了通过主键/外键关系之外，无法跟踪数据库中的关系。

因此，您可能确实可以在没有锁定和上述许多其他提议的情况下复制您的表 - 但是您有烧伤手指的风险，并且取决于系统操作的敏感程度 - 您可能会严重烧伤自己或只是受轻微伤。

例如：如果您的数据库是一个关键任务数据库系统，其中包含ICU生命支持设备的推荐心跳率，那么在进行迁移之前，我会认真考虑两次。

但是，如果数据库包含来自Facebook或类似网站的图片，则您可能能够接受从0到129,388个丢失链接的任何后果 :-）。

现在 - 分析就这么多。解决方案：

你需要创建一个软件，它可以完整地将表集一组一组地进行转储，元组一行一行地进行。您需要识别可以从当前在线24/7/365基础复制到新基础的数据群，然后执行该操作，然后标记已复制。

如果现在对您已经复制的记录进行更改，则需要随后复制这些记录。这可能是一个棘手的问题。

如果您正在运行更高级版本的MYSQL - 您实际上可以创建另一个站点和/或副本，或分布式数据库 - 然后通过这种方式摆脱它。

如果您有10分钟的窗口时间，如果需要，您也可以只复制位于驱动器上的物理文件。我谈论的是.stm .std等文件-然后您可以关闭服务器几分钟，然后复制。

现在到一个基本问题：

您需要定期维护机器。您的系统是否有进行此类操作的空间？如果没有 - 那么当硬盘崩溃时，您将怎么做。请注意“何时”而不是“如果”。

- David Svarrer

---

一个38GB的数据库大小如何暗示图形或BLOB内容？ - MattBianco

Answer 2

1) 使用--opt与指定--add-drop-table、--add-locks、--create-options、--disable-keys、--extended-insert、--lock-tables、--quick和--set-charset的效果相同。因为--opt默认开启，所以代表--opt的所有选项也默认开启。

2) mysqldump可以逐行检索和转储表内容，或者可以从表中检索整个内容并在转储之前将其缓冲到内存中。如果要转储大型表，则在内存中缓冲可能会成为问题。要逐行转储表，请使用--quick选项（或启用--quick的--opt选项）。--opt选项（因此--quick）默认启用，因此要启用内存缓冲，请使用--skip-quick。

3) --single-transaction选项在从服务器转储数据（事务表InnoDB）之前发出BEGIN SQL语句。

如果您的模式同时包含InnoDB和MyISAM，请参考以下示例：

    mysqldump -uuid -ppwd --skip-opt --single-transaction --max_allowed_packet=512M db > db.sql

- naveen_sfx

Answer 3

虽然我以前没有试过，但你可以尝试在转储时使用--skip-add-locks。

虽然这可能需要更长的时间，但你可以将其分成几个补丁进行转储，每个补丁都需要很短的时间来完成。添加--skip--add-drop-table可以使你将这些多个小转储文件上传到同一个表中而无需重新创建它。同时使用--extended-insert可以使 sql 文件更小。

可以尝试类似以下命令：mysqldump -u -p${password} --skip-add-drop-table --extended-insert --where='id between 0 and 20000' test_db test_table > test.sql。你需要先转储表结构并将其上传，或者在第一次转储时移除 --skip-add-drop-table。

- iralls

---

你好，感谢你的建议。但目前我的主要关注点并不是尝试减少 mysqldump 的时间，因为数据太多了。我的意图是找出 mysqldump 是否会在运行时阻止 INSERT。寻找有经验的人来帮助解决这个问题。 - sylye

Answer 4

默认情况下，mysqldump不会添加--lock-tables参数。请尝试使用--lock-tables参数。

顺便说一句 - 您还应该使用add-locks参数，这将使您的导入速度更快！

- Ronen Yacobi

---

您好，我已经进行了再次测试，确认mysqldump默认会发出--lock-tables 参数。有两个证据可以证明。首先，运行命令mysqldump --help|grep lock将显示 'lock-tables' 为TRUE。其次，在 mysqldump 进行期间使用 mysql>show open tables; 命令，其中的 'In_use' 列将指示为1，而其他的 mysql INSERT 没有使其变为2，因此表明它是一个LOCK READ LOCAL。所以我的问题是，是否有其他人遇到了mysqldump锁定表并阻止INSERT的情况？因为我的没有。 - sylye