Database Migration using Flyway on AWS RDS

Database migrations often are a necessity in the application development and maintenance life-cycle.Whenever we need to apply changes to the database structure, insert new data fragments and in doing so want to be sure that this all happens with some control and versioning.

Database migrations are a great way to regain control of this mess. They allow you to:

• Recreate a database from scratch
• Make it clear at all times what state a database is in
• Migrate in a deterministic way from your current version of the database to a newer one

 
Here We’ll be migrating a MySQL Database on AWS RDS using Flyway DB migration tool.
First of all download flyway from flyway website and extract it. (http://flywaydb.org/)
Download link :
http://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/3.0/flyway-commandline-3.0.zip

Change the directory after extracting it.

cd flyway-3.0

There are two ways to migrate:

1. Edit the configuration file in order to specify DB endpoint, username and local sql files directory.
2. Pass the DB endpoint, username and local sql files directory etc on command line .

We can edit the configuration file /conf/flyway.properties and can specify the parameters.

We’ll follow second method i.e. command line parameters.

Now Create the first Migration.

Create a first migration in the /sql directory called 1.0.0__Create_employee_table.sql

[js]create table EMPLOYEE (
ID int not null,
NAME varchar(100) not null
);[/js]

Migrate the database to version say Version 1.0.0.

flyway-3.0/flyway -url=<DATABASE_URL> -user=<DATABASE_USER> -password=<DATABASE_PASSWORD> -target=<DATABASE_VERSION> migrate

For Example :
flyway-3.0/flyway -url=jdbc:mysql://*************.rds.amazonaws.com:3306 -user=root -password=password -target=1.0.0 migrate

In the above scenario the default location of sql files is /sql directory.We can also specify our own sql files directory location instead of default like mention below :

flyway-3.0/flyway -url=<DATABASE_URL> -user=<DATABASE_USER> -password=<DATABASE_PASSWORD> -locations=filesystem:<LOCATION> -target=<DATABASE_VERSION> migrate

After executing this command, you will get output like this

Migrating schema "PUBLIC" to version 1.0.0 Successfully
applied 1 migration to schema "PUBLIC" (execution time 00:00.065s).

Now lets move to further steps

Creating a second migration

We’ll now add a second migration to the /sql directory called V1.1.0__Add_employee.sql

[js]insert into EMPLOYEE (ID, NAME) values (1, ‘Mr.
Kumar’);
insert into EMPLOYEE (ID, NAME) values (2, ‘Mr.
Tomar’);
insert into
EMPLOYEE (ID, NAME) values (3, ‘Mr. Bhatia’);[/js]

Now we can migrate the database to version say Version 1.1.0.

flyway-3.0/flyway -url=<DATABASE_URL> -user=<DATABASE_USER> -password=<DATABASE_PASSWORD> -target=<DATABASE_VERSION> migrate

After executing this command, you will get output like this

Current version of schema "PUBLIC": 1.0.0
Migrating schema "PUBLIC" to version 1.1.0
Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.089s).

Important Action :

If you are upgrading an existing database to a newer version, before that either take RDS Snapshot or mysql dump or both in order to restore it to previous version if any failure occurs during migration.

— RDS Snapshot we can easily take from AWS RDS Console by selecting your RDS DB instance and clicking on instance Actions.
In Instance Actions click on Take DB Snapshot . Enter snapshot name and click on Yes, Take Snapshot.

— We can take dump of mysql by issuing following command :
mysqldump -u <DATABASE_USER> -p <DATABASE_PASSWORD> <DATABASE> > <SQL_FILE_NAME.sql>

References :
http://flywaydb.org/
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html

Thanks
————————————–
Ishant Kumar
AWS Certified Solution Architect – Associate
AWS Administrator @ Intelligrape