mysql最好的备份方法？以及如何将备份转储到目标目录
进行 MySQL 压缩备份的最佳方法是什么？另外，如何将其转储到特定目录，例如 C:\targetdir
What is the best method to do a MySQl backup with compression? Also, how do you dump that to specific directory such a C:\targetdir
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
mysqldump命令将输出足以重新创建整个数据库的 CREATE TABLE 和 INSERT 命令。您可以使用此命令备份单个表或数据库。您可以轻松压缩它。如果您希望对其进行压缩，则需要某种用于命令行的流工具。在 UNIX 上，它将是 mysqldump ... | gzip。在 Windows 上，您必须找到一个与管道一起使用的工具。
我认为这就是您正在寻找的内容。我将列出其他选项只是因为。
FLUSH TABLES WITH READ LOCK会将所有数据刷新到磁盘并锁定它们，以防止您在复制数据文件夹时进行更改。请记住，在进行恢复时，如果您想保留 MySQL bin 日志的全部功能，您将不希望通过直接接触文件来恢复数据库的部分内容。最好的选择是拥有一个备用数据目录，其中包含已恢复的文件并从那里转储，然后使用常规 mysql 连接通道将数据提供给您的生产数据库。对文件系统的任何直接更改都不会被 binlog 记录。
如果你使用文件恢复整个数据库，那就没问题了。只是如果你喜欢的话就不会了。
mysqldump没有这个问题复制将允许您备份到在同一台或不同计算机上运行的另一个 MySQL 实例。
二进制日志。给定数据库的静态副本，您可以使用它们及时向前移动。 binlogs 是曾经更改过数据的所有命令的日志。如果您有第一天的二进制日志，那么您可能已经有了您正在寻找的内容。您可以从第一天到您希望的任何日期运行 binlog 中的所有命令，然后您就拥有该日期的数据库副本。
mysqldumpcommand will output CREATE TABLE and INSERT commands that are sufficient to recreate your whole database. You can back up individual tables or databases with this command.You can easily compress this. If you want it to be compressed as it goes, you will need some sort of streaming tool for the command line. On UNIX it would be
mysqldump ... | gzip. On Windows, you will have to find a tool that works with pipes.This I think is what you are looking for. I will list other options just because.
FLUSH TABLES WITH READ LOCKwill flush all data to the disk and lock them from changing which you can do while you are making a copy of the data folder.Keep in mind, when doing restores, if you want to preserve the full capability of MySQL bin logs, you will not want to restore parts of a database by touching the files directly. Best option is to have an alternate data dir with restored files and dump from there, then feed to your production database using regular mysql connection channels. Any direct changes to the filesystem will not be recorded by binlogs.
If you restore the whole database using files, you will be OK. Just not if you to peices.
mysqldumpdoes not have this problemReplication will allow you to back up to another instance of MySQL running on the same or different machine.
binlogs. Given a static copy of a database, you can use these to move it forward in time. binlogs are a log of all the commands that ever changed the data. If you have binlogs back to day one, then you may already have what you are looking for. You can run all the commands from the binlogs from day one to any date you wish and then you have a copy of the database from that date.
我建议查看 Percona XtraBackup。它是 MySQL 付费企业备份工具的 GPL 许可替代品，即使在写入数据库时也可以从数据库创建一致的非阻塞备份。请参阅本文了解有关原因的更多信息我想通过 mysqldump 使用它。
I recommend checking out Percona XtraBackup. It's a GPL licensed alternative to MySQL's paid Enterprise Backup tool and can create consistent non-blocking backups from databases even when they are written to. See this article for more information on why you'd want to use this over mysqldump.
您可以使用像 AutoMySQLBackup 这样的脚本，它每天自动进行备份，每天保持一次，每周和每月备份，使您的备份目录保持干净整洁，同时仍然为您提供长期的备份历史记录。
当然，备份也会被压缩。
You could use a script like AutoMySQLBackup, which automatically does a backup every day, keeping daily, weekly and monthly backups, keeping your backup directory pretty clean and uncluttered, while still providing you a long history of backups.
The backups are also compressed, naturally.