MySQL Slave Warns: Configuration does not guarantee that the relay log info will be consistent after a crash

Question

I wanted to set up a new replication slave for MySQL 5.6. Just after a CHANGE MASTER and starting the slave, I saw this line in the error log:

[Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

If it matters, these settings are included in my.ini:

skip-name-resolve
skip-host-cache

server-id = 111

report-host = myPC

relay-log-recovery

sync_master_info=1
sync_relay_log=1
sync_relay_log_info=1

replicate-do-db = myDB

skip-slave-start

Replication seems working, but this warning is scary. Any idea what makes MySQL issue this warning?

Answer 1

The warning is about crash-safe replication, a feature added to MySQL 5.6. It does not mean anything scary is going on (basically, replication works as in earlier releases) but it is notifying you that you could do better with the new feature set in MySQL 5.6.

A common request is to have replication crash-safe in the sense that the replication progress information always is in sync with what has actually been applied to the database, even in the event of a crash. Although transactions are not lost if the server crashes, it could require some tweaking to bring the slaves up again. [...]

Crash-safe masters

• If the master crashed when a binary log was rotated, it was possible that some orphan binlog files ended up in the binary log index file. This was fixed in 5.1 but is also a piece in the puzzle of having crash-safe replication.
• Writing to the binary log is not an atomic operation, and if a crash occured while writing to the binary log, there were a possibility of a partial event at the end of the binary log.

Crash-safe slaves

• If the replication information and data is stored in the same storage engine, it will allow both the data and the replication position to be updated as a single transaction, which means that it is crash-safe.
• If the replication information and data is stored in different storage engines, but both support XA, they can still be committed as a single transaction. The replication information is flushed to disk together with the transaction data. Hence writing the replication information directly to the InnoDB redo log does not offer a speed advantage, but does not prevent the user from reading the replication progress information easily.
• The tables can be read from a normal session using SQL commands, which also means that it can be incorporated into such things as stored procedures and stored functions.

See the referenced blog post and the MySQL documentation for details of operation and set up instructions.