[greenleafjacob]

This is a more or less solved problem in MySQL with pt-online-schema-change [1] which accomplishes “create a new table ...” with the “copying data over” done for you using triggers and “switch clients over” done using a table rename, all within a tool with the longest full table lock held during the rename step. Some of this appears to be available in MySQL itself as of 5.6, but regardless all of this is fraught with edge cases.

[1] https://www.percona.com/doc/percona-toolkit/LATEST/pt-online...

[michaeldejong]

Indeed. It seems there are many such tools available for MySQL: Openark kit [0] for instance, whose author also brought us gh-ost. Unfortunately most tools focus on just MySQL, and none really have an answer when you want to use (and enforce) foreign key constraints. Although it's hard to say, I suspect the users of these tools have "given up" on foreign key constraints.

[0] http://code.openark.org/forge/openark-kit [1] https://github.com/github/gh-ost