Beginning with MySQL 8.0.22, you can use the asynchronous
connection failover mechanism to automatically establish an
asynchronous (source to replica) replication connection to a new
source after the existing connection from a replica to its source
fails. The connection fails over if the replication I/O thread
stops due to the source stopping or due to a network failure. The
connection does not fail over in any other situations, such as
when the replication threads are stopped by a
STOP REPLICA |
SLAVE statement. The asynchronous connection failover
mechanism can be used to keep a replica synchronized with multiple
MySQL servers or groups of servers that share data, including
asynchronous replication from servers where Group Replication is
To activate asynchronous connection failover for a replication
CHANGE MASTER TO statement
for the channel. You can set this option while the replica is
When the existing connection to a source fails, the replica
first retries the same connection the number of times specified
MASTER_RETRY_COUNT option of the
CHANGE MASTER TO command. The
interval between attempts is set by the
MASTER_CONNECT_RETRY option. When these
attempts are exhausted, the asynchronous connection failover
mechanism takes over. Note that the defaults for these options,
which were designed for a connection to a single source, make
the replica retry the same connection for 60 days. To ensure
that the asynchronous connection failover mechanism can be
activated promptly, set
a minimal number that just allows a few retry attempts with the
same source, in case the connection failure is caused by a
transient network outage.
Also set a source list on the replica for the replication channel.
You set and manage source lists using the
UDFs to add and remove replication source servers from the source
list for a channel. The source lists are stored in the
table, and can be viewed in the Performance Schema table
The UDFs name the relevant replication channel and specify the
host name, port number, network namespace, and priority (1-100,
with 100 being the highest priority) of a MySQL instance to add to
or delete from the channel's source list. If the priority is
omitted when adding an instance, a default of 50 is applied.
The setting for the
SOURCE_CONNECTION_AUTO_FAILOVER option of the
CHANGE MASTER TO statement, and the
source list, are transferred to a clone of the replica during a
remote cloning operation.
When the asynchronous connection failover mechanism is activated
following the failure of the replica's connection to the source,
it issues a
REPLICA | SLAVE statement to attempt to connect to a new
source. The source with the highest priority setting among the
alternative sources listed in the source list for the channel is
chosen for the first connection attempt. If this attempt does not
work, the replica tries with all the listed sources in descending
order of priority, then starts again from the highest priority
source. If multiple sources have the same priority, the replica
orders them randomly. If the replica needs to start working
through the list again, it includes and retries the source to
which the original connection failure occurred.
Once the replica has succeeded in making a connection, it does not change the connection unless the new source stops or there is a network failure. This is the case even if the source that became unavailable and triggered the connection change becomes available again and has a higher priority setting.
The requirements for using the asynchronous connection failover mechanism are as follows:
GTIDs must be in use on the source and the replica (
gtid_mode=ON), and the
MASTER_AUTO_POSITIONoption of the
CHANGE MASTER TOstatement must be enabled on the replica, so that GTID auto-positioning is used for the connection to the source.
The same replication user account and password must exist on all the source servers in the source list for the channel. This account is used for the connection to each of the sources. You can set up different accounts for different channels.
The replication user account and password cannot be specified on the statement used to start replication, because they need to be available on the automatic restart for the connection to the alternative source. They must be set for the channel using the
CHANGE MASTER TOstatement on the replica, and recorded in the replication metadata repositories.