Re: renaming the database

Mon, 17 Oct 2005 12:33:24 -0700

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

-----


[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
  

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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to