- Do not use LIMIT clauses in statements that can change rows (see Baron's article)
- Do not use session variables in SQL statements that might be written to the binlog. This is OK to do on recent MySQL versions, but some of us still use old releases.
- Do not use temporary tables on the master. When the binlog contains statements that populate and then use a temporary table, a crash or restart of the slave mysqld process between the population and use means that the temp table will not exist after the restart and replication will halt.
- Do not use MyISAM tables on the slave. A slave mysqld process may crash and a slave SQL thread might rollback a transaction because of a lock wait timeout or deadlock. The slave SQL thread must then retry the transaction. But MyISAM doesn't support rollback. At this point, the MyISAM table on the slave is likely to have different contents then the master.
- Do not use MyISAM tables on the master. There is code to keep InnoDB and the binlog in sync on the master. That doesn't work for MyISAM. Also, it is not possible to guarantee that what gets written to the binlog matches the changes done to MyISAM on the master as MyISAM does not do rollback and does not undo partially executed statements.
- Do not allow the slave to crash. Unfortunately, this is impossible. But slave replication state is not updated atomically with the changes made by the slave SQL thread. The slave SQL thread first commits transactions and then updates the files in which replication state is stored. A crash between the two means that the slave SQL thread will replay replay statements from the relay log on restart, statements that have already been executed.
- With many thanks to Peter for documenting the problems, do not use replicate-ignore-db and binlog-ignore-db.
Friday, November 9, 2007
There is a great article on how using LIMIT clauses in update and delete statements can produce different results on a slave than it did on the master. If you really want to keep a master and slave in sync, I have a larger list of rules to follow for SQL that is run on a master. They might not be popular but they make it easy to run a large number of replication slaves with few problems.