How do I quickly rename a MySQL database (change schema name)?
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; 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.
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;
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
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;
Notes: there is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
Also, if you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
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
I think the solution is simpler and was suggested by some developers. phpMyAdmin has an operation for this.
From phpMyAdmin, select the database you want to select. In the tabs there's one called Operations, go to the rename section. That's all.
It does, as many suggested, create a new database with the new name, dump all tables of the old database into the new database and drop the old database.
