Staging MySQL databases

Question

I'm building a Web/Web service application based on a number of (MySQL) databases. The databases are all built from externally derived data from dozens to hundreds of sources. My general plan is to have completely separate test, staging and production databases. The typical workflow is:

• Check to see if any of the data distributions have been updated. The update frequency varies from daily to yearly, and everything in between.
• If so, download the data, perform whatever transformations are required to turn it into SQL, and build a test database
• Run unit tests with the new data
• If everything passes, load the new data to a staging database
• Run tests again on the staging database (because I'm paranoid)
• Once a day, switch the production database to the staging database

All of the databases are read-only, once they're built. I very much like the idea of multiple versions of the database(s), but implementing this has me confused. Specifically:

• Because several of the databases have foreign key constraints, I can't just drop each table in turn and recreate it. I don't see any choice but to delete the whole database and rebuild it, but then I'd need to recreate all of the grants, and I don't want to have to run something with database admin privileges like this on a daily basis.
• I don't know how to swap the staging and production databases in a way that won't cost down-time.

I've looked for best practice in building databases within this general model, and come up dry. So, two questions:

1. Am I on the right track with this general design, and
2. What do I need to implement it?

Answer 1

You are working too hard.

Plan A -- RENAME TABLE

Assuming the production database is really readonly, you don't need FKs, just the indexes they generate. (And you may not need all of them.)

So, before testing in Staging, turn off FK checks and drop all the FKs. Then consider dropping any excess indexes. Then test.

Since there is no RENAME DATABASE, you are stuck with a rather rapid series of

RENAME TABLE live.table1 TO old.table1,
             live.table2 TO old.table2,
             ...
             new.table1 TO live.table1,
             ... ;

Plan B -- 2 Instances

Better than that would be to have two instances of MySQL on the same server or (better yet) on different servers. One for staging, one for production. The flip-over does, however, require some way to redirect clients. This may involve port number, proxy, IP address, etc. Look at HAProxy and MaxScale for achieving the flip for you. (They probably work for either case -- 2 instances on one server or 2 servers.)