Do you need triggers on a mysql database slave?

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.tota开发者_如何转开发l_comments" directly?

---

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.

---

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.

---

You can have the action of requests made by triggers in the binary log with federated tables (MySQL5) by adding the same table with a local connection.

---------------------------------------------------------------------------------------
-- EXEMPLE :
-- We want install a replication of the table test_table that will be managed  by Trg_Update triggers.
---------------------------------------------------------------------------------------

Create database TEST;
USE TEST;
CREATE TABLE test_trigger (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY  (id),
    INDEX name (name),
) ;

DELIMITER |
CREATE TRIGGER Trg_Update AFTER INSERT ON test_trigger
FOR EACH ROW BEGIN
INSERT INTO federated_table (name, other) values (NEW.name, ‘test trigger on federated table -> OK’)
END|
DELIMITER ;


CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  VARCHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
) ;


CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  VARCHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
CONNECTION='mysql://root@localhost/TEST/test_table';

---------------------------------------------------------------------------------------