Crash-Resistant Replication

This feature makes replication much more reliable after a crash by making the replica’s position relative to the master transactional.

MySQL replication normally stores its position in a file that is neither durable nor consistent. Thus, if the replica crashes, it can re-execute committed transactions. This usually causes replication to fail, potentially forcing the replica’s data to be re-initialized from the master or from a recent backup.

The improvement in Percona Server makes InnoDB store the replication position transactionally, and overwrite the usual relay-log.info file upon recovery, so replication restarts from the correct position and does not try to re-execute committed transactions. This change greatly improves the durability of MySQL replication. It can be set to activate automatically, so replication “just works” and no intervention is necessary after a crash.

Restrictions

When innodb_overwrite_relay_log_info is enabled, you should only update InnoDB / XtraDB tables, not MyISAM tables or other storage engines. You should not use relay or binary log filenames longer than 480 characters (normal: up to 512). If longer, the replication position information is not recorded in InnoDB.

Example Server Error Log Output

Upon crash recovery, the error log on a replica will show information similar to the following:

InnoDB: Starting crash recovery.
....
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 468, file name gauntlet3-bin.000015
InnoDB: and relay log file
InnoDB: position 0 617, file name ./gauntlet3-relay-bin.000111

If this feature is enabled, the output will look like the following, with additional lines prefixed with a + symbol:

....
+ InnoDB: Warning: innodb_overwrite_relay_log_info is enabled. Updates of other storage engines may have problem of consistency.
+ InnoDB: relay-log.info is detected.
+ InnoDB: relay log: position 429, file name ./gauntlet3-relay-bin.000111
+ InnoDB: master log: position 280, file name gauntlet3-bin.000015
....
  InnoDB: Starting crash recovery.
....
  InnoDB: Apply batch completed
+ InnoDB: In a MySQL replication slave the last master binlog file
+ InnoDB: position 0 468, file name gauntlet3-bin.000015
+ InnoDB: and relay log file
+ InnoDB: position 0 617, file name ./gauntlet3-relay-bin.000111
  090205 17:41:31 InnoDB Plugin 1.0.2-3 started; log sequence number 57933
+ InnoDB: relay-log.info have been overwritten.
....
  090205 17:41:31 [Note] Slave SQL thread initialized, starting replication in log ``gauntlet3-bin.000015`` at position 468, relay log ``./gauntlet3-relay-bin.000111`` position: 617

In this case, the master log position was overwritten to 468 from 280, so replication will start at position 468 and not repeat the transaction beginning at 280.

System Variables

One new system variable was introduced by this feature.

variable innodb_overwrite_relay_log_info
Version Info:
Command Line:

Yes

Config File:

Yes

Scope:

Global

Dynamic:

No

Variable Type:

BOOLEAN

Default Value:

FALSE

Range:

TRUE/FALSE

If set to true, InnoDB overwrites relay-log.info at crash recovery when the information is different from the record in InnoDB.

© Copyright Percona LLC and/or its affiliates 2009-2014.
Except where otherwise noted, this documentation is licensed under the following license:
CC Attribution-ShareAlike 2.0 Generic
Created using Sphinx 1.2.2.
This documentation is developed in Launchpad as part of the Percona Server source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.