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

Question

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

Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exists only in a handful of versions, and is a bad idea overall.

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

Also on serverfault: http://serverfault.com/questions/195221/how-to-rename-a-mysql-database — Yves Martin, Aug 14 '13 at 06:06

score 1001 · Accepted Answer · edited Apr 19 '20 at 13:51

1001

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

edited Apr 19 '20 at 13:51

Arsen Khachaturyan

7,904
4
42
42

answered Feb 19 '10 at 18:17

Thorsten

2,216
1
14
2

4

This is a good option and the way to go if your db is big but you don't have so many tables (or you are willing to write a script to loop all tables). Besides in innodb it is only a logic renaming and in MyISAM depending of your filesystem it would be a logic renaming or a real copy data on the disk. – Pablo Marin-Garcia Sep 29 '10 at 15:57
37

I've just done this with an InnoDB database with 30+ tables, using the file_per_table setting, and even though some tables were 3+ million rows, it completed in < 1 second. It just seems to move the files on the storage, rather than doing anything more complicated... +2 if possible :) – Dave Rix Nov 25 '11 at 12:21
18

Please note that this will not work for views. You cannot rename views to make them jump from a database to another. Use `DROP VIEW` and `CREATE VIEW` instead. Clumsy, yes. You might want to do a `mysqldump` to move the views, after first moving all the tables. Also note that `SHOW TABLES` will show tables AND views, so beware. – tuomassalo Nov 01 '13 at 12:38
12

Also this will not work for any tables with triggers. You need to find, dump and drop triggers prior to moving the table, then import the dumped triggers into the target db. – Olfan Jan 14 '15 at 15:21
1

