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 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.