MySQL: How Triggers act in Replication

MySQL databaseSupposed you have a table that being updated by MySQL triggers and the DB is a master of replication, how do the triggers act in the environment? Here we might have a list of questions:

  • Q1, Will the definition sql of the triggers be replicated?
  • Q2, Will the data manipulation sqls triggered by triggers be replicated?
  • Q3, Will the data manipulation sqls on this table that issued by applications be replicated?

It’s basically not an issue if you have some understandings of MySQL. But sometimes things are a little different in real world, and it may mess you up.
The answers of these questions depend on which version of MySQL you are running.

MySQL 5.0

The answers are as below.

Q1: Triggers that exist on master side must be re-created on the slave server. Once this is done, the replication flow works as any other standard DML statement that participates in replication.
Q2: NO. SQLs generated by Triggers are not replicated.
Q3: Yes. There is no side effect to triggers.
Reference:  MySQL 5.0 FAQ: Triggers

MySQL 5.1, 5.5 and 5.6

Q1: Re-create of the trigger definition is needed.
Q2: YES for row-based replication, but NO for statement-based replication.
Q3: Yes

The answers depend on whether you are using statement-based or row-based replication. For statement-based replication, it works the same MySQL-5.0. While on row-based replication, the answer for Q2 is YES, the changes caused by executing the trigger on master side are applied on the slave.

Mind that the triggers are not actually executed on slave under row-based replication, in case of being applied twice. In other words, the outcome is the same for both row-based and statement-based replication.

Accordingly, if you have different triggers defined on master and slave, you cannot use row-based replication.

References:

Share Button

Leave a comment

Your email address will not be published. Required fields are marked *