Search code examples
mysqldatabaseinnodbrename

How do I rename a MySQL database (change schema name)?

How do I quickly rename a MySQL database (change its schema name)?

Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exists only in a handful of versions, and is a bad idea overall.

This needs to work with InnoDB, which stores things very differently than MyISAM.

Solution

  • For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

    RENAME TABLE old_db.table TO new_db.table;

    You will need to adjust the permissions after that.

    For scripting in a shell, you can use either of the following:

    mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

    OR

    for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

    Notes:

    • There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.

    • If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

      mysqldump old_db | mysql new_db

    • If you have stored procedures, you can copy them afterwards:

      mysqldump -R old_db | mysql new_db