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:
- By default, the
synchronizedcolumn is set to 0
- I have an external python script that copies each row with
synchronized=0 to another system (with another schema) and then sets the
synchronizedfield to 1
- If the row is updated again by the master through replication, I want the
synchronizedfield 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 :
and the corresponding table on slave
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,