How to rename a MySQL database?

Question

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.

All four answers make sense. It's a question with many possible solutions. — nalply, Jun 10 '12 at 08:58
http://stackoverflow.com/questions/12190000/rename-mysql-database/21429268#21429268 — Sathish D, Apr 15 '14 at 04:21
http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name — Sathish D, Apr 15 '14 at 04:22

score 76 · Accepted Answer · edited Jan 09 '23 at 00:54

From 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)

 #!/bin/bash

 dbuser=xxxx
 dbpass=xxxx
 olddb=xxxx
 newdb=xxxx

 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\`" 
       echo "$sql";
       $mysqlconn -e "$sql";
       echo "Renamed $olddb.$name to $newdb.$name";
 done;

 #$mysqlconn -e "DROP DATABASE \`$olddb\`"

If all your tables are MyISAM, you can rename the old database folder name:
1. Shut down the MySQL server,
2. Rename the database folder name to the new name,
3. Start the MySQL server.

Here is an alternate way to generate rename table command in SQL within MySQL directly: http://blog.marceloaltmann.com/how-to-rename-a-database-in-mysql/ — Yves Martin, Aug 14 '13 at 06:02
This will help to create a query for the first solution. Just copy paste results behind RENAME TABLE `SELECT CONCAT('olddb.', TABLE_NAME, ' TO newdb.', TABLE_NAME, ',') FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='olddb';`; — Syclone, Jul 28 '16 at 05:51
But If you do not insert the data in the new DB, they will be lost — rainman, Feb 10 '17 at 11:43
That script is super neat, worked out of the box on MariaDB 10.1. — Luc, Mar 07 '19 at 09:29

score 13 · Answer 2 · edited Sep 30 '12 at 19:38

13

MySQL kinda sucks for this. The only solid reliable solution is to use phpMyAdmin.

Login --> click Scheme --> click Operations --> find Rename database to: --> write NewName > click Go.

As simple as that. All permissions are carried over.

edited Sep 30 '12 at 19:38

HopelessN00b

53,795
33
135
209

answered Sep 27 '12 at 15:40

spencerthayer

139
1
2

8

+1 for a GUI solution, but the simple commands and scripts in the community wiki answer are also perfectly "solid and reliable" – Will Sheppard Oct 15 '14 at 14:43

score 7 · Answer 3 · edited May 23 '17 at 11:33

I found a very simple solution: Shut down MySQL, rename the database directory and restart. That's all!

It's a bit dangerous if you have SQL code or data referring to the old name. Then you need to change that as well before you restart the application. But I didn't need to do that, but YMV.

Googling gives a few pointers like these two:

https://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name

http://www.delphifaq.com/faq/databases/mysql/f574.shtml

score 5 · Answer 4 · answered Oct 27 '10 at 07:42

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.

score 3 · Answer 5 · answered Sep 11 '13 at 05:26

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

score 2 · Answer 6 · answered Oct 27 '10 at 08:53

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.

Good luck!

Regards, Ben.

How to rename a MySQL database?

6 Answers6