Renaming a MySQL schema Atomically

MySQL briefly supported renaming a schema, but backed out the feature. So how do we rename a schema atomically? In MySQL you can safely rename a table cross-schema so long as the two schemas exist on the same instance. Something like:

RENAME TABLE schema1.tablename TO schema2.tablename;

So to rename a schema, you can do it effectively by the following:

CREATE DATABASE myschema_new; RENAME TABLE myschema.table1 TO myschema_new.table1, myschema.table2 TO myschema_new.table2 …

and so on for each table in the schema. Now, two important items that may trip you up if you are not aware. You can get a confusing error of “table already exists” when in fact the table does NOT exist in the target schema. This can be caused by a couple of things:

1. The two schemas/tables/columns character sets to not exactly match.
2. You are trying to create foreign keys of the same name in the same schema.

#2 really got me when I was trying to replace the schema with a new one atomically, and also wanted to retain the old tables as a backup. You cannot just move table1 to table1_old if they contain foreign keys. The proper way to manage this is to maintain your data in three separate schemas: the target “live” schema, the “old” schema, and the incoming “new” schema. So the RENAME statement would be a long one where it moves all “live” tables to “old”, then moves all “new” tables to “live”.