导读
译者:张锐志(微信号: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
我们将在这个表中进行节点的归属组(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_servers
table:
在设置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_hostgroups
table:
看一下之前没有出现过的新列的含义
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:
然后将新更改的配置保存到磁盘上,并加载到运行环境。
We should not forget to save our mysql servers to disk and load them on runtime:
同时,我们需要在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:
如此,我们便可以从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:
同时,我们需要讲sys库的读权限赋给ProxySQL配置的监控MySQL的账户:
We also must not forget to create in our cluster the monitor user needed by ProxySQL:
接下来,我们马上检查下ProxySQL是如何将MGR节点分发到ProxySQL各个组中:
We can immediately check how ProxySQL has distributed the servers in the hostgroups :
写节点被分配到之前定义好的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:
Enjoy MySQL Group Replication & ProxySQL !
扫码加入知数堂技术交流QQ群
(群号:579036588)
群内可@各位助教了解更多课程信息
知数堂
叶金荣与吴炳锡联合打造
领跑IT精英培训
行业资深专家强强联合,倾心定制
MySQL实战/MySQL优化/大数据实战 / Python/ SQL优化
数门精品课程
紧随技术发展趋势,定期优化培训教案
融入大量生产案例,贴合企业一线需求
社群陪伴学习,一次报名,可学1年
DBA、开发工程师必修课
上千位学员已华丽转身,薪资翻番,职位提升
改变已悄然发生,你还在等什么?
扫码下载知数堂精品课程试听视频
(MySQL 实战/优化、大数据实战、Python开发,及SQL优化等课程)
密码:hg3h