Thursday, August 27, 2009

MySQL Rename database

SkyHi @ Thursday, August 27, 2009
Method 1:
RENAME DATABASE old_name TO new_name;

ERROR: You have an error in your SQL syntax; check manual that corresponds to your mysql version for right syntax.

First check whether it supports or not.

Method 2:
you can rename your database in phpMyAdmin by selecting the database you want to edit, then clicking on the "Operations" tab on the top. From here there is an option to "Rename database to:"

I would suggest u copy the database that you are renaming in case something goes haywire

Method 3:
The safest way is to use mysqldump to back up the old database, then restore the dump

Provided that you can shutdown anything which is updating the database (so that there is no chance of anything changing), this is a good solution:

mysql -p

create database new_database;

mysqldump old_database -p | mysql -D new_database -p

It'll dump out the old database to STDOUT, pipe this to a second MySQL process and insert the dumped data (and schema) to the new DB.

You'll have to manually DROP the old DB and change the permissions on the new DB, but at least you have the data and schema.

I find this very useful for creating 'live' backups of databases.