What's the best way to store a MySQL database in source control?

Question

I am working on an application with a few other people and we'd like to store our MySQL database in source control. My thoughts are two have two files: one would be the create script for the tables, etc, and the other would be the inserts for our sample data. Is this a good approach? Also, what's the best way to export this information?

Also, any suggestions for workflow in terms of ways to speed up the process of making changes, exporting, updating, etc.

Answer 1

This sounds like a good approach. You can diff various revisions, both in terms of table structure and data. Nice!

The best way to create the dumps is probably **mysqldump**, as it can be automated easily - once with --no-data to get the create statements, once with --no-create-info --no-create-db to get the data.

Answer 2

I work in an environment using Microsoft source safe and Oracle / Sql Server.

We found that holding each package / procedure, create table script etc in a separate text file was the best way to do this. It means that when maintaining packages, developers can just checkout the 1 package etc they require. Once the changes have been made and tested, they can be checked in.

Answer 3

You definitely have the right idea. Here are a couple good articles on the subject:

• Get Your Database Under Version Control
• Bulletproof Sql Change Scripts Using INFORMATION_SCHEMA Views (specific to MS SQL Server but makes good points about idempotency)

Answer 4

Asked before: How do I version my MS SQL database in SVN?

Also see: the very good series of articles here

Answer 5

This is the process I use for versioning MySQL databases under Subversion.

Setup SVN

In SVN create a Databases folder with a sub-folder for each database you wish to add to SVN.

Add db_version table to databases

We will need to add a table to each database so we know what version of the database we are currently working with. This table will also serve as a log to track what schema changes have been made to the database.

create table db_version (
        `id` int auto_increment,
        `majorReleaseNumber` int,
        `minorReleaseNumber` int,
        `pointReleaseNumber` int,
        `scriptName` varchar(50),
        `dateApplied` datetime,
        PRIMARY KEY(`id`)
);

majorReleaseNumber - Major releases are significant changes to the database.

minorReleaseNumber - Minor releases are enhancements to the database that do not necessitate a major release.

pointReleaseNumber - A point release is usually a simple bug fix.

scriptName - The name of sql script that made the schema changes.

dateApplied - When the script was run on this database.

Create baseline scripts

I used mysqldump to generate a create script for existing databases. Be sure to include the --no-data option. SVN is used to keep track of the scripts that make schema changes to the database and is not intended to be used as backup tool for data of a particular instance of the application.

$ mysqldump -h localhost -u root -p db_1 --no-data > db_1.1.0.0.sql

The name of the sql script should contain the name of the database and the version of the database the script applies to.

db_1.1.0.0.sql

At the end of the script be sure to add an insert statement for the db_version table.

Changing database schema

When you have a major, minor, or point release change to the database, the change script should be tested and then uploaded to the database folder in SVN. It is a good idea to backup the database before applying a change script. At the end of the change script should be an insert statement for the db_version table.