Configuring MySQL MGR with Asynchronous Replication Automatic Failover for Multi‑Site Disaster Recovery

Author Hong Bin, a MySQL ACE and technical service director at iKexin, introduces the limitations of MySQL Group Replication (MGR) for cross‑WAN disaster recovery and demonstrates how to combine MGR with asynchronous replication automatic failover to meet RPO requirements in multi‑site architectures.

While MGR can achieve RPO=0 within a single metropolitan area (e.g., two data centers within 100 km), it cannot alone satisfy scenarios where the secondary site is hundreds of kilometers away and a non‑zero RPO is acceptable. In such cases, asynchronous replication must be added.

In a two‑city three‑center topology, if the primary node of a local MGR cluster fails, the remote disaster‑recovery node will not automatically follow the new primary without asynchronous replication assistance.

Asynchronous Replication Automatic Failover works by configuring multiple source hosts on an asynchronous replication channel; when a source becomes unavailable, the slave’s IO thread, after failed reconnection attempts, selects a new source based on configured weights.

Step 1 – Deploy an MGR cluster and a single instance for testing.

dbdeployer deploy replication --topology=group 8.0.22 --single-primary
dbdeployer deploy single 8.0.22

Step 2 – Create a replication channel on the slave that points to the MGR primary.

change master to master_user='msandbox',
master_password='msandbox',
master_host='127.0.0.1',
master_auto_position=1,
source_connection_auto_failover=1,
master_port=23223,
master_retry_count=6,
master_connect_retry=10
for channel 'mgr-single';

Adjust master_retry_count and master_connect_retry to control how long the slave retries before switching sources.

Step 3 – Configure asynchronous connection auto‑failover functions.

asynchronous_connection_failover_add_source(channel-name,host,port,network-namespace,weight)

asynchronous_connection_failover_delete_source(channel-name,host,port,network-namespace)

Higher weight values give a source higher priority; these weights can be aligned with MGR election weights so that when MGR elects a new primary, the asynchronous channel follows automatically.

SELECT asynchronous_connection_failover_add_source('mgr-single','127.0.0.1',23223,null,100);
SELECT asynchronous_connection_failover_add_source('mgr-single','127.0.0.1',23224,null,80);
SELECT asynchronous_connection_failover_add_source('mgr-single','127.0.0.1',23225,null,50);
START SLAVE FOR CHANNEL 'mgr-single';

Step 4 – Verify that the asynchronous channel has failover enabled.

mysql> SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration;

The query should show SOURCE_CONNECTION_AUTO_FAILOVER = 1 for the channel.

Step 5 – Simulate a primary failure. Killing the MGR primary forces the slave to attempt reconnection; after the configured retry attempts it automatically switches to the next‑weight source, and the failover event appears in the slave’s error log.

Note: Once failover succeeds, the slave will not automatically switch back if the original primary recovers; it will only switch again if the current channel encounters another failure and a higher‑weight source is available.

Keywords: #异地容灾 #异步复制 #mysql8