How do I quickly rename a MySQL database (change schema name)?

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

OR

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

• 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:

mysqldump -u username -p -v olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql

Or to reduce I/O use the following as suggested by @Pablo Marin-Garcia:

mysqladmin -u username -p create newdatabase
mysqldump -u username -v olddatabase -p | mysql -u username -p -D newdatabase

Three options:

1. 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.

2. Create the new database, use CREATE TABLE ... LIKE statements, and then use INSERT ... SELECT * FROM statements.

3. Use mysqldump and reload with that file.

The simple way

Change to the database directory:

cd /var/lib/mysql/

Shut down MySQL... This is important!

/etc/init.d/mysql stop

Okay, this way doesn't work for InnoDB or BDB-Databases.

Rename database:

mv old-name new-name

...or the table...

cd database/

mv old-name.frm new-name.frm

mv old-name.MYD new-name.MYD

mv old-name.MYI new-name.MYI

Restart MySQL

/etc/init.d/mysql start

Done...

OK, this way doesn't work with InnoDB or BDB databases. In this case you have to dump the database and re-import it.

Steps :

1. Hit http://localhost/phpmyadmin/
2. Select your DB
3. Click on Operations Tab
4. There will be a tab as "Rename database to". Add new name and check Adjust privileges.
5. Click on Go.

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.

When you rename a database in PHPMyAdmin it creates a dump, then drops and recreates the database with the new name.

Seems noone mentioned this but here is another way:

create database NewDatabaseName like OldDatabaseName;

then for each table do:

create NewDatabaseName.tablename like OldDatabaseName.tablename;
insert into NewDataBaseName.tablename select * from OldDatabaseName.tablename;

then, if you want to,

drop database OldDatabaseName;

This approach would have the advantage of doing the entire transfer on server with near zero network traffic, so it will go a lot faster than a dump/restore.

If you do have stored procedures/views/etc you might want to transfer them as well.

For mac users, you can use Sequel Pro (free), which just provide the option to rename Databases. Though it doesn't delete the old DB.

once open the relevant DB just click: Database --> Rename database...

Here is a batch file I wrote to automate it from the command line, but it for Windows/MS-DOS.

Syntax is rename_mysqldb database newdatabase -u [user] -p[password]

:: ***************************************************************************
:: FILE: RENAME_MYSQLDB.BAT
:: ***************************************************************************
:: DESCRIPTION
:: This is a Windows /MS-DOS batch file that automates renaming a MySQL database 
:: by using MySQLDump, MySQLAdmin, and MySQL to perform the required tasks.
:: The MySQL\bin folder needs to be in your environment path or the working directory.
::
:: WARNING: The script will delete the original database, but only if it successfully
:: created the new copy. However, read the disclaimer below before using.
::
:: DISCLAIMER
:: This script is provided without any express or implied warranties whatsoever.
:: The user must assume the risk of using the script.
::
:: You are free to use, modify, and distribute this script without exception.
:: ***************************************************************************

:INITIALIZE
@ECHO OFF
IF [%2]==[] GOTO HELP
IF [%3]==[] (SET RDB_ARGS=--user=root) ELSE (SET RDB_ARGS=%3 %4 %5 %6 %7 %8 %9)
SET RDB_OLDDB=%1
SET RDB_NEWDB=%2
SET RDB_DUMPFILE=%RDB_OLDDB%_dump.sql
GOTO START

:START
SET RDB_STEP=1
ECHO Dumping "%RDB_OLDDB%"...
mysqldump %RDB_ARGS% %RDB_OLDDB% > %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=2
ECHO Creating database "%RDB_NEWDB%"...
mysqladmin %RDB_ARGS% create %RDB_NEWDB%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=3
ECHO Loading dump into "%RDB_NEWDB%"...
mysql %RDB_ARGS% %RDB_NEWDB% < %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=4
ECHO Dropping database "%RDB_OLDDB%"...
mysqladmin %RDB_ARGS% drop %RDB_OLDDB% --force
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
SET RDB_STEP=5
ECHO Deleting dump...
DEL %RDB_DUMPFILE%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR_ABORT
ECHO Renamed database "%RDB_OLDDB%" to "%RDB_NEWDB%".
GOTO END

:ERROR_ABORT
IF %RDB_STEP% GEQ 3 mysqladmin %RDB_ARGS% drop %NEWDB% --force
IF %RDB_STEP% GEQ 1 IF EXIST %RDB_DUMPFILE% DEL %RDB_DUMPFILE%
ECHO Unable to rename database "%RDB_OLDDB%" to "%RDB_NEWDB%".
GOTO END

:HELP
ECHO Renames a MySQL database.
ECHO Usage: %0 database new_database [OPTIONS]
ECHO Options: Any valid options shared by MySQL, MySQLAdmin and MySQLDump.
ECHO          --user=root is used if no options are specified.
GOTO END    

:END
SET RDB_OLDDB=
SET RDB_NEWDB=
SET RDB_ARGS=
SET RDB_DUMP=
SET RDB_STEP=

For your convenience, below is a small shellscript that has to be executed with two parameters: db-name and new db-name.

You might need to add login-parameters to the mysql-lines if you don't use the .my.cnf-file in your home-directory. Please make a backup before executing this script.

#!/usr/bin/env bash

mysql -e "CREATE DATABASE $2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
for i in $(mysql -Ns $1 -e "show tables");do
    echo "$1.$i -> $2.$i"
    mysql -e "rename TABLE $1.$i to $2.$i"
done
mysql -e "DROP DATABASE $1"

There is a reason you cannot do this. (despite all the attempted answers)

• Basic answers will work in many cases, and in others cause data corruptions.
• A strategy needs to be chosen based on heuristic analysis of your database.
• That is the reason this feature was implemented, and then removed. [doc]

You'll need to dump all object types in that database, create the newly named one and then import the dump. If this is a live system you'll need to take it down. If you cannot, then you will need to setup replication from this database to the new one.

If you want to see the commands that could do this, @satishD has the details, which conveys some of the challenges around which you'll need to build a strategy that matches your target database.

In MySQL Administrator do the following:

1. Under Catalogs, create a new database schema.
2. Go to Backup and create a backup of the old schema.
3. Execute backup.
4. Go to Restore and open the file created in step 3.
5. Select 'Another Schema' under Target Schema and select the new database schema.
6. Start Restore.
7. Verify new schema and, if it looks good, delete the old one.

Here is a one-line Bash snippet to move all tables from one schema to another:

history -d $((HISTCMD-1)) && mysql -udb_user -p'db_password' -Dold_schema -ABNnqre'SHOW TABLES;' | sed -e's/.*/RENAME TABLE old_schema.`&` TO new_schema.`&`;/' | mysql -udb_user -p'db_password' -Dnew_schema

The history command at the start simply ensures that the MySQL commands containing passwords aren't saved to the shell history.

Make sure that db_user has read/write/drop permissions on the old schema, and read/write/create permissions on the new schema.

If you prefer GUI tools and happen to have MySQL Workbench installed, you can use the built-in Migration Wizard

UPDATE `db`SET Db = 'new_db_name' where Db = 'old_db_name';

Quickest and simplest solution i can give is...in MySql Workbench right click on your schema -> Click on create schema -> Enter name for that schema.

Drop your old schema with old name.

You are ready to rock....

NOTE :: For your local purpose only do this. Do not try at production database tables. Schema is created but there is no data in it. So be careful.

The simple way

ALTER DATABASE `oldName` MODIFY NAME = `newName`;

or you can use online sql generator