Hot vs cold mysql schema migrations and improving speed

Question

I have recently been doing cold migrations...which means that I make it impossible from an application level to read/write to the database while doing the migration (Maintenance page).

This way errors won't happen for changes to the structure and also if there is a lot of load I wouldn't want mysql to crash in the middle of the migration.

My structure is that every clients get their own database. The only downside to this approach is their can be downtime of 15-45 minutes depending on how many changes are made.

My solution to this would be the following:

Have 2 copies of the code running at the same time. I have code that detects what version of the program they are on and if they are still on old show them the old code...if they are on new show them the new code

The only part that scares me is if someone does a denial of service attack in the middle of the migration I could have serious problems.

I have about 360 databases right now.

Is the hot method recommended? I just get worried about a denial of service in the middle of it or some sort of mysql query error because their could be data changes going on. I did have this happen once before but luckily it was just before I started the migration.

Answer 1

Your idea would work only if the "new Code Base" is 100% compatible with the "old DB version", or else it might crash while the DB migration is in progress. Also, it requires that at no stage during the DB migration, the database is never in an inconsistent state (perhaps by wrapping migration steps in appropriate transactions).

If ressources allow, I would:

• install and configure the new code base under a new virtual host, pointing at the new database (see below)
• put the "old" site in read-only mode
• duplicate the current database, on the same DB server
• migrate the duplicate database to the new version
• switch the virtualhost to the new code base (make sure you lift the maintenance mode :)
• let the new version mature for a few hours, and then drop the old code base, DB, and virtual host.

(you can even skip the tinkering with virtual hosts and use symbolic links instead)