We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between master and slave.
On master:
mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
Value: ROW
On slave:
mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
Value: STATEMENT
Once in a while, the replication breaks with an error similar to the following:
Last_SQL_Error: Could not execute Delete_rows event on table test.pruning; Can’t find record in ‘pruning’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000002, end_log_pos 1448
We don't know for sure whether this was caused due to the different binlog_format setting between the master & slave.
Could the above error potentially be caused by this inconsistent setting? Should I change the slave to "row" based format? It is hard to reproduce the error so I am looking for the best practice we should be following and we are very new to MySQL.