如何对 MySQL 数据模型进行版本控制和同步？
保存 MySQL 数据模型并自动将更改应用到开发数据库服务器（或至少每晚）的最佳方法是什么？
例如，今天我正在处理我的项目并在数据库中创建此表，并将语句保存到 SQL 文件以便稍后部署到生产中：
create table dog (
uid int,
name varchar(50)
);
明天，我决定也记录每只狗的品种。因此，我将 SQL 文件更改为：
create table dog (
uid int,
name varchar(50),
breed varchar(30)
);
该脚本将在第一个版本的生产中运行，但它不会帮助我更新我的开发数据库，因为ERROR 1050 (42S01): Table 'dog'已经存在。此外，如果此更改是在第一个版本之后进行的，则它将无法在生产中运行。所以我现在确实需要ALTER该表。
所以现在我有两个担忧：
- 这是我应该如何拯救我的 数据模型（一堆创建 SQL 文件中的语句），以及
- 如何 我应该应用像这样的更改 这到我的数据库？
我的目标是准确地发布变更并实现持续集成。我使用一个名为 DDLSYNC 的工具来查找并应用 Oracle 数据库中的差异，但我不确定 MySQL 是否存在类似的工具。
What's the best way to save my MySQL data model and automatically apply changes to my development database server as they are made (or at least nightly)?
For example, today I'm working on my project and create this table in my database, and save the statement to SQL file to deploy to production later:
create table dog (
uid int,
name varchar(50)
);
And tomorrow, I decide I want to record the breed of each dog too. So I change the SQL file to read:
create table dog (
uid int,
name varchar(50),
breed varchar(30)
);
That script will work in production for the first release, but it won't help me update my development database because ERROR 1050 (42S01): Table 'dog' already exists. Furthermore, it won't work in production if this change was made after the first release. So I really need to ALTER the table now.
So now I have two concerns:
- Is this how I should be saving my
data model (a bunch of create
statements in a SQL file), and - How
should I be applying changes like
this to my database?
My goal is to release changes accurately and enable continuous integration. I use a tool called DDLSYNC do find and apply difference in an Oracle database, but I'm not sure what similar tools exist for MySQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在工作中，我们开发了一个小脚本来管理数据库版本控制。对任何表或数据集的每次更改都会获得其自己的 SQL 文件。
文件按顺序编号。我们通过将信息存储在数据库中来跟踪已运行的更新文件。该脚本在文件即将执行时插入包含文件名的行，并在执行完成时使用完成时间戳更新该行。这是包含在交易中的。 （值得记住的是，MySQL 中的 DDL 命令不能在事务内发生。任何在事务中执行 DDL 的尝试都会导致隐式提交。）
因为 SQL 文件是我们源代码存储库的一部分，所以我们可以将运行更新脚本作为一部分正常的推出过程。这使得保持数据库和代码同步变得非常容易。老实说，最难的部分是确保另一个开发人员没有抓住待提交的下一个数字。
我们将此更新系统与（可选）每晚擦除我们的开发数据库相结合，用昨晚的实时系统备份替换内容。恢复备份后，将运行更新，并在此过程中运行任何挂起的更新文件。
恢复的方式是仅覆盖实时数据库中的表。因此，任何添加表的更新也必须负责仅在表不存在时添加它。
DROP TABLE IF EXISTS很方便。不幸的是，并非所有数据库都支持这一点，因此更新系统还允许执行用我们选择的语言编写的脚本，而不仅仅是 SQL。所有这些都只需大约 150 行代码。它就像读取目录、将内容与表进行比较以及按确定的顺序执行尚未执行的任何内容一样简单。
At work, we developed a small script to manage our database versioning. Every change to any table or set of data gets it's own SQL file.
The files are numbered sequentially. We keep track of which update files have been run by storing that information in the database. The script inserts a row with the filename when the file is about to be executed, and updates the row with a completion timestamp when the execution finishes. This is wrapped inside a transaction. (It's worth remembering that DDL commands in MySQL can not occur within a transaction. Any attempt to perform DDL in a transaction causes an implicit commit.)
Because the SQL files are part of our source code repository, we can make running the update script part of the normal rollout process. This makes keeping the database and the code in sync easy as pie. Honestly, the hardest part is making sure another dev hasn't grabbed the next number in a pending commit.
We combine this update system with an (optional) nightly wipe of our dev database, replacing the contents with last night's live system backup. After the backup is restored, the update gets run, with any pending update files getting run in the process.
The restoration occurs in such a way that only tables that were in the live database get overwritten. Any update that adds a table therefore also has to be responsible for only adding it if it doesn't exist.
DROP TABLE IF EXISTSis handy. Unfortunately not all databases support that, so the update system also allows for execution of scripts written in our language of choice, not just SQL.All of this in about 150 lines of code. It's as easy as reading a directory, comparing the contents to a table, and executing anything that hasn't already been executed, in a determined order.
许多框架中都有用于此目的的标准工具：Rails 有一个名为 Migrations 的东西这可以很容易地用 PHP 或任何类似的语言复制。
There are standard tools for this in many frameworks: Rails has something called Migrations, something that's easily replicated in PHP or any similar language.