How can I change database name in mysql ?

Question

How can I change database name in mysql ?

My Database name is SPM and I want to change it to spm(small letters)

I tried using

RENAME DATABASE SPM TO spm;

and am getting following error message: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE SPM to spm' at line 1

MY Server version: 5.0.45

Answer 1

This is done with a RENAME DATABASE statement:

RENAME DATABASE old_db_name TO new_db_name;

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.

Answer 2

Use rename database command.

You can also try to stop your mysql server and rename a folder that contains your db data to the name you prefer. Then start your server and check the grants - they might still contain references to your old database name.

Answer 3

There is no database command to do it. You basically have to do it outside the database. Below are some references outlining possible solutions. It has been answered pretty good in this question

This is probably what it should look like in your case

mysqladmin create spm
mysqldump SPM | mysql spm

After you have verified that everything is in order you can drop the original database.

drop database SPM

References Rename database 1 / Rename database 2

Answer 4

Use mysql_dump to dump out the database contents of the old schema (it produces SQL output, and can include all the object CREATE statements), switch to the new schema, and execute that script mysql> . dump.sql

If it's a large database, this may take a while, but it's the safest way to do it (make sure you suspend any applications using the database while the conversion process is going on).

Delete the old schema when you're satisfied that everything worked.