How to rename a MySQL database (change schema name)?

Question

How to quickly rename a MySQL database (change its schema name)?

Usually I just dump the database and re-import it with a new name. For very large databases this is not an option. Apparently RENAMED {DATABASE|SCHEMA} db_name TO new_db_name; does something bad, only exists in a few versions, and is overall a bad idea.

This needs to be used with InnoDB, which stores very different content than MyISAM.

Answer 1

Use the following simple commands:

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

Or to reduce I/O, use the following as suggested by @Pablo Marin-Garcia:

mysqladmin -u username -p create newdatabase
mysqldump -u username -v olddatabase -p | mysql -u username -p -D newdatabase

Answer 2

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

RENAME TABLE old_db.table TO new_db.table;

You need to adjust the permissions later.

To write scripts in the shell, you can use any of the following methods:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

or

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

---

Comments:

There is no space between
• option-p and the password. If your database does not have a password, remove the -u username -ppassword part.

• If a table has triggers, it cannot be moved to another database using the above method (will result in a Trigger Error Schema error). If this is the case, use traditional methods to clone the database and then delete the old database:

  <代码>mysqldump old_db | mysql new_db

• If you have stored procedures, you can then copy them:

  <代码>mysqldump -R old_db | mysql new_db