如何减缓MySQL转储的速度，以免影响服务器当前的负载？

Question

虽然MySQL的导出很容易，但我有一个专用的在线MySQL服务器，想要设置复制。为此，我需要导出数据库并将其导入到复制从服务器中。

问题在于当我进行导出时，MySQL会全力以赴地使用资源，导致连接到它的网站受阻。我想知道是否有一种方法可以将导出查询限制在低优先级状态下，使活动连接更受优先？其想法是外部网站的负载不会受到MySQL全力导出的影响...

- z33k3r

---

您应该选择顶部答案作为正确答案。 - bryan kennedy

你更担心磁盘I/O还是网络流量？ - Rick James

如果使用mysqldump，请参见下面的顶部答案。如果使用MySQL Workbench，请转到高级选项，取消选中“lock-tables”[以不阻止]并选中“compress”[使用更少的网络带宽，因此备份速度更快]。另一方面，如果您有一个“复制从属”，而不是转储（作为第一操作），然后导入（作为第二操作）到复制从属，请调查其他直接复制到从属的替代方法。 - ToolmakerSteve

Answer 1

我拥有非常大的数据库，其中有数万个表格，其中一些表格中的数据量高达数十亿条，每个表格都有 5GB 左右的数据。（我运行的是一个受欢迎的服务）... 当备份这些数据库时，我总是很头疼。使用默认的 mysqldump 工具会迅速让服务器负载失控并锁死一切... 影响我的用户。尝试停止进程可能会导致表崩溃，并在恢复这些表期间出现大量停机时间。

我现在使用...

mysqldump -u USER -p --single-transaction --quick --lock-tables=false DATABASE | gzip > OUTPUT.gz

从dev.mysql.com的mysqldump参考文档中可以看到...

为了备份大表，你应该将--single-transaction和--quick选项结合使用。

该文档并未说明这取决于数据库是否为InnoDB，我的数据库是myISAM，这对我来说非常有效。服务器负载几乎没有受到影响，在整个过程中我的服务都像劳力士一样运行良好。如果您有大型数据库，并且备份正在影响终端用户...那么这就是解决方案。 ;)

- CA3LE

---

这对我很有效，能在 MySQL 数据库备份时将页面加载时间减半。 - Richard Frank

由于MyISAM不支持事务，我不确定您的备份是否一致，特别是如果您有一个大型数据库。我建议只对InnoDB数据库进行备份。 - Renan

已在InnoDB上进行了测试。您可以使用SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES或SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<您的数据库名称>'来检查您的引擎。 - vladkras

dev.mysql.com表示：“使用此选项时，应注意只有InnoDB表以一致的状态转储。” - arlomedia

最终，我使用了MyISAM表来实现这个解决方案，因为无论如何，当我需要使用数据库备份时，我只需要检索部分数据，并且最新数据之间的一致性不是问题。此外，我认为在备份窗口期间创建的任何不一致性都会相形见绌，与需要完全恢复数据库的任何灾难性事件相比。但是，我计划在时间允许的情况下探索切换到InnoDB（不幸的是，我的托管情况并不容易）。 - arlomedia

Answer 2

如果使用InnoDB表，请使用mysqldump的--single-transaction和--quick选项

- Drew Clayton

---

哇，InnoDb表格里有一百万条记录时真是大不同啊。使用这些选项后，整个服务器不再被阻塞十分钟，一切都运转得很顺畅。早些时候应该就找到这篇帖子了~~~ - BurninLeo

如果您使用的是InnoDB，那么这就是正确的答案。因为默认情况下，InnoDB会在任何查询上开始/提交单个事务。在转储期间会有许多查询。 - gaRex

对我们来说也产生了很大的影响。 - Kevin Parker

Answer 3

除了已经提到的使用--single-transaction和--quick的解决方案之外，我建议不要直接将结果导入gzip中，而是先将其转储为.sql文件，然后再进行gzip压缩（使用&&代替|）。

.dump本身会更快，因此停机时间更短。 （根据我的测试，速度是快两倍）

所以我会选择"&& gzip"而不是"| gzip"。 重要提示：首先使用df -h检查免费磁盘空间！因为你需要的磁盘空间比管道 | gzip多。

mysqldump --single-transaction --quick -u user -p my_db_name > dump_name.sql && gzip dump_name.sql

-> 这将导致生成名为dump_name.sql.gz的1个文件。

- Julesezaar

Answer 4

1) 首先，您需要查看您的MySQL版本。使用至少5.7版本以支持多线程。旧版本仅使用1个线程，如果您有大型数据库同时使用DB和执行mysqldump不是一个好主意。

2) 最好不要在同一DB磁盘上构建备份，因为读/写性能可能会受到影响，或者您可能需要RAID 10。

3) MySQL Enterprise的mysqlbackup更好，但需要付费，我不知道这是否适用于您。

4) 有时许多表不需要事务处理，因此只在需要的表上使用事务处理。

5) 通常需要事务处理，使用InnoDB格式以获得更好的性能并避免锁定表。

6) 在某些情况下，最好只编写一个程序，这样您可以仅针对读取表创建事务，而无需锁定任何人，并使用一些睡眠测试，以避免冻结您的服务。

- Roberto Novakosky

Answer 5

您可以在mysqldump命令前加上以下内容：

ionice -c3 nice -n19 mysqldump ...

这将以低IO和CPU优先级运行，因此应该限制其影响。

请注意，这只会延迟MySQL执行之间的时间。脚本本身仍然像以前一样密集，只是脚本之间有更长的间隔。

- Darryl at NetHosted

---

这种方法并不影响在运行中的服务器上进行mysqldump的结果。我尝试了各种更改优先级等设置。我还要补充的是，我正在处理一个7GB的数据库... - z33k3r

目前我们不得不在深夜（午夜至凌晨5点）进行数据库锁定和转储。仍然希望有更优雅的解决方案... - z33k3r

这将只会降低mysqldump进程的优先级。然而，mysqldump进程仅仅是在查询数据库，而这些查询本身在mysql服务器上以通常的优先级和原因运行，对服务器造成（几乎）相同的影响。mysqldump不是瓶颈所在。 - adhominem

Answer 6

使用nice和gzip命令以最低优先级执行命令。

nice -n 10 ionice -c2 -n 7 mysqldump db-name | gzip > db-name.sql.gz 

- khushi muhammad

---

这将使mysqldump客户端变慢，但它会影响mysql服务器的读取吗？ - dave1010

如果输入/输出比网络带宽更成问题，那么将所有这些内容都运行在另一台服务器上。 - Rick James