How Can I Efficiently Rename a MySQL InnoDB Database?

Renaming a MySQL Database: An Efficient Solution for InnoDB

Renaming a MySQL database can be a daunting task, especially for large databases and those utilizing InnoDB, which stores data differently than MyISAM. However, with the right approach, it can be done efficiently.

To rename an InnoDB database, we need to create a new empty database and then move each table individually into it:

RENAME TABLE old_db.table TO new_db.table;

It's important to adjust the database permissions after the table migration.

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

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:

• Do not leave a space between the -p option and the password.
• If the database has no password, remove the -u username -ppassword part.
• If a table contains a trigger, it cannot be moved using the above method. In such cases, use the traditional database cloning technique: mysqldump old_db | mysql new_db.
• Stored procedures can be copied after the database rename: mysqldump -R old_db | mysql new_db.

The above is the detailed content of How Can I Efficiently Rename a MySQL InnoDB Database?. For more information, please follow other related articles on the PHP Chinese website!