We recently underwent the process of adding a new node to our Group Replication cluster from a backup, a critical step to ensure the cluster's seamless operation. However, as we delved deeper, we discovered challenges in managing connection routing directly from the application side.

To overcome these challenges and streamline our connection handling, we decided to implement a load balancer between the application and Group Replication (GR) nodes. Our choice was ProxySQL, a versatile solution that could bridge the gap between applications and GR nodes.

In this blog post, we'll guide you through the process of configuring ProxySQL for Group Replication, addressing the challenges we encountered along the way and providing practical solutions for a smoother implementation.

Environment details

‍

Architecture

Adding GR Under ProxySQL

We set up Group Replication with two nodes.

• Node 1 is the primary server, and it's online.
• Node 2 is the secondary server, and it's also online.

 
mysql> select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;
+------------------+-------------+--------------+-------------+
| MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+------------------+-------------+--------------+-------------+
| node 1           |        3306 | ONLINE       | PRIMARY     |
| node 2           |        3306 | ONLINE       | SECONDARY   |
+------------------+-------------+--------------+-------------+
2 rows in set (0.00 sec)
	

Proxy Setup: Adding GR Under ProxySQL

We integrated Group Replication (GR) with ProxySQL. The first step involved adding the GR servers to the 'mysql_servers' table. We placed the primary server in 'hostgroup_id = 1' and the secondary server in 'hostgroup_id = 2' for effective management.

• Primary server (node1) is in hostgroup_id 1.
• Secondary server (node2) is in hostgroup_id 2.

 
proxysql>select hostgroup_id,hostname,status from mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname      | status |
+--------------+---------------+--------+
| 1            | node1         | ONLINE |
| 2            | node2         | ONLINE |
+--------------+---------------+--------+
2 rows in set (0.00 sec)
	

Once we've added the servers to the mysql_servers table in ProxySQL, the next important task is creating an entry in the mysql_group_replication_hostgroups table. This entry plays a vital role in setting up an automatic failover mechanism, ensuring that operations continue seamlessly even if the primary server encounters a failure.

 
proxysql>select writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active |
+------------------+-------------------------+------------------+-------------------+--------+
| 1                | 4                       | 2                | 3                 | 1      |
+------------------+-------------------------+------------------+-------------------+--------+
1 row in set (0.00 sec)
	

After implementing the failover mechanism in the 'mysql_group_replication_hostgroups' table, we then carefully added the required information to both the mysql_users and mysql_query_rules tables. Following this, we conducted thorough testing of manual routing. It's important to highlight that during testing, ProxySQL faced challenges in effectively routing connections as anticipated.

Connection Routing Validation

We attempted manual connection routing to the database server through ProxySQL, but unfortunately, the connection to the database server couldn't be established.

 
root@mydbops-labs:/home/ubuntu# mysql -u test -p' -h 127.0.0.1 -P 6033 -e "select @@hostname;"
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 2 after 10000ms
	

Debugging

While troubleshooting the issue, our investigation led us to examine the runtime_mysql_servers configuration. Surprisingly, we found that all the servers were incorrectly assigned to the offline_hostgroup with hostgroup_id 3.

 
proxysql>select hostgroup_id,hostname,status from runtime_mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname      | status |
+--------------+---------------+--------+
| 3            | node1         | ONLINE |
| 3            | node2         | ONLINE |
+--------------+---------------+--------+
2 rows in set (0.01 sec)
	

Upon conducting a more in-depth analysis of the error log, a noteworthy observation emerged. The log indicated the presence of an error message, specifically stating: sys.gr_member_routing_candidate_status doesn't exist This error message prompted us to delve deeper into the root cause of the issue.

 
2023-10-08 15:49:57 MySQL_HostGroups_Manager.cpp:4651:update_group_replication_set_offline(): [WARNING] Group Replication: setting host node2:3306 off
line because: Table 'sys.gr_member_routing_candidate_status' doesn't exist
2023-10-08 15:49:57 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
	

Root Cause and Fix

Through our investigation, we pinpointed a bug within ProxySQL as the underlying cause of the issue. To rectify this, we took the necessary steps to manually enable the sys.gr_member_routing_candidate_status view on all the nodes. Following its activation, we experienced a significant improvement in functionality. ProxySQL promptly resumed mapping the servers under the correct hostgroup.

 
proxysql>select hostgroup_id,hostname,status from runtime_mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname      | status |
+--------------+---------------+--------+
| 1            | node1         | ONLINE |
| 2            | node2         | ONLINE |
+--------------+---------------+--------+
2 rows in set (0.01 sec)
	

With the successful resolution of the ProxySQL issue, our system is now operating as intended. ProxySQL has seamlessly taken on its role of routing traffic with precision. Specifically, write operations are correctly directed to the writer_hostgroup, while read operations are intelligently routed to the reader_hostgroup, ensuring efficient and balanced database access.

 
root@mydbops-labs:/home/ubuntu# mysql -u test -p -h 127.0.0.1 -P 6033 -e "select @@hostname;"
+----------------+
| @@hostname     |
+----------------+
| node2          |
+----------------+


root@mydbops-labs:/home/ubuntu# mysql -u test -p -h 127.0.0.1 -P 6033 -e "start transaction;select @@hostname;commit;"
+------------------+
| @@hostname       |
+------------------+
| node1            |
+------------------+
	

Flowchart

We're delighted to announce that this problem has been resolved in ProxySQL version 2.5.X. As a best practice, we highly recommend keeping your ProxySQL up to date with the latest versions. This proactive approach not only helps prevent potential issues caused by bugs but also ensures your environment remains current, benefiting from the latest enhancements and security updates.

Stay connected with Mydbops blogs for more technical insights and discoveries in the ProxySQL ecosystem.