Rename MySQL Database

No matter how far ahead you can see and how good you are in predicting the future, you will need to rename MySQL database sooner or later. Unfortunately, ALTER DATABASE statement does not have syntax to simply rename the database (as of today; correct me if I am wrong). The most common way found on the internet is to use ALTER TABLE command which allows to change table name. Since database name is part of the table name, database name can be changes as well.

So, to rename database this way, one needs to create new database, and then run ALTER TABLE command for every table. If there are quite a few tables, it is possible to get list of all tables and create PHP script that would execute all those commands one by one. However, programmers are lazy – is there an easier way to do that? That simple PHP script mentioned above will turn out not so simple once MySQL error checking is implemented. Testing script is time consuming as well since every mistake will mean complete rollback of the whole database.

The method below is much simpler but it only works for MyISAM tables. MyISAM engine stores every table as three physical files, hence we can access them. InnoDB stores tables in a different way.

Another downside of this method is complete shutdown of MySQL server. If using ALTER TABLE way, MySQL server would still continue serving all other databases, not involved in the renaming process. However, I still believe the method below is useful to know about.

WARNING While I do hope this info is extremely helpful to solve unsolvable, use it with great care.
As with any web page, use it as a guide but not as a substitute for your own brain.
Always know what you are doing.
  1. Login to MySQL and create new database (together the with database user, if required). The CREATE DATABASE statement should ideally be identical to the use you used to create the old database.
  2. [Optional] If new database will be accessed with the existing database user (the same which is used to access the old database), do not forget to grant this existing user the same level of privileges as for the old database. Find out how to list MySQL databases, users, and permissions.
  3. Navigate to the folder for the new database. If there is no folder for the database you have just created, most likely, you are using InnoDB engine. Usually, the path will be /var/lib/mysql/, so the following statement could be useful: 
    cd /var/lib/mysql/new_db
    The folder should contain one file, db.opt. Compare the content of this file to the one from the old database. They should match. If they don’t, come back to step 1. Database parameters defined in these files should be identical.
  4. [Stop Apache with httpd -k stop]
  5. Stop MySQL with service mysqld stop
  6. Physically copy all the files from the old database to the new. MySQL engine constantly accesses files, that is why it was necessary to shut it down. This command could be useful: 
    cp  -p  ../old_db/*  .
    Override db.opt when it asks. Option -p is supposed to preserve file attributes. However, it will not work if you copy files from your local machine, that’s why we need the next step.
  7. MySQL engine must have full access to these files, that is why we need to check file attributes. They should look like this:
    Setting file attributes
    Set owner, owner group, and file permissions for all the files under the new_db folder. Set permissions to rw-rw---- Make sure to highlight all the files before doing that.
  8. Start MySQL server with service mysqld start but do not start Apache yet.
  9. [Optional] Run database checks to make sure everything is fine, as described here.
  10. [Start Apache with httpd -k start]
  11. Drop the old database and it’s now done!

2,889 total views, 2 views today