Renaming databases

The best way to rename a database is to use the mysqldump command. Dump all the tables, and reimport them into the new database name.

You may try the "rename directory" method described below if your tables are all MyISAM.

There is no "ALTER DATABASE RENAME ..." command within MySQL, however, renaming a database is still possible. The database must contain MyISAM tables only, so before attempting this, issue a "SHOW TABLE STATUS" first, checking the "Engine" column for each table before proceeding.

Please don't forget to check that no privileges are referencing the database you want to move. The following query should not return any rows (after you replaced db_name by the name of the database):

SELECT db FROM mysql.db WHERE db = 'db_name' UNION
SELECT db FROM mysql.tables_priv WHERE db = 'db_name' UNION
SELECT db FROM mysql.columns_priv WHERE db = 'db_name';
Empty set (0.00 sec)

Once satisfied that all tables are using the MyISAM storage engine, shutdown MySQL, rename the actual database directory, and restart MySQL.

The following is an example of the procedure run on Windows:

C:\Program Files\MySQL\MySQL Server 5.0\data>dir /ad
Volume in drive C is XP
Volume Serial Number is 3890-00D2
Directory of C:\Program Files\MySQL\MySQL Server 5.0\data
16/02/2005  19:51    <DIR>          .
16/02/2005  19:51    <DIR>          ..
16/02/2005  19:52    <DIR>          foo
14/12/2004  21:27    <DIR>          mysql
11/02/2005  00:20    <DIR>          test
              0 File(s)              0 bytes
              5 Dir(s)  30,498,512,896 bytes free

Here we can see the directories for the "foo", "mysql" and "test" databases.

C:\Program Files\MySQL\MySQL Server 5.0\data>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.2-alpha-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Login to MySQL to check the table engines.

mysql> use foo;
Database changed
mysql> show table status;
+------+--------+
| Name | Engine | 
+------+--------+
| x    | MyISAM |
| y    | MyISAM |
+------+--------+
2 rows in set (0.02 sec)

We will rename the "foo" database, so USE that and SHOW TABLE STATUS (output cropped for readability).

mysql> Aborted

Press CTRL+C to abort.

C:\Program Files\MySQL\MySQL Server 5.0\data>net stop mysql5
The MySQL5 service is stopping..
The MySQL5 service was stopped successfully.

Stop the MySQL service.

C:\Program Files\MySQL\MySQL Server 5.0\data>rename foo bar

Rename the directory.

C:\Program Files\MySQL\MySQL Server 5.0\data>dir /ad
Volume in drive C is XP
Volume Serial Number is 3890-00D2
Directory of C:\Program Files\MySQL\MySQL Server 5.0\data
16/02/2005  19:56    <DIR>          .
16/02/2005  19:56    <DIR>          ..
16/02/2005  19:52    <DIR>          bar
14/12/2004  21:27    <DIR>          mysql
11/02/2005  00:20    <DIR>          test
              0 File(s)              0 bytes
              5 Dir(s)  30,498,512,896 bytes free

Check to be sure ;)

C:\Program Files\MySQL\MySQL Server 5.0\data>net start mysql5
The MySQL5 service is starting.
The MySQL5 service was started successfully.

Restart the MySQL service.

C:\Program Files\MySQL\MySQL Server 5.0\data>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Login to MySQL again to confirm change.

mysql> show databases;
+----------+
| Database |
+----------+
| bar      |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

You now have a renamed database!

mysql> use bar
Database changed
mysql> show tables;
+---------------+
| Tables_in_bar |
+---------------+
| x             |
| y             |
+---------------+
2 rows in set (0.00 sec)

With the tables still intact!