高流量站点的MySQL备份策略
我目前正在使用 mysqldump 来备份规模快速增长的数据库。虽然我在深夜运行它，但当它碰巧在高流量时刻运行时（有时会在夜间发生），偶尔会出现问题。例如，昨晚，我的一个站点在数据库备份后立即锁定，并且进程列表完全已满（且未清除）。
有没有人有更好的方法来解决这个问题的建议？在备份期间将站点置于临时维护状态不是一种选择，因为目标是最大限度地提高可用性（某些 SQL 转储需要一段时间）。我想到的一个想法是同时运行主副本和从副本，然后关闭+备份从副本，在此过程中保留主副本。但希望有一个更简单的解决方案 - 除非绝对必要，否则我宁愿不运行仅用于备份目的的从属副本。有什么建议吗？
谢谢。
I'm currently using mysqldump to back up databases that are growing rapidly in size. Though I run it late at night, there have been occasional problems when it happens to run during a moment of high traffic (which happens at night sometimes). For example, last night one of my sites locked up just after the time of the database backup with a completely full (and non-clearing) processlist.
Does anyone have a suggestion for a better way to approach this? Putting the site in a temporary maintenance state during backup is not an option as the goal is to maximize availability (some sql dumps take awhile). One idea that comes to mind is to run both master and slave copies and shut down + back up the slave copy, leaving the master copy alone during the process. Hopefully there is a simpler solution though - I'd rather not run a slave copy for backup purposes only unless absolutely necessary. Any suggestions?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
两个想法：
祝你好运！
Two thoughts:
Good luck!
我使用 Percona Xtrabackup，它类似于 InnoDB Hot Backup 具有更多功能，并且免费分发。 Xtrabackup 会在不锁定 innodb 表的情况下拍摄快照，并且会记录当前的主日志文件信息，如果需要，如果您要从从属设备进行备份，还会记录从属信息（如果需要）。
我建议运行一个从站并像这样或使用 mysqldump 进行备份。从服务器为您提供热备份，如果您的主服务器由于硬件问题或各种软件或用户错误问题导致服务器崩溃，您可以在几分钟内快速切换到并启动并运行。使用 xtrabackup 或 mysqldump 的备份为您提供了一个备份，您可以使用该备份来恢复数据，以防您意外删除表或删除一些不应该删除的行，因为复制的服务器不会将您保存在那里。
I use Percona Xtrabackup, which is similar to InnoDB Hot Backup with more functionality and is distributed for free. Xtrabackup takes snapshots without locking innodb tables and will record the current master logfile info and, if requested, the slave info if you are taking a backup from a slave.
I would recommend running a slave and doing a backup like this or with mysqldump. The slave gives you a hot backup that you can quickly switch over to and be up and running within minutes if your master blows up due to a hardware issue or various software or user error issues that take out the server. The backup with xtrabackup or mysqldump gives you a backup that you can use to restore data in case you accidentally drop a table or delete some rows you shouldn't have, since the replicated server wouldn't save you there.