如何备份MySQL数据库？

根据您的要求，有几个选项我一直在使用自己：

Depending on your requirements, there's several options that I have been using myself:

• 如果不需要热备份，请删除db服务器并备份文件系统水平e。使用tar，rsync或类似的。


• 如果你确实需要数据库服务器继续运行，你可以开始使用 mysqlhotcopy 工具（perl脚本），它锁定正在备份的表，并允许您选择单个表和数据库。 / li>
  
• 如果您希望备份可移植，则可以使用 mysqldump ，它创建用于重新创建数据的SQL脚本，但是比mysqlhotcopy


• 更慢如果你有一个db的副本时间点，你也可以保持binlogs（从那个时间点开始）安全。这可以很容易做，不会干扰服务器的操作，但可能不是最快的恢复，你必须确保你不要错过部分日志。

• if you don't need hot backups, take down the db server and back up on the file system level, i. e. using tar, rsync or similar.
• if you do need the database server to keep running, you can start out with the mysqlhotcopy tool (a perl script), which locks the tables that are being backed up and allows you to select single tables and databases.
• if you want the backup to be portable, you might want to use mysqldump, which creates SQL scripts to recreate the data, but which is slower than mysqlhotcopy
• if you have a copy of the db at a certain point in time, you could also just keep the binlogs (starting at that point in time) somewhere safe. This can be very easy to do and doesn't interfere with the server's operation, but might not be the fastest to restore, and you have to make sure you don't miss part of the logs.

方法我没有尝试，但对我有意义：

Methods I haven't tried, but that make sense to me:

• 如果您有像ZFS这样的文件系统或者正在LVM上运行，那么通过执行文件系统快照来执行数据库快照可能是个好主意，因为它们非常快速。只要记住在整个操作过程中确保数据库的一致状态，e。 G。通过执行 FLUSH TABLES WITH READ LOCK （当然，不要忘记 UNLOCK TABLES ）

• if you have a filesystem like ZFS or are running on LVM, it might be a good idea to do a snapshot of the database by doing a filesystem snapshot, because they are very, very quick. Just remember to ensure a consistent state of your db during the whole operation, e. g. by doing FLUSH TABLES WITH READ LOCK (and of course, don't forget UNLOCK TABLES afterwards)

此外：

• 安装程序将生产服务器复制到不同的机器或同一台机器上的第二个实例，并对复制副本执行上述任一操作，单独留下生产机器。


• 我认为，MySQL集群和企业版许可版本都有自己的权限，而不是连续运行，而是定期启动从属计算机，让它读取binlog并再次关闭。更多工具，但我从未尝试过。