Rename Database

from the Artful Common Queries page

Sometimes it's necessary to rename a database. There are two crucial reasons there's no such command in MySQL: there's no way to lock a whole database, and information_schema isn't transactional.

MySQL 5.1.7 introduced a RENAME DATABASE command, but the command left several unchanged database objects behind, and was found to lose data, so it was dropped in 5.1.23.

The operating system's view of a database is that it's just another folder on the disk. That may tempt you to think you could just rename it. Don't.

Renaming a database is perfectly safe if and only if:

  • it has no tables with foreign keys that reference, or are referenced by, a table in another database;
  • it has no procedures, functions or triggers that reference objects in another database;
  • no other database has tables, functions, procedures or triggers that reference this database.
  • The database has no tables in other locations
So there is one safe way to rename a database: dump it completely ... 
mysqldump -uUSR -pPWD YOURDBNAME --add-drop-database -K -E -R >YOURDUMPFILE
then edit the dump script to change the database name, run the dump script, test the new database, and when you've proved to your satisfaction that the new database is an exact functional copy of the old, down to every last detail, delete the old database.

Here is a stored procedure that moves tables and views from one database to another, but please remember ...

  • It does not move procedures, functions, or triggers,
  • Before calling the sproc, the new database must have been created.
  • The procedure refuses to rename the mysql database.
  • The old database is left behind, minus what was moved.
DROP PROCEDURE IF EXISTS RenameDatabase;
DELIMITER go
CREATE PROCEDURE RenameDatabase( oldname CHAR (64), newname CHAR(64) )
BEGIN
  DECLARE version CHAR(32);
  DECLARE sname CHAR(64) DEFAULT NULL;
  DECLARE rows, ok INT DEFAULT 1;
  DECLARE changed INT DEFAULT 0;
  IF (SELECT Count(*) FROM information_schema.schemata 
      WHERE schema_name IN(oldname,newname)
     ) <> 2 THEN 
    SELECT Concat( oldname, " or ", newname, " doesn't exist." ) AS Error;
    SET ok=0;
  END IF;
  IF StrCmp( oldname, 'mysql' ) = 0 THEN
    SELECT "Cannot rename mysql database." AS Error;
    SET ok=0;
  END IF;
  IF ok=1 THEN
    SET @old_unique_checks=@@unique_checks, @unique_checks=0;
    SET @old_foreign_key_checks=@@foreign_key_checks, @foreign_key_checks=0;
    REPEAT
      SELECT table_name INTO sname 
      FROM information_schema.tables AS t
      WHERE t.table_schema = oldname
      ORDER BY 1 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;
        SET changed = 1;
      END IF;
    UNTIL rows = 0 END REPEAT;
    IF changed > 0 THEN
      SET @scmd = CONCAT( "UPDATE mysql.db SET Db = '",
                          newname,
                          "' WHERE Db = '", oldname, "'" );
      PREPARE cmd FROM @scmd;
      EXECUTE cmd;
      DROP PREPARE cmd;
      SET @scmd = CONCAT( "UPDATE mysql.proc SET Db = '",
                          newname,
                          "' WHERE Db = '", oldname, "'" );
      PREPARE cmd FROM @scmd;
      EXECUTE cmd;
      DROP PREPARE cmd;
      SELECT version() INTO version;
      IF version >= '5.1.7' THEN
        SET @scmd = CONCAT( "UPDATE mysql.event SET db = '",
                            newname,
                            "' WHERE db = '", oldname, "'" );
        PREPARE cmd FROM @scmd;
        EXECUTE cmd;
        DROP PREPARE cmd;
      END IF;
      SET @scmd = CONCAT( "UPDATE mysql.columns_priv SET Db = '",
                          newname,
                          "' WHERE Db = '", oldname, "'" );
      PREPARE cmd FROM @scmd;
      EXECUTE cmd;
      DROP PREPARE cmd;
      FLUSH PRIVILEGES;
    END IF;
    SET foreign_key_checks=@old_foreign_key_checks, unique_checks=@old_unique_checks;
  END IF;
END;
go
DELIMITER ;

Last updated 9 Mar 2025