Wednesday, December 09, 2009

Renaming a MySQL database without having access to datafiles

If you've used MySQL then you will know this situation; your database name is wrong & MySQL does not support any kind of command like

rename database my_db to my_real_name;

In fact the only way to do this is to rename the directory name the database files are held in on the data store; or so I thought. Turns out that the rename table command comes to the rescue with this beautiful line
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:

Giving us something quite simple looking like this:

RENAME TABLE my_db.tbl_name TO my_real_name.tbl_name;

Pin this together with a loop over all tables in a schema & bob is your uncle you've got a database re-namer without going anywhere near the datafiles. The thing that confuses me is why MySQL dropped support for the rename function (which is still documented) when they could have implemented it like this; I'm sure they had their reasons.

1 comment:

Jody Clements said...

Looks like a nice way to get around the problem. Better than dumping the data out and reloading it.