How do I quickly rename a MySQL database change schema name

Question

The MySQL manual at MySQL covers this   Usually I just dump the database and reimport 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  exist 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

User · Answer

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

User · Answer

If you are using phpMyAdmin then you just go to the mysql folder in the xamp  close phpMyAdmin and just rename the folder you just see there as your database name and just restart your phpMyAdmin  You can see that that database as renamed

User · Answer

Use these few simple commands   mysqldump -u username -p -v olddatabase  gt  olddbdump sql mysqladmin -u username -p create newdatabase mysql -u username -p newdatabase  lt  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

User · Answer

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

User · Answer

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

User · Answer

Most of the answers here are wrong for one of two reasons    You cannot just use RENAME TABLE  because there might be views and triggers  If there are triggers  RENAME TABLE fails 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  lt server gt   lt database gt   lt new database gt       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  gt   tmp   2  views  TIMESTAMP  dump fi mysqldump -h  1  2 -d -t -R -E  gt   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  lt   tmp   2  views  TIMESTAMP  dump fi echo  loading triggers  routines and events  mysql -h  1  3  lt   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

User · Answer

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

User · Answer

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

User · Answer

In MySQL Administrator do the following    Under Catalogs  create a new database schema  Go to Backup and create a backup of the old schema  Execute backup  Go to Restore and open the file created in step 3  Select  Another Schema  under Target Schema and select the new database schema  Start Restore  Verify new schema and  if it looks good  delete the old one

User · Answer

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

User · Answer

This is what I use     mysqldump -u root -p olddb  gt   olddb sql   mysql -u root -p mysql gt  create database newdb  mysql gt  use newdb mysql gt  source   olddb sql mysql gt  drop database olddb

User · Answer

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   gt   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   lt   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

User · Answer

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

User · Answer

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

User · Answer

If you are using phpMyAdmin then you just go to the mysql folder in the xamp  close phpMyAdmin and just rename the folder you just see there as your database name and just restart your phpMyAdmin  You can see that that database as renamed

User · Answer

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  -

User · Answer

Steps     Hit http   localhost phpmyadmin  Select your DB Click on Operations Tab There will be a tab as  Rename database to   Add new name and check Adjust privileges   Click on Go

User · Answer

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

User · Answer

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

User · Answer

UPDATE  db SET Db    new db name  where Db    old db name

User · Answer

It is possible to rename all tables within a database to be under another database without having to do a full dump and restore    DROP PROCEDURE IF EXISTS mysql rename db  DELIMITER    CREATE PROCEDURE mysql rename db IN old db VARCHAR 100   IN new db VARCHAR 100   BEGIN SELECT CONCAT  CREATE DATABASE    new db          create new database   SELECT CONCAT  RENAME TABLE     old db         table name     TO     new db         table name           alter table  FROM information schema tables WHERE table schema   old db  SELECT CONCAT  DROP DATABASE     old db           drop old database   END   DELIMITER      time mysql -uroot -e  call mysql rename db  db1    db2       mysql -uroot   However any triggers in the target db will not be happy  You ll need to drop them first then recreate them after the rename    mysql -uroot -e  call mysql rename db  test    blah2       mysql -uroot ERROR 1435  HY000  at line 4  Trigger in wrong schema

User · Answer

Emulating the missing RENAME DATABASE command in MySQL    Create a new database 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    Run that output Delete old database   It was taken from Emulating The Missing RENAME DATABASE Command in MySQL

User · Answer

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  taltman ai sri com   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

User · Answer

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

User · Answer

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

User · Answer

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   gt   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   lt   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

User · Answer

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

User · Answer

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

User · Answer

This works for all databases and works by renaming each table with maatkit mysql toolkit  Use mk-find to print and rename each table  The man page has many more options and examples  mk-find --dblike OLD DATABASE --print --exec  RENAME TABLE  D  N TO NEW DATABASE  N    If you have maatkit installed  which is very easy   then this is the simplest way to do it

