CODEWITHSUNDEEP
mysqldatabaseinnodbrename
deadprogrammer
deadprogrammer

Reputation: 11283

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 {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exists only in a handful of versions, and is a bad idea overall.

This needs to work with InnoDB, which stores things very differently than MyISAM.

Upvotes: 1170

Views: 1690320

Answers (30)

Steve Chambers
Steve Chambers

Reputation: 39464

Simplest 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 newdb
mysqldump -uroot -pmypassword --routines olddb | mysql -uroot -pmypassword newdb
# CHECK NEW DATABASE AND ONLY PROCEED IF POPULATED SUCCESSFULLY!!!
mysqladmin -uroot -pmypassword drop olddb

Notes:

  1. Please be aware that you'll be saving your password forever in shell history. For some this won't be an issue but if it is for you, remove mypassword from the commands and enter it when requested instead.

Steps:

  1. Copy the lines into Notepad.
  2. Replace all references to "olddb", "newdb", "mypassword" (+ optionally "root") with appropriate names/values.
  3. Execute one by one on the command line (entering "y" when prompted).

Upvotes: 23

raphie
raphie

Reputation: 3305

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.

Enter image description here

Upvotes: 249

ᴍᴇʜᴏᴠ
ᴍᴇʜᴏᴠ

Reputation: 5266

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

Upvotes: 1

Aԃιƚყα Gυɾαʋ
Aԃιƚყα Gυɾαʋ

Reputation: 127

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.

Upvotes: -2

Marciano
Marciano

Reputation: 123

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.

Upvotes: 91

New Alexandria
New Alexandria

Reputation: 7324

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.

Upvotes: 7

nafischonchol
nafischonchol

Reputation: 88

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

Upvotes: -2

Thorsten
Thorsten

Reputation: 2206

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

Upvotes: 1027

overals
overals

Reputation: 92

The simple way

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

or you can use online sql generator

Upvotes: -7

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58291

You may use this shell script:

Reference: How to rename a MySQL database? 

#!/bin/bash
set -e # terminate execution on command failure

mysqlconn="mysql -u root -proot"
olddb=$1
newdb=$2
$mysqlconn -e "CREATE DATABASE $newdb"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
                           WHERE table_schema='$olddb'")
for name in $params; do
      $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
done;
$mysqlconn -e "DROP DATABASE $olddb"

It's working:

$ sh rename_database.sh oldname newname

Upvotes: 35

ErichBSchulz
ErichBSchulz

Reputation: 15679

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

SELECT CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name, '; ')
FROM information_schema.TABLES 
WHERE table_schema='$1';

or

SELECT GROUP_CONCAT('RENAME TABLE $1.', table_name, ' TO $2.', table_name SEPARATOR '; ')
FROM information_schema.TABLES 
WHERE table_schema='$1';

($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).

Upvotes: 126

Roee Gavirel
Roee Gavirel

Reputation: 19453

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

Upvotes: 10

Tuncay Göncüoğlu
Tuncay Göncüoğlu

Reputation: 1767

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.

Upvotes: 11

Samra
Samra

Reputation: 2013

I did it this way: Take backup of your existing database. It will give you a db.zip.tmp and then in command prompt write following

"C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysql.exe" -h localhost -u root -p[password] [new db name] < "C:\Backups\db.zip.tmp"

Upvotes: 4

RotS
RotS

Reputation: 2430

In the case where you start from a dump file with several databases, you can perform a sed on the dump:

sed -i -- "s|old_name_database1|new_name_database1|g" my_dump.sql
sed -i -- "s|old_name_database2|new_name_database2|g" my_dump.sql
...

Then import your dump. Just ensure that there will be no name conflict.

Upvotes: 0

ryantm
ryantm

Reputation: 8427

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
# @see 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

Upvotes: 15

Tim Duncklee
Tim Duncklee

Reputation: 1420

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

Upvotes: -3

Fathah Rehman P
Fathah Rehman P

Reputation: 8761

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.

Upvotes: 8

xelber
xelber

Reputation: 4637

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.

Upvotes: 5

coffeefiend
coffeefiend

Reputation: 81

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.

Upvotes: 4

gerrit damen
gerrit damen

Reputation: 31

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"

Upvotes: 9

i.jolly
i.jolly

Reputation: 61

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!

Upvotes: -3

Nadav Benedek
Nadav Benedek

Reputation: 1101

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%"

Upvotes: 3

nicky
nicky

Reputation: 5

Really, the simplest answer is to export your old database then import it into the new one that you've created to replace the old one. Of course, you should use phpMyAdmin or command line to do this.

Renaming and Jerry-rigging the database is a BAD-IDEA! DO NOT DO IT. (Unless you are the "hacker-type" sitting in your mother's basement in the dark and eating pizza sleeping during the day.)

You will end up with more problems and work than you want.

So,

  1. Create a new_database and name it the correct way.
  2. Go to your phpMyAdmin and open the database you want to export.
  3. Export it (check the options, but you should be OK with the defaults.
  4. You will get a file like or similar to this.

  5. The extension on this file is .sql

    -- phpMyAdmin SQL Dump -- version 3.2.4

    -- http://www.phpmyadmin.net

    -- Host: localhost -- Generation Time: Jun 30, 2010 at 12:17 PM -- Server version: 5.0.90 -- PHP Version: 5.2.6

    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /; /!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /; /!40101 SET NAMES utf8 */;

    --

    -- Database: mydatab_online

    --

    -- Table structure for table user

    CREATE TABLE IF NOT EXISTS user ( timestamp int(15) NOT NULL default '0', ip varchar(40) NOT NULL default '', file varchar(100) NOT NULL default '', PRIMARY KEY (timestamp), KEY ip (ip), KEY file (file) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --

    -- Dumping data for table user

    INSERT INTO user (timestamp, ip, file) VALUES (1277911052, '999.236.177.116', ''), (1277911194, '999.236.177.116', '');

This will be your .sql file. The one that you've just exported.

Find it on your hard-drive; usually it is in /temp. Select the empty database that has the correct name (the reason why you are reading this). SAY: Import - GO

Connect your program to the correct database by entering it into what usually is a configuration.php file. Refresh the server (both. Why? Because I am a UNIX oldtimer, and I said so. Now, you should be in good shape. If you have any further questions visit me on the web.

Upvotes: -3

hendrasaputra
hendrasaputra

Reputation: 1450

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

Upvotes: 518

DeeCee
DeeCee

Reputation: 400

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.

Upvotes: 23

longneck
longneck

Reputation: 293

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.

Upvotes: 25

gadelat
gadelat

Reputation: 1470

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

Upvotes: 1

Lawrence
Lawrence

Reputation: 1056

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

Upvotes: -1

rajesh
rajesh

Reputation: 191

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;

Upvotes: 0

Related Questions