How to Rename a MySQL Database
As an administrator, you may need to change the name of a database. However, for security, the command to rename a database directly was removed in MySQL 5.1.23.
This guide provides three options to rename a MySQL database.
- The cPanel server management software (optional)
- An SSH login to the server, if working remotely
- A user account with sudo or root privileges
- Access to the command line/terminal window
- A user account and password for the MySQL database
Rename a MySQL Database Using cPanel
Servers configured with cPanel offer the easiest way to rename a MySQL database.
1. Log in to cPanel.
2. In the Databases section, click MySQL Databases.
3. A new page will open. Scroll down to the database you want to rename and select the Rename link under the Actions column.
4. Type the new database name, then click Proceed.
Rename MySQL Database from Command Line
If you’re working on a server that doesn’t support cPanel, you’ll need to create a new database and import the data.
1. Log into the server, and open a command line / terminal window. (If you’re working remotely, connect to the server via SSH.)
2. Create a dump file for the database:
Replace [UserName] and [Password] with the actual credentials for the database, and replace [DB_Name] with the exact name of the database you’re changing. There should be no space between -p and the password. The -R flag indicates that the dump file should retain all stored procedures and functions.
You may want to copy this file to a different location as a backup.
3. Create a new blank database by using the mysqladmin command:
Note: Make sure the database name isn’t already in use.
4. Import the dump file into the new database you created:
5. Delete the old MySQL database name (optional):
It won’t hurt if you skip this step. However, it can help you keep a clean database environment.
Renaming Tables with InnoDB
The InnoDB storage engine is a feature included in all versions of MySQL since MySQL 5.5. It can be used to create a new database, then rename each table from the old database to the new database.
1. Start by creating a new database:
Replace [UserName] with the database username, and [Password] with the password for that account. Replace [New_DB_Name] with any name you’d like.
2. Use a script to rename all the tables in the database:
The script above cycles through each table in the database and renames it. Provide your password in the script to avoid having to enter it for each cycle.
3. If you have a very small database, you can move the tables manually. This can be less intimidating than running a script, but time-consuming. Start by logging into the MySQL shell:
Use the RENAME TABLE command to rename a table:
Instead [Table1], type the name of a table in the existing [DB_Name] database. If you have more than one table in this database, you’ll need to repeat this action for each table.
By following this guide, you should now know how to rename a MySQL database.
When working with an older version of MySQL (5.1.7 and older), you may have the ability to use the RENAME DATABASE command. It is strongly recommended that you update your database for security and stability, and use the renaming methods in this guide.
How to rename a MySQL database?
The MySQL online manual has said about the RENAME DATABASE command (this documentation page has been removed by Oracle some time ago):
This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.
So, how to proceed? The rationale: We started with a code name for the project and want the database name now to reflect the definitive name of the project.
6 Answers 6
From this blog post by Ilan Hazan:
In MySQL there is no support for database renaming. In order to rename a MySQL database you can do one of the following:
Create new database and rename all tables in the old database to be in the new database:
In Linux shell, use mysqldump to back up the old database, then restore the dumped database under a new name using the MySQL utility. Finally, use the drop database command to drop the old database. This option can perform badly for large database.
Write a simple Linux script (my favorite solution)
If all your tables are MyISAM, you can rename the old database folder name:
How do I rename a MySQL database (change schema name)?
How do I quickly rename a MySQL database (change its schema name)?
Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME
This needs to work with InnoDB, which stores things very differently than MyISAM.
46 Answers 46
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
-
There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:
mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
Use these few simple commands:
Or to reduce I/O use the following as suggested by @Pablo Marin-Garcia:
I think the solution is simpler and was suggested by some developers. phpMyAdmin has an operation for this.
From phpMyAdmin, select the database you want to select. In the tabs there’s one called Operations, go to the rename section. That’s all.
It does, as many suggested, create a new database with the new name, dump all tables of the old database into the new database and drop the old database.
You can use SQL to generate an SQL script to transfer each table in your source database to the destination database.
You must create the destination database before running the script generated from the command.
You can use either of these two scripts (I originally suggested the former and someone «improved» my answer to use GROUP_CONCAT . Take your pick, but I prefer the original):
($1 and $2 are source and target respectively)
This will generate a SQL command that you’ll have to then run.
Note that GROUP_CONCAT has a default length limit that may be exceeded for databases with a large number of tables. You can alter that limit by running SET SESSION group_concat_max_len = 100000000; (or some other large number).
Emulating the missing RENAME DATABASE command in MySQL:
Create a new database
Create the rename queries with:
Run that output
Delete old database
You may use this shell script:
Create the new database, bring down the server, move the files from one database folder to the other, and restart the server. Note that this will only work if ALL of your tables are MyISAM.
Create the new database, use CREATE TABLE . LIKE statements, and then use INSERT . SELECT * FROM statements.
Use mysqldump and reload with that file.
The simple way
Change to the database directory:
Shut down MySQL. This is important!
Okay, this way doesn’t work for InnoDB or BDB-Databases.
OK, this way doesn’t work with InnoDB or BDB databases. In this case you have to dump the database and re-import it.
/Library/LaunchAgents/homebrew.mxcl.mysql.plist cd /usr/local/var/mysql mv old-name new-name launchctl load -w
Simplest bullet-and-fool-proof way of doing a complete rename (including dropping the old database at the end so it’s a rename rather than a copy):
Steps:
- Copy the lines into Notepad.
- Replace all references to «olddbname», «newdbname», «mypassword» (+ optionally «root») with your equivalents.
- Execute one by one on the command line (entering «y» when prompted).
I’ve only recently came across a very nice way to do it, works with MyISAM and InnoDB and is very fast:
I don’t remember where I read it but credit goes to someone else not me.
This is what I use:
MySQL does not support the renaming of a database through its command interface at the moment, but you can rename the database if you have access to the directory in which MySQL stores its databases. For default MySQL installations this is usually in the Data directory under the directory where MySQL was installed. Locate the name of the database you want to rename under the Data directory and rename it. Renaming the directory could cause some permissions issues though. Be aware.
Note: You must stop MySQL before you can rename the database
I would recommend creating a new database (using the name you want) and export/import the data you need from the old to the new. Pretty simple.
Well there are 2 methods:
Method 1: A well-known method for renaming database schema is by dumping the schema using Mysqldump and restoring it in another schema, and then dropping the old schema (if needed).
Although the above method is easy, it is time and space consuming. What if the schema is more than a 100GB? There are methods where you can pipe the above commands together to save on space, however it will not save time.
To remedy such situations, there is another quick method to rename schemas, however, some care must be taken while doing it.
Method 2: MySQL has a very good feature for renaming tables that even works across different schemas. This rename operation is atomic and no one else can access the table while its being renamed. This takes a short time to complete since changing a table’s name or its schema is only a metadata change. Here is procedural approach at doing the rename:
Create the new database schema with the desired name. Rename the tables from old schema to new schema, using MySQL’s “RENAME TABLE” command. Drop the old database schema. If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too . MySQL’s “RENAME TABLE” fails if there are triggers exists on the tables. To remedy this we can do the following things :
1) Dump the triggers, events and stored routines in a separate file. This done using -E, -R flags (in addition to -t -d which dumps the triggers) to the mysqldump command. Once triggers are dumped, we will need to drop them from the schema, for RENAME TABLE command to work.
2) Generate a list of only “BASE” tables. These can be found using a query on information_schema.TABLES table.
3) Dump the views in an out file. Views can be found using a query on the same information_schema.TABLES table.
4) Drop the triggers on the current tables in the old_schema.
5) Restore the above dump files once all the “Base” tables found in step #2 are renamed.
Intricacies with above methods : We may need to update the GRANTS for users such that they match the correct schema_name. These could fixed with a simple UPDATE on mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db tables updating the old_schema name to new_schema and calling “Flush privileges;”. Although “method 2″ seems a bit more complicated than the “method 1″, this is totally scriptable. A simple bash script to carry out the above steps in proper sequence, can help you save space and time while renaming database schemas next time.
The Percona Remote DBA team have written a script called “rename_db” that works in the following way :
To demonstrate the use of this script, used a sample schema “emp”, created test triggers, stored routines on that schema. Will try to rename the database schema using the script, which takes some seconds to complete as opposed to time consuming dump/restore method.
As you can see in the above output the database schema “emp” was renamed to “emp_test” in less than a second. Lastly, This is the script from Percona that is used above for “method 2″.
Renaming database schema in MySQL
One of the routine tasks for a DBA is MySQL renaming database schemas, and as such MySQL added a command to carry out that purpose called “RENAME DATABASE <database_name>”. However, this command just made it through a few minor releases before being discontinued (from MySQL 5.1.7 to 5.1.23). Here’s a link to the reference manual regarding the command http://dev.mysql.com/doc/refman/5.1/en/rename-database.html. Vadim wrote a MySQL Performance Blog post about this a few years ago where he mentions the dangerous nature of this command – that post was appropriately headlined, “Dangerous Command.” Today we will see what are the ways in which a database schema can be renamed and which of them is the quickest.
How to Rename MySQL Databases
Method 1: A well-known method for renaming database schema is by dumping the schema using Mysqldump and restoring it in another schema, and then dropping the old schema (if needed).
] # mysqldump emp > emp.out
] # mysql -e «CREATE DATABASE employees;»
] # mysql employees < emp.out
] # mysql -e «DROP DATABASE emp;»
Although the above method is easy, it is time and space consuming. What if the schema is more than a 100GB? There are methods where you can pipe the above commands together to save on space, however, it will not save time.
To remedy such situations, there is another quick method to rename schemas, however, some care must be taken while doing it.
Method 2: MySQL has a very good feature for renaming tables that even works across different schemas. This rename operation is atomic and no one else can access the table while it is being renamed. This takes a short time to complete since changing a table’s name or its schema is only a metadata change. Here is the procedural approach at doing the rename:
- a) Create the new database schema with the desired name.
- b) Rename the tables from old schema to the new schema, using MySQL’s “RENAME TABLE” command.
- c) Drop the old database schema.
If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too. MySQL’s “RENAME TABLE” fails if there are triggers exists on the tables. To remedy this we can do the following things :
1) Dump the triggers, events and stored routines in a separate file. This is done using -E, -R flags (in addition to -t -d which dumps the triggers) to the mysqldump command. Once triggers are dumped, we will need to drop them from the schema, for RENAME TABLE command to work.