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

2

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.

answered Mar 13, 2014 at 9:06

codemania

1,0901 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

1

I follow these simple steps:

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

answered Mar 13, 2014 at 9:04

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Comments

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

1

You can use mysqldump

using mysqldump

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

answered Mar 13, 2014 at 9:05

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Comments

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

0

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

answered Mar 13, 2014 at 9:04

giammin

19.1k9 gold badges74 silver badges90 bronze badges

Collectives™ on Stack Overflow

How to rename database in MySQL without loosing data?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related