Swapping/toggling two MySQL databases

Question

We recently lost some data from our live DB. We restored an older backup as our current live DB, and have been repairing a more recent partially broken DB as a test DB. Both are running on the same server and the plan is the testing DB will replace the live DB as soon as we're happy the fixes are valid.

Is there an easy way to swap the two DBs, e.g. swap their names, so we can just switch which one is used by the website for some last-minute checks? i.e. we want to set the repaired DB as the live one, but be able to flip back without re-building/re-importing a whole exported version.

I don't know if MySQL allows renaming DBs in this way.

While this is an interesting question, isn't it easier to just update the DB name in your website's configuration file? — Stobor, Aug 14 '13 at 14:54
Also, this question might be better suited to ServerFault... — Stobor, Aug 14 '13 at 14:54
This could be useful: http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name — Alma Do, Aug 14 '13 at 14:57
@Stobor I'm not a web guy, I _hope_ we have a single config file :) Thanks! — Mr. Boy, Aug 14 '13 at 14:58

score 1 · Answer 1 · edited Sep 25 '17 at 15:34

To swap two database schemas that are named db1 and db2:

mysqladmin -uroot -pmypassword create swaptemp
mysqldump -uroot -pmypassword --routines db1 | mysql -u root -pmypassword swaptemp
mysqladmin -uroot -pmypassword drop db1
mysqladmin -uroot -pmypassword create db1
mysqldump -uroot -pmypassword --routines db2 | mysql -u root -pmypassword db1
mysqladmin -uroot -pmypassword drop db2
mysqladmin -uroot -pmypassword create db2
mysqldump -uroot -pmypassword --routines swaptemp | mysql -u root -pmypassword db2
mysqladmin -uroot -pmypassword drop swaptemp

Steps:

Copy the lines into Notepad.
Replace all references to "db1", "db2", "mypassword" (+ optionally "root") with your equivalents.
Execute one by one on the command line (entering "y" when prompted).

Swapping/toggling two MySQL databases

1 Answers1