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
In phpmyadmin you can just click on your database, then go to the Operations tab which lets you rename it.
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`;
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
SQL query: ALTER DATABASE test_bkp UPGRADE DATA DIRECTORY NAME; MySQL said: #1221 - Incorrect usage of ALTER DATABASE UPGRADE DATA DIRECTORY NAME and nameYou 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
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
Ok, so if the rename option doesn't show, here a quick way of cloning a database using phpMyAdmin:
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
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
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! :)
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
