Mysql – How to rename a MySQL database

MySQL

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.

Best Answer

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:

  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.table1, db_name.table2 TO new_db_name.table2;
 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 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"

  3. 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
       $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
       echo "Renamed $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:

    1. Shut down the MySQL server,
    2. Rename the database folder name to the new name,
    3. Start the MySQL server.
Related Topic