Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rename mysql database?

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
like image 918
Ramesh Moorthy Avatar asked Nov 30 '12 10:11

Ramesh Moorthy


2 Answers

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`;
like image 198
Salman A Avatar answered Oct 22 '22 00:10

Salman A


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

like image 30
Lucas Arrefelt Avatar answered Oct 22 '22 02:10

Lucas Arrefelt