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.<\/p>\n
Database migrations are a great way to regain control of this mess.<\/strong> They allow you to:<\/p>\n Change the directory after extracting it.<\/p>\n There are two ways to migrate:<\/p>\n 1. Edit the configuration file in order to specify DB endpoint, username and local sql files directory. We can edit the configuration file \/conf\/flyway.properties and can specify the parameters.<\/p>\n We’ll follow second method i.e. command line parameters.<\/p>\n Now Create the first Migration.<\/p>\n Create a first migration in the \/sql directory called 1.0.0__Create_employee_table.sql<\/p>\n [js]create table EMPLOYEE ( Migrate the database to version say Version 1.0.0.<\/p>\n flyway-3.0\/flyway -url=<DATABASE_URL> -user=<DATABASE_USER> -password=<DATABASE_PASSWORD> -target=<DATABASE_VERSION> migrate<\/p>\n For Example<\/span> : 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 :<\/p>\n flyway-3.0\/flyway -url=<DATABASE_URL> -user=<DATABASE_USER> -password=<DATABASE_PASSWORD> -locations=filesystem:<LOCATION> -target=<DATABASE_VERSION> migrate<\/p>\n After executing this command, you will get output like this<\/p>\n Now lets move to further steps<\/p>\n We’ll now add a second migration to the \/sql directory called V1.1.0__Add_employee.sql<\/p>\n [js]insert into EMPLOYEE (ID, NAME) values (1, ‘Mr. Now we can migrate the database to version say Version 1.1.0.<\/p>\n flyway-3.0\/flyway -url=<DATABASE_URL> -user=<DATABASE_USER> -password=<DATABASE_PASSWORD> -target=<DATABASE_VERSION> migrate<\/p>\n After executing this command, you will get output like this<\/p>\n Important Action :<\/p>\n 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.<\/p>\n — RDS Snapshot we can easily take from AWS RDS Console by selecting your RDS DB instance and clicking on instance Actions. — We can take dump of mysql by issuing following command : References :\n
\nHere We’ll be migrating a MySQL Database on AWS RDS using Flyway DB migration tool.
\nFirst of all download flyway from flyway website and extract it. (http:\/\/flywaydb.org\/)
\nDownload link :
\nhttp:\/\/repo1.maven.org\/maven2\/org\/flywaydb\/flyway-commandline\/3.0\/flyway-commandline-3.0.zip<\/a><\/p>\ncd flyway-3.0<\/pre>\n
\n2. Pass the DB endpoint, username and local sql files directory etc on command line .<\/p>\n
\n ID int not null,
\n NAME varchar(100) not null
\n);[\/js]<\/p>\n
\nflyway-3.0\/flyway -url=jdbc:mysql:\/\/*************.rds.amazonaws.com:3306 -user=root -password=password -target=1.0.0 migrate<\/p>\nMigrating schema \"PUBLIC\" to version 1.0.0 Successfully\r\napplied 1 migration to schema \"PUBLIC\" (execution time 00:00.065s).<\/pre>\n
\nCreating a second migration<\/h2>\n
\nKumar’);
\ninsert into EMPLOYEE (ID, NAME) values (2, ‘Mr.
\nTomar’);
\ninsert into
\nEMPLOYEE (ID, NAME) values (3, ‘Mr. Bhatia’);[\/js]<\/p>\nCurrent version of schema \"PUBLIC\": 1.0.0\r\nMigrating schema \"PUBLIC\" to version 1.1.0\r\nSuccessfully applied 1 migration to schema \"PUBLIC\" (execution time 00:00.089s).<\/pre>\n
\nIn Instance Actions click on Take DB Snapshot . Enter snapshot name and click on Yes, Take Snapshot.<\/p>\n
\nmysqldump -u <DATABASE_USER> -p <DATABASE_PASSWORD> <DATABASE> > <SQL_FILE_NAME.sql><\/p>\n
\nhttp:\/\/flywaydb.org\/<\/a>
\nhttp:\/\/docs.aws.amazon.com\/AmazonRDS\/latest\/UserGuide\/USER_CreateSnapshot.html<\/a><\/p>\n
\n