MySQL 5.1 Reference Manual :: 12 SQL Statement Syntax :: 12.1 Data Definition Statements :: 12.1.32 RENAME DATABASE Syntax
Section Navigation [Toggle]
- 12.1 Data Definition Statements
- 12.1.1 ALTER DATABASE Syntax
- 12.1.2 ALTER EVENT Syntax
- 12.1.3 ALTER LOGFILE GROUP Syntax
- 12.1.4 ALTER FUNCTION Syntax
- 12.1.5 ALTER PROCEDURE Syntax
- 12.1.6 ALTER SERVER Syntax
- 12.1.7 ALTER TABLE Syntax
- 12.1.8 ALTER TABLESPACE Syntax
- 12.1.9 ALTER VIEW Syntax
- 12.1.10 CREATE DATABASE Syntax
- 12.1.11 CREATE EVENT Syntax
- 12.1.12 CREATE FUNCTION Syntax
- 12.1.13 CREATE INDEX Syntax
- 12.1.14 CREATE LOGFILE GROUP Syntax
- 12.1.15 CREATE PROCEDURE and CREATE FUNCTION Syntax
- 12.1.16 CREATE SERVER Syntax
- 12.1.17 CREATE TABLE Syntax
- 12.1.18 CREATE TABLESPACE Syntax
- 12.1.19 CREATE TRIGGER Syntax
- 12.1.20 CREATE VIEW Syntax
- 12.1.21 DROP DATABASE Syntax
- 12.1.22 DROP EVENT Syntax
- 12.1.23 DROP FUNCTION Syntax
- 12.1.24 DROP INDEX Syntax
- 12.1.25 DROP LOGFILE GROUP Syntax
- 12.1.26 DROP PROCEDURE and DROP FUNCTION Syntax
- 12.1.27 DROP SERVER Syntax
- 12.1.28 DROP TABLE Syntax
- 12.1.29 DROP TABLESPACE Syntax
- 12.1.30 DROP TRIGGER Syntax
- 12.1.31 DROP VIEW Syntax
- 12.1.32 RENAME DATABASE Syntax
- 12.1.33 RENAME TABLE 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. 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 8.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.
To perform the task of upgrading database names with the new
encoding, use ALTER DATABASE
instead (see Section 12.1.1, “db_name UPGRADE DATA DIRECTORY
NAMEALTER DATABASE Syntax”).
User Comments
You can change the database, table by table, using RENAME TABLE:
CREATE DATABASE new_db_name;
for each table: RENAME TABLE old_db_name.table_name TO new_db_name.table_name;
For some reasons table with triggers gave me a error and rename didn't work. I was forced to drop the triggers and after to recreate them in the new database.
I did SHOW TRIGGERS; to get all the triggers name after I did SHOW CREATE TRIGGER name; for every trigger, copied the results to a file, after I did DROP TRIGGER name; and finally I did RENAME TABLE .. (like in the beginning) After the table is in the new database, you can create the triggers.
Hope this helped some one.
Cheers from Moldova.
You don't have to do it on a table basis. Just use mysqldump to a file without the create-db option (msyqldump --routines xyz > dump.sql). Then create your new database and source that dump.
Add your own comment.