Hot and cold mysql schema migration and speed improvement

Question

I recently did a cold migration ... this means that I am making it impossible from the application level to read / write to the database when performing the migration (maintenance page).

Thus, errors will not occur for changes in the structure, and also if there is a large load, I would not want mysql to crash in the middle of the migration.

My structure is that each client gets its own database. The only drawback of this approach is that their downtime is 15-45 minutes, depending on how many changes are made.

My solution for this would be the following:

You have 2 copies of the code at the same time. I have a code that determines which version of the program they are in, and if they are still on the old one, show them the old code ... if they are on the new one, show them the new code

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

I now have about 360 databases.

Is a hot method recommended? I'm just worried about a denial of service in the middle or some kind of mysql query error, because they can be modified by data. I used to do this before, but, fortunately, it was not long before the start of migration.

Answer 1

Your idea will work only if the "new code base" is 100% compatible with the "old version of the database", otherwise it may fail during database migration. In addition, this requires that, at no stage during the database migration, the database should never be in an inconsistent state (possibly by transferring the migration steps to the corresponding transactions).

If ressources allow, I would:

• install and configure the new code database under the new virtual host, pointing to the new database (see below)
• set the "old" site in read-only mode.
• duplicate the current database on the same database server
• transfer duplicate database to new version
• switch the virtual host to the new code base (make sure that you raise the maintenance mode :)
• let the new version mature in a few hours, and then discard the old code base, database, and virtual host.

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