Renombrar base de datos en MySQL

Parece ser que antes existía un comando en MySQL para renombrar una base de datos pero fue quitado por cuestiones de seguridad.

RENAME {DATABASE | SCHEMA} db_name TO new_db_name; 

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names (see Section 8.2.3, “Mapping of Identifiers to File Names”). However, use of this statement could result in loss of database contents, which is why it was removed. Do not useRENAME DATABASE in earlier versions in which it is present.

Existe una manera alternativa pero solo funciona con el motor MyiSAM. Me tomó un poco de tiempo pero encontré un script para poder renombrar con InnoDB. El script requiere que exista una BD con el nuevo nombre que se va utilizar.

DROP PROCEDURE IF EXISTS RenameDatabase;
DELIMITER |
CREATE PROCEDURE RenameDatabase(
IN oldname CHAR (64), IN newname CHAR(64)
)
BEGIN
DECLARE version CHAR(32);
DECLARE sname CHAR(64) DEFAULT NULL;
DECLARE rows INT DEFAULT 1;
DECLARE changed INT DEFAULT 0;
IF STRCMP( oldname, 'mysql' ) <> 0 THEN
REPEAT
SELECT table_name INTO sname
FROM information_schema.tables AS t
WHERE t.table_type='BASE TABLE'
AND 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;
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;
END IF;
END;
|
DELIMITER ;