How to Rename a MySQL Database Efficiently Without Performance Issues?

Renaming a MySQL Database (Schema) without Creating Performance Bottlenecks

Renaming a MySQL database can be a tedious task, especially for large databases. The conventional method of dumping, then re-importing the data can strain your system, impacting performance.

Step-by-Step Solution for InnoDB Databases

To rename an InnoDB database, consider the following approach:

1. Create a New Empty Database: Begin by creating a new database with the desired name.

2. Rename Database Tables: Transfer each table individually from the old database to the new database using the command:

   RENAME TABLE old_db.table TO new_db.table;

3. Adjust Permissions: Once the tables have been moved, it's necessary to modify the permissions to reflect the changes.

Automated Solutions

To automate the process in a shell script, you can run one of the following commands:

1. Using a While Loop:

   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

2. Using a for Loop:

   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;

Additional Considerations

Triggers: Tables with triggers cannot be moved between databases using this method.

Stored Procedures: If you have stored procedures, remember to copy them afterward using:

mysqldump -R old_db | mysql new_db

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