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

Question

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.

Oh, one more thing - this needs to work with InnoDB, which stores things very differently than MyISAM.

Answer 1

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

Answer 2

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.

Answer 3

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, then use INSERT ... SELECT * FROM statements.

3) use mysqldump and reload with that file.

Answer 4

the simple way

Change to DB directory:

cd /var/lib/mysql/

Shut down SQL...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-DBs. In this case you have to dump the db and re-import it

Answer 5

In MySQL Administrator do the following:

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

Answer 6

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

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

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

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

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

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

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

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

Answer 7

I dumped the old_database (using export in phpmyadmin) then simply open the dumped database in a text editor (gedit) and replace any instance of old_name to new_name and then I import the changed file. have I done a wrong work? why?

Answer 8

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

Answer 9

I try to rename database film to database film_3

mysql> create database film_3

/etc/init.d/mysql stop

cd /var/lib/mysql/

cp film/* film3/.

chown -R mysql:mysql film3

/etc/init.d/mysql start

mysql> show tables;

+-----------------+

| Tables_in_film3 |

+-----------------+

| acteur |

| commentaire |

| download |

| film |

+-----------------+

mysql> select * from acteur; ERROR 1146 (42S02): Table 'film3.acteur' doesn't exist

Any idea why it does not work !

Answer 10

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database (old_db_ame.table_name to new_db_name.table_name). You will need to adjust the permissions after that.

Answer 11

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

Answer 12

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.

Answer 13

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

Answer 14

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 myPHPAdmin 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 MyPHPAdmin 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 Iam 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.

Answer 15

If you r using the PHP MY ADMIN then you just go to the mysql folder in the xamp ,close the php myadmin and just rename the folder you just see there as ur db name and just restart ur php myadmin.you can see that that db as renamed

Answer 16

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.

Answer 17

This is what I use:

$ mysqldump -u root -p olddb >~/olddb.sql
$ mysql -u root -p
mysql> create database newdb;
mysql> use newdb
mysql> source ~/olddb.sql
mysql> drop database olddb;