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

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.

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.

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

Also see: the very good series of articles here