MySQL Group Replication: native support in ProxySQL

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

ProxySQL is the leader in proxy and load balancing solution for MySQL. It has great features like query caching, multiplexing, mirroring, read/write splitting, routing, etc… The latest enhancement in ProxySQL is the native support of MySQL Group Replication. No more need to use an external script within the scheduler like I explained in this previous post.

This implementation supports Groups in Single-Primary and in Multi-Primary mode. It is even possible to setup a Multi-Primary Group but dedicate writes on only one member.

René, the main developer of ProxySQL, went even further. For example in a 7 nodes clusters (Group of 7 members) where all nodes are writers (Multi-Primary mode), it’s possible to decide to have only 2 writers, 3 readers and 2 backup-writers. This mean that ProxySQL will see all the nodes as possible writers but will only route writes on 2 nodes (add them in the  writer hostgroup, because we decided to limit it to 2 writers for example), then it will add the others in the backup-writers group, this group defines the other writer candidates. An finally add 2 in the readers hostgroup.

It’s also possible to limit the access to a member that is slower in applying the replicated transactions (applying queue reaching a threshold).

It is time to have a look at this new ProxySQL version. The version supporting MySQL Group Replication is 1.4.0 and currently is only available on github (but stay tuned for a new release soon).

So let’s have a look at what is new for users. When you connect to the admin interface of ProxySQL, you can see a new table: mysql_group_replication_hostgroups

ProxySQL> show tables ;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
...
| scheduler                                  |
+--------------------------------------------+
15 rows in set (0.00 sec)

This is the table we will use to setup in which hostgroup a node will belongs.

To illustrate how ProxySQL supports MySQL Group Replication, I will use a cluster of 3 nodes:

name ip
mysql1 192.168.90.2
mysql2 192.168.90.3
mysql3 192.168.90.4

So first, as usual we need to add our 3 members into the mysql_servers table:

mysql> insert into mysql_servers (hostgroup_id,hostname,port) values (2,'192.168.90.2',3306);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers (hostgroup_id,hostname,port) values (2,'192.168.90.3',3306);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers (hostgroup_id,hostname,port) values (2,'192.168.90.4',3306);
Query OK, 1 row affected (0.00 sec)


mysql> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 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              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

Now we can setup ProxySQL’s behavior with our Group Replication cluster, but before let’s check the definition of the new mysql_group_replication_hostgroups table:

ProxySQL> show create table mysql_group_replication_hostgroups\G
*************************** 1. row ***************************
       table: mysql_group_replication_hostgroups
Create Table: CREATE TABLE mysql_group_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
    offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,
    max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
    comment VARCHAR,
    UNIQUE (reader_hostgroup),
    UNIQUE (offline_hostgroup),
    UNIQUE (backup_writer_hostgroup))

There are many new columns, let’s have a look at their meaning:

Column Name Description
writer_hostgroup the id of the hostgroup that will contain all the members that are writer
backup_writer_hostgroup if the group is running in multi-primary mode, there are multi writers (read_only=0) but if the amount of these writer is
larger than the max_writers, the extra nodes are located in that backup writer group
reader_hostgroup the id of the hostgroup that will contain all the members in read_only
offline_hostgroup the id of the hostgroup that will contain the host not being online or not being part of the Group
active when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups
max_writers limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode
writer_is_also_reader boolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup
max_transactions_behind if the value is greater than 0, it defines how much a node can be lagging in applying the transactions from the Group, see this post for more info

Now that we are (or should be) more familiar with that table, we will set it up like this:

So let’s add this:

ProxySQL> insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,
reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values (2,4,3,1,1,1,0,100);

We should not forget to save our mysql servers to disk and load them on runtime:

ProxySQL> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)

ProxySQL> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

It’s also important with the current version of MySQL Group Replication to add a view and its dependencies in sys schema: addition_to_sys.sql:

# mysql -p < addition_to_sys.sql

So now from every members of the group, we can run the following statement. ProxySQL based its internal monitoring this same view:

mysql> select * from gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | YES       |                  40 |                    0 |
+------------------+-----------+---------------------+----------------------+

We also must not forget to create in our cluster the monitor user needed by ProxySQL:

mysql> GRANT SELECT on sys.* to 'monitor'@'%' identified by 'monitor';

We can immediately check how ProxySQL has distributed the servers in the hostgroups :

ProxySQL>  select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+--------------+--------+
| hostgroup_id | hostname     | status |
+--------------+--------------+--------+
| 2            | 192.168.90.2 | ONLINE |
| 3            | 192.168.90.3 | ONLINE |
| 3            | 192.168.90.4 | ONLINE |
+--------------+--------------+--------+

The Writer (Primary-Master) is mysql1 (192.168.90.2 in hostgroup 2) and the others are in the read hostgroup (id=3).

As you can see, there is no more need to create a scheduler calling an external script with complex rules to move the servers in the right hostgroup.

Now to use the proxy, it’s exactly as usual, you need to create users associated to default hostgroup or add routing rules.

An extra table has also been added for monitoring:

