Tips and tricks for LAMP (Linux Apache MySQL PHP) developers.

When I cant find a suitable solution to a problem and have to work it out for myself I'll post the result here so hopefully others will find it useful. (All code is offered in good faith. It may not be the best solution, just a solution).

Saturday, 12 November 2011

Rename a MySQL database

Recently I needed to rename a MySQL database I was working with to make way for another with the same name. When I looked around for how to do this it seemed that the only option was to create the new database and then copy each table to it, via INSERT...SELECTs or a dump and import. It was quite a large database and this would have been very time consuming.

After discussing it with a colleague we came up with another way to do it by using bash to iterate over each table in the schema and then use the RENAME TABLE command:

for TABLE in `mysql -u[USERNAME] -p[PASSWORD] [OLD DB] -e "SHOW TABLES" -B -N -s`; do mysql -u[USERNAME] -p[PASSWORD] [OLD DB] -e "RENAME TABLE [OLD DB].$TABLE TO [NEW DB].$TABLE"; done;

This worked a treat (I've been using my new database reliably for a while now) and was fast too!