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?
+2
A:
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
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.
Stefan Gehrig
2009-03-27 11:53:54
+3
A:
The best way is probably to rename each of the tables inside the database to the new name. For example:
RENAME TABLE accounts.tablename TO newaccounts.tablename;
See http://dev.mysql.com/doc/refman/5.0/en/rename-table.html for more information.
We're talking about renaming a DATABASE... Renaming a table is no problem at all.
Stefan Gehrig
2009-03-27 11:57:08
thats what he says: RENAME TABLE database_name.table_name TO new_database_name.table_name
ax
2009-03-27 11:58:58
That's correct - didn't think of that possibility. This method perhaps generate some issues with open transactions or current locks on that table. But nevertheless it's a solution. Sorry for my first comment!
Stefan Gehrig
2009-03-27 12:09:27
from the mysql docs: As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another: `RENAME TABLE current_db.tbl_name TO other_db.tbl_name;`Any privileges granted specifically for the renamed table or view are not migrated to the new name. They must be changed manually.
Pablo Marin-Garcia
2010-09-29 16:02:34