MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
In order for replication to be resilient to unexpected halts of the server (sometimes described as crash-safe) it must be possible for the replica to recover its state before halting. This section describes the impact of an unexpected halt of a replica during replication, and how to configure a replica for the best chance of recovery to continue replication.
After an unexpected halt of a replica, upon restart the
replication SQL thread must recover information about which
transactions have been executed already. The information required
for recovery is stored in the replica's applier metadata
repository. In older MySQL Server versions, this repository could
only be created as a file in the data directory that was updated
after the transaction had been applied. In MySQL 5.7 you can
instead use an InnoDB
table named
mysql.slave_relay_log_info
to store the applier
metadata repository. As a table, updates to the applier metadata
repository are committed together with the transactions, meaning
that the replica's progress information recorded in that
repository is always consistent with what has been applied to the
database, even in the event of an unexpected server halt. To
configure MySQL 5.7 to store the applier metadata repository as an
InnoDB
table, set the system variable
relay_log_info_repository
to
TABLE
. For more information on the applier
metadata repository, see Section 16.2.4, “Relay Log and Replication Metadata Repositories”.
The recovery process by which a replica recovers from an unexpected halt varies depending on the configuration of the replica. The details of the recovery process are influenced by the chosen method of replication, whether the replica is single-threaded or multithreaded, and the setting of relevant system variables. The overall aim of the recovery process is to identify what transactions had already been applied on the replica's database before the unexpected halt occurred, and retrieve and apply the transactions that the replica missed following the unexpected halt.
For GTID-based replication, the recovery process needs the GTIDs of the transactions that were already received or committed by the replica. The missing transactions can be retrieved from the source using GTID auto-positioning, which automatically compares the source's transactions to the replica's transactions and identifies the missing transactions.
For file position based replication, the recovery process needs an accurate replication SQL thread (applier) position showing the last transaction that was applied on the replica. Based on that position, the replication I/O thread (receiver) retrieves from the source's binary log all of the transactions that should be applied on the replica from that point on.
Using GTID-based replication makes it easiest to configure replication to be resilient to unexpected halts. GTID auto-positioning means the replica can reliably identify and retrieve missing transactions, even if there are gaps in the sequence of applied transactions.
The following information provides combinations of settings that are appropriate for different types of replica to guarantee recovery as far as this is under the control of replication.
Some factors outside the control of replication can have an
impact on the replication recovery process and the overall state
of replication after the recovery process. In particular, the
settings that influence the recovery process for individual
storage engines might result in transactions being lost in the
event of an unexpected halt of a replica, and therefore
unavailable to the replication recovery process. The
innodb_flush_log_at_trx_commit=1
setting mentioned in the list below is a key setting for a
replication setup that uses InnoDB
with transactions. However, other settings specific to
InnoDB
or to other storage engines,
especially those relating to flushing or synchronization, can
also have an impact. Always check for and apply recommendations
made by your chosen storage engines about crash-safe settings.
The following combination of settings on a replica is the most resilient to unexpected halts:
When GTID-based replication is in use
(gtid_mode=ON
), set
MASTER_AUTO_POSITION=1
, which activates
GTID auto-positioning for the connection to the source to
automatically identify and retrieve missing transactions. This
option is set using a CHANGE MASTER
TO
statement. If the replica has multiple
replication channels, you need to set this option for each
channel individually. For details of how GTID auto-positioning
works, see
Section 16.1.3.3, “GTID Auto-Positioning”. When
file position based replication is in use,
MASTER_AUTO_POSITION=1
is not used, and
instead the binary log position or relay log position is used
to control where replication starts.
Set sync_relay_log=1
, which
instructs the replication I/O thread to synchronize the relay
log to disk after each received transaction is written to it.
This means the replica's record of the current position read
from the source's binary log (in the source metadata
repository) is never ahead of the record of transactions saved
in the relay log. Note that although this setting is the
safest, it is also the slowest due to the number of disk
writes involved. With sync_relay_log > 1
,
or sync_relay_log=0
(where synchronization
is handled by the operating system), in the event of an
unexpected halt of a replica there might be committed
transactions that have not been synchronized to disk. Such
transactions can cause the recovery process to fail if the
recovering replica, based on the information it has in the
relay log as last synchronized to disk, tries to retrieve and
apply the transactions again instead of skipping them. Setting
sync_relay_log=1
is particularly important
for a multi-threaded replica, where the recovery process fails
if gaps in the sequence of transactions cannot be filled using
the information in the relay log. For a single-threaded
replica, the recovery process only needs to use the relay log
if the relevant information is not available in the applier
metadata repository.
Set
innodb_flush_log_at_trx_commit=1
,
which synchronizes the InnoDB
logs to disk before each transaction is committed. This
setting, which is the default, ensures that
InnoDB
tables and the
InnoDB
logs are saved on disk so
that there is no longer a requirement for the information in
the relay log regarding the transaction. Combined with the
setting sync_relay_log=1
,
this setting further ensures that the content of the
InnoDB
tables and the
InnoDB
logs is consistent with
the content of the relay log at all times, so that purging the
relay log files cannot cause unfillable gaps in the replica's
history of transactions in the event of an unexpected halt.
Set relay_log_info_repository =
TABLE
, which stores the replication SQL thread
position in the InnoDB
table
mysql.slave_relay_log_info
, and updates it
together with the transaction commit to ensure a record that
is always accurate. This setting is not
the default in MySQL 5.7. If the default
FILE
setting is used, the information is
stored in a file in the data directory that is updated after
the transaction has been applied. This creates a risk of
losing synchrony with the source depending at which stage of
processing a transaction the replica halts at, or even
corruption of the file itself. With the setting
relay_log_info_repository =
FILE
, recovery is not guaranteed.
Set relay_log_recovery = ON
,
which enables automatic relay log recovery immediately
following server startup. This global variable defaults to
OFF
and is read-only at runtime, but you
can set it to ON
with the
--relay-log-recovery
option at
replica startup following an unexpected halt of a replica.
Note that this setting ignores the existing relay log files,
in case they are corrupted or inconsistent. The relay log
recovery process starts a new relay log file and fetches
transactions from the source beginning at the replication SQL
thread position recorded in the applier metadata repository.
The previous relay log files are removed over time by the
replica's normal purge mechanism.
For a multithreaded replica, from MySQL 5.7.13, setting
relay_log_recovery = ON
automatically handles any inconsistencies and gaps in the sequence
of transactions that have been executed from the relay log. These
gaps can occur when file position based replication is in use.
(For more details, see
Section 16.4.1.32, “Replication and Transaction Inconsistencies”.)
The relay log recovery process deals with gaps using the same
method as the START
SLAVE UNTIL SQL_AFTER_MTS_GAPS
statement would. When the
replica reaches a consistent gap-free state, the relay log
recovery process goes on to fetch further transactions from the
source beginning at the replication SQL thread position. In MySQL
versions prior to MySQL 5.7.13, this process was not automatic and
required starting the server with
relay_log_recovery = OFF
,
starting the replica with
START SLAVE UNTIL
SQL_AFTER_MTS_GAPS
to fix any transaction
inconsistencies, and then restarting the replica with
relay_log_recovery = ON
. When
GTID-based replication is in use, this process is unnecessary, and
from MySQL 5.7.28 a multithreaded replica automatically skips
relay log recovery when MASTER_AUTO_POSITION
is
set to ON
, so the setting for
relay_log_recovery
makes no
difference.