In some cases it may be desirable to quickly alter the name of a MySQL database. While there used to exist a simple
RENAME DATABASE command in older versions of MySQL which was intended to perform this task,
RENAME DATABASE has since been removed from all newer versions to avoid security risks.
Instead, we’ll briefly explore a handful of optional methods that can be used to quickly and safely rename your MySQL database.
Dumping and Reimporting
When working with a relatively small database, the fastest method is typically to use the
mysqldump shell command to create a dumped copy of the entire database, then import all the data into the newly created database with the proper name.
Begin by issuing the following
mysqldump command from your shell prompt, replacing the appropriate
mysqldump is used to create physical backups of a database, so we can use this copy to import the data back into a new database.
$ mysqldump -u username -p"password" -R oldDbName > oldDbName.sql
We’re using the
-p flag immediately followed by our password to connect to the database (with no space between) and avoid password entry prompts when issuing these commands. Be sure to leave the surrounding quotations because passwords with unique characters may otherwise cause execution issues. The
-R flag is also important and tells
mysqldump to copy stored procedures and functions along with the normal data from the database.
Next use the
mysqladmin command to create a new database:
$ mysqladmin -u username -p"password" create newDbName
Lastly, with the new database created, use
mysql to import the dump file we created into the new database.
$ mysql -u username -p"password" newDbName < oldDbName.sql
Three basic commands and your new database has been created. Once you’ve verified everything is as intended, you can proceed with removing the old database.
Renaming Tables with InnoDB
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
$ mysqladmin -u username -p"password" create library
Now connect to the
mysql prompt and issue the following MySQL
RENAME TABLE statement for a table of your choice:
mysql> RENAME TABLE catalog.books TO library.books;
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.
Using a Shell Command Script
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:
$ mysql -u dbUsername -p"dbPassword" oldDatabase -sNe 'show tables' | while read table; do mysql -u dbUsername -p"dbPassword" -sNe "RENAME TABLE oldDatabase.$table TO newDatabase.$table"; done
Thus, for our move from the old
catalog database to the new
library database, we’d change the statement as follows:
$ mysql -u dbUsername -p"dbPassword" catalog -sNe 'show tables' | while read table; do mysql -u dbUsername -p"dbPassword" -sNe "RENAME TABLE catalog.$table TO library.$table"; done
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.