Monday, April 7, 2008

Renaming a MySQL database

MySQL does not support renaming a database (and prefers not to be done in the supported versions) as mentioned here.

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.
  • But there might be times when you want to do this, and the simple workaround I use is this.
  • Just export the current database. You can use mysqldump or any other GUI tool like HeidiSQL or phpMyAdmin.
  • The dump typically contains SQL statements to create the table, populate it, or both.
  • Then create a database with the name you want, the new database.
  • Then just mysqlimport the exported SQL file to the new database you just created.
  • Final step would be to drop the old database.
Also note,
If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores.
 
This is not big deal and just a matter of simple workaround, thats all. So I'm sure there is no way one could get doubts on this. Still if you get any I might be able to give a hand. Feedback and questions are welcome via comment or you can email me at talkout AT SPAMFREE gmail DOT com

 

1 comment:

srini said...

Hi,
how abt renaming the database in data directory

sudo mv /var/lib/mysql/foo /var/lib/mysql/bar

-srini