Databases
InterBase (29)
MS-SQL (5)
mysql (37)
Oracle (1)
Exchange Links
About this site Links to us 
|
mysql: How can I rename a database
 13 comments. Current rating:  (7 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:
|
anonymous from Hungary 
|
 |
|
|
|
|
|
|
|
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';
|
|
|
|
|
try this..
$ mysqladmin create new_db
$ mysqldump --opt old_db | mysql new_db
mysql>; revoke all from old_db_user@host;
|
|
|
|
|
I think that renaming the directory will cause trouble for those of you using stored procedures.
|
|
|
|
|
not being able to just rename a schema blows ass
|
2008-08-12, 02:43:25 (updated: 2008-08-12, 02:52:59) |
|
|
|
|
|
|
|
|
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.
|
|
|
|
|
you're a crack!!!
perfect!
|
|
|
|
| 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.
|
|
