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.
- 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. - [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.
- 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. - [Stop Apache with
httpd -k stop
] - Stop MySQL with
service mysqld stop
- 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/* .
Overridedb.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. - MySQL engine must have full access to these files, that is why we need to check file attributes. They should look like this:
Set owner, owner group, and file permissions for all the files under thenew_db
folder. Set permissions torw-rw----
Make sure to highlight all the files before doing that. - Start MySQL server with
service mysqld start
but do not start Apache yet. - [Optional] Run database checks to make sure everything is fine, as described here.
- [Start Apache with
httpd -k start
] - Drop the old database and it’s now done!
2,812 total views, 2 views today