How can we rename the database name in MySQL 5.0

Question

I am using MySQL 5.0.

I have created a database named accounts, but now I want to change the database name to FinanceAccounts.

How can I change the database name in MySQL 5.0?

Answer 1

The best way is probably to rename each of the tables inside the database to the new name. For example:

Update: There are two steps here

1. Create a new blank database as you want say "new accounts"

   CREATE DATABASE newaccounts;

2. Migrate each table one-by-one

   RENAME TABLE accounts.tablename TO newaccounts.tablename;

See http://dev.mysql.com/doc/refman/5.0/en/rename-table.html for more information.

Answer 2

I think there is only one way (besides renaming directory in the MySQL datadir which will fail for InnoDB tables):

• create new database (with new name)
• make dump of old database
• import dumped data into new database
• delete old database

To create the new DB:

mysql> CREATE DATABASE new_database;

To create the dump of the old DB:

mysqldump -u "your_username" -p --lock-tables old_database > old_database_dump.sql

To import dumped data into the new DB:

mysql -u "your username" -p new_database  < old_database_dump.sql

To delete the old DB:

mysql> DROP DATABASE old_database;

Bear in mind that your permissions on the old DB will need to be deleted as well. See here for more info: Revoke all privileges for all users on a MySQL DB

MySQL 5.1.7 to MySQL 5.1.22 had a RENAME {DATABASE | SCHEMA} db_name TO new_db_name; command but this one has been removed in MySQL 5.1.23 for being too dangerous.