Do not disable the triggers on your slaves, and do not configure your slaves to skip errors.
When replication is set up correctly with consistent data sets and identical configurations, including tables and their data, views, triggers, stored functions, and stored procedures, it just works. If it isn't "just working," then it was already not consistent in one of these ways. The duplicate key errors are a red flag that your data is not identical, and you don't want to suppress this.
If you aren't already, you should be using binlog_format
= MIXED
unless you have a specific reason not to. The default value prior to MySQL 5.6 was STATEMENT
but this was largely for legacy reasons.
Using mixed logging allows the optimizer on the master to choose the best way to log each change to the master's data -- either by:
- logging the literal and exact query your client sent to the master, so that the slaves can execute that same query, thereby modifying their data (statement based logging), or
- logging the specific rows that were changed on the master so that the slave can change those same rows in the affected table without regard to the actual query that caused the changes (row based-logging).
If a statement is determined to be "unsafe" for statement-based logging (meaning that it could potentially cause the data to diverge from its initial identical state), it will be automatically logged as row-based, if you're using MIXED
mode. Examples of this would be statements involving calls to non-deterministic functions like UUID()
which would return different values on master and slave. Row-based logging handles this without a problem, since it passes the literal values across.
You might think NOW()
might also be such a function, and that a similar limitation might apply to automatic timestamp columns, if the server clocks are not in sync, but that's not the case -- because with each binary (replication) log entry, the master logs what its current timestamp was at the time the query was executed -- allowing the slave to have a "pseudo-system clock" to actually fudge-in the master's clock's value at the time that statement was executed when needed. This log entry, incidentally, is how the slave calculates "seconds behind master," by comparing its system clock to the logged timestamps. (The exception to this is when the slave has executed everything in its relay logs, it always shows "0" seconds behind, because it doesn't know whether the former logs were delayed by clock skew or by an actual replication lag -- so if it has run out of things to execute, it is no longer technically "behind").
If a query is logged using row-based logging, then any data changed by triggers are also logged as row-based log entries, and the triggers on the slave will automatically not fire.
On the other hand, if the same query is logged using statement-based logging, then replication depends on the identical triggers being present on the slave, because the master will not log what the trigger did -- it only logs the query issued, and depends on the slave triggers firing exactly as they did on the master, assuming you want the data on all servers to be consistent.
With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design. [...]
http://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html
The documentation often speaks of replication in what sounds like terms of either/or (statement or row) but for each query executed MIXED
logging will choose the logging method on a per-query basis. You may also find statements to the effect that row-based logging is "required" in some conditions but this only means that it must be available (i.e., via MIXED
); it does not mean that the server must be configured for ROW
. DDL, of course, is always logged as statements, regardless of the binlog format.