在 MySQL 数据库上使用版本控制 (Git)

Using version control (Git) on a MySQL database

提问by Zach Russell

I am a WordPress Designer/Developer, who is getting more and more heavily involved with using version control, notably Git, though I do use SVN for some projects. I am currently using Beanstalk for my remote repo.

我是一名 WordPress 设计师/开发人员，他越来越多地参与使用版本控制，尤其是 Git，尽管我确实在某些项目中使用了 SVN。我目前正在将 Beanstalk 用于我的远程存储库。

Adding all of the WordPress files to my repo is no problem, if I wanted to I know I could .gitignore the wp-config file, but since I'm the only developer, currently, and these projects are closed source, it really makes little sense.

将所有 WordPress 文件添加到我的 repo 中没有问题，如果我想知道我可以 .gitignore wp-config 文件，但由于我是目前唯一的开发人员，而且这些项目是闭源的，这确实使小意思。

WordPress relies heavily on the database, as any CMS does, to keep textual content, and many settings depending on the specific plugin/theme configuration I'm using. I'm wondering what the best way of using version control on the database would be, if it's even possible. I guess I could do a SQL dump, though my MySQL server is running on Windows (read as: I don't know how to do it), and then add the SQL dump to my repository. But when I push something live, that poses huge security threats.

与任何 CMS 一样，WordPress 严重依赖数据库来保留文本内容和许多设置，具体取决于我使用的特定插件/主题配置。我想知道在数据库上使用版本控制的最佳方式是什么，如果可能的话。我想我可以做一个 SQL 转储，虽然我的 MySQL 服务器在 Windows 上运行（读作：我不知道如何做），然后将 SQL 转储添加到我的存储库中。但是当我推送一些东西时，就会带来巨大的安全威胁。

Is there an accepted practice of doing this?

这样做是否有公认的做法？

采纳答案by Kevin A. Naudé

You can backup your database within a git repository. Of course, if you place the data into git in a binary form, you will lose all of git's ability to efficiently store the data using diffs (changes). So the number one best practice is this: store the data in a text serialised format.

您可以在 git 存储库中备份您的数据库。当然，如果您将数据以二进制形式放入 git 中，您将失去 git 使用差异（更改）有效存储数据的所有能力。因此，第一个最佳实践是：以文本序列化格式存储数据。

mysqldump is a suitable program to help you do this. It isn't perfect though. If anything disturbs the serialisation order of items (eg. as a result of creating new tables, etc.) then artificial breaks will enter into the diff. That will decrease the efficiency of storage. You could write a custom serialiser to serialise changes only -- but then you are doing the hard work that git is already good at. Just use the sql dump.

mysqldump 是一个合适的程序来帮助你做到这一点。虽然它并不完美。如果有任何事情扰乱了项目的序列化顺序（例如，由于创建新表等），那么人为中断将进入差异。这会降低存储效率。您可以编写一个自定义序列化程序来仅序列化更改——但是您正在做 git 已经擅长的艰苦工作。只需使用 sql 转储。

That being said, what you are wanting to do isn't what devs normally mean when they talk about putting the database in git. For instance, if you read the link posted by @eggyal (link to codinghorror) you will see that what is actually placed in git are the scripts needed to generate the initial database. There may be additional scripts, like those to populate the database data with a clean state, or to populate it with testing data. All such sql scripts are text files, and pretty much the same format as the sql dump you would get from mysqldump. So there's no reason you can't do it that way with your day-to-day data as well.

话虽如此，您想要做的并不是开发人员谈论将数据库放入 git 时通常的意思。例如，如果您阅读@eggyal 发布的链接（指向codinghorror 的链接），您将看到实际放置在 git 中的是生成初始数据库所需的脚本。可能还有其他脚本，例如用干净状态填充数据库数据或用测试数据填充数据库数据的脚本。所有这些 sql 脚本都是文本文件，并且与您从 mysqldump 获得的 sql 转储格式几乎相同。因此，您也没有理由不能以这种方式处理日常数据。

回答by Kevin

There are not many software available to version control databases like MySQL and MongoDB.

MySQL和MongoDB等版本控制数据库可用的软件并不多。

But one is under development and the beta version is about to be launched soon. Check out Klonio - Version Control for databases

但一个正在开发中，测试版即将推出。查看Klonio - 数据库的版本控制

回答by abanmitra

The article How to Sync A Local & Remote WordPress Blog Using Version Controlgives advice on how to automate sync between two instances (development, production) of a WordPress blog using Mercurial. Is mentions that for this scenario, Git and Mercurial are very similar.

文章如何使用版本控制同步本地和远程 WordPress 博客提供了有关如何使用 Mercurial 在 WordPress 博客的两个实例（开发、生产）之间自动同步的建议。提到对于这个场景，Git 和 Mercurial 非常相似。

Step 4 (Synchronizing The Databases) is of interest here.

第 4 步（同步数据库）在这里很有趣。

The database content will be exported to a file that is tracked by the revision control. Each time we pull changes, the database content will be replaced by this file, making our database up-to-date.

数据库内容将导出到由修订控制跟踪的文件。每次我们拉取更改时，数据库内容将被此文件替换，使我们的数据库保持最新。

Then, it elaborates on conflicts and the scripting part of the job.

然后，它详细说明了冲突和作业的脚本部分。

There is a version control tutorial in Mercurialout there, if you're not familiar with it.

Mercurial中有一个版本控制教程，如果您不熟悉的话。

回答by Anonymous

Be aware that Wordpress stores all news feed content in the database, so even if you don't make any changes, there will be a lot of changing content.

请注意，Wordpress 将所有新闻提要内容存储在数据库中，因此即使您不进行任何更改，也会有很多更改的内容。