MySQL数据库备份：性能问题
各位，
我正在尝试为一个相当大的生产数据库（半个千兆）设置定期备份，该数据库同时包含 InnoDB 和 MyISAM 表。到目前为止，我一直在使用 mysqldump，但我发现它花费的时间越来越长，并且在 mysqldump 运行时服务器完全没有响应。
我想征求你的建议：我如何
使 mysqldump 备份非阻塞 - 为进程分配低优先级或类似的东西，或者
找到另一种更好/更快/非阻塞的备份机制。
我知道 MySQL Enterprise Backup 产品（http://www.mysql.com/products/enterprise/backup.html）的存在 - 它很昂贵，并且这不是该项目的选择。
我读过有关将第二台服务器设置为“复制从站”的信息，但这对我来说也不是一个选择（这需要硬件，成本很高）。
谢谢你！
更新：有关我的环境的更多信息：Ubuntu、最新的 LAMPP、Amazon EC2。
Folks,
I'm trying to set up a regular backup of a rather large production database (half a gig) that has both InnoDB and MyISAM tables. I've been using mysqldump so far, but I find that it's taking increasingly longer periods of time, and the server is completely unresponsive while mysqldump is running.
I wanted to ask for your advice: how do I either
Make mysqldump backup non-blocking - assign low priority to the process or something like that, OR
Find another backup mechanism that will be better/faster/non-blocking.
I know of the existence of MySQL Enterprise Backup product (http://www.mysql.com/products/enterprise/backup.html) - it's expensive and this is not an option for this project.
I've read about setting up a second server as a "replication slave", but that's not an option for me either (this requires hardware, which costs $$).
Thank you!
UPDATE: more info on my environment: Ubuntu, latest LAMPP, Amazon EC2.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
编辑：（之前的答案是建议从属数据库进行备份，然后我注意到亚历克斯在他的问题中排除了这一点。）
假设硬件可以跟上，您的复制从属设备没有理由不能在同一硬件上运行。获取源代码 tarball，
./configure --prefix=/dbslave；制作; make install;然后你就会有第二个 mysql 服务器完全位于 /dbslave 下。EDIT2：复制还有很多其他好处。例如，在运行复制的情况下，您可能能够恢复二进制日志并在上次备份之上重播它，以在某些类型的灾难后恢复额外的数据。
EDIT3：您提到您正在 EC2 上运行。另一个降低成本的有点人为的想法是尝试使用 EBS 卷设置另一个实例。然后使用 AWS api 启动此实例足够长的时间，使其能够跟上二进制日志的写入速度，转储/压缩/发送快照，然后将其关闭。不是免费的，而且设置起来需要大量人力，但比 24x7 运行实例要便宜得多。
Edit: (previous answer was suggestion a slave db to back up from, then I noticed Alex ruled that out in his question.)
There's no reason your replication slave can't run on the same hardware, assuming the hardware can keep up. Grab a source tarball,
./configure --prefix=/dbslave; make; make install;and you'll have a second mysql server living completely under /dbslave.EDIT2: Replication has a bunch of other benefits, as well. For instance, with replication running, you'll may be able to recover the binlog and replay it on top your last backup to recover the extra data after certain kinds of catastrophes.
EDIT3: You mention you're running on EC2. Another, somewhat contrived idea to keep costs down is to try setting up another instance with an EBS volume. Then use the AWS api to spin this instance up long enough for it to catch up with writes from the binary log, dump/compress/send the snapshot, and then spin it down. Not free, and labor-intensive to set up, but considerably cheaper than running the instance 24x7.
尝试使用 maatkit (http://www.maatkit.org/) 的 mk-parallel-dump 实用程序
，
Try mk-parallel-dump utility from maatkit (http://www.maatkit.org/)
regards,
您可能会考虑通过一种称为“日志传送”的方法在这里使用二进制日志。在每次备份之前，发出命令来刷新二进制日志，然后您可以通过常规文件系统操作复制除当前二进制日志之外的所有内容。
此方法的优点是您根本不会锁定数据库，因为当它按顺序打开下一个二进制日志时，它会释放先前日志上的所有文件锁，因此处理不会受到影响。 Tar'em、zip'em 就位，按照您的意愿进行操作，然后将其作为一个文件复制到备份系统中。
使用二进制日志的另一个优点是，如果日志可用，您可以恢复最多 X 个时间点。也就是说，您拥有去年的完整备份，以及从那时到现在的所有日志。但是您想查看数据库在 2011 年 1 月 1 日的情况。您可以发出“直到 2011 年 1 月 1 日”的恢复，当它停止时，就数据库而言，您的数据库是在 2011 年 1 月 1 日。
我曾经不得不使用这个来扭转黑客造成的损害。
这绝对值得一试。
请注意...二进制日志通常用于复制。没有什么说你必须这样做。
Something you might consider is using binary logs here though a method called 'log shipping'. Just before every backup, issue out a command to flush the binary logs and then you can copy all except the current binary log out via your regular file system operations.
The advantage with this method is your not locking up the database at all, since when it opens up the next binary log in sequence, it releases all the file locks on the prior logs so processing shouldn't be affected then. Tar'em, zip'em in place, do as you please, then copy it out as one file to your backup system.
An another advantage with using binary logs is you can restore up to X point in time if the logs are available. I.e. You have last year's full backup, and every log from then to now. But you want to see what the database was on Jan 1st, 2011. You can issue a restore 'until 2011-01-01' and when it stops, your at Jan 1st, 2011 as far as the database is concerned.
I've had to use this once to reverse the damage a hacker caused.
It is definately worth checking out.
Please note... binary logs are USUALLY used for replication. Nothing says you HAVE to.
添加到 Rich Adams 和 timdev 已经建议的内容，编写一个在低使用时间段触发的 cron 作业，按照建议执行从属任务，以避免 CPU 利用率过高。
还请检查mysql-parallel-dump。
Adding to what Rich Adams and timdev have already suggested, write a cron job which gets triggered on low usage period to perform the slaving task as suggested to avoid high CPU utilization.
Check mysql-parallel-dump also.
如果无法复制到从属服务器，您可以利用文件系统，具体取决于您使用的操作系统，
我在一个相当大的 MySQL 数据库（30GB+）上使用 ZFS 快照作为备份方法，它完成得非常快（不会超过几分钟）并且不会阻塞。然后，您可以将快照安装到其他位置并将其备份到磁带等。
If replication to a slave isn't an option, you could leverage the filesystem, depending on the OS you're using,
I've used ZFS snapshots on a quite large MySQL database (30GB+) as a backup method and it completes very quickly (never more than a few minutes) and doesn't block. You can then mount the snapshot somewhere else and back it up to tape, etc.