I'm focusing on a credit card applicatoin having a couple of others and we want to keep our MySQL database in source control. My ideas are two have two files: one will be the create script for that tables, etc, and also the other will be the card inserts for the sample data. Is a good idea? Also, what's the easiest method to export these details?
Also, any recommendations for workflow when it comes to methods to accelerate the entire process of making changes, conveying, upgrading, etc.
This seems like a good idea. You are able to diff various revisions, both when it comes to table structure and data. Nice!
The easiest method to produce the dumps is most likely
**mysqldump**, as possible automated easily - once with
--no-data to find the create claims, once with
--no-create-info --no-create-db to find the data.
Sometimes within an atmosphere using Microsoft source safe and Oracle / Sql Server.
We discovered that holding each package / procedure, create table script etc inside a separate text file was the easiest method to do that. This means that after maintaining packages, designers can just checkout the 1 package etc they might require. When the changes happen to be made and examined, they may be checked in.
You certainly possess the right idea. Listed here are a few good articles about them:
- Get Your Database Under Version Control
- Bulletproof Sql Change Scripts Using INFORMATION_SCHEMA Sights (specific to MS SQL Server but makes good points about idempotency)
This is actually the process I personally use for versioning MySQL databases under Subversion.
In SVN produce a
Databases folder having a sub-folder for every database you want to increase SVN.
Add db_version table to databases
We will have to give a table to every database therefore we understand what version from the database we're presently dealing with. This table will even function as a log to trace what schema changes happen to be designed 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 towards the database.
minorReleaseNumber - Minor releases are improvements towards the database that don't necessitate a significant release.
pointReleaseNumber - A place release is generally a simple bug fix.
scriptName - The title of sql script that made the schema changes.
dateApplied - Once the script was operate on this database.
Create baseline scripts
I made use of mysqldump to develop a create script for existing databases. Make sure to range from the --no-data option. SVN can be used to keep an eye on the scripts which make schema changes towards the database and isn't supposed to have been used as backup tool for data of the particular demonstration of the applying.
$ mysqldump -h localhost -u root -p db_1 --no-data > db_126.96.36.199.sql
The title from the sql script should retain the title from the database and also the version from the database the script is applicable to.
In the finish from the script make sure to add an place statement for that db_version table.
Altering database schema
If you have a significant, minor, or point release switch to the database, the modification script ought to be examined after which submitted towards the database folder in SVN. It may be beneficial to backup the database before using a big change script. In the finish from the change script ought to be an place statement for that db_version table.