Hi Team,
I have deployed two group replication clusters each in one DC. Active cluster in DC1 and passive cluster in DC2. I am trying to enable asynchronous replication between the two clusters. I am also trying to enable asynchronous automatic connection failover so that if a new primary is elected in either DC1 or DC2, the replication can automatically resume.
MySQL versions in both the DC’s are 8.0.35. As per the doc, post MySQL 8.0.27, if we set SOURCE_CONNECTION_AUTO_FAILOVER=1 on the primary of the passive cluster, incase the primary in the passive cluster goes down, the newly elected primary will be able to automatically establish the connection with the primary of the active cluster.
I am running the below commands on the primary of the passive cluster
SELECT asynchronous_connection_failover_add_managed('ms_async_channel', 'GroupReplication', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'xx.yy.zz.xx', 3306, '', 80, 60);
CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'xx.yy.zz.xx', SOURCE_USER = 'ms_usr', SOURCE_PASSWORD = 'password', SOURCE_PORT = 3306, SOURCE_AUTO_POSITION = 1, SOURCE_CONNECTION_AUTO_FAILOVER = 1, SOURCE_CONNECT_RETRY = 10, SOURCE_RETRY_COUNT = 3 FOR CHANNEL 'ms_async_channel';
As soon as I run the 2nd command, although the primary of the passive cluster is able to establish the connection with the primary of the active cluster, the other members in the passive cluster leave the group with the error
[Repl] Unable to set SOURCE_CONNECTION_AUTO_FAILOVER on a non-existent or misconfigured replication channel 'ms_async_channel', please create the channel and rejoin the server to the group
While setting up the GR clusters in both the DCs, I had created the user and the replication channel on all the servers of the active and the passive clusters
CREATE USER IF NOT EXISTS 'ms_usr'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'ms_usr'@'%';
GRANT SELECT ON performance_schema.* TO 'ms_usr'@'%'
CHANGE REPLICATION SOURCE TO SOURCE_USER='ms_usr', SOURCE_PASSWORD='password' FOR CHANNEL 'ms_async_channel';