Schema Migration

Schema Migration is done whenever the application needs any database changes. Here, i would like to share some of thoughts about how to migrate from the old schema to a new schema. Say for eg. application is using A table and A table needs some schema changes during the enhancement of the application. we do schema migration using the sql statement with care.

1. Take the Mysql Dump using mysqldump 

mysqldump -u root -p databasename > /tmp/oldDBbeforeChange-filename.sql

above command generates both data and schema from the given database.

2. Analyse which table needs the schema change

3. Take the dump of tables for which schema change is needed. 

 mysqldump -u root -p databasename tablename > /tmp/oldDBtablename-filename.sql

4. write the bunch of alter table scripts modifying the newtablename1

5. using both insert and select sql statement, you can do data migration 

insert into newtablename1 select col1,col2,col3 from oldtablename1

6. exectute the alter table scripts

7. rename the oldtablename1 to some other name (to keep it safe) 

rename table oldtablename1 to oldtablename2

8. rename the newtablename1 to the original table name 

rename table newtablename1 to oldtablename1

In that case, after you, commit your code changes, now check whether the migration process is completed by checking the workflow of the application.

MediaWiki

This page has been accessed 1,336 times.

This page was last modified 09:24, 28 June 2006.