CODEWITHSUNDEEP
mysqlsqlamazon-rds
priya
priya

Reputation: 26699

How to rename an Amazon RDS hosted MySQL database instance

I tried using the rename database command, but it fails with the following error. I don't see an option to stop the RDS instance via the Amazon RDS console either. 

mysql> rename database foo to foo_orig;
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 foo to foo_orig' at line 1

Upvotes: 1

Views: 1733

Answers (2)

aporat
aporat

Reputation: 5932

the command was removed like @ajreal mentioned, however doing any maintenance on the mysql file system is dangerous to say to the least. One small mistake and your mysql service won't start.

The recommended method is to export the entire database into a sql file and import it again under a different name. You can put your site on maintenance mode to make sure no one read or write data while doing this process.

mysqldump -u username -ppassword old_database_name > dump.sql

and then import it to the same server:

mysql -u username -ppassword new_database_name < dump.sql

Upvotes: 2

ajreal
ajreal

Reputation: 47321

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

From http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

Is too little chance Amazon RDS using version less than 5.2.
If you have the access to mysql server, you can try :-

  1. stop mysql daemon
  2. go to the datadir
  3. mv $old_name $new_name
  4. restart mysql daemon
  5. login to mysql database
  6. update all the privileges for your $old_name to $new_name (under table mysql.db)
  7. flush privileges

Upvotes: 2

Related Questions