User · Answer

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

User · Answer

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

User · Answer

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- gt connect errno       echo  Failed to connect to MySQL        oldDB- gt connect errno           oldDB- gt connect error      exit     newDBQuery    CREATE DATABASE IF NOT EXISTS   newDbName     oldDB- gt query  newDBQuery    newDB   new mysqli  localhost    user    pass    if  newDB- gt connect errno       echo  Failed to connect to MySQL        newDB- gt connect errno           newDB- gt connect error      exit      tableQuery     SHOW TABLES    tableResult    oldDB- gt query  tableQuery    renameQuery    RENAME TABLE n   while  table    tableResult- gt fetch array          tableName    table  Tables in   oldDbName          renameQuery       oldDbName    tableName  TO   newDbName    tableName        renameQuery   substr  renameQuery  0  strlen  renameQuery  - 1    newDB- gt query  renameQuery

User · Answer

You can do it in two ways    RENAME TABLE old db table name TO new db table name  Goto operations-  there you can see Table options tab  you can edit table name there

User · Answer

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

User · Answer

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

User · Answer

The simple way  ALTER DATABASE  oldName  MODIFY NAME    newName     or you can use online sql generator

User · Answer

I used following method to rename the database   take backup of the file using mysqldump  or any DB tool eg heidiSQL mysql administrator etc Open back up  eg backupfile sql  file in some text editor  Search and replace the database name and save file    4 Restore the edited sql file

User · Answer

in phpmyadmin you can easily rename the database  select database     goto operations tab    in that rename Database to      type your new database name and click go   ask to drop old table and reload table data click OK in both  Your database is renamed

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Use these few simple commands   mysqldump -u username -p -v olddatabase  gt  olddbdump sql mysqladmin -u username -p create newdatabase mysql -u username -p newdatabase  lt  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

User · Answer

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

User · Answer

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

User · Answer

Most of the answers here are wrong for one of two reasons    You cannot just use RENAME TABLE  because there might be views and triggers  If there are triggers  RENAME TABLE fails 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  lt server gt   lt database gt   lt new database gt       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  gt   tmp   2  views  TIMESTAMP  dump fi mysqldump -h  1  2 -d -t -R -E  gt   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  lt   tmp   2  views  TIMESTAMP  dump fi echo  loading triggers  routines and events  mysql -h  1  3  lt   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

User · Answer

For those who are Mac users  Sequel Pro has a Rename Database option in the Database menu  http   www sequelpro com

User · Answer

UPDATE  db SET Db    new db name  where Db    old db name

User · Answer

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   lt  gt          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

User · Answer

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   lt   C  Backups db zip tmp

User · Answer

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

User · Answer

Three options    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I posted this How do I change the database name using MySQL  today after days of head scratching and hair pulling  The solution is quite simple export a schema to a  sql file and open the file and change the database schema name in the sql CREAT TABLE section at the top  There are three instances or more and may not be at the top of the page if multible schemas are saved to the file  It is posible to edit the entire database this way but I expect that in large databases it could be quite a pain following all instances of a table property or index

User · Answer

You can do it in two ways    RENAME TABLE old db table name TO new db table name  Goto operations-  there you can see Table options tab  you can edit table name there

User · Answer

I posed a question on Server Fault trying to get around downtime when restoring very large databases by using MySQL Proxy  I didn t have any success  but I realized in the end what I wanted was RENAME DATABASE functionality because dump import wasn t an option due to the size of our database   There is a RENAME TABLE functionality built in to MySQL so I ended up writing a simple Python script to do the job for me  I ve posted it on GitHub in case it could be of use to others

User · Answer

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    Create a new database and name it the correct way  Go to your phpMyAdmin and open the database you want to export  Export it  check the options  but you should be OK with the defaults  You will get a file like or similar to this  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

User · Answer

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   gt   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   lt   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

User · Answer

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    Create a new database and name it the correct way  Go to your phpMyAdmin and open the database you want to export  Export it  check the options  but you should be OK with the defaults  You will get a file like or similar to this  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

