Rename MySQL database

111

I created a database with the name of hrms. Now I need to change database name to sunhrm. But, It is disabled in MySQL workbench. Can I do that on the Linux server itself?

This question is tagged with mysql database linux rename

~ Asked on 2012-08-30 04:41:22

The Best Answer is


61

I don't think you can do this. Basic answers will work in many cases, and in others cause data corruptions. A strategy needs to be chosen based on heuristic analysis of your database. That is the reason this feature was implemented, and then removed. [doc]

You'll need to dump all object types in that database, create the newly named one and then import the dump. If this is a live system you'll need to take it down. If you cannot, then you will need to setup replication from this database to the new one.

If you want to see the commands that could do this, @satishD has the details, which conveys some of the challenges around which you'll need to build a strategy that matches your target database.

~ Answered on 2012-08-30 04:59:34


68

In case you need to do that from the command line, just copy, adapt & paste this snippet:

mysql -e "CREATE DATABASE \`new_database\`;"
for table in `mysql -B -N -e "SHOW TABLES;" old_database`
do 
  mysql -e "RENAME TABLE \`old_database\`.\`$table\` to \`new_database\`.\`$table\`"
done
mysql -e "DROP DATABASE \`old_database\`;"

~ Answered on 2012-12-19 03:05:40


Most Viewed Questions: