MySQL Automatic DB Migration
Today I am going to explain about MySQL DB migration with maven using “c5-db-migration”.
The Carbon Five Database Migration framework and Maven plugin for Java provides a simple solution
to the problem of managing discrete, incremental changes to databases over time across multiple
environments. Each migration is versioned and tracked when applied to the database.
Actually we got some request from client side that they need to execute DB scripts automatically,whenever some any script is added or some changes are made.
I am showing this using hudson(I’ll post about hudson Later in detail. In Short : Hudson is a continuous integration (CI) tool written in Java, which runs in a servlet container, such as Apache Tomcat, the GlassFish or Jboss application server. It supports SCM tools including CVS, Subversion, Git and Clearcase and can execute Apache Ant and Apache Maven based projects, as well as arbitrary shell scripts and Windows batch commands.).
SVN Contents:
#/test-db/trunk/
pom.xml
src/
[mc26157@ph158004]~$ tree src/
src/
`-- main
`-- db
`--
migrations
|--
20120514_ks.sql
|--
20120515_test1.sql
`--
20120515_test2.sql
3 directories, 3 files
Below is the pom.xml file with contents as below :
 |
| pom.xml |
From the command line, you can run the migration plugin like this:
$ mvn db-migration:migrate
If you want the plugin to also create the database, you can invoke the
plugin like this:
$ mvn db-migration:create db-migration:migrate
Sometimes you want to blow away the database and start fresh:
$ mvn db-migration:reset
You can also just check to see if the database is up-to-date:
$ mvn db-migration:validate
When it's time to create a new migration script, you can do so by:
$ mvn db-migration:new -Dname=users_and_roles
Which results in a new, empty file called:
src/main/db/migrations/yyyyMMddHHmmss_users_and_roles.sql (assuming default
settings).
Maven
Plugin Goal Reference
The maven plugin has several goals:
Goal
|
Description
|
db-migration:check
|
Check to see if the database is
up-to-date and fail the build if there are pending migrations
|
db-migration:create
|
Create a new, empty database1
|
db-migration:drop
|
Drop the database1
|
db-migration:migrate
|
Apply all pending migrations2
|
db-migration:new
|
Create a new, empty migration file
(-name=... to include a name in the filename)
|
db-migration:reset
|
Drop the existing database, create
a new one, and apply all pending migrations1
|
db-migration:validate
|
Check to see if the database is
up-to-date and report pending migrations
|
Notes:
- Must have create and drop database privileges.
- Must have schema update privileges.
I am running some examples below manually :
$ mvn db-migration:reset
[mc26157@ph158004]/local2/hudson/jobs/test-db/workspace$
sudo mvn db-migration:reset
[INFO]
Scanning for projects...
[WARNING]
[WARNING]
Some problems were encountered while building the effective model for
com.carbonfive:migration-sample1:jar:1.0
[WARNING]
'build.plugins.plugin.version' for
org.apache.maven.plugins:maven-compiler-plugin is missing. @ line 35, column 21
[WARNING]
The expression ${artifactId} is deprecated. Please use ${project.artifactId}
instead.
[WARNING]
[WARNING]
It is highly recommended to fix these problems because they threaten the
stability of your build.
[WARNING]
[WARNING]
For this reason, future Maven versions might no longer support building such
malformed projects.
[WARNING]
[INFO]
[INFO]
------------------------------------------------------------------------
[INFO]
Building Carbon Five Migration Sample 1 1.0
[INFO]
------------------------------------------------------------------------
[INFO]
[INFO]
--- db-migration-maven-plugin:0.9.9-m2:reset (default-cli) @ migration-sample1
---
[INFO]
Resetting database jdbc:mysql://10.122.70.142:3306/test.
[INFO]
Dropping database jdbc:mysql://10.122.70.142:3306/test.
[INFO]
Creating database jdbc:mysql://10.122.70.142:3306/test.
[INFO]
Migrating database jdbc:mysql://10.122.70.142:3306/test.
[INFO]
Loaded JDBC driver: com.mysql.jdbc.Driver
[INFO]
Successfully enabled migrations.
[INFO]
Migrating database... applying 3 migrations.
[INFO]
Running migration 20120514_ks.sql.
[INFO]
Running migration 20120515_test1.sql.
[INFO]
Running migration 20120516_test2.sql.
[INFO]
Migrated database in 0:00:00.065.
[INFO]
------------------------------------------------------------------------
[INFO]
BUILD SUCCESS
[INFO]
------------------------------------------------------------------------
[INFO]
Total time: 1.246s
[INFO]
Finished at: Wed May 16 19:42:46 PDT 2012
[INFO]
Final Memory: 7M/723M
[INFO]
------------------------------------------------------------------------
$ mvn db-migration:migrate
[mc26157@ph158004]/local2/hudson/jobs/test-db/workspace$
sudo mvn db-migration:migrate
[INFO] Scanning for projects...
[WARNING]
[WARNING] Some problems were encountered while building the effective
model for com.carbonfive:migration-sample1:jar:1.0
[WARNING] 'build.plugins.plugin.version' for
org.apache.maven.plugins:maven-compiler-plugin is missing. @ line 35, column 21
[WARNING] The expression ${artifactId} is deprecated. Please use
${project.artifactId} instead.
[WARNING]
[WARNING] It is highly recommended to fix these problems because they
threaten the stability of your build.
[WARNING]
[WARNING] For this reason, future Maven versions might no longer
support building such malformed projects.
[WARNING]
[INFO]
[INFO]
------------------------------------------------------------------------
[INFO] Building Carbon Five Migration Sample 1 1.0
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- db-migration-maven-plugin:0.9.9-m2:migrate (default-cli) @
migration-sample1 ---
[INFO] Migrating jdbc:mysql://10.122.70.142:3306/test using migrations
at src/main/db/migrations/.
[INFO] Loaded JDBC driver: com.mysql.jdbc.Driver
[INFO] Successfully enabled migrations.
[INFO] Migrating database... applying 3 migrations.
[INFO] Running migration 20120514_ks.sql.
[INFO] Running migration 20120515_test1.sql.
[INFO] Running migration 20120516_test2.sql.
[INFO] Migrated database in 0:00:00.073.
[INFO]
------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO]
------------------------------------------------------------------------
[INFO] Total time: 1.045s
[INFO] Finished at: Wed May 16 19:47:57 PDT 2012
[INFO] Final Memory: 7M/723M
[INFO]
------------------------------------------------------------------------
Now if you are using hudson, then you don’t need to run
these above commands. You have to just click on “Build Now” Button on UI or it
will automatically run based on you SCM configuration.
Some More Useful Information that may give some more idea about the things.
Maven Plugin Configuration
General Settings
Here is the exhaustive list of configuration options and the goal to which they apply.
Option
|
Description
|
Required
|
Default
|
Goals
|
driver
|
JDBC driver class name (fully-qualified)
|
No
|
Autodetected from JDBC connection URL
|
migrate, validate, drop, create, reset
|
url
|
JDBC connection URL
|
Yes
|
N/A
|
migrate, validate, drop, create, reset
|
username
|
Database username
|
Yes
|
N/A
|
migrate, validate, drop, create, reset
|
password
|
Database password
|
No
|
"" - empty string
|
migrate, validate, drop, create, reset
|
databaseType
|
MYSQL, POSTGRESQL, SQL_SERVER, HSQL, H2, DB2, ORACLE,
UNKNOWN
|
No
|
Autodetected from JDBC connection URL
|
migrate, validate, drop, create, reset
|
migrationsPath
|
Location of migrations
|
No
|
"src/main/db/migrations/"
|
migrate, validate, new
|
versionPattern
|
Date format for new migration filenames
|
No
|
"yyyyMMddHHmmss"
|
new
|
versionTimeZone
|
Time zone for new migration filename date format
|
No
|
"UTC"
|
new
|
migrationExtension
|
Filename extension for new migrations
|
No
|
".sql"
|
new
|
versionTable
|
Name of version tracking table
|
No
|
"schema_version"
|
migrate, validate
|
versionColumn
|
Name of version column
|
No
|
"version"
|
migrate, validate
|
appliedDate
|
Name of applied-date column
|
No
|
"applied_on"
|
migrate, validate
|
durationColumn
|
Name of duration column
|
No
|
"duration"
|
migrate, validate
|
createSql
|
SQL to execute to create the database
|
No
|
"create database %s"1
|
create, drop, reset
|
dropSql
|
SQL to execute to drop the database
|
No
|
"drop database %s"1
|
create, drop, reset
|
- Where %s is the name of the database, as extracted from the JDBC connection URL.
Hope the things will help you!!