There are many things to be aware of here. Checkout the [source](https://github.com/phpmyadmin/phpmyadmin/blob/master/db_operations.php#L37) for `phpmyadmin`'s Rename Database – Peter V. Mørch Feb 11 '16 at 13:07
Although `mysqldump` is **not a good option for large databases**, it supports triggers by default as well as procedures with the `-R` or `--routines` parameter (as mentioned in [the answer](http://stackoverflow.com/a/2298602/3787376)) and tables support (by default). It doesn't seem to support views though. – Edward Apr 15 '16 at 19:13
9

Updated (i.e. working) link documenting why `RENAME DATABASE` was removed: http://dev.mysql.com/worklog/task/?id=4030 – alexis May 21 '16 at 11:39
thank you for your suggestion. Notice that you should add a ';' after the show tables command if you don't want to be prompted for the missin semi-colon. – Izerlotti Jul 13 '16 at 17:44
1

Or if you have installed MySQL Utilities: `mysqldbcopy --source=username@localhost:port --destination=username@localhost:port old_db:new_db` and drop old_db afterwards. – bjmi Aug 30 '16 at 08:12
Get a script to rename with the following select 'RENAME TABLE ' , concat(TABLE_SCHEMA,'.',TABLE_NAME) , concat(' TO .',TABLE_NAME) from information_schema.tables WHERE TABLE_SCHEMA = '' order by TABLE_NAME – ChrisR Aug 31 '16 at 07:01
In phpMyAdmin, I used Operations/Rename Database. And it was surprisingly fast, nearly instant (1-2 seconds), so I assume it uses this `RENAME TABLE` strategy. MySQL 5.6. – Buttle Butkus Mar 20 '17 at 08:35
1

Please do not forget to use the command `history -cw` in the console after using this shell script since you would not want the password to appear in clear text in the console history – Vincent Oct 07 '17 at 18:08
Please can someone explain what the OP Answerer means when he says "You will need to adjust the permissions after that."? – 0xVox Jul 26 '18 at 08:58
1

First option didn't work for me, threw "Command not found". Second option worked pretty well! – Lucas Bustamante Jul 12 '19 at 14:40
1

Does this work if table have referential constraints? I expect not. – dolmen Nov 05 '19 at 16:24
1

Personally, I like to do it this way, SELECT concat("rename table old_database_name.",TABLE_NAME,' to new_database_name.',TABLE_NAME,';') FROM information_schema.tables WHERE TABLE_SCHEMA='old_database_name'; which creates a series of commands that I can execute manually. – jbrahy Jul 14 '20 at 18:21
If your table name uses slashes ('/') or other special characters, you will need to use backticks around the table name. For them to be propely interpreted, write the command like so ```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;``` – Bruno Degomme May 12 '21 at 10:15

score 501 · Answer 2 · edited Nov 09 '15 at 11:48

501

Use these few simple commands:

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

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

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

edited Nov 09 '15 at 11:48

Peter Mortensen

30,738
21
105
131

answered Jul 02 '09 at 07:34

hendrasaputra

1,470
1
11
12

113

As the OP said, "[t]his is not an option for very big databases." – pilcrow May 07 '10 at 13:19
4

Don't forget to DROP the original database – Pavel Radzivilovsky Oct 11 '10 at 12:17
4

Brilliant answer! Couple of suggestions to further improve as this is probably being googled by all abilities: (1) Move Pablo Marin-Garcia's code fragment to the top as it seems the best answer (2) Put `-p` instead of `-p` everywhere so the statements run without a prompt appearing. – Steve Chambers Apr 21 '14 at 19:55
10

Using the piped version I get two "Enter password:" prompts like so: `Enter password: Enter password:` It seems to take one password, but not both. Am I missing a detail? – Ryan Jun 27 '14 at 07:59
43

I'm surprised that nobody has mentioned this, but you really should add the `--routines` flag to the mysqldump commands too, to ensure that stored procedures are copied across. – Carlos P Oct 08 '14 at 13:11
I tend to use [SQLyog](https://www.webyog.com/product/sqlyog) GUI. 1) creating a new DB with required name and then using the 'Copy to Different Host/DB' feature to copy the old database. 2) Exporting the DB as sqldump and then importing using 'Execute SQL script' option from the menu "Tools" – Apr 03 '17 at 13:13
This solution is preferred because it is a HUGE convenience to NOT have to iterate over all the tables. And while it's been stated that this is not the best for "large" databases, no one has described what that means quantitatively. My suggestion is to try it this way first. Your database may not be the behemoth you think it is. Worst case is probably a disk-full problem, so you should be prepared to handle that. – Tom Wilson Mar 20 '19 at 17:31
When starting mysqldump from Windows Command (cmd.exe) you need to run it as an administrator (otherwise it will say "Access is denied"). – Björn Nov 24 '20 at 13:55

score 242 · Answer 3 · edited Feb 15 '23 at 18:37

242

I think the solution is simpler and was suggested by some developers. phpMyAdmin has an operation for this.

From phpMyAdmin, select the database you want to select. In the tabs there's one called Operations, go to the rename section. That's all.

It does, as many suggested, create a new database with the new name, dump all tables of the old database into the new database and drop the old database.

Enter image description here

edited Feb 15 '23 at 18:37

Dharman

30,962
25
85
135

answered Jan 15 '12 at 19:46

raphie

3,285
2
29
26

96

Assuming you even have php on your environment or use phpmyadmin. – Chris Aug 28 '12 at 18:41
37

Pretty dangerous even if you do have phpMyAdmin - the back end could fail mid process leaving the two dbs in an unknown state, or it could take a very long time, leading to the front-end hanging or PHP timing out. – mozboz Sep 14 '12 at 16:25
21

That's true @mozboz, but I've have done this for 10 years and never had that problem. Is the same if your running the command through a shell and you computer crashes. There is a possibility but what? 1 to 1 quadrillion? – raphie Sep 22 '12 at 13:24
32

A script via console is also a front-end that can hang with the same problems. – Greg Sep 24 '12 at 02:45
16

Yet console operations are far more reliable than PhpMyAdmin, especially where big databases are involved, which is the OP's case. Personally i would strongly suggest any console method rather than PMA if you have a reasonably large database. Needless to say, on small databases PMA is just as good. – Teodor Sandu Apr 25 '13 at 07:12
8

The console operations can also be guarded from network hangups and drops by using gnu screen to run them in. – Danny Staple Aug 14 '13 at 09:56
3

Regarding these various concerns there seems to be 2 obvious solutions. 1 - Always test in your dev environment first. 2 - Instead of renaming, you could copy to the new name. You can always roll back to the original DB. – Andrew Magill Sep 06 '13 at 18:16
@AyexeM This is totally true, either environment or tool you use it is always require to make a back up or copy of the data regardless the process you're are planning to do. Recovering data, depending in the amount of data is normally quick. Agreed. – raphie Oct 01 '13 at 15:17
This answers is a very good answer.. especially when all I am doing is testing some book exercices.. in php and phpmyadmin. I have named my db wrongly... and had created some tables.. and now all i have to do is rename the db.. and voila.. job done. – ihightower Dec 06 '13 at 18:46
phpmyadmin is a rigorous tool. I would hope that they implement this functionality as a proper [ACID transaction](http://en.wikipedia.org/wiki/ACID), by the use of logging and re-rolling when encountering errors? I have not actually checked that though. – Domi Jun 07 '14 at 18:17
I've done this with many databases of different sizes using phpMyAdmin. To date I've never had a failure. Caveat: We only use MyISAM, no InnoDB tables in any of our systems (ok, a few tiny exceptions). – TheSatinKnight May 28 '17 at 05:21
I get the error "Notice in ./libraries/operations.lib.php#361. Undefined variable: sql_query. Backtrace ./db_operations.php#69: PMA_createDbBeforeCopy()" – Paul Chris Jones Mar 10 '19 at 14:52
OMG! my db has been droped and new db hasn't created, everythings gone :) – Sazzad Aug 29 '22 at 10:54

ErichBSchulz · Answer 4 · 2018-02-24T03:19:46.340

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

Does this work if table have referential constraints? I expect not. — dolmen, Nov 05 '19 at 16:25

score 81 · Answer 5 · edited Aug 27 '21 at 11:52

81

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.

edited Aug 27 '21 at 11:52

xyz

559
2
11

answered Aug 10 '12 at 20:43

Marciano

123
4
9

1

Perfect! I tested this with InnoDB and MyISAM tables. The fastest solution I tested (renaming the table is almost instant, no delay)! – Philipp Oct 09 '18 at 14:29
Great! Just remember to fix privileges afterwards. – Navidot Aug 30 '19 at 12:43
ps. Better do this before you run the rename queries if you are working on live DB. – Navidot Aug 30 '19 at 13:02
1

Does this work if table have referential constraints? I expect not. – dolmen Nov 05 '19 at 16:25
This is a great solution, especially when you do not have to move stored procedures, triggers, and events. – dankilev Mar 25 '22 at 13:13
it works like magic!!! – Shawn Guo Mar 09 '23 at 04:06

score 33 · Answer 6 · edited Apr 09 '18 at 15:59

33

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

edited Apr 09 '18 at 15:59

Mikkel

1,192
9
22

answered Feb 07 '13 at 06:54

Grijesh Chauhan

57,103
20
141
208

9

Careful with this. If you're not logging in with root user, you may have limited permission. Causing the rename to fail but the drop to succeed resulting in a dropped database. Nice script otherwise. – Lex Mar 06 '13 at 21:46
5

I added `set -e` to the beginning of the script, which will cause execution to terminate on failure and should mitigate that problem. – Mikkel Apr 09 '18 at 16:00
somewhere in the middle, i got the error ERROR 1435 (HY000) at line 1: Trigger in wrong schema – pymen Aug 20 '21 at 09:16
@pymen can you share the screenshot for the same? – Grijesh Chauhan Aug 20 '21 at 10:41

score 25 · Answer 7 · edited Nov 09 '15 at 11:45

25

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.

edited Nov 09 '15 at 11:45

Peter Mortensen

30,738
21
105
131

answered Sep 15 '08 at 21:28

longneck

287
2
2

+ for the myisam reference. I couldn't figure out why this hadn't worked for me. – Christian Payne Oct 14 '10 at 03:23
6

The question states that this must work for InnoDB, not MyISAM – D-Rock Mar 12 '12 at 17:06
@D-Rock tell that to Google, who brings people here based on the title. – jiggunjer Apr 04 '20 at 15:02

score 23 · Answer 8 · edited Jun 20 '20 at 09:12

23

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 15 '08 at 21:30

DeeCee

400
2
6

21

Renaming folders breaks toys. – ViniciusPires Jul 24 '13 at 20:54
1

@Rahly, even if one file per table is set, it's still dangerous, the tables created before one file per table was set will be in trouble, unless you know for sure that the database is created after that flag was set. – Jun 05 '15 at 13:18
Generally speaking though, most people are going to have their systems either one way or the other, people are not going to be randomly flip flopping on whether to have or have not one table per file. Besides, even in your scenario, if the tables were created before the flag, they wouldn't exist as separate files in the first place, so the move wouldn't work and its still safe, no danger. Remember, the database is NOT running when the move is taking place. – Rahly Jun 10 '15 at 00:51
The equivalent for mysql installed with homebrew on OS X: `launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist cd /usr/local/var/mysql mv old-name new-name launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist` – coberlin Aug 19 '15 at 13:28
Be careful renaming the folder names, it can contain hidden files that refers to old folder – MGE Jun 26 '21 at 11:36

Steve Chambers · Answer 9 · 2017-05-16T09:38:22.847

21

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

edited May 16 '17 at 09:38

answered Jan 14 '16 at 16:42

Steve Chambers

37,270
24
156
208

Avoid adding your password to the console since it is not secure. If you've done this already use history -cw to remove. Instead leave the password empty and enter it after the prompt. – Tommie C. Sep 19 '17 at 05:51
It is taking abnormally long, more than 20 minutes without finishing up. Is it okay to cancel? – Sigu Magwa Sep 10 '18 at 09:54

score 19 · Answer 10 · answered May 07 '10 at 13:15

19

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.

answered May 07 '10 at 13:15

Amr Mostafa

23,147
2
29
24

@ArkadijKuzhel don't think so. I think you're talking about RENAME DATABASE. – Rob Grant Sep 15 '15 at 20:08
This really helped, I created a new blank Database and then used the code, all tables were imported with the desired names. – Incredible May 12 '16 at 06:47
4

This suffers from the same problem as the accepted answer — "RENAME DATABASE was found to be dangerous and was removed in MySQL 5.1.23" - from dev.mysql.com/doc/refman/5.1/en/rename-database.html – Blake Frederick Aug 09 '16 at 17:27

score 15 · Answer 11 · answered Jan 29 '14 at 11:14

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 > emp.out
 mysql -e "CREATE DATABASE employees;"
 mysql employees < 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 <old_schema_name> -d -t -R -E > 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> select TABLE_NAME from information_schema.tables where 
    table_schema='<old_schema_name>' 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> select TABLE_NAME from information_schema.tables where 
   table_schema='<old_schema_name>' and TABLE_TYPE='VIEW';
 $ mysqldump <database> <view1> <view2> … > views.out

4) Drop the triggers on the current tables in the old_schema.

mysql> DROP TRIGGER <trigger_name>;
...

5) Restore the above dump files once all the “Base” tables found in step #2 are renamed.

mysql> RENAME TABLE <old_schema>.table_name TO <new_schema>.table_name;
...
$ mysql <new_schema> < views.out
$ mysql <new_schema> < 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 <server> <database> <new_database>

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> 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> 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 <server> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $3"
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "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 > /tmp/${2}_views${TIMESTAMP}.dump
fi
mysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h $1 $2 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
    echo "rename table $2.$TABLE to $3.$TABLE"
    mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database $2"
    mysql -h $1 $2 -e "drop database $2"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sss` -gt 0 ]; then
    TABLES_PRIV="    UPDATE mysql.tables_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='$3' WHERE db='$2';"
fi
if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"
    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
    echo "    flush privileges;"
fi

What about referential constraints? – dolmen Nov 05 '19 at 16:29 — dolmen, Nov 05 '19 at 16:29

score 15 · Answer 12 · answered Oct 28 '10 at 15:01

15

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;

answered Oct 28 '10 at 15:01

eaykin

3,713
1
37
33

14

Not doable for huge databases. – mikesl Sep 21 '11 at 21:24

score 14 · Answer 13 · edited Jun 16 '17 at 23:06

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

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

I like this script, it is almost universal. However it failed to process a case when there are several chained VIEWs where definer is not root. — ENargit, Dec 26 '17 at 11:44

score 14 · Answer 14 · answered Sep 15 '08 at 21:26

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.

score 12 · Answer 15 · answered Nov 30 '12 at 01:06

12

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

answered Nov 30 '12 at 01:06

Duke

7,070
3
38
28

5

Beware of this option if you have any views or triggers in your database. Behind this menu option is a script that will create a new database and move all tables over. This will not work for views or triggers, so they will be left behind in your old database. The result is two broken databases in need of fixing. – Olfan Jan 14 '15 at 15:02

score 11 · Answer 16 · answered Sep 21 '17 at 15:22

11

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.

answered Sep 21 '17 at 15:22

Tuncay Göncüoğlu

1,699
17
21

2

As far as I know 5.x does not support "like" keyword in `create database` statement? Where'd you get that from? – Dragas May 03 '18 at 10:44
1

Here is the link for `create table like` syntax: https://dev.mysql.com/doc/refman/5.7/en/create-table-like.html . As for create database like, it seems MySQL dropped that clause since. – Tuncay Göncüoğlu May 04 '18 at 11:40

score 10 · Answer 17 · answered May 19 '10 at 17:16

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

small tweak which makes this work w/ mysql 5.x `mysql --batch-uroot -e "call mysql.rename_db('test', 'blah2');" | mysql -uroot` Notice, you have to use --batch to change formatting to raw formatting which outputs the results w/ zero formatting. — mikesl, Sep 21 '11 at 21:21

score 10 · Answer 18 · answered Oct 04 '17 at 06:51

10

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

answered Oct 04 '17 at 06:51

Roee Gavirel

18,955
12
67
94

It sometimes leave the old DB alive but it is empty. Still, if it makes a copy, you can make the copy and delete the old one, it is still 2 simple steps. – Roee Gavirel Oct 07 '17 at 09:10
My `stored procedures` and `views` were not copied along with the rename – Wayne Jul 28 '20 at 01:49

score 9 · Answer 19 · edited Nov 09 '15 at 12:08

9

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

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

#!/usr/bin/env bash

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

edited Nov 09 '15 at 12:08

Peter Mortensen

30,738
21
105
131

answered Mar 15 '12 at 14:05

gerrit damen

31
1
1

1

This, too, will not work for tables with triggers attached, or for views which can't be renamed into other databases. – Olfan Jan 14 '15 at 15:16

score 8 · Answer 20 · edited Nov 09 '15 at 12:58

8

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

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

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

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

It is so simple.

edited Nov 09 '15 at 12:58

Peter Mortensen

30,738
21
105
131

answered Feb 07 '13 at 07:02

Fathah Rehman P

8,401
4
40
42

2

If it has stored procedures it can not be renamed. – abksharma Sep 28 '16 at 07:32
@abksharma Actually you'll get message `Database "database_name" contains stored routine(s) which cannot be moved.` Triggers (at least for MariDB database) are counted as stored routines. I didn't have any stored procedures, but was unable to rename the database until I dropped all the triggers. – izogfif Feb 11 '20 at 10:06

score 8 · Answer 21 · answered Dec 12 '08 at 10:27

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=

score 7 · Answer 22 · answered May 17 '11 at 18:24

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 ;

This will work only for tables, and only if these tables don't have any triggers. Views and triggers will not be moved by this. — Olfan, Jan 14 '15 at 15:05

score 6 · Answer 23 · answered Dec 10 '20 at 12:55

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.

score 5 · Answer 24 · edited Nov 09 '15 at 12:16

5

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.

edited Nov 09 '15 at 12:16

Peter Mortensen

30,738
21
105
131

answered Aug 16 '12 at 02:04

xelber

4,197
3
25
33

7

Do you have any example syntax? I don't know of any way to use `alter database` to rename the database itself, and the documentation you linked to doesn't suggest that it's possible to. – Jordan Aug 16 '12 at 02:10
@Jordan I'd be interested, too. I tried and tried and found out, that it only works with version > 5.1 but I can't update right now. – fancyPants Aug 21 '12 at 14:10
6

-1: For writing about proposed ways, then giving an example of the non-proposed way while totally missing to even show example. – hakre Jun 04 '14 at 10:53
4

This is wrong. [MySQL rename database documentation](http://dev.mysql.com/doc/refman/5.1/en/rename-database.html) says rename_database was intended for a very specific renaming task (not general case of DB renaming), which is now handled with alter database: 'To perform the task of upgrading database names with the new encoding, use ALTER DATABASE db_name UPGRADE DATA DIRECTORY NAME instead' You can't use this to rename database as you wish, there is not even any place for new db name in this command! – Kanat Bolazar Jan 15 '15 at 00:21

score 5 · Answer 25 · answered Aug 21 '13 at 06:17

5

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

answered Aug 21 '13 at 06:17

murtaza.webdev

3,523
4
22
32

score 5 · Answer 26 · answered Aug 11 '14 at 19:00

5

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

answered Aug 11 '14 at 19:00

yantaq

3,968
2
33
34

Looks good, but this doesn't move the stored procedures or views. – davidpricedev Jan 23 '15 at 15:35
you should probably add hash marks to wrap around the table name and schema name – dansch Mar 09 '15 at 19:14

score 4 · Answer 27 · answered Oct 03 '08 at 04:17

4

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.

answered Oct 03 '08 at 04:17

MySQL Administrator can't handle big databases and there's nothing quick about it – deadprogrammer Oct 03 '08 at 21:08

score 4 · Answer 28 · answered Jul 26 '17 at 01:03

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

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

score 4 · Answer 29 · edited Nov 09 '15 at 12:11

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

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

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

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

score 3 · Answer 30 · answered Jan 11 '13 at 22:33

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.

score 3 · Answer 31 · edited Nov 09 '15 at 12:04

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

score 2 · Answer 32 · edited Sep 06 '12 at 12:47

2

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.

edited Sep 06 '12 at 12:47

j0k

22,600
28
79
90

answered Sep 06 '12 at 12:45

jeeva

1,573
2
15
24

score 2 · Answer 33 · answered Oct 12 '10 at 18:30

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.

score 1 · Answer 34 · answered Sep 11 '13 at 05:24

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

score 1 · Answer 35 · answered Aug 21 '15 at 12:48

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

It loooks like this:

#!/usr/bin/env perl

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


my $views_sql;

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

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

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

score 1 · Answer 36 · answered Jan 28 '22 at 16:33

1

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

answered Jan 28 '22 at 16:33

ᴍᴇʜᴏᴠ

4,804
4
44
57

score 0 · Answer 37 · answered Sep 02 '14 at 11:52

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;

RotS · Answer 38 · 2017-07-03T15:44:33.387

0

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.

edited Jul 03 '17 at 15:44

answered Jul 03 '17 at 15:38

RotS

2,142
2
24
30

2

...and if that old database name is present in any string value in data, you will have also destroyed your data. ouch. – Tuncay Göncüoğlu Aug 28 '18 at 15:48

score -1 · Answer 39 · edited Dec 07 '14 at 03:03

-1

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

edited Dec 07 '14 at 03:03

Buhake Sindi

87,898
29
167
228

answered Aug 02 '13 at 13:20

Lawrence

1,035
13
8

3

Multiple reasons why this is a terrible answer, already discussed on this page. – gwideman Jul 30 '14 at 11:14

score -1 · Answer 40 · edited May 23 '17 at 12:02

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.

score -1 · Answer 41 · answered Jul 16 '20 at 05:02

-1

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

answered Jul 16 '20 at 05:02

nafischonchol

104
1
9

score -1 · Answer 42 · answered Jan 20 '22 at 10:51

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

Drop your old schema with old name.

You are ready to rock....

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

score -3 · Answer 43 · edited Nov 09 '15 at 11:57

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.

score -3 · Answer 44 · answered May 22 '16 at 21:33

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

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

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

score -3 · Answer 45 · edited Nov 09 '15 at 12:05

-3

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!

edited Nov 09 '15 at 12:05

Peter Mortensen

30,738
21
105
131

answered Jan 05 '12 at 10:04

i.jolly

61
2
9

10

You are obviously referring to some GUI management tool. It would help if you said what that was. – phils Jan 25 '12 at 01:46
I think it is a good solution. Though my question is how to add external database into phpmyadmin. I mean how to manage many mysql instance in a single phpmyadmin site. – truease.com Jul 26 '12 at 09:13

overals · Answer 46 · 2018-07-16T20:13:55.423

-6

The simple way

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

or you can use online sql generator

edited Jul 16 '18 at 20:13

answered Jul 16 '18 at 18:59

overals

92
3

3

there is no MODIFY NAME in mysql – Asmir Mustafic Sep 18 '18 at 12:06

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

46 Answers46

The simple way

-- http://www.phpmyadmin.net

-- Database: `mydatab_online`

-- Table structure for table `user`

-- Dumping data for table `user`

Linked

Related

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

46 Answers46

The simple way

-- http://www.phpmyadmin.net

-- Database: mydatab_online

-- Table structure for table user

-- Dumping data for table user

Linked

Related

-- Database: `mydatab_online`

-- Table structure for table `user`

-- Dumping data for table `user`