How to rename a database in MySQL
Posted by: AJ Welch
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 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.
$ mysqldump -u username -p"password" -R oldDbName > oldDbName.sqlWe’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 newDbNameLastly, 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.sqlThree 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 library database:
$ mysqladmin -u username -p"password" create libraryNow 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"; doneWe’ve added a few flags to our commands as well:
- -s is the flag for
silent modeso there is less output to the shell. - -N prevents output of column names from the results.
- -e indicates the statement that follows the
-eflag 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.