Stack Exchange Network
Stack Exchange network consists of 181 Q&A communities including
Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Visit Stack Exchange
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
Anybody can ask a question
The best answers are voted up and rise to the top
12 years, 7 months ago
How to rename a MySQL database?
The MySQL online manual has said about the RENAME DATABASE command (this documentation page has been removed by Oracle some time ago):
This statement was added in MySQL 5.1.7 but was found to be dangerous and
was removed in MySQL 5.1.23.
So, how to proceed? The rationale: We started with a code name for the project and want the database name now to reflect the definitive name of the project.
Oct 27, 2010 at 7:35
1,087 1 1 gold badge 10 10 silver badges 19 19 bronze badges
this blog post by Ilan Hazan:
In MySQL there is no support for database renaming.
In order to rename a MySQL database you can do one of the following:
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.table1, db_name.table2 TO new_db_name.table2;
DROP database db_name;
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 perform 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"
Write a simple Linux script (my favorite solution)
mysqlconn="mysql -u $dbuser -p$dbpass -h localhost"
$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
sql="RENAME TABLE \`$olddb\`.\`$name\` to \`$newdb\`.\`$name\`"
$mysqlconn -e "$sql";
echo "Renamed $olddb.$name to $newdb.$name";
#$mysqlconn -e "DROP DATABASE \`$olddb\`"
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.
MySQL kinda sucks for this. The only solid reliable solution is to use
Operations --> find
Rename database to: --> write
As simple as that. All permissions are carried over.
53.7k 32 32 gold badges 135 135 silver badges 209 209 bronze badges
Sep 27, 2012 at 15:40
I tend to create a new database, and then dump the tables out of the old one, into a .sql file (with mysqldump), edit the file, do some kind of
s/old_database/new_database/g and then reimport it into the new db.
Probably not the best way to do it, but it does work.
Oct 27, 2010 at 7:42
Tom O'Connor Tom O'Connor
27.5k 10 10 gold badges 72 72 silver badges 148 148 bronze badges
I used following method to rename the database
take backup of the file using mysqldump or any DB tool eg heidiSQL,mysql administrator etc
Open back up (eg backupfile.sql) file in some text editor.
Search and replace the database name and save file.
Restore the edited sql file
Sep 11, 2013 at 5:26
If you have chance to use a MySQL Management-Tool (e.g. phpMyAdmin) then you can rename it easily as they create the query for you.
In phpMyAdmin they also create each table and insert the data by "INSERT INTO... SELECT * FROM...". So by chaining they copy the data over.
If you can't do this I would recommend to make a dump and re-import the sql-File into a new database.
Oct 27, 2010 at 8:53
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our
Accept all cookies
Necessary cookies only