In some cases, it may be desirable to quickly alter the name of a MySQL database. While there used to be a simple RENAME DATABASE command in older versions of MySQL that was intended to perform this task, RENAME DATABASE has 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 securely rename your MySQL database.
Dump and reimport
When working with a relatively small database, the fastest method is usually to use the mysqldump shell command
to create a dumped copy of the entire database, and then import all the data into the newly created database with the correct name.
Start by issuing the following mysqldump command from your shell prompt, replacing the appropriate username, password and oldDbName values. 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.
We are using the -p prompt immediately followed by our password to connect to the database (no space between them) and avoid password entry requests when issuing these commands. Be sure to leave surrounding appointments because passwords with unique characters can cause execution problems. The -R flag is also important and tells mysqldump to copy the stored procedures and functions along with the normal database data.
Next, use the
mysqladmin command to create a
new database: Finally, with the new database
created, use mysql to import the dump file we created into the
new database.
Three basic commands and their new database have been created. Once you have verified that everything is as planned, you can proceed to delete the old database.
Renaming tables with InnoDB If you’re using
MySQL version 5.5 (or higher), chances are you’re using the InnoDB storage engine, which makes the task
of renaming databases fairly simple.
In short, you can use the RENAME TABLE command inside a MySQL command prompt to effectively rename the database of a particular table while keeping the table name intact. However, doing so requires that the database with the new name already exists, so start 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 would first create
the new library database:
Now connect to the mysql command prompt and issue the following MySQL RENAME TABLE statement for a table of your choice:
We have just moved the entire table of books from the database
from catalog data to our new library database. This command can be run 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 will not be very practical or efficient, but fortunately 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:
Therefore, for our change from the old catalog database to the
new library database, we would change the statement as follows
:
We’ve also added some flags to our commands:
-s is the
- flag for silent mode, so there is less output in
- N prevents column name output from the results
- indicates that the statement following the -e prompt should be executed, and then the shell closes. This means that the ‘show tables’ and ‘RENAME TABLE catalog.$table TO library.$table’ statements are executed as normal SQL statements, as desired.
the shell. –
. -e
That’s all there is to it. Your MySQL database has now been effectively renamed.