How do I quickly rename a MySQL database (change schema name)?

manpreet Best Answer 2 years ago

The MySQL manual at MySQL covers this.

Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exist only in a handful of versions, and is a bad idea overall.

This needs to work with InnoDB, which stores things very differently than MyISAM.

manpreet 2 years ago

Use these few 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