How Can I Reliably Rename a MySQL Database with InnoDB Tables?

Renaming a MySQL Database: A Detailed Guide for InnoDB Tables

Renaming a MySQL database, also known as changing its schema name, can be a challenge, especially for large databases. The usual method of dumping and re-importing is inefficient, while the RENAME {DATABASE | SCHEMA} command is not recommended for various reasons.

However, there is a reliable solution for InnoDB tables. Follow these steps:

1. Create the New Database: Create an empty database with the new name.
2. Rename Tables Individually: For each table in the old database, use the following SQL command to rename it into the new database:

RENAME TABLE old_db.table TO new_db.table;

3. Adjust Permissions: After renaming the tables, adjust the permissions as needed.
4. Command-Line Scripting: To automate the renaming process, you can use a command-line script:

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

Notes:

• Drop the "-u username -ppassword" part if the database has no password.
• If a table has a trigger, use the traditional method of cloning the database with mysqldump:

mysqldump old_db | mysql new_db

• Stored procedures can be copied afterwards using:

mysqldump -R old_db | mysql new_db

By following these steps, you can efficiently and reliably rename a MySQL database with InnoDB tables.

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