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
MySQL Quiz
16 Comments
  1. Sorry for double-posting. Do note that ALTER DATABASE does not work properly, which is the reason for the above link. See documentation here.

  2. VadimTK says:

    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

  3. 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

  4. huarong says:

    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;

  5. Chaos says:

    I used example one. Works perfect and easy… End of issue.

  6. Jeff says:

    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.

  7. Anuj K Singh says:

    Rename Database syntax is not working with MYSQL5.0 version

  8. 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%”

  9. Marc says:

    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

  10. MattP says:

    Please note that you’ll have to recreate grants according to the new names (sorry if it’s obvious!).

  11. Neil says:

    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

  12. Doug says:

    Very nice option 1, and illustrates the underlying container structure too.

Leave a Reply

*