Mysql – Create triggers on slave only in thesql replication with a slave with different schema

MySQLreplicationtrigger

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

Best Answer

@Mati when your Master/Slave is row based replication the replication thread replicate a copy of the row into the slave instead of ruining the "insert into xx" statement into the slave.

therefore it'll never run your trigger.

however in your case i suggest you the following solution: 1- create synchronized field on the master with default value 0, and never call populate this field 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 occur 3- remove the trigger from the slave

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

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

Related Question