How to rename database in MySQL without loosing data?

Question

I am trying to rename my database by the following query:

RENAME DATABASE my_db TO newDB;

but its showing me the following error response:

Error Code: 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 activation_server_db TO activationserver' at line 1

Please help me find where I am going wrong?

@codemania , hi I am new user on stackoverflow, what does this mean when you get votes in minus, as in this post I have -2 votes? — Amir
– Amir, Commented Mar 13, 2014 at 9:08
@amir see this for your question about voating stackoverflow.com/help/privileges/vote-down — codemania
– codemania, Commented Mar 13, 2014 at 9:12
@codemania , oh my GOD stackoverflow is a whole science :P , its really interesting! — Amir
– Amir, Commented Mar 13, 2014 at 9:28

codemania · Accepted Answer · 2014-03-13 09:06:40Z

Use these few simple commands

mysqldump -u username -p -v olddatabase > olddbdump.sql mysqladmin -u username -p create newdatabase mysql -u username -p newdatabase < olddbdump.sql

or For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

Joe Taras · Accepted Answer · 2014-03-13 09:04:24Z

I follow these simple steps:

Create new database
Backup the old database
Restore old database under new database

Vignesh Kumar A · Accepted Answer · 2014-03-13 09:05:48Z

You can use mysqldump

using mysqldump mysqldump [OPTIONS] --database oldSchema > oldSchema.sql mysql new_schema < oldSchema.sql

giammin · Accepted Answer · 2014-03-13 09:04:23Z

You need to create a dump of your db and then create a new db with different name with that dump.

If it is online you need to take ofline it for avoiding data loss

Collectives™ on Stack Overflow

How to rename database in MySQL without loosing data?

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related