Triggers are are executed on the slave under statement-based replication, but not under row-based replication. Instead, when using row-based replication, the changes caused by executing the trigger on the master are applied on the slave.
This behavior is by design. If the slave applied row-based changes from the master as well as changes caused by triggers, the trigger changes would in effect be applied twice on the slave, leading to different data on the master and the slave.
If you want triggers to execute on both the master and the slave — perhaps because you have different triggers on the master and slave — then you must use statement-based replication. However, it is not necessary to use statement-based replication exclusively if you want to enable slave-side triggers; it is sufficient in such cases to switch to statement-based replication only for those statements where you want this effect, and to use row-based replication the rest of the time.
Before MySQL 5.1.31, a trigger that was defined on a transactional table but that updated a nontransactional tables could cause updates on the transactional table to be replicated before they were actually committed on the master, and not be rolled back correctly on the slave if they were rolled back on the master. (Bug#40116) See also Section 16.4.1.28, “Replication and Transactions”.
User Comments
Add your own comment.