HA with MySQL Group Replication and ProxySQL

After having played with MySQL Group Replication and HAProxy, it’s time to show you how easy it’s to setup MySQL HA with ProxySQL.

ProxySQL is a high performance open source proxy for MySQL. It has many features that invite you to discover on proxysql.com and on github.

If you remember, I wrote in my last post that it is recommended to use Group Replication with only one WRITER group member. As it is the preferred architecture, I will show you how to achieve this using ProxySQL. With ProxySQL, you don’t need to have two different interfaces to split reads and writes.

In fact, when you use ProxySQL, you have a much larger amount of options to route your queries. In production, the smart DBA, will identify the queries that would be better to move away from the writer member. ProxySQL allows you to route the queries using regexp (this is what I am using here for demo purpose) but also digest of your queries. This is what I would recommend to use in production.

So in summary about how to define the best routing, you should configure ProxySQL to send all the traffic to only one member. Then check and determine which are the expensive read statements in “`stats_mysql_query_digest“` and create rules to send those queries using their digest to the READ hostgroup.

Let’s go back to our architecture for today. This is how it looks like :

So we have two hostgroups :

• one (in red in the diagram) for writes and reads not matching our rule(s), where only one node is “active” and in case there is a “problem”, ProxySQL will route to another one.
• one (in green) for all the queries matching our rule(s), where all members are active and get the requests

Let me show you how to configure those groups in ProxySQL:

[root@mysql1 ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql1 mysql> \R Admin>
PROMPT set to 'Admin> '

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.90.2',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.90.3',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.90.4',3306);

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.90.2',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.90.3',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.90.4',3306);

So here we can see we will use the same Groups members that are part of our “cluster”.

Admin> select * from mysql_servers;
+--------------+--------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname     | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+--------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| 1            | 192.168.90.2 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 1            | 192.168.90.3 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 1            | 192.168.90.4 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 2            | 192.168.90.2 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 2            | 192.168.90.3 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 2            | 192.168.90.4 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |
+--------------+--------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+

Easy isn’t it ?

Now we will add a scheduler that will use a script that verifies our MySQL InnoDB Cluster (Group Replication). The script is available on my github: here and you can put it in “`/var/lib/proxysql/“`

Admin> INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) 
       VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');

Let’s save and load the scheduler:

Admin> SAVE SCHEDULER TO DISK;

Admin> LOAD SCHEDULER TO RUNTIME;

What are those values in arg1 to arg5 ?

• arg1 is the hostgroup_id for write
• arg2 is the hostgroup_id for read
• arg3 is the number of writers we want active at the same time
• arg4 represents if we want that the member acting for writes is also candidate for reads
• arg5 is the log file

So now we can see that the script modified the status of the members :

Admin> select * from mysql_servers;
+--------------+--------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname     | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+--------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| 1            | 192.168.90.2 | 3306 | ONLINE       | 1      | 0           | 1000            | 5                   | 0       | 0              |
| 1            | 192.168.90.3 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 5                   | 0       | 0              |
| 1            | 192.168.90.4 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 5                   | 0       | 0              |
| 2            | 192.168.90.2 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 10                  | 0       | 0              |
| 2            | 192.168.90.3 | 3306 | ONLINE       | 1      | 0           | 1000            | 10                  | 0       | 0              |
| 2            | 192.168.90.4 | 3306 | ONLINE       | 1      | 0           | 1000            | 10                  | 0       | 0              |
+--------------+--------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+

It’s time to add some routing rules to be able to use those hostgroups. If you don only the first hostgroup will be used.

Admin> insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) 
values (1,"^SELECT",2,1);

We will route all queries starting by select (this is not a recommendation of course a we will also send to hostgroup 2 all SELECT… FOR UPDATE, for example).

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;

You can now save to disk the setup as it works as expected and it was easy and quick to setup:

Admin> save MYSQL SERVERS TO DISK;

Admin> SAVE MYSQL QUERY RULES TO DISK;

This is a demo in video :