MySQL 大数据库备份最佳实践
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10498320/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Big Database backup best practice
提问by Roman Gelembjuk
I maintain big MySQL database. I need to backup it every night, but the DB is active all the time. There are queries from users. Now I just disable the website and then do a backup, but this is very bad as the service is disabled and users don't like this.
我维护大型 MySQL 数据库。我需要每晚备份它，但数据库一直处于活动状态。有来自用户的询问。现在我只是禁用该网站然后进行备份，但这非常糟糕，因为该服务被禁用并且用户不喜欢这样。
What is a good way to backup the data if data is changed during the backup?
如果在备份期间数据发生更改，有什么好的备份数据的方法？
What is best practice for this?
什么是最佳实践？
回答by Kevin Bedell
I've implemented this scheme using a read-only replication slave of my database server.
我已经使用我的数据库服务器的只读复制从属实现了这个方案。
MySQL Database Replication is pretty easy to set up and monitor. You can set it up to get all changes made to your production database, then take it off-line nightly to make a backup.
MySQL 数据库复制非常容易设置和监控。您可以将其设置为获取对生产数据库所做的所有更改，然后每晚脱机进行备份。
The Replication Slave server can be brought up as read-only to ensure that no changes can be made to it directly.
复制从属服务器可以设置为只读，以确保不能直接对其进行更改。
There are other ways of doing this that don't require the replication slave, but in my experience that was a pretty solid way of solving this problem.
还有其他不需要复制从站的方法，但根据我的经验，这是解决这个问题的非常可靠的方法。
Here's a link to the docs on MySQL Replication.
这是MySQL Replication文档的链接。
回答by sekrett
If you have a really large (50G+ like me) MySQL MyISAM only databases, you can use locks and rsync. According to MySQL documentation you can safely copy raw files while read lock is active and you cannot do it with InnoDB.
So if the goal is zero downtime and you have extra HD space, create a script:
如果你有一个非常大的（像我这样的 50G+）MySQL MyISAM 数据库，你可以使用锁和rsync. 根据 MySQL 文档，您可以在读取锁定处于活动状态时安全地复制原始文件，而您不能使用 InnoDB 进行复制。因此，如果目标是零停机时间并且您有额外的高清空间，请创建一个脚本：
rsync -aP --delete /var/lib/mysql/* /tmp/mysql/sync
Then do the following:
然后执行以下操作：
- Do
flush tables - Run script
- Do
flush tables with read lock; - Run script again
- Do
unlock tables;
- 做
flush tables - 运行脚本
- 做
flush tables with read lock; - 再次运行脚本
- 做
unlock tables;
On first run rsync will copy a lot without stopping MySQL. The second run will be very short, it will only delay write queries, so it is a real zero downtime solution.
在第一次运行时，rsync 会在不停止 MySQL 的情况下复制很多内容。第二次运行会很短，只会延迟写查询，所以它是一个真正的零停机解决方案。
- Do another
rsyncfrom/tmp/mysql/syncto a remote server, compress, keep incremental versions, anything you like.
- 做另一个
rsync从/tmp/mysql/sync远程服务器，压缩，保持增量版本，任何你喜欢的。
回答by Christopher Padfield
This partly depends upon whether you use innodb or myiasm. For innodb; mySQL have their own (which costs money) solution for this (innodb hot copy) but there is an open source version from Percona you may want to look at:
这部分取决于您使用的是 innodb 还是 myiasm。对于innodb；mySQL 为此（innodb 热拷贝）有自己的（需要花钱）解决方案，但您可能需要查看 Percona 的开源版本：
回答by Mario
What you want to do is called "online backup". Here's a pointer to a matrix of possible options with more information:
您要做的就是所谓的“在线备份”。这是一个指向包含更多信息的可能选项矩阵的指针：
http://www.zmanda.com/blogs/?p=19
http://www.zmanda.com/blogs/?p=19
It essentially boils down to the storage backend that you are using and how much hardware you have available.
它本质上归结为您正在使用的存储后端以及您有多少可用的硬件。
