How to Efficiently Rename a MySQL Database and its Tables?

Renaming MySQL Databases: A Comprehensive Approach

Renaming a MySQL database, also known as changing the schema name, can present challenges, especially for large databases or those using the InnoDB storage engine. Here we delve into an effective method that addresses these complexities.

Renaming InnoDB Tables

For InnoDB tables, the following approach has proven effective:

1. Create a new empty database with the desired name.
2. Sequentially rename each table from the old database to the new database using the following command:

RENAME TABLE old_db.table TO new_db.table;

Adjusting Permissions

After renaming the tables, you may need to adjust the permissions to ensure proper access.

Automating the Process

For efficient renaming in a shell script, you can utilize either 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;

Additional Considerations

• Triggers must be dropped before renaming tables with triggers.
• Stored procedures can be copied afterward using mysqldump -R old_db | mysql new_db.

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