Howto rename a MySQL database

By | November 14, 2012

Three ways to rename a MySQL database

1. Table renaming

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.TABLE TO new_db.TABLE;

After the rename you have to adjust the permissions of the table!

To generate a script to generate the sql to rename all tables you can get all tablenames
from the information schmema:

SELECT concat('RENAME TABLE $1.',TABLE_NAME, ' TO $2.',TABLE_NAME, ';')
FROM information_schema.TABLES
WHERE table_schema='$1';

2. Dump it

An alternative way is to dump all the table structur and data and import it again to mysql. This is an easy method if your database is not to big.

Use these few simple commands

mysqldump --routines -u username -p -v olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql

3. MyIsam move

If ALL your tables are MyIsam you just can move the files which contain the tables. For this you create the new database and shutdown mysql. Move all the files from the old database folder to the newly created one.

5 thoughts on “Howto rename a MySQL database

  1. Rolf

    Sadly, rename only works when the dbs are on the same partition. Maybe an improvement to so this online for mysql 5.6 or pt-online-schema-change.

    Reply
  2. Shlomi Noach

    In the case of table renaming, you still have to move the routines.

    In the case of mysqldump, make sure you include the “–routines” switch. mysqldump does not dump stored routines by default.

    Reply
  3. saikumar

    we have one more way to rename the database with linux move command go to datadirectory of mysql mv old_database_name new_database_name , but i have never tired this on productions… can anyone have idea on this.

    Reply

Leave a Reply