您如何管理开发、测试和生产中的数据库?
我很难找到关于如何在开发、测试和生产服务器之间管理数据库架构和数据的好例子.
I've had a hard time trying to find good examples of how to manage database schemas and data between development, test, and production servers.
这是我们的设置.每个开发人员都有一个运行我们的应用程序和 MySQL 数据库的虚拟机.这是他们的个人沙箱，可以为所欲为.目前，开发人员将对 SQL 架构进行更改，并将数据库转储到他们提交到 SVN 的文本文件中.
Here's our setup. Each developer has a virtual machine running our app and the MySQL database. It is their personal sandbox to do whatever they want. Currently, developers will make a change to the SQL schema and do a dump of the database to a text file that they commit into SVN.
我们想要部署一个持续集成开发服务器，该服务器将始终运行最新提交的代码.如果我们现在这样做，它会为每次构建从 SVN 重新加载数据库.
We're wanting to deploy a continuous integration development server that will always be running the latest committed code. If we do that now, it will reload the database from SVN for each build.
我们有一个运行候选发布"的测试(虚拟)服务器.部署到测试服务器目前是一个非常手动的过程，通常需要我从 SVN 加载最新的 SQL 并对其进行调整.另外，测试服务器上的数据不一致.您最终会得到最后一个开发人员提交的沙盒服务器上的任何测试数据.
We have a test (virtual) server that runs "release candidates." Deploying to the test server is currently a very manual process, and usually involves me loading the latest SQL from SVN and tweaking it. Also, the data on the test server is inconsistent. You end up with whatever test data the last developer to commit had on his sandbox server.
一切都失败的地方是部署到生产.由于我们不能用测试数据覆盖实时数据，这涉及手动重新创建所有架构更改.如果有大量架构更改或转换脚本来操作数据，这会变得非常麻烦.
Where everything breaks down is the deployment to production. Since we can't overwrite the live data with test data, this involves manually re-creating all the schema changes. If there were a large number of schema changes or conversion scripts to manipulate the data, this can get really hairy.
如果问题只是架构，那会是一个更简单的问题，但数据库中有基础"数据，在开发过程中也会更新，例如安全和权限表中的元数据.
If the problem was just the schema, It'd be an easier problem, but there is "base" data in the database that is updated during development as well, such as meta-data in security and permissions tables.
这是我在向持续集成和一步构建迈进的过程中看到的最大障碍.你是如何解决的?
This is the biggest barrier I see in moving toward continuous integration and one-step-builds. How do you solve it?
一个后续问题:您如何跟踪数据库版本，以便知道要运行哪些脚本来升级给定的数据库实例?标准程序下面是不是 Lance 提到的版本表?
A follow-up question: how do you track database versions so you know which scripts to run to upgrade a given database instance? Is a version table like Lance mentions below the standard procedure?
感谢您对塔伦蒂诺的引用.我不在 .NET 环境中，但我找到了他们的 DataBaseChangeMangement wiki 页面非常有帮助.特别是这个Powerpoint Presentation (.ppt)
Thanks for the reference to Tarantino. I'm not in a .NET environment, but I found their DataBaseChangeMangement wiki page to be very helpful. Especially this Powerpoint Presentation (.ppt)
我将编写一个 Python 脚本，该脚本根据数据库中的表检查给定目录中 *.sql 脚本的名称，并按顺序运行不存在的脚本在构成文件名第一部分的整数上.如果这是一个非常简单的解决方案，正如我怀疑的那样，那么我会在此处发布.
I'm going to write a Python script that checks the names of *.sql scripts in a given directory against a table in the database and runs the ones that aren't there in order based on a integer that forms the first part of the filename. If it is a pretty simple solution, as I suspect it will be, then I'll post it here.
我有一个可用的脚本.如果数据库不存在，它会处理初始化数据库并根据需要运行升级脚本.还有用于擦除现有数据库和从文件导入测试数据的开关.它大约有 200 行，所以我不会发布它(不过如果有兴趣，我可能会将它放在 pastebin 上).
I've got a working script for this. It handles initializing the DB if it doesn't exist and running upgrade scripts as necessary. There are also switches for wiping an existing database and importing test data from a file. It's about 200 lines, so I won't post it (though I might put it on pastebin if there's interest).
推荐答案
有几个不错的选择.我不会使用恢复备份"策略.
There are a couple of good options. I wouldn't use the "restore a backup" strategy.
为所有架构更改编写脚本，并让 CI 服务器在数据库上运行这些脚本.有一个版本表来跟踪当前的数据库版本，并且只有在更新版本时才执行脚本.
Script all your schema changes, and have your CI server run those scripts on the database. Have a version table to keep track of the current database version, and only execute the scripts if they are for a newer version.
使用迁移解决方案.这些解决方案因语言而异，但对于 .NET，我使用 Migrator.NET.这允许您对数据库进行版本控制并在版本之间上下移动.您的架构是在 C# 代码中指定的.
Use a migration solution. These solutions vary by language, but for .NET I use Migrator.NET. This allows you to version your database and move up and down between versions. Your schema is specified in C# code.
相关文章