List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 17 2005 7:32pm
Subject:Re: renaming the database
View as plain text  
Shawn wrote

 >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.

It's easier & less error-prone to do it in an sproc which prepares & 
executes the rename cmds. Only hitch is that MySQL doesn't yet let you 
prepare a drop database cmd.

SET GLOBAL log_bin_trust_routine_creators=TRUE;
DROP PROCEDURE IF EXISTS RenameDatabase;
DELIMITER |
CREATE PROCEDURE RenameDatabase (IN oldname CHAR(64), IN newname CHAR(64))
BEGIN
  DECLARE sname CHAR(64) DEFAULT NULL;
  DECLARE rows INT DEFAULT 1;
  CREATE DATABASE IF NOT EXISTS newname;
  REPEAT
    SELECT table_name INTO sname
    FROM information_schema.tables AS t
    WHERE t.table_schema = oldname
    LIMIT 1;
    SET rows = FOUND_ROWS();
    IF rows = 1 THEN
      SET @scmd = CONCAT( 'RENAME TABLE ', oldname, '.', sname,
                         ' TO ', newname, '.', sname );
      PREPARE cmd FROM @scmd;
      EXECUTE cmd;
      DEALLOCATE PREPARE cmd;
    ELSE
      SET @scmd = CONCAT( "UPDATE mysql.db SET Db = '",
                          @newname,
                          "' WHERE Db = '", @oldname, "'" );
      PREPARE cmd FROM @scmd;
      EXECUTE cmd;
      SET @scmd = CONCAT( "UPDATE mysql.columns_priv SET Db = '",
                          @newname,
                          "' WHERE Db = '", @oldname, "'" );
      PREPARE cmd FROM @scmd;
      EXECUTE cmd;
      DEALLOCATE PREPARE cmd;
      FLUSH PRIVILEGES;
      -- MySQL does not yet support PREPARE DROP DATABASE:
      -- SET @scmd = CONCAT( 'DROP DATABASE ', oldname );
      -- PREPARE cmd FROM @scmd;
      -- EXECUTE cmd;
      -- DEALLOCATE PREPARE cmd;
    END IF;
  UNTIL rows = 0 END REPEAT;
END;
|
DELIMITER ;

PB

-----


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
>  
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005
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