使用 git 处理 MySQL 备份是否可行？
今天，我对备份数据库有了一个非常巧妙的想法：将转储文件放入 git 存储库中，然后提交每个转储，以便我拥有最新的副本，但可以轻松回滚到任何以前的备份。我还可以轻松地定期提取存储库的副本，以在我自己的计算机上保留一个副本作为备份的备份。这听起来确实很聪明。
然而，我知道聪明的解决方案有时会存在根本性缺陷。在 git 中存储 mysqldump diff 可能会遇到什么类型的问题？值得吗？为了在服务器上拥有多个数据库备份并在其他地方保留冗余副本，大多数人会做什么？
Today I had this really neat idea for backing up my database: put the dump file in a git repository, then commit on each dump so that I have both the most recent copy, but can easily roll back to any previous backup. I can also easily pull a copy of the repository on a regular basis to keep a copy on my own computer as a backup of the backups. It definitely sounds clever.
However, I'm aware that clever solutions sometimes have fundamental flaws. What sort of issues might I hit storing mysqldump diffs in git? Is it worth it? What do most people do in order to have multiple database backups on the server and keep redundant copies elsewhere?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
通常，您不会永远保留每个备份（或快照）。 git 存储库确实会保存您所做的每一次签入。如果您决定修剪旧的修订版（例如，一个月前的修订版减少到每周一次，一年前的修订版减少到每月一次，等等），您将必须使用 git filter-branch 来执行此操作，它将重写整个历史。然后 git gc 删除不需要的修订。
鉴于 git 的优势是分布式版本控制和复杂的补丁/分支工作流程（这两者都不适用于快照或备份），我会考虑使用具有更具延展性历史记录的不同 VCS。
Normally you don't keep every backup (or snapshot) forever. A git repository does keep every checkin you ever make. If you ever decide to prune old revisions (say month-old revisions down to once a week, year old to once a month, etc) you will have to do it with
git filter-branchwhich will rewrite the entire history. Thengit gcto remove the unwanted revisions.Given that git's strengths are distributed version control and complex patch/branch workflows (neither of which apply to snapshots or backups) I'd consider using a different VCS with a more malleable history.
这种方法对我来说听起来不错。我使用 Git 来备份我自己的重要数据。
请注意，您没有存储差异 - Git 有效地存储每次提交的目录状态快照。你可以生成两次提交的 diff，但实际的存储机制与 diff 无关。
This approach sounds fine to me. I use Git for backing up my own important data.
Note that you are not storing diffs -- Git effectively stores snapshots of the directory state with each commit. You can generate the diff of two commits, but the actual storage mechanism has nothing to do with diff.
理论上这是可行的，但是当数据库转储变大时，您就会开始遇到问题。
Git 没有任何硬文件大小限制，但它会将最新转储的内容与之前存储在存储库中的内容进行比较，这将需要至少与这两个文件加在一起的大小一样多的内存 - 所以我想当文件超过 100MB（甚至 10MB）时，它会开始变得非常慢、非常快。
Git 不是为处理这种类型的文件（即大数据文件而不是源代码）而设计的，所以我认为这从根本上来说是一个坏主意。但是，您可以使用 Dropbox 之类的工具来存储转储 - 它仍然会为您保存版本历史记录，但更适合无法有效区分的文件。
In theory this will work, but you will start to have problems when the database dumps get large.
Git doesn't have any hard file size limits, but it will diff the contents of your latest dump with the one previously stored in the repository, which will require at least as much memory as the sizes of both of those files added together - so I would imagine it will start to get very slow, very quickly with files over 100MB (or even 10MB).
Git wasn't made for dealing with files of this type (i.e. big data files instead of source code), so I think this is fundamentally a bad idea. You could, however, use something like Dropbox to store the dumps - which will still save the version history for you, but is more tailored towards files which can't effectively be diffed.
如果您使用 MySQL（也可能是其他）并启用了二进制日志记录，您可能会考虑为 bin 日志目录设置一个 git 存储库，并制定定期提交更新到 binlog 的策略。
在 MySQL 中，binlog 存储对数据库上任何表更改数据的查询。如果您将提交与数据库的常规转储同步，则应该有一种版本化的方法来恢复数据。
老实说，我认为仅使用 MySQL 的本机工具可能是一个更好的解决方案，但我在这里概述的内容可以让您对 MySQL 数据进行版本控制，这正是我认为您首先想要的。
If you're using MySQL (and possibly others) and have binary logging enabled, you might consider setting up a git repo for the directory of your bin log and developing a strategy to regularly commit updates to the binlog.
In MySQL, the binlog stores the queries that change data to any tables on the database. If you sync up your commits with regular dumps of the database, you should have a versioned way to restore data.
Honestly, I think just using MySQL's native tools would probably be a better solution, but what I've outlined here gets you versioning your MySQL data which is what I think you were after in the first place.