How to rename a database in MySQL
In MySQL there is no support for database renaming.
In order to rename a MySQL database you can do one of the following:
1. Create new database and rename all tables in the old database to be in the new database:
CREATE database new_db_name; RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name; DROP database db_name; |
2. In Linux shell, use mysqldump to back up the old database, then restore the dumped database under a new name using the MySQL utility. Finally, use the
drop database command to drop the old database. This option can preform badly for large database.
mysqldump -uxxxx -pxxxx -h xxxx db_name > db_name_dump.sql mysql -uxxxx -pxxxx -h xxxx -e "CREATE DATABASE new_db_name" mysql -uxxxx -pxxxx -h xxxx new_db_name < db_name_dump.sql mysql -uxxxx -pxxxx -h xxxx -e "DROP DATABASE db_name" |
3. Write a simple Linux script (my favorite solution)
#!/bin/bash mysqlconn="mysql -u xxxx -pxxxx -S /var/lib/mysql/mysql.sock -h localhost" olddb=xxxx newdb=xxxx #$mysqlconn -e "CREATE DATABASE $newdb" params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$olddb'") for name in $params; do $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name"; done; #$mysqlconn -e "DROP DATABASE $olddb" |
4. If all your tables are MyISAM, you can rename the old database folder name:
- Shut down the MySQL server,
- Rename the database folder name to the new name,
- Start the MySQL server.
Note: This post was improved thanks to the comments I received from:
- Shlomi Noach
- VadimTK
- Cédric PEINTRE
16 Comments
Leave a Reply
Also read this: Emulating The Missing RENAME DATABASE Command in MySQL
Sorry for double-posting. Do note that ALTER DATABASE does not work properly, which is the reason for the above link. See documentation here.
Thanks
http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
RENAME DATABASE Syntax
RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23
Hi, “rename database” is no longer supported since 5.1.23 !
http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
I think you have to change this on your post. Thx
CREATE database new_db_name;
RENAME TABLE db_name.table1 TO new_db_name.table1,
db_name.table2 TO new_db_name.table2;
DROP database db_name;
I used example one. Works perfect and easy… End of issue.
I’m new to scripting. I tried your 3rd script to rename a database and it worked great. Thank you. I have a question about your script.
The line params=$($mysqlconn -N -e “SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=’$olddb’”) what does the “-N” and “-e” do?
Thanks.
Rename Database syntax is not working with MYSQL5.0 version
This is the script I wrote for renaming DB in windows shell:
@echo off
set olddb=olddbname
set newdb=newdbname
SET count=1
SET act=mysql -uroot -e “select table_name from information_schema.tables where table_schema=’%olddb%’”
mysql -uroot -e “create database %newdb%”
echo %act%
FOR /f “tokens=*” %%G IN (‘%act%’) DO (
REM echo %count%:%%G
echo mysql -uroot -e “RENAME TABLE %olddb%.%%G to %newdb%.%%G”
mysql -uroot -e “RENAME TABLE %olddb%.%%G to %newdb%.%%G”
set /a count+=1
)
mysql -uroot -e “drop database %olddb%”
Just a small change for the script in option 3.
You can add this to create the DB for you:
$mysqlconn -e “CREATE DATABASE IF NOT EXISTS $newdb”
Add it just above the ‘for’ loop.
Marc
it seems to exist since version 5.1.7 of MysSQL :
http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
I add the syntax :
RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
Please note that you’ll have to recreate grants according to the new names (sorry if it’s obvious!).
Nice post, thanks. You mentioned in the original “This option can preform badly for large database.”
Can you shed some more light on what you considered “badly” and how big a database has to be for you to consider it to be “large”
Many thanks,
Neil
Very nice option 1, and illustrates the underlying container structure too.