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:




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


  • 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.

Christopher Nadeau

London, UK