User · Answer

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    From Shell   mysqldump emp  gt  emp out  mysql -e  CREATE DATABASE employees    mysql employees  lt  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 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      mysqldump  lt old schema name gt  -d -t -R -E  gt  stored routines triggers events out   2  Generate a list of only    BASE    tables  These can be found using a query on information schema TABLES table    mysql gt  select TABLE NAME from information schema tables where      table schema   lt old schema name gt   and TABLE TYPE  BASE TABLE     3  Dump the views in an out file  Views can be found using a query on the same information schema TABLES table   mysql gt  select TABLE NAME from information schema tables where     table schema   lt old schema name gt   and TABLE TYPE  VIEW      mysqldump  lt database gt   lt view1 gt   lt view2 gt       gt  views out   4  Drop the triggers on the current tables in the old schema   mysql gt  DROP TRIGGER  lt trigger name gt         5  Restore the above dump files once all the    Base    tables found in step  2 are renamed   mysql gt  RENAME TABLE  lt old schema gt  table name TO  lt new schema gt  table name        mysql  lt new schema gt   lt  views out   mysql  lt new schema gt   lt  stored routines triggers events out   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     root dba     tmp rename db rename db  lt server gt   lt database gt   lt new database gt    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   mysql gt  show databases   --------------------    Database              --------------------    information schema     emp                    mysql                  performance schema     test                  --------------------     root dba     time  tmp rename db localhost emp emp test create database emp test DEFAULT CHARACTER SET latin1 drop trigger salary trigger rename table emp   emp new to emp test   emp new rename table emp  emp new to emp test  emp new rename table emp departments to emp test departments rename table emp dept to emp test dept rename table emp dept emp to emp test dept emp rename table emp dept manager to emp test dept manager rename table emp emp to emp test emp rename table emp employees to emp test employees rename table emp salaries temp to emp test salaries temp rename table emp titles to emp test titles loading views loading triggers  routines and events Dropping database emp  real    0m0 643s user    0m0 053s sys     0m0 131s   mysql gt  show databases   --------------------    Database              --------------------    information schema     emp test               mysql                  performance schema     test                  --------------------    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       bin bash   Copyright 2013 Percona LLC and or its affiliates set -e if   -z   3     then     echo  rename db  lt server gt   lt database gt   lt new database gt       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  show create database  2 G  -sss   grep  Create   awk -F CHARACTER SET     print  2     awk   print  1    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  gt   tmp   2  views  TIMESTAMP  dump fi mysqldump -h  1  2 -d -t -R -E  gt   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  lt   tmp   2  views  TIMESTAMP  dump fi echo  loading triggers  routines and events  mysql -h  1  3  lt   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

User · Answer

Here is a one-line Bash snippet to move all tables from one schema to another   history -d    HISTCMD-1    amp  amp  mysql -udb user -p db password  -Dold schema -ABNnqre SHOW TABLES     sed -e s    RENAME TABLE old schema   amp   TO new schema   amp        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

User · Answer

This is what I use     mysqldump -u root -p olddb  gt   olddb sql   mysql -u root -p mysql gt  create database newdb  mysql gt  use newdb mysql gt  source   olddb sql mysql gt  drop database olddb

User · Answer

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  -

User · Answer

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

User · Answer

Three options    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

User · Answer

The simple way  ALTER DATABASE  oldName  MODIFY NAME    newName     or you can use online sql generator

User · Answer

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- gt connect errno       echo  Failed to connect to MySQL        oldDB- gt connect errno           oldDB- gt connect error      exit     newDBQuery    CREATE DATABASE IF NOT EXISTS   newDbName     oldDB- gt query  newDBQuery    newDB   new mysqli  localhost    user    pass    if  newDB- gt connect errno       echo  Failed to connect to MySQL        newDB- gt connect errno           newDB- gt connect error      exit      tableQuery     SHOW TABLES    tableResult    oldDB- gt query  tableQuery    renameQuery    RENAME TABLE n   while  table    tableResult- gt fetch array          tableName    table  Tables in   oldDbName          renameQuery       oldDbName    tableName  TO   newDbName    tableName        renameQuery   substr  renameQuery  0  strlen  renameQuery  - 1    newDB- gt query  renameQuery

