List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 17 2005 3:10pm
Subject:Re: renaming the database
View as plain text  
Robert L Cochran <cochranb@stripped> wrote on 10/17/2005 11:00:33 AM:

> And, of course, you need to grant permissions for the new database name.
> 
> Bob
> 
> SGreen@stripped wrote:
> 
> >Robert L Cochran <cochranb@stripped> wrote on 10/17/2005 07:37:26 
AM:
> >
> > 
> >
> >>I think you can just rename the directory that that database lives in. 

> >>If you read the documentation for CREATE DATABASE in dev.mysql.com, 
> >>you'll see they discuss renaming the directory (although it does not 
> >>directly say this can be done to rename the database, but it comes 
> >>really close to that.) Based on the documentation the database name is 

> >>simply a directory name, no more and no less.
> >>
> >>Renaming tables has its own command syntax, you can look it up.
> >>
> >>Bob Cochran
> >>
> >>
> >>
> >>Octavian Rasnita wrote:
> >>
> >> 
> >>
> >>>Hi,
> >>>
> >>>Is there a command for renaming a MySQL database?
> >>>
> >>>Thank you.
> >>>
> >>>Teddy
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> 
> >>>
> >
> >The only way I have done it has been to create an empty database with 
the 
> >name I want. Then I used RENAME TABLE to "move" all of the tables into 
the 
> >new database. Sure it takes a lot of RENAME TABLE statements but it 
works. 
> >If these are InnoDB tables, all I am doing is moving metadata and that 
is 
> >FAST. For MyISAM or other file-based storage engines, it copies files 
from 
> >one folder to another. For some file systems, that is also just a 
metadata 
> >shift and will still be FAST. Others will require a physical move of 
the 
> >data from one location to another (good thing that those filesystems 
are 
> >becoming quite rare these days)
> >
> >I have a 2.1GB database with all InnoDB tables in it that I wanted to 
> >change the name of. It took me longer to write my RENAME TABLE script 
than 
> >it did to actually move the data.
> >
> >http://dev.mysql.com/doc/refman/4.1/en/rename-table.html
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> > 
> >
> 


Good catch! Alternatively, if he wanted to move all of his permissions to 
the new database name, he could simply update any old entries in the 
`mysql.db` and `mysql.columns_priv` tables then issue a FLUSH PRIVILEGES 
command. 

UPDATE `mysql.db`
SET `Db` = 'new_dbname`
WHERE `Db` = 'old_dbname';

UPDATE `mysql.columns_priv`
SET `Db` = 'new_dbname`
WHERE `Db` = 'old_dbname';

FLUSH PRIVILEGES;

This way if he wants to archive his old database, he can grant a new set 
of permissions (with new GRANT statements). And if he just wants to rename 
his database (migrating any existing permissions to the new name) he can 
do that, too.

Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
renaming the databaseOctavian Rasnita16 Oct
  • Re: renaming the databaseJigal van Hemert17 Oct
  • Re: renaming the databaseOctavian Rasnita17 Oct
  • Re: renaming the databaseC.R. Vegelin17 Oct
    • Re: renaming the databaseJigal van Hemert17 Oct
  • Re: renaming the databaseRobert L Cochran17 Oct
    • Re: renaming the databaseSGreen17 Oct
      • Re: renaming the databaseRobert L Cochran17 Oct
        • Re: renaming the databaseSGreen17 Oct
          • Re: renaming the databasePeter Brawley17 Oct
      • Re: renaming the databasePeter Brawley17 Oct
  • Re: renaming the databaseGleb Paharenko17 Oct