How do I change the database name using MySQL?

Question

How can I change the database name of my database?

I tried to use the rename database command, but on the documents about it it is said that it is dangerous to use. Then what should I need to do to rename my database name?

For example, if I want to rename my database to this.

database1 -> database2?

Please refer to the following link: http://stackoverflow.com/questions/12190000/rename-mysql-database — nom_nutella, Nov 06 '13 at 07:12
Ah ok so there's no code for that. So I just need to dump my database and rename it. Thanks — Jerielle, Nov 06 '13 at 07:13
Yes that's a better idea, also please check http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name — MusicLovingIndianGirl, Nov 06 '13 at 07:15
This question should not been closed, it refers to a answer without mysql. — Grumpy, Feb 17 '23 at 14:49

score 44 · Answer 1 · answered Sep 17 '16 at 07:46

44

Follow bellow steps:

shell> mysqldump -hlocalhost -uroot -p  database1  > dump.sql

mysql> CREATE DATABASE database2;

shell> mysql -hlocalhost -uroot -p database2 < dump.sql

If you want to drop database1 otherwise leave it.

mysql> DROP DATABASE database1;

Note : shell> denote command prompt and mysql> denote mysql prompt.

answered Sep 17 '16 at 07:46

Nanhe Kumar

15,498
5
79
71

4

Thanks for providing a detailed solution instead of saying why it can't be done – Haydentech Jul 05 '17 at 21:45
1

Just a note that specific user permissions need to be readded to the new database. – ngm Jan 24 '18 at 11:59
Don't forget to open cmd using **Run as Administrator**. Otherwise you will get [access denied](https://stackoverflow.com/a/19602502/8383332). – Soon Santos Dec 13 '18 at 11:11
This should be the accepted answer. It only misses the sentence, "you cannot really rename a mysql database, you'll have to copy it and delete the original", but that's been abundantly said in the other answers. – Francesco Marchetti-Stasi Jul 09 '20 at 15:26

score 20 · Accepted Answer · edited Nov 09 '15 at 13:01

20

I don't think it's possible.

You can use mysqldump to dump the data and then create a schema with your new name and then dump the data into that new database.

edited Nov 09 '15 at 13:01

Peter Mortensen

30,738
21
105
131

answered Nov 06 '13 at 07:16

Mad Dog Tannen

7,129
5
31
55

score 16 · Answer 3 · edited Jun 20 '20 at 09:12

16

Unfortunately, MySQL does not explicitly support that (except for dumping and reloading database again).

From http://dev.mysql.com/doc/refman/5.1/en/rename-database.html:

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 was found to be dangerous and was removed in MySQL 5.1.23. ... Use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 06 '13 at 07:18

mvp

111,019
13
122
148

Thanks for the tip.. I was about to do rename database then saw your answer. can you suggest any workaround for this. I have just imported 4gb db and want to rename it. dont wanna re-import it as it's taking so much time. – Suraj Nov 16 '16 at 11:25

score 6 · Answer 4 · answered Jan 17 '17 at 21:31

6

"As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another"

-- ensure the char set and collate match the existing database.
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';


CREATE DATABASE `database2` DEFAULT CHARACTER SET = `utf8` DEFAULT COLLATE = `utf8_general_ci`;
RENAME TABLE `database1`.`table1` TO `database2`.`table1`;
RENAME TABLE `database1`.`table2` TO `database2`.`table2`;
RENAME TABLE `database1`.`table3` TO `database2`.`table3`;

http://dev.mysql.com/doc/refman/5.7/en/rename-table.html

answered Jan 17 '17 at 21:31

txyoji

6,680
1
44
46

That's renaming a table, not renaming a database. – Douglas Gaskell Feb 18 '17 at 02:44
2

True but the end result is the same. All your data ends up in a new database and you don't need to reload it. Note the caviot of the new and old database needing to be on the same logical disk. – txyoji Feb 19 '17 at 12:10
1

I like this idea. True, it doesn't answer the OP's original question, but it does provide a mechanism that allows the OP do to what they wanted. Assuming the database has hundreds of thousands of rows in tens of tables, it's likely to be a lot quicker. It certainly worked out that way for me, with a database of three tables, on of which had 2 million rows. – nurdglaw Jun 28 '21 at 18:32

score 5 · Answer 5 · edited Nov 09 '15 at 13:02

5

You can change the database name using MySQL interface.

Go to http://www.hostname.com/phpmyadmin

Go to database which you want to rename. Next, go to the operation tab. There you will find the input field to rename the database.

edited Nov 09 '15 at 13:02

Peter Mortensen

30,738
21
105
131

answered Nov 06 '13 at 07:22

user1407252

91
7

score 3 · Answer 6 · answered Apr 16 '20 at 11:25

3

InnoDB supports RENAME TABLE statement to move table from one database to another. To use it programmatically and rename database with large number of tables, I wrote a couple of procedures to get the job done. You can check it out here - SQL script @Gist

