MySQL Replication - Issues with Triggers

Question

MySQL master has a table Products that has triggers on update and delete. Deleted/Updated records are stored in ProductsUpdatedDeleted tab.

When a record is deleted, the operation is replicated to the slave server. The issue is that slave also executes the trigger thus adding a record in its ProductsUpdatedDeleted table. Then a duplicate record in ProductsUpdatedDeleted received from the master server through replication.

What is the best way to stop the slave to execute triggers? I can comment trigger code in the slave but I would prefer both servers in the same state to quick failover.

Answer 1

Basically, there are two methods

METHOD #1

Use row-based replication. It says so in Paragraphs 1-3 of "Replication and Triggers"

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.

This behavior is by design. If under row-based replication the slave applied the triggers as well as the row changes caused by them, the 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—you must use statement-based replication. However, to enable slave-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient 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.

METHOD #2

Drop all the triggers on the Slave. No trigger present, no trigger to execute.

Do not do both method. Just select one method.

• Allen King: I guess I will comment code inside triggers on slave.
• RolandoMySQLDBA: Hey, commenting out the code inside all Slave Triggers is a third method.
• Allen King: Yes. Because if I delete, I will forget later which triggers were there and how to recode them :-) But there are many of them. Wish there was an option in mysql not to execute triggers in slave machine.
• Allen King: Actually, this is not an issue at all. in Row based replication, triggers are not executed on slaves. They are executed in statement based replication. Without this master master or group replication would never work.