How to Rename a MySQL Database with InnoDB Tables Without Dumping and Re-importing?

Renaming MySQL Databases for InnoDB Tables

As an alternative to dumping and re-importing vast databases, you may wish to rename them directly. While the RENAME syntax is not advisable, a more reliable approach for InnoDB tables exists.

Steps:

1. Create an empty new database with the desired name.
2. For each table in the original database, execute the following command:

RENAME TABLE old_db.table TO new_db.table;

Scripting the Process:

For convenience, use shell scripting to automate the renaming process:

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

Alternatively:

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:

• Remove the password portion if your database is not password-protected.
• Triggers will prevent tables from being moved using this method. Consider cloning instead.
• Stored procedures can be copied afterwards using mysqldump -R old_db | mysql new_db.

The above is the detailed content of How to Rename a MySQL Database with InnoDB Tables Without Dumping and Re-importing?. For more information, please follow other related articles on the PHP Chinese website!