Quickie: Quickly rename MySQL database

There's no way to rename a MySQL database. Typical advice is to dump/restore the database, but this is obviously very slow if you have a huge database.

I found a solution today. Create your database, then execute this one-line:

mysql -uUSER -pPASS OLD_DB -sNe 'SHOW TABLES' | while read t; do mysql -u username -ppassword -sNe "rename table OLD_DB.$t to NEW_DB.$t"; done  

Obviously replace the params as necessary.

Or here's the same as a slightly easier to read bash script:

#!/bin/bash

DB_HOST="localhost"  
DB_USER="root"  
DB_PASS="pass"

OLD_DB="old_db"  
NEW_DB="new_db"

mysql -u$DB_USER -p$DB_PASS -h$DB_HOST $OLD_DB -sNe 'SHOW TABLES' | while read t; \  
    do mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -sNe "RENAME TABLE $OLD_DB.$t to $NEW_DB.$t"; done

Caveats

  • Does not rename/copy triggers
  • Does not rename views
    • In fact, will cause errors if you have views installed because SHOW TABLES will return views too.