3.9 Switching Sources with Asynchronous Connection Failover

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.

To activate asynchronous connection failover for a replication channel set SOURCE_CONNECTION_AUTO_FAILOVER=1 on the CHANGE MASTER TO statement for the channel. You can set this option while the replica is running.

Important

When the existing connection to a source fails, the replica first retries the same connection the number of times specified by the 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 MASTER_RETRY_COUNT to 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 asynchronous_connection_failover_add_source and asynchronous_connection_failover_delete_source UDFs to add and remove replication source servers from the source list for a channel. The source lists are stored in the mysql.replication_asynchronous_connection_failover table, and can be viewed in the Performance Schema table replication_asynchronous_connection_failover. 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 START 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_POSITION option of the CHANGE MASTER TO statement 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 TO statement on the replica, and recorded in the replication metadata repositories.