mysql: How can I rename a database

16 comments. Current rating: (8 votes). Leave comments and/ or rate it.

Question:

I want to rename my database from 'TEST' to something more meaningful. Do I have to make a backup, modify the SQL script and then restore it?

Answer:

While that would work, there is an easier way. Shutdown the mysql server, rename the directory that stores the database from 'TEST' to your desired name. This 'TEST' directory is below the data directory. If you don't know where that is, try:

$ mysqladmin variables | grep datadir

After renaming, restart the MySQL server and check the grants - they might still contain references to the old database name.

You can also use a tool like phpMyAdmin (web interface to mysql) which has an option to rename a database.

Comments:

You are on page 1 of 2, other pages: [1] 2

2006-02-24, 18:13:55

anonymous from Hungary

2006-03-13, 00:51:59

anonymous from United States

Worked perfectly! If you're renaming the DB by command line, you'll want to grant user permissions to the new DB and delete permissions granted for the old DB. At a minimum, you'll need to run the following SQL: remove from mysql.db where db='oldDBname';

2006-04-04, 07:02:07

[hidden] from Italy

try this.. $ mysqladmin create new_db $ mysqldump --opt old_db | mysql new_db mysql>; revoke all from old_db_user@host;

2008-01-21, 07:49:01

anonymous

I think that renaming the directory will cause trouble for those of you using stored procedures.

2008-05-30, 01:01:25

anonymous from Australia

not being able to just rename a schema blows ass

2008-08-12, 02:43:25   (updated: 2008-08-12, 02:52:59)

armmani2001@yahoo.com from United States

I tried to shutdown mysql and rename the database in data folder. apperently it worked but when I tried to use the tables it gave me the error the tables does not exist ! Keywords:

2009-02-15, 07:27:24

[hidden] from United Kingdom

Provided that you can shutdown anything which is updating the database (so that there is no chance of anything changing), this is a good solution: mysql -p <enter password> create database new_database; quit mysqldump old_database -p | mysql -D new_database -p <enter password> It'll dump out the old database to STDOUT, pipe this to a second MySQL process and insert the dumped data (and schema) to the new DB. You'll have to manually DROP the old DB and change the permissions on the new DB, but at least you have the data and schema. I find this very useful for creating 'live' backups of databases.

2009-03-20, 11:58:05

anonymous from Spain

you're a crack!!! perfect!

2009-06-26, 15:01:47

anonymous

Provided that you can shutdown anything which is updating the database (so that there is no chance of anything changing), this is a good solution: unfortunately, this is not a good solution for a sizable database. on a dual core 2.4Ghz workstation, loading my database from a dump takes about 52 hours.

2010-01-30, 09:10:26

tink3rbel1@yahoo.com from United States

THANK YOU!! I renamed my DB perfectly thanks to YOU!! But Wanted to add.... if you go to your DB files on HD, I found them with no prob... but I did 1st try to rename it as store = store-1st = did not like the nameing config... I then renamed it to mystore, and it worked perfectly!!!! Thanks again you saved me a TON of time!!!! -tink3rbel1

You are on page 1 of 2, other pages: [1] 2

NEW: Optional: Register   Login Email address (not necessary): Rate as Off-topic Very Helpful Helpful Ok article Not useful Very bad	Hide my email when showing my comment. Please notify me once a day about new comments on this topic. Please provide a valid email address if you select this option, or post under a registered account.
	Show city and country Show country only Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:
Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity. The owner of this web site reserves the right to delete such material. Add a picture: