Rename mysql database?

Question

How can I rename the MySQL database name using query?

I tried rename database via phpmyadmin and getting the following error,

SQL query:

    RENAME DATABASE test TO test_bkp

MySQL said: 

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE test To test_bkp' at line 1

The whole point of phpMyAdmin is to offer a GUI, so that you don't have to write such queries yourself — Osiris, Nov 30 '12 at 10:58

score 16 · Answer 1 · answered Nov 30 '12 at 10:58

16

In phpmyadmin you can just click on your database, then go to the Operations tab which lets you rename it.

answered Nov 30 '12 at 10:58

Lucas Arrefelt

3,879
5
41
71

2

that is also removed in latest `XAMPP` – xkeshav Nov 30 '12 at 10:59
1

No, it depends on MySQL version and PMA version – xkeshav Nov 30 '12 at 11:00

Salman A · Accepted Answer · 2012-11-30T11:52:31.067

It says here that:

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names (see Section 9.2.3, “Mapping of Identifiers to File Names”). However, 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.

You can import all data into a new database then delete the old one.

It is also possible to use the RENAME TABLE statement using same from/to table name but different from/to database. There are a few catches.

CREATE DATABASE `test_bkp`;

RENAME TABLE 
`test`.`table1` TO `test_bkp`.`table1`,
`test`.`table2` TO `test_bkp`.`table2`,
`test`.`table3` TO `test_bkp`.`table3`;

The 5.1 manual is no longer on Oracle's site. (But probably can be found elsewhere.) Feel free to add a comment to http://bugs.mysql.com/86289 . Or at least click on "Affects me". — Rick James, May 11 '17 at 21:18

score 2 · Answer 3 · edited May 23 '17 at 10:30

2

It's been disabled by the developers. See http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

Adding to this, I'd use a copy (then delete) rather than a rename. Have a look at Cloning a MySQL database on the same MySql instance

edited May 23 '17 at 10:30

Community

1
1

answered Nov 30 '12 at 10:58

joedborg

17,651
32
84
118

I got the error, `SQL query:` `ALTER DATABASE test_bkp UPGRADE DATA DIRECTORY NAME;` MySQL said: `#1221 - Incorrect usage of ALTER DATABASE UPGRADE DATA DIRECTORY NAME and name` – Ramesh Moorthy Nov 30 '12 at 11:08

score 0 · Answer 4 · answered Nov 30 '12 at 10:57

0

You can do this in MySQL < 5.1.23 with

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

In later versions have a look at

ALTER {DATABASE | SCHEMA} [db_name]

answered Nov 30 '12 at 10:57

Alex2php

10,337
1
16
22

I tried the query, getting error again, `SQL query:` `RENAME { DATABASE | SCHEMA}test TO test_bkp;` MySQL said: `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{DATABASE | SCHEMA} test TO test_bkp' at line 1` – Ramesh Moorthy Nov 30 '12 at 11:04
Hi, {DATABASE | SCHEMA} is just a placeholder, use DATABASE. – Alex2php Nov 30 '12 at 11:50

score 0 · Answer 5 · answered Dec 02 '14 at 06:14

Ok, so if the rename option doesn't show, here a quick way of cloning a database using phpMyAdmin:

Open the database you want to copy
Click the Operations tab.
where it says "Copy database to:" type in the name of the new database. Select "structure and data" to copy everything or "Structure only" Check the box "CREATE DATABASE before copying" to create a new database. Check the box "Add AUTO_INCREMENT value." Click on the Go button to proceed. NB before deleting the old database, make sure that passwords have also copied over.

score 0 · Answer 6 · answered Dec 22 '16 at 07:24

0

in wamp server click on databases then choose your database from header menu choose operations make the new name it will rename the database but actually it create a new database copy all the data then drop the old data base

answered Dec 22 '16 at 07:24

Amado

371
1
16

score 0 · Answer 7 · answered Feb 09 '18 at 08:30

0

Export current database.
create a new database with a name you want.
import old database file on the new database.

answered Feb 09 '18 at 08:30

Manoj kumar

1
2
6

score 0 · Answer 8 · answered Apr 20 '18 at 20:04

Well, it's possible to rename your database but here's the logic behind the rename option.

1. Xampp creates a new database with the name
2. Xampp copies all table and data in it from the previous database
3. Xampp migrates data to the new database table and drops the previous database

Steps to rename Database

1. Click the Operations option in the nav pane
2. Find the ' Rename database to ' Spot, and input new name in the input field
3. Tick the ' Adjust privileges ' checkbox
4. Click go

I hope this works! :)

score -1 · Answer 9 · answered Jun 19 '16 at 09:52

-1

you can export your databse table, create a new one and import table to the new database! also you can rename your data base using phpmyadmin by selecting your database and click on operations tab and rename your database! the last and not recommended way is create a new databse and use this, but you may lost your data!

CREATE DATABASE new_db_name / DROP DATABASE old_db_name

answered Jun 19 '16 at 09:52

Hamid

1,493
2
18
32

Hamid, did you read that Salman's post. He already mentioned that thought. – Ramesh Moorthy Jun 20 '16 at 07:23

Rename mysql database?

9 Answers9

Steps to rename Database

Linked