{"id":3852,"date":"2020-11-05T11:32:23","date_gmt":"2020-11-05T10:32:23","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=3852"},"modified":"2020-11-24T10:47:48","modified_gmt":"2020-11-24T09:47:48","slug":"automatic-connection-failover-for-asynchronous-replication","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2020\/11\/05\/automatic-connection-failover-for-asynchronous-replication\/","title":{"rendered":"Automatic connection failover for Asynchronous Replication"},"content":{"rendered":"\n
\"Plage<\/figure>\n\n\n\n
\n\n\n\n

TL;DR<\/strong><\/p>\n\n\n\n

Since MySQL 8.0.22<\/strong> there is a mechanism in asynchronous replication that makes the receiver automatically try to re-establish an asynchronous replication connection to another sender<\/strong>, in case the current connection gets interrupted due to the failure of the current sender.<\/p>\n\n\n\n

\n\n\n\n\n\n

Asynchronous automatic connection failover<\/a> automates the process of re-establishment of an asynchronous replication connection to another sender of sender list.
That means if a the source of a replica crashed, this replica will be able to automatically connect to another source.
One of the biggest interest is to improve Disaster Recovery (DR) architecture.<\/p>\n\n\n\n

With this feature, a typical architecture is to have a 3 nodes asynchronous replication cluster.
2 primary nodes in active\/passive mode (if you need active\/active architecture use MySQL InnoDB Cluster<\/strong><\/a><\/em>) and the third one is connected to one of the primary, either for DR or for some specialized task like analytics for example.
So in case of unavailability of its primary – if the replication I\/O thread stops due to the source stopping or due to a network failure<\/strong><\/em> – this replica will automatically connect to the other primary.<\/p>\n\n\n\n\n\n

Another architecture is to use this asynchronous automatic connection failover<\/a> feature with MySQL InnoDB Cluster.<\/p>\n\n\n\n

Hey guess what? this is the topic of this tutorial and by the way, we will use some of the fanciest MySQL 8.0 features \ud83d\ude42<\/p>\n\n\n\n\n\n\n\n

Context<\/h2>\n\n\n\n

4 MySQL 8.0.22<\/strong> instances :<\/p>\n\n\n\n


– mysql_node1<\/strong> : 172.20.0.11<\/strong>
– mysql_node2<\/strong> : 172.20.0.12<\/strong>
– mysql_node3<\/strong> : 172.20.0.13<\/strong>
These 3 are members of a MySQL Group Replication<\/a> cluster.
I created a MySQL InnoDB Cluster<\/a> (resources here<\/a> and here<\/a>).
I will not use MySQL Router<\/a> in this tutorial.<\/p>\n\n\n\n


– mysql_node4<\/strong> : 172.20.0.14<\/strong>
It’s my replica.
It will be asynchronously connected to the Group Replication.<\/p>\n\n\n\n

Because a picture is worth a thousand words, below is the architecture I want to achieve :<\/p>\n\n\n\n

\"Asynchronous<\/figure>\n\n\n\n

Graphs in this article are from MySQL Enterprise Monitor<\/a>.<\/p>\n\n\n\n\n\n\n\n

Current status<\/h2>\n\n\n\n

I’m using MySQL 8.0.22<\/a><\/strong> :<\/p>\n\n\n\n

$ mysqlsh root@mysql_node1:3306 --cluster\n\nmysql_node1:3306 ssl JS> \\sql SELECT VERSION();\n+-----------+\n| VERSION() |\n+-----------+\n| 8.0.22    |\n+-----------+<\/code><\/pre>\n\n\n\n\n\n
I have a 3 nodes Group Replication cluster up and running :<\/p>\n\n\n\n
mysql_node1:3306 ssl JS> \ncluster.status()\n{\n    \"clusterName\": \"ic1\", \n    \"defaultReplicaSet\": {\n        \"name\": \"default\", \n        \"primary\": \"mysql_node1:3306\", \n        \"ssl\": \"REQUIRED\", \n        \"status\": \"OK\", \n        \"statusText\": \"Cluster is ONLINE and can tolerate up to ONE failure.\", \n        \"topology\": {\n            \"mysql_node1:3306\": {\n                \"address\": \"mysql_node1:3306\", \n                \"mode\": \"R\/W\", \n                \"readReplicas\": {}, \n                \"replicationLag\": null, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"version\": \"8.0.22\"\n            }, \n            \"mysql_node2:3306\": {\n                \"address\": \"mysql_node2:3306\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"replicationLag\": null, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"version\": \"8.0.22\"\n            }, \n            \"mysql_node3:3306\": {\n                \"address\": \"mysql_node3:3306\", \n                \"mode\": \"R\/O\", \n                \"readReplicas\": {}, \n                \"replicationLag\": null, \n                \"role\": \"HA\", \n                \"status\": \"ONLINE\", \n                \"version\": \"8.0.22\"\n            }\n        }, \n        \"topologyMode\": \"Single-Primary\"\n    }, \n    \"groupInformationSourceMember\": \"mysql_node1:3306\"\n}<\/code><\/pre>\n\n\n\n\n\n\n\n
Setup the replication user<\/h2>\n\n\n\n
Like you know, there are some preparation steps to be able to use MySQL Asynchronous Replication<\/a>. If you’re not familiar with replication using GTID<\/strong>, please read this<\/a>.<\/p>\n\n\n\n
On the Group Replication primary – currently mysql_node1<\/em> – I setting up the asynchronous replication user:<\/p>\n\n\n\n
mysql_node1:3306 ssl SQL> \nCREATE USER 'repl'@'172.20.0.%' IDENTIFIED BY 'S3cr\u20actRepl' REQUIRE SSL;\n\n\nGRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.20.0.%';<\/code><\/pre>\n\n\n\n\n\n\n\n
Setup the clone user<\/h2>\n\n\n\n
The clone plugin<\/a> is one of my favorite feature!<\/p>\n\n\n\n
It permits cloning data locally or from a remote MySQL server instance. 
Cloned data is a physical snapshot of data stored in InnoDB<\/code> that includes schemas, tables, tablespaces, and data dictionary metadata. 
The cloned data comprises a fully functional data directory, which permits using the clone plugin for MySQL server provisioning.<\/p>\n\n\n\n
It’s a very convenient way to copy data from the source (the Group Replication) to the replica (mysql_node4).<\/p>\n\n\n\n
Thanks to InnoDB Cluster the Clone plugin is already installed on the 3 members. 
So on the primary member – currently mysql_node1<\/em> – I’ll create a dedicated clone user with the donor privileges for using and monitoring the clone plugin:<\/p>\n\n\n\n
mysql_node1:3306 ssl SQL> \nCREATE USER clone_user IDENTIFIED BY \"S3cr\u20actClone\";\n\n\nGRANT BACKUP_ADMIN, EXECUTE ON *.* TO clone_user;\n\n\nGRANT SELECT ON performance_schema.* TO clone_user;<\/code><\/pre>\n\n\n\n
Note that I could have used the cluster administrator dedicated user instead of create a specialized clone user.<\/p>\n\n\n\n\n\n
On mysql_node4<\/em>, the future replica, I’ll create the same user but with the recipient privileges. 
But before I’ll install the clone plugin and set the clone donor list:<\/p>\n\n\n\n
mysql_node4:3306 ssl SQL> \nINSTALL PLUGIN clone SONAME 'mysql_clone.so';\n\n\nSET PERSIST clone_valid_donor_list = 'mysql_node1:3306,mysql_node2:3306,mysql_node3:3306';\n\n\nCREATE USER clone_user IDENTIFIED BY \"S3cr\u20actClone\";\n\n\nGRANT CLONE_ADMIN, EXECUTE ON *.* to clone_user;\n\n\nGRANT SELECT ON performance_schema.* TO clone_user; <\/code><\/pre>\n\n\n\n\n\n\n\n
Clone a MySQL instance<\/h2>\n\n\n\n
Now we have everything all set to create the replica from a member of the group.<\/p>\n\n\n\n
On the future replica – mysql_node4<\/em> – we can now run the clone instance command:<\/p>\n\n\n\n
mysql_node4:3306 ssl SQL> \nCLONE INSTANCE FROM 'clone_user'@'mysql_node1':3306 IDENTIFIED BY 'S3cr\u20actClone';<\/code><\/pre>\n\n\n\n\n\n
If you want to monitor the cloning progress run the following query:<\/p>\n\n\n\n
mysql_node4:3306 ssl SQL> \nSELECT \n   STATE, \n   CAST(BEGIN_TIME AS DATETIME) AS \"START TIME\", \n   CASE WHEN END_TIME IS NULL THEN  LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ') \nELSE \nLPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ') END AS DURATION \nFROM performance_schema.clone_status;<\/code><\/pre>\n\n\n\n\n\n
When the cloning is over, the MySQL instance must be restarted (that will normally happen automatically). 
After the restart, you can verify that the clone completed successfully with the queries below:<\/p>\n\n\n\n
mysql_node4:3306 ssl SQL> \nSELECT \n   STATE, \n   ERROR_NO, \n   BINLOG_FILE, \n   BINLOG_POSITION, \n   GTID_EXECUTED, \n   CAST(BEGIN_TIME AS DATETIME) as \"START TIME\", \n   CAST(END_TIME AS DATETIME) as \"FINISH TIME\", \n   sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))) AS DURATION \nFROM performance_schema.clone_status \\G<\/code><\/pre>\n\n\n\n
and:<\/p>\n\n\n\n
mysql_node4:3306 ssl SQL> \nSELECT \n   STAGE, \n   STATE, \n   CAST(BEGIN_TIME AS DATETIME) as \"START TIME\", \n   CAST(END_TIME AS DATETIME) as \"FINISH TIME\", \n   LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ') AS DURATION \nFROM performance_schema.clone_progress;<\/code><\/pre>\n\n\n\n\n\n\n\n
Add the replica<\/h2>\n\n\n\n
First I will setup the configuration information for a replication source server to the source list for a replication channel.
To do that we use the function: asynchronous_connection_failover_add_source<\/a>
Information needed are the replication channel name, the source server address, port and network namespace, and also the weight.<\/p>\n\n\n\n
More information are available here<\/a>.<\/p>\n\n\n\n
For this tutorial I chose the following values:<\/p>\n\n\n\n