ProxySQL> SHOW TABLES FROM monitor ;
+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect               |
| mysql_server_connect_log           |
| mysql_server_group_replication_log |
| mysql_server_ping                  |
| mysql_server_ping_log              |
| mysql_server_read_only_log         |
| mysql_server_replication_lag_log   |
+------------------------------------+
7 rows in set (0.00 sec)

ProxySQL> select * from mysql_server_group_replication_log order by time_start_us desc  limit 5 ;
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname     | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 192.168.90.4 | 3306 | 1490187314429511 | 1887            | YES              | NO        | 0                   | NULL  |
| 192.168.90.3 | 3306 | 1490187314429141 | 1378            | YES              | YES       | 0                   | NULL  |
| 192.168.90.2 | 3306 | 1490187314428743 | 1478            | NO               | NO        | 0                   | NULL  |
| 192.168.90.4 | 3306 | 1490187309406886 | 3639            | YES              | NO        | 0                   | NULL  |
| 192.168.90.3 | 3306 | 1490187309406486 | 2444            | YES              | YES       | 0                   | NULL  |
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+

Enjoy MySQL Group Replication & ProxySQL !

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

11 thoughts on “MySQL Group Replication: native support in ProxySQL

  1. Just to be clear: for group replication we do not need query rules anymore, because they are implemented by default?

    1. Hi Wolf,

      You still need to have some rules if your Primary-Master node is not part of the default hostgroup assigned to the user.

      Cheers,

      1. Hi, and thanks for the answer!

        The question is, if the connections are routed in a read-write splitted way by default, or if it’s just a primary-only connection. 🙂

        Kind regards,
        Tobias

  2. mysql> SELECT * FROM performance_schema.replication_group_members\G
    *************************** 1. row ***************************
    CHANNEL_NAME: group_replication_applier
    MEMBER_ID: ac41a42c-9e03-11e7-b155-00163e0038ad
    MEMBER_HOST: hadoop-master-ppe
    MEMBER_PORT: 3307
    MEMBER_STATE: ONLINE
    *************************** 2. row ***************************
    CHANNEL_NAME: group_replication_applier
    MEMBER_ID: bb872c4c-9e03-11e7-b31b-00163e0038ad
    MEMBER_HOST: hadoop-master-ppe
    MEMBER_PORT: 3308
    MEMBER_STATE: ONLINE
    *************************** 3. row ***************************
    CHANNEL_NAME: group_replication_applier
    MEMBER_ID: d7a278c0-9dd6-11e7-bdeb-00163e0038ad
    MEMBER_HOST: hadoop-master-ppe
    MEMBER_PORT: 3309
    MEMBER_STATE: ONLINE
    3 rows in set (0.00 sec)

    mysql> select * from mysql_group_replication_hostgroups;
    +——————+————————-+——————+——————-+——–+————-+———————–+————————-+———+
    | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
    +——————+————————-+——————+——————-+——–+————-+———————–+————————-+———+
    | 2 | 4 | 3 | 1 | 1 | 1 | 0 | 100 | NULL |
    +——————+————————-+——————+——————-+——–+————-+———————–+————————-+———+
    1 row in set (0.00 sec)
    mysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
    +————–+———–+——+——–+
    | hostgroup_id | hostname | port | status |
    +————–+———–+——+——–+
    | 2 | 127.0.0.1 | 3307 | ONLINE |
    | 4 | 127.0.0.1 | 3309 | ONLINE |
    | 4 | 127.0.0.1 | 3308 | ONLINE |
    +————–+———–+——+——–+

    mysql> select * from sys.gr_member_routing_candidate_status;
    +——————+———–+———————+———————-+
    | viable_candidate | read_only | transactions_behind | transactions_to_cert |
    +——————+———–+———————+———————-+
    | YES | NO | 0 | 0 |
    +——————+———–+———————+———————-+
    1 row in set (0.01 sec)

    why i just got the back_write hostgroup.

      1. ok, Thanks.

        The node of MGR variable read_only is OFF, If I need to adjust it.

        and the follow step, if I need execute it at every node of MGR.

        mysql -p < addition_to_sys.sql
        GRANT SELECT on sys.* to 'monitor'@'%' identified by 'monitor';

        now I do it at every node. I think if i made mistake.

        1. Hi, Lefred:

          I found if I modify the read_only at MGR , the runtime_mysql_servers hostgroup list can be changed.

          mysql> select hostgroup_id, hostname, port, status from runtime_mysql_servers;
          +————–+——————-+——+——–+
          | hostgroup_id | hostname | port | status |
          +————–+——————-+——+——–+
          | 2 | hadoop-master-ppe | 3309 | ONLINE |
          | 3 | hadoop-master-ppe | 3308 | ONLINE |
          | 3 | hadoop-master-ppe | 3307 | ONLINE |
          +————–+——————-+——+——–+
          3 rows in set (0.00 sec)

          I think it’s automatic. There is no need to modify.

Leave a Reply

Your email address will not be published. Required fields are marked *

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories