How to rename a MySQL database?

Solution 1:

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.

Solution 2:

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.