使用mysqldump快速转储和加载MySQL InnoDB数据库的最佳方法是什么？

Question

我想使用mysqldump和MySQL 5.1创建一个包含约40个InnoDB表和大约1.5GB数据的数据库副本。

什么是最好的参数（例如：--single-transaction），可以得到最快的导出和加载数据？

另外，在将数据加载到第二个数据库时，哪种方式更快：

1）直接将结果管道传输到第二个MySQL服务器实例，并使用--compress选项？

还是

2）从文本文件中加载（例如：mysql < my_sql_dump.sql）？

- Josh Schwartzman

Answer 1

快速导出静默数据库：

使用mysqldump的"-T"选项会在指定目录中产生大量的.sql和.txt文件。相比于只有INSERT语句的单个.sql文件，这种方法对于导出大表格来说要快50%（需要的墙上时间少1/3）。

此外，如果可以并行加载多个表格并饱和多个核心，则恢复时会有巨大的好处。在一个8核的机器上，这可能会使恢复转储的墙上时间快8倍，除了"-T"提供的效率改进之外。因为"-T"会导致每个表格都存储在单独的文件中，所以并行加载它们比拆分一个大的.sql文件更容易。

将上述策略推向其逻辑极端，就可以创建一个脚本来广泛地并行导出数据库。这正是Maakit mk-parallel-dump（请参见http://www.maatkit.org/doc/mk-parallel-dump.html）和mk-parallel-restore工具所做的事情；这些perl脚本可以多次调用底层的mysqldump程序。然而，当我尝试使用这些工具时，我遇到了一些问题，在不使用vanilla dumps的情况下，无法完成还原而出现重复键错误，所以请记住，你的结果可能会有所不同。

从LIVE数据库（无服务中断）中导出数据：

--single-transaction开关非常有用，可以在不需要静默或停止复制的情况下对主库或从库进行转储。

遗憾的是，-T 与 --single-transaction 不兼容，因此您只能使用其中一种。

通常情况下，导出数据的速度比恢复数据的速度快得多。目前还没有这样一种工具，可以将单个大型导出文件分割成小块并并行加载。

---

通过网络传输转储通常是一种胜利

要在一个主机上监听传入的转储，请运行:

nc -l 7878 > mysql-dump.sql

然后在您的数据库主机上运行。

mysqldump $OPTS | nc myhost.mydomain.com 7878

这减少了主服务器将转储写入磁盘的争用，略微加快了转储速度（假设网络足够快以保持同一数据中心内两个主机之间的传输），此外，如果您正在构建新的从服务器，则可以节省在转储文件完成后传输转储文件的步骤。

注意事项 - 显然，您需要具有足够的网络带宽才能避免过慢的情况，并且如果TCP会话中断，则必须重新开始，但对于大多数转储来说，这不是一个主要问题。

---

最后，我想澄清一个常见的困惑点。

尽管你在mysqldump示例和教程中经常看到这些标志，但它们是多余的，因为它们默认已经打开：

--opt --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

来自http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html：

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

其中“--quick”是最重要的行为之一（在传输第一行之前跳过将整个结果集缓存到mysqld中），并且可以与“mysql”一起使用（它不会默认打开--quick）以大大加快返回大型结果集的查询（例如转储大表的所有行）。

- Dave Dopson

Answer 2

直接将其发送到另一个实例，以避免磁盘开销。如果您在快速局域网或回环上运行，则不必费心使用 --compress，因为在此情况下，网络开销并不重要。

- John Millikin

Answer 3

对于InnoDB引擎，--order-by-primary --extended-insert通常是最佳组合。如果您追求每一点性能，并且目标服务器有多个CPU核心，则可以将生成的转储文件拆分并在许多线程中进行并行插入，最多达到innodb_thread_concurrency/2。

此外，请将目标服务器上的innodb_buffer_pool_size调整到您能够承受的最大值，并将innodb_log_file_size增加到128或256 MB（请注意，您需要在重新启动mysql守护程序之前删除旧的日志文件，否则它将无法重新启动）。

- ggiroux

Answer 4

我认为如果您尝试使用数据库复制而不是使用mysqldump，它将更快且节省磁盘空间。个人而言，我使用sqlyog企业版进行重度工作，但也有许多其他工具可以提供相同的服务。除非您只想使用mysqldump。

- jake

---

当您设置新的从服务器时，通常需要使用一个好的转储文件来启动它...否则它必须重放自时间开始以来的每个事务。 - Dave Dopson

Answer 5

mydumper 是一个不错的选择，它支持并行导出，甚至可以为每个表使用多个线程，并且支持压缩文件，详情请见：

- Andris

Answer 6

使用来自Maatkit的mk-parallel-dump工具。

至少这可能会更快。我会更信任mysqldump。

您有多频繁地进行此操作？这确实是应用程序性能问题吗？也许您应该设计一种不需要倒入整个数据（复制？）的方式。

另一方面，1.5G是相当小的数据库，因此可能不会成为太大问题。

- MarkR