User · Answer

This works for all databases and works by renaming each table with maatkit mysql toolkit  Use mk-find to print and rename each table  The man page has many more options and examples  mk-find --dblike OLD DATABASE --print --exec  RENAME TABLE  D  N TO NEW DATABASE  N    If you have maatkit installed  which is very easy   then this is the simplest way to do it

User · Answer

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 nbsp OS nbsp 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

User · Answer

It is possible to rename all tables within a database to be under another database without having to do a full dump and restore    DROP PROCEDURE IF EXISTS mysql rename db  DELIMITER    CREATE PROCEDURE mysql rename db IN old db VARCHAR 100   IN new db VARCHAR 100   BEGIN SELECT CONCAT  CREATE DATABASE    new db          create new database   SELECT CONCAT  RENAME TABLE     old db         table name     TO     new db         table name           alter table  FROM information schema tables WHERE table schema   old db  SELECT CONCAT  DROP DATABASE     old db           drop old database   END   DELIMITER      time mysql -uroot -e  call mysql rename db  db1    db2       mysql -uroot   However any triggers in the target db will not be happy  You ll need to drop them first then recreate them after the rename    mysql -uroot -e  call mysql rename db  test    blah2       mysql -uroot ERROR 1435  HY000  at line 4  Trigger in wrong schema

User · Answer

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 - gt   2  i      mysql -e  rename TABLE  1  i to  2  i  done mysql -e  DROP DATABASE  1

User · Answer

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   lt   C  Backups db zip tmp

User · Answer

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  taltman ai sri com   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

User · Answer

in phpmyadmin you can easily rename the database  select database     goto operations tab    in that rename Database to      type your new database name and click go   ask to drop old table and reload table data click OK in both  Your database is renamed

User · Answer

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   gt   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   lt   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

User · Answer

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

User · Answer

Three options    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

User · Answer

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

User · Answer

I ve only recently came across a very nice way to do it  works with MyISAM and InnoDB and is very fast   RENAME TABLE old db table TO new db table    I don t remember where I read it but credit goes to someone else not me

User · Answer

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 nbsp OS nbsp 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For those who are Mac users  Sequel Pro has a Rename Database option in the Database menu  http   www sequelpro com

User · Answer

I posted this How do I change the database name using MySQL  today after days of head scratching and hair pulling  The solution is quite simple export a schema to a  sql file and open the file and change the database schema name in the sql CREAT TABLE section at the top  There are three instances or more and may not be at the top of the page if multible schemas are saved to the file  It is posible to edit the entire database this way but I expect that in large databases it could be quite a pain following all instances of a table property or index

User · Answer

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

User · Answer

I used following method to rename the database   take backup of the file using mysqldump  or any DB tool eg heidiSQL mysql administrator etc Open back up  eg backupfile sql  file in some text editor  Search and replace the database name and save file    4 Restore the edited sql file

User · Answer

Three options    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

User · Answer

Here is a one-line Bash snippet to move all tables from one schema to another   history -d    HISTCMD-1    amp  amp  mysql -udb user -p db password  -Dold schema -ABNnqre SHOW TABLES     sed -e s    RENAME TABLE old schema   amp   TO new schema   amp        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

User · Answer

In MySQL Administrator do the following    Under Catalogs  create a new database schema  Go to Backup and create a backup of the old schema  Execute backup  Go to Restore and open the file created in step 3  Select  Another Schema  under Target Schema and select the new database schema  Start Restore  Verify new schema and  if it looks good  delete the old one

User · Answer

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

User · Answer

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

User · Answer

