Create triggers on slave only in mysql replication with a slave with different schema

Question

I have a mysql replication setup with one master reporting to one slave. The schema on the slave is the same than the master but for each table I have an additional synchronized column only on the slave which I use for sync with a third system. I need the following behavior:

1. By default, the synchronized column is set to 0
2. I have an external python script that copies each row with synchronized=0 to another system (with another schema) and then sets the synchronized field to 1
3. If the row is updated again by the master through replication, I want the synchronized field to be set to 2 (probably through a trigger on update) so that my python script can detect updated rows and update them in the third system.

To simplify, let's say we have a master table like this :

People
id (int)
name (varchar)

and the corresponding table on slave

People
id (int)
name (varchar)
synchronized (tinyint)

If I insert I already tried to set up a trigger on update on the slave to change the synchronized field to 2. It works when I update a row manually but not when it is updated through replication.

I understood that in row-based replication triggers are not replicated from the master but I am not trying to replicate a trigger from the master but rather create a trigger on slave whenever a row is updated through replication.

Right now my replication is row-based but i could change it to statement based or mixed if needed. Hope it makes sense and that everything is clear, any suggestions are welcome,

Thanks

Answer 1

When your Master/Slave is row based replication, the replication thread replicate a copy of the row to the slave instead of running the INSERT statement on the slave.

Therefore, it'll never run your trigger on the slave.

However, in your case I suggest you try following solution.

1. Create a synchronized column on the master with default value 0 and never call populate this column during an insert statement from the master
2. Create a trigger on the master for each update to set synchronized to 2 when an update occurs.
3. Remove the trigger from the slave.

Because the "synchronized" field already exists your replication will fail when you create the column on the master at step 1. You can tell your slave to skip the temporary error code related to column already exists below.

slave-skip-errors = xxx (xxx is the error code)

Answer 2

This question is six years old, but I'm answering for future reference.

As the other answer states, in MySQL, row-based replication doesn't run triggers. Triggers can only be fired by SQL statements.

I don't recommend changing the replication style to STATEMENT, because it's unsafe. I don't recommend MIXED neither, because it's safe but less efficient than ROW.

However, you could consider switching to MariaDB. MariaDB can run triggers on the replica, even with row-based replication.

If you don't want to touch your current master, you might be able attach a MariaDB replica to it. If you opt for this solution, test it carefully before going production.