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!