Emulating the missing RENAME DATABASE command in MySQL    Create a new database 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    Run that output Delete old database   It was taken from Emulating The Missing RENAME DATABASE Command in MySQL

User · Answer

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   lt  gt          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

User · Answer

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

User · Answer

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 - gt   2  i      mysql -e  rename TABLE  1  i to  2  i  done mysql -e  DROP DATABASE  1

User · Answer

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

User · Answer

I ve only recently came across a very nice way to do it  works with MyISAM and InnoDB and is very fast   RENAME TABLE old db table TO new db table    I don t remember where I read it but credit goes to someone else not me

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I posed a question on Server Fault trying to get around downtime when restoring very large databases by using MySQL Proxy  I didn t have any success  but I realized in the end what I wanted was RENAME DATABASE functionality because dump import wasn t an option due to the size of our database   There is a RENAME TABLE functionality built in to MySQL so I ended up writing a simple Python script to do the job for me  I ve posted it on GitHub in case it could be of use to others

User · Answer

Steps     Hit http   localhost phpmyadmin  Select your DB Click on Operations Tab There will be a tab as  Rename database to   Add new name and check Adjust privileges   Click on Go

User · Answer

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

User · Answer

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

User · Answer

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    From Shell   mysqldump emp  gt  emp out  mysql -e  CREATE DATABASE employees    mysql employees  lt  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 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      mysqldump  lt old schema name gt  -d -t -R -E  gt  stored routines triggers events out   2  Generate a list of only    BASE    tables  These can be found using a query on information schema TABLES table    mysql gt  select TABLE NAME from information schema tables where      table schema   lt old schema name gt   and TABLE TYPE  BASE TABLE     3  Dump the views in an out file  Views can be found using a query on the same information schema TABLES table   mysql gt  select TABLE NAME from information schema tables where     table schema   lt old schema name gt   and TABLE TYPE  VIEW      mysqldump  lt database gt   lt view1 gt   lt view2 gt       gt  views out   4  Drop the triggers on the current tables in the old schema   mysql gt  DROP TRIGGER  lt trigger name gt         5  Restore the above dump files once all the    Base    tables found in step  2 are renamed   mysql gt  RENAME TABLE  lt old schema gt  table name TO  lt new schema gt  table name        mysql  lt new schema gt   lt  views out   mysql  lt new schema gt   lt  stored routines triggers events out   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     root dba     tmp rename db rename db  lt server gt   lt database gt   lt new database gt    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   mysql gt  show databases   --------------------    Database              --------------------    information schema     emp                    mysql                  performance schema     test                  --------------------     root dba     time  tmp rename db localhost emp emp test create database emp test DEFAULT CHARACTER SET latin1 drop trigger salary trigger rename table emp   emp new to emp test   emp new rename table emp  emp new to emp test  emp new rename table emp departments to emp test departments rename table emp dept to emp test dept rename table emp dept emp to emp test dept emp rename table emp dept manager to emp test dept manager rename table emp emp to emp test emp rename table emp employees to emp test employees rename table emp salaries temp to emp test salaries temp rename table emp titles to emp test titles loading views loading triggers  routines and events Dropping database emp  real    0m0 643s user    0m0 053s sys     0m0 131s   mysql gt  show databases   --------------------    Database              --------------------    information schema     emp test               mysql                  performance schema     test                  --------------------    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       bin bash   Copyright 2013 Percona LLC and or its affiliates set -e if   -z   3     then     echo  rename db  lt server gt   lt database gt   lt new database gt       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  show create database  2 G  -sss   grep  Create   awk -F CHARACTER SET     print  2     awk   print  1    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  gt   tmp   2  views  TIMESTAMP  dump fi mysqldump -h  1  2 -d -t -R -E  gt   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  lt   tmp   2  views  TIMESTAMP  dump fi echo  loading triggers  routines and events  mysql -h  1  3  lt   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

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

Examples related to mysql

Examples related to database

Examples related to innodb

Examples related to rename