[FIXED] How to migrate my database schema only once when deploying to multiple instances?

Issue

I have a MySQL database backed web application which I deploy to multiple cloud instances. I am using a database migration tool which means my schema changes are run as part of the deployment (say, via a configuration management tool). However, when I am spinning up multiple instances of my web app, all of these instances will try to apply the schema change to the MySQL database and I can basically end up in an unknown state of my final DB/error out. What is the recommended approach to database schema change deployments in such a setup?

The only possible solution that I see is to simply run the schema change separate from the app deployment process. Any other thoughts?

Solution

Here are some solutions:

• Separate the migration action into something that you can call independently, and call it explicitly in one place in your production and production-like deployments.

• Designate one cloud instance as the migration runner. Mark it in some way so that your deployment process can distinguish it from other instances: give it a special hostname; on EC2, set a value in user data; if using Chef, give the instance a Chef role; etc. Make your deployment check whether it is deploying to the migration runner and migrate only if so.

• Write code that obtains a lock in your database (or possibly in some other singleton resource in your production environment), checks the database schema version, exits if it is at the expected version, and migrates if it is not at the expected version.

Which of these approaches is the best will depend on your deployment environment and tools. In any case, you'll want to continue migrating automatically (e.g. as part of server startup) for convenience in non-production environments.

Answered By - Dave Schweisguth

Answer Checked By - Marilyn (PHPFixing Volunteer)