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

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.

UPDATE 2022-09-22: MySQL 8.0+ added a more simpler solution:

Not sure since when has been added RENAME TO keywords, but sure is simpler. Though I would do a back up of the table before trying it, especially if you have a very large table. Anyway, it can be implemented as follow:

ALTER TABLE `schema_name`.`table_name` 
RENAME TO  `schema_name`.`new_table_name` ;

I put it in two lines for easy reading but it can go in a single line as follow:

ALTER TABLE `schema_name`.`table_name` RENAME TO `schema_name`.`new_table_name` ;

Emulating the missing RENAME DATABASE command in MySQL:

1. Create a new database

2. Create the rename queries with:

    SELECT CONCAT('RENAME TABLE ',table_schema,'.`',table_name,
        '` TO ','new_schema.`',table_name,'`;')
    FROM information_schema.TABLES
    WHERE table_schema LIKE 'old_schema';
   

3. Run that output

4. Delete old database

It was taken from Emulating The Missing RENAME DATABASE Command in MySQL.

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.

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

mysqladmin -uroot -pmypassword create newdbname
mysqldump -uroot -pmypassword --routines olddbname | mysql -uroot -pmypassword newdbname
mysqladmin -uroot -pmypassword drop olddbname

Steps:

1. Copy the lines into Notepad.
2. Replace all references to “olddbname”, “newdbname”, “mypassword” (+ optionally “root”) with your equivalents.
3. Execute one by one on the command line (entering “y” when prompted).

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.

Most of the answers here are wrong for one of two reasons:

1. You cannot just use RENAME TABLE, because there might be views and triggers. If there are triggers, RENAME TABLE fails
2. You cannot use mysqldump if you want to “quickly” (as requested in the question) rename a big database

Percona has a blog post about how to do this well:
https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/

and script posted (made?) by Simon R Jones that does what is suggested in that post. I fixed a bug I found in the script. You can see it here:

https://gist.github.com/ryantm/76944318b0473ff25993ef2a7186213d

Here is a copy of it:

#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
# Richardseeme https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/
set -e
if [ -z "$3" ]; then
    echo "rename_db <server> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $3"
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = '$2'" -sss`
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
    echo "Error retrieving tables from $2"
    exit 1
fi
echo "create database $3 DEFAULT CHARACTER SET $character_set"
mysql -h $1 -e "create database $3 DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h $1 $2 -e "show triggers\G" | grep Trigger: | awk '{print $2}'`
VIEWS=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
    mysqldump -h $1 $2 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dump
