No more MySQL Crash Safe Replication in 5.0 ?

As you might know even if you’re only using Innodb tables your replication is not completely crash safe – if Slave MySQL Server crashes/power goes down it is likely for relay logs to run out of sync (they are not synced to the disk) plus position on the master which slave remembers becomes stale.

During MySQL 4.0 and 4.1 series there was a great workaround if you’re using only Innodb tables – Innodb when Innodb does crash recovery it would print position in master log files up to which replication was done:

All you needed to do is to use –skip-slave-start on the slave server and have a little script which will do CHANGE MASTER TO to specified location to restore replication in case of crash (assuming you’re only using Innodb tables of course)

Another way this functionality was usable is cloning Slave->Slave by use of LVM without pausing replication (so you can get consistent master position).

It is all great but it does not work any more in MySQL 5.0 Baron has spotted it by incident when we were verifying some of examples for High Performance MySQL book.

In the bug Heikki explains the code was probably removed in MySQL 5.0 during XA implementation though the code which prints the data back on recovery was not, so it prints you some log file name and position but they have nothing to do with real position on the master anymore.

I hope Innodb team will find a way to restore this functionality or at least remove confusing message which leaves impression this thing works.

Until this issue is fixed getting Crash Safe replication with MySQL is not impossible but surely more complicated and has much higher performance overhead – you can run slave with –sync-binlog and –log-slave-updates so you can see what last statement was executed before the crash and then find matching position in the master logs.

Interesting enough similar functionality is implemented in Mark Callaghan’s patches if you use rpl_transaction_enabled=1

Note even though this functionality is currently broken other somewhat similar functionality works as expected.
Innodb during recovery also prints position in the MySQL binary log:

This one is helpful in other cases – for example when you’re taking LVM snapshot (for backup or to clone slave from the master) and can’t do traditional and recommended way with FLUSH TABLES WITH READ LOCK for snapshot creation. This happens when you have large amount of tables or your load pattern is to cause unacceptable stall if this lock is used. You can just take LVM Snapshot (assuming you’re only using Innodb tables and not touching your MyISAM system tables) and use this position to point to proper location on the master, or later use for point in time recovery using binary log.