If you’re using MySQL version 5.5 (or greater), you are likely using the InnoDB storage engine, which makes the task of renaming databases quite simple.
In short, you can use the RENAME TABLE command within a MySQL prompt to effectively change the database name of a particular table while keeping the table name intact. However, doing so requires that the database with the new name already exists, so begin by creating a new database using the
mysqladmin shell command as seen above.
For example, if we already have a
catalog database that we want to rename to
library, we’d first create the new
Now connect to the
mysql prompt and issue the following MySQL
RENAME TABLE statement for a table of your choice:
We’ve just moved the entirety of the
books table from the
catalog database to our new
library database. This command can be executed manually for all relevant tables as desired, or we can simplify the task with a shell script as seen below.
For all but the smallest databases, manually issuing
RENAME TABLE commands for each table won’t be very practical nor efficient, but thankfully we can use a simple shell command using the
mysql utility to loop through all the tables in our old database and rename them, thus moving them to the new database.
This is the basic structure of the command:
Thus, for our move from the old
catalog database to the new
library database, we’d change the statement as follows:
We’ve added a few flags to our commands as well:
- -s is the flag for
silent mode so there is less output to the shell.
- -N prevents output of column names from the results.
- -e indicates the statement that follows the
-e flag should be executed then the shell is quit. This means the statements
'show tables' and
"RENAME TABLE catalog.$table TO library.$table" are executed as normal SQL statements, as desired.
That’s all there is to it. Your MySQL database is now effectively renamed.