0

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

1 Answer 1

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.