Robert L Cochran <[EMAIL PROTECTED]> wrote on 10/17/2005 11:00:33 AM:
> And, of course, you need to grant permissions for the new database name. > > Bob > > [EMAIL PROTECTED] wrote: > > >Robert L Cochran <[EMAIL PROTECTED]> 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
