非阻塞MySQL备份

Question

我正在运行一个带有两个客户数据库的MySQL服务器，其中一些数据库相当大。我们每天做一次完整的mysql转储。大约需要半个小时才能完成。

在此期间，对MySQL服务器的每一个请求都会得到异常缓慢的响应(有时大约是1位S，而不是大约10 ms)。即使没有超时错误，我也希望看到查询在相当长的时间内完成，这样客户网站就不会因为挂起MySQL查询而慢下来。

我做了很多研究，学习了数据复制、LVM快照、各种mysqldump标志选项和其他工作，但是没有什么能真正改善这种情况。

有在MySQL?

中设置查询优先级的选项吗？

如果有一种方法来定义查询优先级，我可以给备份过程一个较低的优先级，以确保所有其他查询都能更快地执行。当mysqldump进程受到另一个请求的挑战时，它可以暂时暂停(SIGTSTP)，在查询完成时可以继续(SIGCONT)。但是，我无法在MySQL中找到这样的特性。

给mysqldump进程一个更高的nice值不起作用，因为它只会增加时间，备份进程和表锁都会出现。(我把桌子都锁上了)此外，限制IO写率只会导致更长的痛苦。

除了LVM和复制之外，还有其他我错过的解决方案吗？

Answer 1

事实上，瓶颈是IO。由于我使用tar -czf压缩文件，而且CPU性能很好，驱动器根本无法处理所有数据的速度太快(尽管我们使用镜像RAID卷)。

mysqldump本身一点问题都没有，但是tar正在大幅度降低整个系统的运行速度。

我现在使用pv来塑造管道流：

tar -czf - ./ | pv -q -L 10m > output.tgz

• -q禁用pv的输出。
• -L 10m将写入操作限制为每秒10 MB。只需通过通过gzip查看top进程来测试您应该在服务器上使用的值。我希望它有大约50%的CPU使用率，所以IO的影响也降低到50%。
• CPU影响被隐式地降低了，因为pv减缓了一切(除了转储本身，它是在压缩之前完成的)。

非常感谢@Michael - sqlbot和其他人把我推向正确的方向。

Answer 2

您很可能需要使用-单笔交易选项，以便每个表的转储位于一个巨大的读取事务中。这将允许对数据进行一致的快照。

尽管可以在活动系统上执行mysqldumps，但最好不要在活动系统上执行mysqldumps，因为InnoDB缓冲池基本上会清除其最常用的数据。

即使您使用XtraBackup，如果您在凌晨12:00启动备份，并且备份需要30分钟，那么当涉及到备份的时点时，请记住XtraBackup和mysqldump之间的区别：

• 使用mysqldump -单笔交易创建备份，其时间点是备份的开始(上午12:00，甚至30分钟后)。
• 使用XtraBackup创建数据和日志的快照，其时间点为备份的结束(上午12:30)。

建议

这听起来像是真的需要设置MySQL复制。这将使动态数据库成为主数据库。你的奴隶可以成为后援的来源。

一旦建立了MySQL复制，您就可以在从服务器上执行所有类型的备份(mysqldump )，而不会干扰您的主服务器。您不再需要担心设置查询优先级，或者担心与mysqldump竞争的查询速度慢，或者缓冲区池被mysqldump抹掉，从而导致更频繁地从磁盘读取数据。

在那个奴隶身上，你可以在午夜做以下的事情

STOP SLAVE;
FLUSH TABLES;
<< Run your backup with whatever method you choose >>
START SLAVE;

或者你可以对奴隶更有侵略性

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown = 0;
service mysql stop
Copy /var/lib/mysql to a backup folder
service mysql start
START SLAVE;    

如需更多建议，请回答以下帖子

• Apr 17, 2011：如何优化大型数据库的mysqldump？
• Jun 20, 2012：在具有活动读写功能的活动系统上执行mysqldump的最安全方法？

Answer 3

您肯定应该看看percona的XtraBackup，它只是复制数据目录，而不是查询DB。

如果您想继续使用mysqldump，您应该查看快速选项：

mysqldump可以逐行检索和转储表内容，也可以从表中检索整个内容，并在转储之前将其缓冲在内存中。如果您正在转储大型表，内存中的缓冲可能是一个问题。若要逐行转储表，请使用-快速选项(或--opt，它启用--快速)。默认情况下--选择选项(因此--快速)是启用的，所以要启用内存缓冲，使用--跳过-快速。