Changing the name of a MySQL database
MySQL doesn't currently support altering the name of a database using SQL so here's a workaround.
Support added then removed ¶
Support for renaming databases in MySQL was added in version 5.1.7 and then removed in 5.1.23 as the RENAME statement was found to result in the loss of database contents. So what if you need to rename a database?
MySQL Administrator to the rescue ¶
Using MySQL administrator you can backup the database. MySQL Administrator is a free GUI for administering MySQL databases. This gives you a SQL file with a full backup of the database. To run the back up click backup and then select the database you want to rename. Save the file to a location where you can edit it.
Next fire up at text editor that allows you to Find and Replace. My choice is TextMate. Open the SQL file you have just created and invoke the find and replace dialog. Then simply search for the old name and replace it with the new name. Eyeball the SQL statements to check that everything has gone to plan. Save the file again.
Return to MySQL administrator and this time use the restore utility. Choose the SQL file you have just saved and import it. Job done! If you now refresh the list of databases you will see a copy of the old database with the new name.
Don’t forget privileges! ¶
Don’t forget that you will need to assign privileges to the user to access and use the new database. Do this in the normal way by clicking Accounts and then assigning rights to the user.
Tags
Can you help make this article better? You can edit it here and send me a pull request.