重命名含Before Create触发器的MySQL数据库触发‘Trigger in wrong schema’错误

Got it, let's break down why you're hitting that error and how to fix it. The core issue here is that Before Create triggers are tightly bound to your original database's schema. When you tried moving tables directly via a looped command, the triggers stayed tied to the old database instead of updating to match the new one—MySQL throws that error because it detects the trigger's schema doesn't align with the table's new location.

Here's a reliable, step-by-step solution to migrate your tables (and their triggers) correctly:

Step 1: Export original schema + triggers (no data) #

First, dump your old database's table structures and all associated triggers. We'll exclude data for now to keep this file focused on schema elements:

mysqldump -u dbUsername -p dbOldName --no-data --triggers > schema_with_triggers.sql

When prompted, enter your database password (avoid hardcoding it directly in the command for security).

Step 2: Update schema references in the dump file #

Open the schema_with_triggers.sql file in a text editor. You'll notice every trigger definition includes the old database name (e.g., dbOldName.table_name). Replace all instances of dbOldName. with dbNewName. to point triggers to your new database.

Step 3: Export table data only #

Now dump just the data from your old database (skip schema and triggers this time):

mysqldump -u dbUsername -p dbOldName --no-create-info > table_data.sql

Step 4: Create the new database (if not done already) #

Run this command to set up your target database:

mysql -u dbUsername -p -e "CREATE DATABASE dbNewName;"

Step 5: Import updated schema + triggers into the new database #

This will create all tables and their triggers in the correct schema:

mysql -u dbUsername -p dbNewName < schema_with_triggers.sql

Step 6: Import data into the new database #

Finally, populate your new tables with the original data:

mysql -u dbUsername -p dbNewName < table_data.sql

Why your original method failed #

The looped command you tried only moves the tables themselves, not their associated triggers. In MySQL, triggers are stored with explicit references to their original database schema. When the table moves to a new database, the trigger's schema doesn't update automatically—hence the "wrong schema" error. This method ensures triggers are recreated directly in the new database with the correct references from the start.

内容的提问来源于stack exchange，提问作者Shriram Balakrishnan