MySQL binlog format dilemma

mysql-replication

MySQL version: 5.5.13

If I set binlog format to STATEMENT, I got the following warnings on the Master:

[Warning] Unsafe statement written to the binary log using statement
format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it
uses a system function that may return a different value on the
slave…

I also read the limitations of replicate stores procedures and functions: http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.html

But if I switch to MIXED, mysqld.log on the Slave shows:

[Warning] Slave SQL: Could not execute Update_rows event on table
hdcn.sessions; Can't find record in 'sessions', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event's master log
mysql-bin.003834, end_log_pos 602692401, Error_code: 1032

[Warning] Slave SQL: Could not execute Delete_rows event on table
reportingdb.102_rpt_clickview; Can't find record in
'102_rpt_clickview', Error_code: 1032; handler error
HA_ERR_END_OF_FILE; the event's master log mysql-bin.003834,
end_log_pos 725203511, Error_code: 1032

Looks like the MIXED binlog format causes the Master doesn't replicate completely to Slave.

I switched back to STATEMENT format. Can I ignore the unsafe warnings?

Best Answer

I assume you did this on one setup without cleaning up the slave and setting it up from scratch.

As the error message for the statement based replication tells you your application used some commands which can't be replicated using statement based replication. An example is a query like

INSERT INTO t (t) VALUES(NOW())

where NOW() will return different values when executed on the master and on the slave.

By doing this you have different data on the master and slave. Which is bad as depending on your slave your clients will read different data and subsequent writes will change other data, so you end up with data which is even more different.

Now you switch over to mixed replication, which might use row-based replication for some statements. With RBR you really need the exact same data as it has a hard time to identify the rows which were changed and update them.

So what should you do? - Configure your master to use mixed logging and then setup the slave using a consistent snapshot.

Related Topic