0

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?
  • What about deleting the tables with FK dependencies in reverse order of dependency - i.e. those at the bottom of the chain first? – Vérace Feb 2 '16 at 19:08
  • Is there a way to retrieve a list of pairs of (source, target) table pairs for all foreign keys in a database? – Scott Deerwester Feb 2 '16 at 19:50
  • Try checking out the information_schema (don't have MySQL running). But, if you don't change the schema, can't you do it once even manually? Also Google "find foreign keys in schema mysql" – Vérace Feb 2 '16 at 22:18
  • I can certainly do it manually, but I've got dozens of databases that I'm importing, all updated whenever the maintainer feels like it, so it's a much better idea to script the updates. A manual step turns a daily five minute task into a half an hour. – Scott Deerwester Feb 2 '16 at 22:43
  • performance_schema should help. – Vérace Feb 3 '16 at 1:05
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.)

  • It appears from his response to my idea (appears to have been removed) that @scottdeerwester 's schemas are changed on a daily basis. It's more than simple cutting over he needs. He needs to automatically update his schemas also. – Vérace Feb 3 '16 at 1:53
  • Cutting over should allow for schema change. But what about the application? Does it need to be cut over simultaneously? – Rick James Feb 3 '16 at 2:07
  • But, my understanding is that that the OP is looking for a way to cut over (with schema change) automatically. Maybe Liquibase or Flywaydb is a partial solution. Not sure if total automatic (for app and db) cutover is possible? Puppet/Chef? My view is hierarchy - prioritise that. – Vérace Feb 3 '16 at 2:20
  • Few databases will change schema on a daily basis. The problem is that I have as many as 50 different databases. Each has an implementation of a common API sitting on top of it, and almost all of them are stable almost all of the time. When one doesn't pass a unit test, it's time to see what the maintainer's done to me and adjust the respective API implementation... Basically I want to (1) check for updates (2) run the installers for the ones that changed and (3) see if I broke anything. Every morning. For all 10.. 50... 100.. whatever databases. – Scott Deerwester Feb 3 '16 at 2:59
  • Hmm.… worked in this scenario before. Check out database links. Maybe you can incrementally change the tables through a series of links (as required)? Worked for Oracle - maybe it can be implemented in MySQL also? When you say "databases" - if you mean 50 schemas for the same app with 50 different clients, then that's how we managed it. – Vérace Feb 3 '16 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.