How Do I Change Mysql Database Names?

[David Valdes]

How do I change the name of a MySQL database without disrupting the tables and data?

[freddy]

RENAME TABLE Syntax

 

 

RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]

 

The rename is done atomically, which means that no other thread can access any of the tables while the rename is running. This makes it possible to replace a table with an empty one:

 

 

CREATE TABLE new_table (...);

RENAME TABLE old_table TO backup_table, new_table TO old_table;

 

The rename is done from left to right, which means that if you want to swap two tables names, you have to:

 

 

RENAME TABLE old_table TO backup_table,

new_table TO old_table,

backup_table TO new_table;

 

As long as two databases are on the same disk you can also rename from one database to another:

 

 

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

 

When you execute RENAME, you can't have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

 

If MySQL encounters any errors in a multiple-table rename, it will do a reverse rename for all renamed tables to get everything back to the original state.

 

RENAME TABLE was added in MySQL 3.23.23.

 

Taken from the MySQL Reference Manual © 2002 MySQL AB

 

 

Hope that helps

[David Valdes]

Thanks-useful info but what about the database itself. Can the name of the database be changed without creating a new blank one and importing the tables and data?

[surefire]

I don't know if this is the most efficient way of doing it... but I'd export the info (yes, the entire database) into a text file and then upload it again, changing the name of the database in the text file mysql language.

 

Go to phpmyadmin, choose your database, choose export, choose 'select all' and 'save as file' and 'structure and data'. After the file download to your computer, look at the file in a text editor and it will be pretty obvious where to change the name of the database.

 

Then just go to phpmyadmin and upload your database under the new name.