To use it simply call the renameDatabase procedure.

CALL renameDatabase('old_name', 'new_name');

Tested on MariaDB and should work ideally on all RDBMS using InnoDB transactional engine.

answered Apr 16 '20 at 11:25

shivanshs9

96
1
8

To be safe with some schema and table names, back-ticks (`)are needed around the schema and table names. I added a comment to the Gist with the corrections. – E-Riz Jul 10 '23 at 21:05

score 1 · Answer 7 · answered Oct 22 '14 at 07:49

I agree with above answers and tips but there is a way to change database name with phpmyadmin

Renaming the Database From cPanel, click on phpMyAdmin. (It should open in a new tab.) Click on the database you wish to rename in the left hand column. Click on the Operations tab. Where it says "Rename database to:" enter the new database name. Click the Go button. When it asks you to want to create the new database and drop the old database, click OK to proceed. (This is a good time to make sure you spelled the new name correctly.) Once the operation is complete, click OK when asked if you want to reload the database.

here's the video tutorial:

http://support.hostgator.com/articles/specialized-help/technical/phpmyadmin/how-to-rename-a-database-in-phpmyadmin

score 0 · Answer 8 · edited Nov 09 '15 at 13:03

0

Another way to rename the database or taking an image of the database is by using the reverse engineering option in the database tab. It will create an ER diagram for the database. Rename the schema there.

After that, go to the File menu and go to export and forward engineer the database.

Then you can import the database.

edited Nov 09 '15 at 13:03

Peter Mortensen

30,738
21
105
131

answered Mar 07 '14 at 08:59

Benny

432
1
6
21

MySQL Workbench would be the name of the tool that he is referencing. – Gwi7d31 Jun 03 '16 at 20:55

score 0 · Answer 9 · answered Oct 10 '22 at 14:36

0

Sequel Ace database client have a rename database functionality. Select the database you would like to edit and click Database in the menu and then click Rename Database from the dropdown. Rename the database and ckick rename. Done!

answered Oct 10 '22 at 14:36

MugoTech

11
3

E.R.Rider · Answer 10 · 2014-04-20T16:03:26.150

After much aggravation this is what I have found to work"simply". First thing, I am using MYSQL Workbench and the import would not work as it should, as the import dump file would always revert to the original schema name. I spent several hours trying every thing to no avail,all for a spelling error. I solved the issue by opening one of the .sql dump files in notebook and hand editing the typo's of the schema name, take care to rename all instances schema name has three in the beginning, save the file and then import. this worked perfectly for me and hope that it will help others looking for the simple answer to changing database names/schema names. One more tip that I have found true, when programs do not do as they should go to the "source" literally find the source code. Hope this helps someone

Low rep so they wont let me comment on the prior/post answer(it keeps changing rank or position), so I added it here. reverse engineering will work fine as long as there is no data in the sever table. if data exists and you try to update the server after the name change it will either pull an error or just create a new database/schema with no data, I know I tried ten times to no avail. The above works simply and avoids headaches, as one can review the SQL code for other errors if any or change table names or creation data. the .sql file is just a compiled SQL code so in theory one could copy and add it through PHP or the script console of the database management tool.

score -1 · Answer 11 · edited Jan 05 '22 at 10:23

-1

You can use below command

alter database Testing modify name=LearningSQL;

Old Database Name = Testing,
New Database Name = LearningSQL

edited Jan 05 '22 at 10:23

RiggsFolly

93,638
21
103
149

answered Sep 25 '17 at 12:07

Ashutosh Kumar

7
2

score -4 · Answer 12 · edited May 18 '18 at 08:09

-4

Go to data directory and try this:

mv database1 database2

It works for me on a 900 MB database size.

edited May 18 '18 at 08:09

IKavanagh

6,089
11
42
47

answered May 18 '18 at 07:51

user9810365

1

This works in one very specific use case... your using MyISAM as the storage engine. Make sure you've shutdown mysql before doing that and you're not using replication. Direct changes to the file system like that won't be picked up by any replicas. – txyoji Mar 08 '22 at 22:00

score -5 · Answer 13 · edited Nov 09 '15 at 13:01

-5

Try:

RENAME database1 TO database2;

edited Nov 09 '15 at 13:01

Peter Mortensen

30,738
21
105
131

answered Nov 06 '13 at 07:10

MusicLovingIndianGirl

5,909
9
34
65

I tried but in the document that I read about it you may loose your data if you perform this code. – Jerielle Nov 06 '13 at 07:11
1

This is the general syntax but it is always better to drop the database and create a new one instead. – MusicLovingIndianGirl Nov 06 '13 at 07:15
Thanks.That's the best solution for that. – Jerielle Nov 06 '13 at 07:17
This statement RENAME DATABASE Syntax was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23 – Ahmad May 04 '21 at 08:16

How do I change the database name using MySQL?

13 Answers13

Linked