导读
译者：张锐志（微信号：516160507），知数堂第10期MySQL学员
原文出处：****http://lefred.be/content/mysql-group-replication-native-support-in-proxysql/
原文作者：lefred
ProxySQL在MySQL的代理和负载均衡中一直处于领先地位。其中包含了诸如缓存查询，多路复用，流量镜像，读写分离，路由等等的强力功能。在最新的功能性增强中，包含了对MGR的原生支持，不在需要使用第三方脚本进行适配。
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.
ProxySQL的主要开发者René，更进一步的可以（利用ProxySQL）做到例如在一个七个节点的多写集群中，指定2组写节点，2组备用写节点，3个只读节点的操作。即ProxySQL虽然识别出来所有的节点皆为写节点，但只路由写操作到选定的两个写节点（通过Hostgroup的方式），同时将另外两个写节点添加到备用写节点组中，最后三个读节点加入读组。（本段中的组皆为ProxySQL中的hostgroup含义）。
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).
ProxySQL从1.4.0版本开始增加对MGR的原生支持，若发行版中没有，可以从GitHub中编译获取。
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).
下面我们看下对于用户来说有哪些明显的变化，开始进行admin端口连接后会发现比之前多了一个mysql_group_replication_hostgroups表
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)
我们将在这个表中进行节点的归属组（hostgroup）的设置。
This is the table we will use to setup in which hostgroup a node will belongs.
为了阐明ProxySQL 对MGR支持的原理，下面我会用到一个三节点的集群。
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
首先，我们照旧插入三个节点的信息到mysql_servers表中。
So first, as usual we need to add our 3 members into the
mysql_serverstable:
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 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
在设置MGR节点在ProxySQL中的行为之前，先查看下新加入的mysql_group_replication_hostgroups表的DDL。
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_hostgroupstable:
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
熟悉了表的定义后，整个拓补将会如下图所示：
Now that we are (or should be) more familiar with that table, we will set it up like this:
下面我们将MGR集群的分组定义和关键参数写入
mysql_group_replication_hostgroups表中
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)
同时，我们需要在MGR中添加如下的视图，及其依赖的存储过程。
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
如此，我们便可以从MGR集群中任意一个节点上执行下面的语句获取MGR成员的基本信息，ProxySQL 也是根据这个办法进行监测节点的健康与落后情况。
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 |
+------------------+-----------+---------------------+----------------------+
同时，我们需要讲sys库的读权限赋给ProxySQL配置的监控MySQL的账户：
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';
接下来，我们马上检查下ProxySQL是如何将MGR节点分发到ProxySQL各个组中：
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 |
+--------------+--------------+--------+
写节点被分配到之前定义好的ID为2的写组中，其他所有的节点被分配到ID为3的只读组中。（单写模式）
The Writer (Primary-Master) is mysql1 (192.168.90.2 in hostgroup 2) and the others are in the read hostgroup (id=3).
这样，我们就省掉了通过定时器去调用第三方复杂定义的脚本将MGR节点匹配并分配到对应的ProxySQL组中的操作。
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.
接下来，你就可以按照之前的做法对ProxySQL进行配置，例如关联用户到默认ProxySQL组中，或者添加查询路由规则。
Now to use the proxy, it’s exactly as usual, you need to create users associated to default hostgroup or add routing rules.
另外，ProxySQL比之前多了一个监控MySQL实例的表，具体信息如下面所示：
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 !
扫码加入知数堂技术交流QQ群
（群号：****579036588）
群内可@各位助教了解更多课程信息
知数堂
叶金荣与吴炳锡联合打造
领跑IT精英培训
行业资深专家强强联合，倾心定制
MySQL实战/MySQL优化/大数据实战 / Python/ SQL优化
数门精品课程
紧随技术发展趋势，定期优化培训教案
融入大量生产案例，贴合企业一线需求
社群陪伴学习，一次报名，可学1年
DBA、开发工程师必修课
上千位学员已华丽转身，薪资翻番，职位提升
改变已悄然发生，你还在等什么？
扫码下载知数堂精品课程试听视频
（MySQL 实战/优化、大数据实战、Python开发，及SQL优化等课程）
密码：hg3h
本文分享自微信公众号 - 老叶茶馆（iMySQL_WX）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