fi
mysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h $1 $2 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
    echo "rename table $2.$TABLE to $3.$TABLE"
    mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database $2"
    mysql -h $1 $2 -e "drop database $2"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sss` -gt 0 ]; then
    TABLES_PRIV="    UPDATE mysql.tables_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='$3' WHERE db='$2';"
fi
if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"
    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
    echo "    flush privileges;"
fi

Save it to a file called rename_db and make the script executable with chmod +x rename_db then use it like ./rename_db localhost old_db new_db

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=

The simplest method is to use HeidiSQL software. It’s free and open source. It runs on Windows and on any Linux with Wine (run Windows applications on Linux, BSD, Solaris and Mac OS X).

To download HeidiSQL, goto http://www.heidisql.com/download.php.

To download Wine, goto http://www.winehq.org/.

To rename a database in HeidiSQL, just right click on the database name and select ‘Edit’. Then enter a new name and press ‘OK’.

It is so simple.

TodoInTX’s stored procedure didn’t quite work for me. Here’s my stab at it:

-- stored procedure rename_db: Rename a database my means of table copying.
-- Caveats: 
-- Will clobber any existing database with the same name as the 'new' database name.
-- ONLY copies tables; stored procedures and other database objects are not copied.
-- Tomer Altman ([email protected])

delimiter //
DROP PROCEDURE IF EXISTS rename_db;
CREATE PROCEDURE rename_db(IN old_db VARCHAR(100), IN new_db VARCHAR(100))
BEGIN
    DECLARE current_table VARCHAR(100);
    DECLARE done INT DEFAULT 0;
    DECLARE old_tables CURSOR FOR select table_name from information_schema.tables where table_schema = old_db;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @output = CONCAT('DROP SCHEMA IF EXISTS ', new_db, ';'); 
    PREPARE stmt FROM @output;
    EXECUTE stmt;

    SET @output = CONCAT('CREATE SCHEMA IF NOT EXISTS ', new_db, ';');
    PREPARE stmt FROM @output;
    EXECUTE stmt;

    OPEN old_tables;
    REPEAT
        FETCH old_tables INTO current_table;
        IF NOT done THEN
        SET @output = CONCAT('alter table ', old_db, '.', current_table, ' rename ', new_db, '.', current_table, ';');
        PREPARE stmt FROM @output;
        EXECUTE stmt;

        END IF;
    UNTIL done END REPEAT;

    CLOSE old_tables;

END//
delimiter ;

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.

ALTER DATABASE is the proposed way around this by MySQL and RENAME DATABASE is dropped.

From 13.1.32 RENAME DATABASE Syntax:

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

This statement was added in MySQL 5.1.7, but it was found to be dangerous and was removed in MySQL 5.1.23.

Here is a quick way to generate renaming sql script, if you have many tables to move.

SELECT DISTINCT CONCAT('RENAME TABLE ', t.table_schema,'.', t.table_name, ' TO ',     
t.table_schema, "_archive", '.', t.table_name, ';' ) as Rename_SQL 
FROM information_schema.tables t
WHERE table_schema='your_db_name' ;

If you are using phpMyAdmin you can go to the “operations” tab once you have selected the database you want to rename. Then go to the last section “copy database to” (or something like that), give a name, and select the options below. In this case, I guess you must select “structure and data” and “create database before copying” checkboxes and, finally, press the “go” button in that section.

By the way, I’m using phpMyAdmin in Spanish so I’m not sure what the names of the sections are in English.

This is the batch script I wrote for renaming a database on Windows:

@echo off
set olddb=olddbname
set newdb=newdbname
SET count=1
SET act=mysql -uroot -e "select table_name from information_schema.tables where table_schema='%olddb%'"
mysql -uroot -e "create database %newdb%"
echo %act%
 FOR /f "tokens=*" %%G IN ('%act%') DO (
  REM echo %count%:%%G
  echo mysql -uroot -e "RENAME TABLE %olddb%.%%G to %newdb%.%%G"
  mysql -uroot -e "RENAME TABLE %olddb%.%%G to %newdb%.%%G"
  set /a count+=1
 )
mysql -uroot -e "drop database %olddb%"

Neither TodoInTx’s solution nor user757945’s adapted solution worked for me on MySQL 5.5.16, so here is my adapted version:

DELIMITER //
DROP PROCEDURE IF EXISTS `rename_database`;
CREATE PROCEDURE `rename_database` (IN `old_name` VARCHAR(20), IN `new_name` VARCHAR(20))
BEGIN
  DECLARE `current_table_name` VARCHAR(20);
  DECLARE `done` INT DEFAULT 0;
  DECLARE `table_name_cursor` CURSOR FOR SELECT `table_name` FROM `information_schema`.`tables` WHERE (`table_schema` = `old_name`);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = 1;

  SET @sql_string = CONCAT('CREATE DATABASE IF NOT EXISTS `', `new_name` , '`;');
  PREPARE `statement` FROM @sql_string;
  EXECUTE `statement`;
  DEALLOCATE PREPARE `statement`;

  OPEN `table_name_cursor`;
  REPEAT
    FETCH `table_name_cursor` INTO `current_table_name`;
    IF NOT `done` THEN

      SET @sql_string = CONCAT('RENAME TABLE `', `old_name`, '`.`', `current_table_name`, '` TO `', `new_name`, '`.`', `current_table_name`, '`;');
      PREPARE `statement` FROM @sql_string;
      EXECUTE `statement`;
      DEALLOCATE PREPARE `statement`;

    END IF;
  UNTIL `done` END REPEAT;
  CLOSE `table_name_cursor`;

  SET @sql_string =  CONCAT('DROP DATABASE `', `old_name`, '`;');
  PREPARE `statement` FROM @sql_string;
  EXECUTE `statement`;
  DEALLOCATE PREPARE `statement`;
END//
DELIMITER ;

Hope it helps someone who is in my situation! Note: @sql_string will linger in the session afterwards. I was not able to write this function without using it.

If you use hierarchical views (views pulling data from other views), import of raw output from mysqldump may not work since mysqldump doesn’t care for correct order of views. Because of this, I wrote script which re-orders views to correct order on the fly.

It loooks like this:

#!/usr/bin/env perl

use List::MoreUtils 'first_index'; #apt package liblist-moreutils-perl
use strict;
use warnings;


my $views_sql;

while (<>) {
    $views_sql .= $_ if $views_sql or index($_, 'Final view structure') != -1;
    print $_ if !$views_sql;
}

my @views_regex_result = ($views_sql =~ /(\-\- Final view structure.+?\n\-\-\n\n.+?\n\n)/msg);
my @views = (join("", @views_regex_result) =~ /\-\- Final view structure for view `(.+?)`/g);
my $new_views_section = "";
while (@views) {
    foreach my $view (@views_regex_result) {
        my $view_body = ($view =~ /\/\*.+?VIEW .+ AS (select .+)\*\/;/g )[0];
        my $found = 0;
        foreach my $view (@views) {
            if ($view_body =~ /(from|join)[ \(]+`$view`/) {
                $found = $view;
                last;
            }
        }
        if (!$found) {
            print $view;
            my $name_of_view_which_was_not_found = ($view =~ /\-\- Final view structure for view `(.+?)`/g)[0];
            my $index = first_index { $_ eq $name_of_view_which_was_not_found } @views;
            if ($index != -1) {
                splice(@views, $index, 1);
                splice(@views_regex_result, $index, 1);
            }
        }
    }
}

Usage:
mysqldump -u username -v olddatabase -p | ./mysqldump_view_reorder.pl | mysql -u username -p -D newdatabase

You guys are going to shoot me for this, and most probably this won’t work every time, and sure, it is against all logic blah blah… But what I just tried is… STOP the MySQL engine, log on as root and simply renamed the DB on the file system level….

I am on OSX, and only changed the case, from bedbf to BEDBF. To my surprise it worked…

I would not recommend it on a production DB. I just tried this as an experiment…

Good luck either way 🙂

I).There is no way directly by which u can change the name of an existing DB
But u can achieve ur target by following below steps:-
1). Create newdb.
2). Use newdb.
3). create table table_name(select * from olddb.table_name);

By doing above, u r copying data from table of olddb and inserting those in newdb table. Give name of the table same.

II). RENAME TABLE old_db.table_name TO new_db.table_name;

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

There are many really good answers here already but I do not see a PHP version. This copies an 800M DB in about a second.

$oldDbName = "oldDBName";
$newDbName = "newDBName";
$oldDB     = new mysqli("localhost", "user", "pass", $oldDbName);
if($oldDB->connect_errno){
    echo "Failed to connect to MySQL: (" . $oldDB->connect_errno . ") " . $oldDB->connect_error;
    exit;
}
$newDBQuery = "CREATE DATABASE IF NOT EXISTS {$newDbName}";
$oldDB->query($newDBQuery);
$newDB = new mysqli("localhost", "user", "pass");
if($newDB->connect_errno){
    echo "Failed to connect to MySQL: (" . $newDB->connect_errno . ") " . $newDB->connect_error;
    exit;
}

$tableQuery  = "SHOW TABLES";
$tableResult = $oldDB->query($tableQuery);
$renameQuery = "RENAME TABLE\n";
while($table = $tableResult->fetch_array()){
    $tableName = $table["Tables_in_{$oldDbName}"];
    $renameQuery .= "{$oldDbName}.{$tableName} TO {$newDbName}.{$tableName},";
}
$renameQuery = substr($renameQuery, 0, strlen($renameQuery) - 1);
$newDB->query($renameQuery);

Simplest of all, open MYSQL >> SELECT DB whose name you want to change >> Click on “operation” then put New name in “Rename database to:” field then click “Go” button

Simple!

The simple way

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

or you can use online sql generator