Mysql trigger events in a row based master-slave replication on slave-side

Question

I do have a MySQL Master-Slave database and a process on the slave-side which listens to the latest updates from the Slave DB and acts accordingly. I also wrote a trigger on the slave-side to update another table (which the process is listening to).

delimiter #
CREATE  TRIGGER on_insert AFTER INSERT ON test
FOR EACH ROW
BEGIN 
    INSERT INTO Ins_table 
    VALUES(NEW.firstname, NEW.id);
end#
delimiter ;

I inserted the trigger on the Slave server (not on the Master because I am using row-based replication). The process checks the "Ins_table" every 10 seconds to obtain new records. The replication happens perfectly fine. However, the trigger on the slave-side never works. I have tested above triggers in a separate database (with no replication), and it works without any issue. Can you please help me to understand why the triggers after insert do not function on the slave-side of a row-based Master-Slave replication?

Answer 1

That is the expected behaviour for row-based replication, see mysql documentation

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.