MySQL replication is very widely used for MySQL HA (High Availability) and the general presumption and expectation is that the master/master (or master/slave) produce exact copy of the master. False expectation to say the least, well to the defense of MySQL replication I might say that it was never designed for HA, it was merely adapted to be used for MySQL high availability. To be fair this is true not only MySQL replication but for any other asynchronous replication out there. The database replication was designed for "data propagation" i.e. moving the data from one database to another and in turn for adapted for proving high availability.
We are all used to "skip" troublesome replicated entries - cancelled transaction on the master, corrupted relay log etc:
mysql>; SLAVE STOP;
mysql>; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>; SLAVE START;
But the issues that cause most trouble are the silent ones. Those ones you do not see until its too late and you might need a slave rebuild once discovered.
Mixing The Engines, Transactions and Statement Based Replication
Statement Based Replication.
The idea behind is very simple - record all statement in binary log, retrieve the log from the slave in the form of relay log and replay the SQL statements. The problems are located at the cove of this brilliant simplicity.
Hence the statement based replication simply replays the binary log on the slave it does not really care if replay does not delete or update the exact rows that were deleted or updated on the master.
How? Take the LIMIT clause - this clause unless enforced by ORDER BY very frequently will produce different order on the master and slave databases. Most importantly this is silent, you'll never notice it until too late that master and slave drifted out of control.
Simply put - when using statement based replication you letting the database engine (on master and slave) to make the determination which rows will be updated or deleted. To avoid this make the determination yourself i.e. tell the database engine the exact rows you want updated or deleted.
Mixing the Engines.
When is comes to mixing the engines or transactional and non-transactional engines the resulting "replication" could be really twisted. SQL statements that are deemed by database engine as "non-transactional" are written to the binary log immediately and SQL statements that are deemed "transactional" are written to the binary log only when they are committed. The result is the inevitably the statements can be added and replayed in incorrect order depending on the storage engines.
Mixing the engined is actually very widely used. For example that are a lot of "reporting" databases slaving of NDB and INNODB for reporting purposes (merge tables feature is used) inevitable those reporting databases frequently produce "strange" results.
Row Based Replication
Row based replication records and propagates to the slaves the changes to the actual rows, thus avoiding the possibility of the individual database engine (one on the master and one on the slave) to make a determination which rows are to be deleted or updated. Yes there is a whole list of drawbacks to row based replication - increased log size, longer database restore times etc...
In conclusion - I have attempted to highlight the issues that are inherently present when using MySQL replication for HA or data propagation. The list of the actual issues that can be encountered is far larger for the single post.
When designing MySQL databases for HA (or reporting) take into consideration replication formats (STATEMENT, ROW), stored procedures and functions, databases engines (TRANSACTIONAL, NON TRANSACTIONAL) and most importantly review the applications SQL code to assure that the selected strategy will produce the desired results.
For DBA's this simply boils down to - review all SQL statements that you are deploying to the databases - this will save you sleepless nights after you end up with a copy "closely resembling the original".
