1

I have a classical statement-based replication between the master and the slave. Triggers were created on the master and replicated on the slave as well.

Assuming that I have, say an ON UPDATE trigger, is the following correct?

MySQL will only write to the binary log statements that were not the result of the trigger. Data consistency between the master and the slave is therefore ensured by the fact that the same triggers are present on both.

The relevance of this pertains to incremental updates (or accumulutors), eg.

CREATE TRIGGER vesion_increment AFTER UPDATE ON table
FOR EACH ROW
BEGIN
  UPDATE table SET version=version+1;
END

In this case, the UPDATE table SET version=version+1 will not be in the binary log and hence not replicated but taken care of by the same trigger on the slave. Is that correct?

0

Yes, you are correct. You can often get bitten from the fact that the triggers are missing on the slave and thus the trigger effects are not there.

Also if the triggers use any routines (PROCEDUREs and FUNCTIONs) you have to make sure they are present on the slave too. The latter might bite you if you provision your slaves from a mysqldump, since by default mysqldump does not dump routines.

  • "When using row-based replication, triggers on the slave are not activated by statements originating on the master." -- see manual. – Rick James Apr 10 '15 at 17:52
  • Thanks, @RickJames. But you're quoting the manual for row based replication and I'm in a statement based setting. Moreover, my question states the opposite of your comment, ie. that triggers ON the master are not written to the log AND data integrity is therefore ensured by the presence of said triggers on the slave. – VH-NZZ May 9 '15 at 6:26

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.