Do you need triggers on a mysql database slave?

Question

Say you are running two mysql servers: one a master, the other the slave. The master has triggers set that update columns with the COUNT of the number of rows in other tables. For instance, you have a news table and a comments table. News contains an INT column called "total_comments" which is incremented via trigger every time a new row is put into "comments." Does the slave need this trigger as well (to keep "news.total_comments" up to date) or will it get be told to update the appropriate "news.total_comments" directly?

Answer 1

It depends on the replication you're using. If you use statement based replication, then you must use matching triggers in the master and the slave. If you use row-based replication, then you must not include the triggers on the slave.

Answer 2

From the docs http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html:

22.5.4: How are actions carried out through triggers on a master replicated to a slave? First, the triggers that exist on a master must be re-created on the slave server. Once this is done, the replication flow works as any other standard DML statement that participates in replication. For example, consider a table EMP that has an AFTER insert trigger, which exists on a master MySQL server. The same EMP table and AFTER insert trigger exist on the slave server as well. The replication flow would be: An INSERT statement is made to EMP. The AFTER trigger on EMP activates. The INSERT statement is written to the binary log. The replication slave picks up the INSERT statement to EMP and executes it. The AFTER trigger on EMP that exists on the slave activates.

And

22.5.4 Actions carried out through triggers on a master are not replicated to a slave server.

Thus, you DO need the triggers on the slave.