Renaming a Database in Mysql

Unfortunately – and probably for good reason – the RENAME DATABASE command had a short life in Mysql for safety precaution reasons.

After doing some research, it appears that the best approach is as follows as it works well for both MyISAM and InnoDB table types:

1. Create new database
2. Perform RENAME TABLE command that includes the table and database from the old one to the newly created database
3. Repeat once for each table

In all of the examples I didn’t find an automated script – time to make one I thought!

The first challenge is to retrieve a list of the tables in the database.  Once I have this list, it’s as simple as looping through the items via a CURSOR and executing the RENAME TABLE command dynamically with the table name.

Here is the stored procedure that I was able to come up with and successfully execute.  I even went the extra effort to accept parameters for the database name!

[code]
DELIMITER //

DROP PROCEDURE IF EXISTS `RenameDatabase` //

CREATE PROCEDURE `RenameDatabase`(
new_database VARCHAR(255)
)

BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE num_rows INT;
DECLARE i INT;
DECLARE the_name VARCHAR(255);
DECLARE ren VARCHAR(255);

DECLARE table_names CURSOR FOR SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema = DATABASE();

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN table_names;

read_loop: LOOP
FETCH table_names INTO the_name;

IF done THEN
LEAVE read_loop;
END IF;

SET @ren = concat(“RENAME TABLE `”, DATABASE(), “`.”, the_name, ” TO `”, new_database, “`.”, the_name);

PREPARE ren FROM @ren;

EXECUTE ren;

END LOOP;

CLOSE table_names;

END //

DELIMITER ;
[/code]

Next, you simply need to call the stored procedure.  Before executing the stored procedure, simply make sure that the new database has been created and execute as follows:

[code]
call RenameDatabase(‘NEW_DATABASE_NAME’);
[/code]

Note: Being sure to replace NEW_DATABASE_NAME with the actual name of the database.