Mysql rename database does not work

Question

Unable to rename the database with the following query. I am using mysql2

RENAME DATABASE a to b;

ERROR 1064 (42000): 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 a to b' at line 1

I am new to mysql. What is the correct syntax?

possible duplicate of [How do I quickly rename a mysql database (change schema name)?](http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name) — Ole Haugset, Sep 30 '14 at 13:02
That command was removed see - http://dev.mysql.com/doc/refman/5.1/en/rename-database.html. Use ALTER DATABASE instead. — Giles, Sep 30 '14 at 13:04
@Jeff Perhaps you should also read the links that Giles posted? — fancyPants, Sep 30 '14 at 13:22

score 1 · Answer 1 · edited Oct 01 '14 at 05:41

1

That command was removed see - dev.mysql.com/doc/refman/5.1/en/rename-database.html . Use ALTER DATABASE instead.

edited Oct 01 '14 at 05:41

Sam

5,040
12
43
95

answered Sep 30 '14 at 14:40

Giles

1,597
11
15

score 0 · Answer 2 · answered Sep 30 '14 at 13:23

I think you should create a new database and rename the tables so they will be in the new database, and the old one can be dropped afterwards.

Find more here: https://blog.marceloaltmann.com/how-to-rename-a-database-in-mysql/

But basically something like:

CREATE DATABASE my_new_database;
RENAME TABLE my_old_database.tbl_name TO my_new_database.tbl_name;
DROP DATABASE my_old_database;

Using the script from the link above will allow you to use all your tables, so this can be done almost automatically.

(I mean this: SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT( table_schema,'.',table_name, ' TO ','new_schema.',table_name,' '),';') as stmt FROM information_schema.TABLES WHERE table_schema LIKE 'oldSchema' GROUP BY table_schema)

Mysql rename database does not work

2 Answers2