My (SQL) WorkLog: MySQL - Renaming database

Friday, June 19, 2009

MySQL - Renaming database

You would hear a big "NO" if you ask some some one about the command to RENAME the DATABASE.

Sometime ago MySQL tried to include the SQL command(This statement was added in MySQL 5.1.7) to rename the database "RENAME {DATABASE | SCHEMA} db_name TO new_db_name;"

but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names . However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

Then is there any workaround for this? yes there couple of ways to do this.. below suggestion are given by some of MySQL experts on online forums which I'm including here


A) use mysqldump to dump the database, create the new
database, reload the dump file into the new database, then drop the old
database.  That is, in effect, a database rename, although (I suspect)
it can cause problems if you have foreign key relationships that refer
to the table names in the original database.


B) Another approach is to create the new database, and then, for each
table in the original database, use RENAME TABLE orig_db.t TO new_db.t
to move the table from one database to the other.  Then drop the old
database.

C) Some ppl suggest this way ( NOT A SAFE-WAY though if you have mixed engines)

i) Bring down MySQL if it is running
ii) rename database directory using OS commands
iii) Bring up MySQL

1 comment:

Guess said...

Ushastry, Thanks a ton for putting all the workarounds for renaming DATABASEs.

Post a Comment