符合中小企业对网站设计、功能常规化式的企业展示型网站建设
本套餐主要针对企业品牌型网站、中高端设计、前端互动体验...
商城网站建设因基本功能的需求不同费用上面也有很大的差别...
手机微信网站开发、微信官网、微信商城网站...
本篇文章给大家主要讲的是关于MySQL中ProxySQL新版本如何实现对MGR的原生支持的内容,感兴趣的话一起来看看这篇文章吧,相信看完MySQL中ProxySQL新版本如何实现对MGR的原生支持对大家多少有点参考价值吧。
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)
name | ip |
---|---|
mysql1 | 192.168.90.2 |
mysql2 | 192.168.90.3 |
mysql3 | 192.168.90.4 |
Column Name | Description |
---|---|
writer_hostgroup | the id of the hostgroup that will contain all the members that are writer MGR写节点都应被包含在这个组中 |
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 在MGR多写的模式下,如果可以提供写属性的节点超过实际使用的写节点数,剩下的节点将在这个备用写节点组中存放。 |
reader_hostgroup | the id of the hostgroup that will contain all the members in read_only 该组将会包含所有具有只读属性的MGR节点 |
offline_hostgroup | the id of the hostgroup that will contain the host not being online or not being part of the Group 改组将会包含所有无法提供服务或者不处于online情况下的节点 |
active | when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups 当该列属性启动时,ProxySQL将会监察整个集权,并根据hostgroup和节点的属性,进行匹配。 |
max_writers | limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode 控制MGR多写模式下实际对外提供写服务的节点数量 |
writer_is_also_reader | boolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup 布尔值0或1,当启动时写节点组中的节点会同时出现在读组中 |
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 定义节点最大落后整个集群的事务数量(ProxySQL内部,非MGR中的) |
创新互联建站为客户提供专业的成都网站设计、网站制作、程序、域名、空间一条龙服务,提供基于WEB的系统开发. 服务项目涵盖了网页设计、网站程序开发、WEB系统开发、微信二次开发、成都做手机网站等网站方面业务。
ProxySQL在MySQL的代理和负载均衡中一直处于领先地位。其中包含了诸如缓存查询,多路复用,流量镜像,读写分离,路由等等的强力功能。在最新的功能性增强中,包含了对MGR的原生支持,不在需要使用第三方脚本进行适配。
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.
ProxySQL的主要开发者René,更进一步的可以(利用ProxySQL)做到例如在一个七个节点的多写集群中,指定2组写节点,2组备用写节点,3个只读节点的操作。即ProxySQL虽然识别出来所有的节点皆为写节点,但只路由写操作到选定的两个写节点(通过Hostgroup的方式),同时将另外两个写节点添加到备用写节点组中,最后三个读节点加入读组。(本段中的组皆为ProxySQL中的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).
ProxySQL从1.4.0版本开始增加对MGR的原生支持,若发行版中没有,可以从GitHub中编译获取。
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
下面我们看下对于用户来说有哪些明显的变化,开始进行admin端口连接后会发现比之前多了一个mysql_group_replication_hostgroups表
This is the table we will use to setup in which hostgroup a node will belongs.
我们将在这个表中进行节点的归属组(hostgroup)的设置。
To illustrate how ProxySQL supports MySQL Group Replication, I will use a cluster of 3 nodes:
为了阐明ProxySQL 对MGR支持的原理,下面我会用到一个三节点的集群。
So first, as usual we need to add our 3 members into the mysql_servers table:
首先,我们照旧插入三个节点的信息到mysql_servers表中。
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:
在设置MGR节点在ProxySQL中的行为之前,先查看下新加入的mysql_group_replication_hostgroups表的DDL。
There are many new columns, let’s have a look at their meaning:
看一下之前没有出现过的新列的含义
Now that we are (or should be) more familiar with that table, we will set it up like this:
熟悉了表的定义后,整个拓补将会如下图所示:
So let’s add this:
下面我们将MGR集群的分组定义和关键参数写入mysql_group_replication_hostgroups表中
We should not forget to save our mysql servers to disk and load them on runtime:
然后将新更改的配置保存到磁盘上,并加载到运行环境。
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中添加如下的视图,及其依赖的存储过程。
So now from every members of the group, we can run the following statement. ProxySQL based its internal monitoring this same view:
如此,我们便可以从MGR集群中任意一个节点上执行下面的语句获取MGR成员的基本信息,ProxySQL 也是根据这个办法进行监测节点的健康与落后情况。
We also must not forget to create in our cluster the monitor user needed by ProxySQL:
同时,我们需要讲sys库的读权限赋给ProxySQL配置的监控MySQL的账户:
We can immediately check how ProxySQL has distributed the servers in the hostgroups :
接下来,我们马上检查下ProxySQL是如何将MGR节点分发到ProxySQL各个组中:
The Writer (Primary-Master) is mysql1 (192.168.90.2 in hostgroup 2) and the others are in the read hostgroup (id=3).
写节点被分配到之前定义好的ID为2的写组中,其他所有的节点被分配到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.
这样,我们就省掉了通过定时器去调用第三方复杂定义的脚本将MGR节点匹配并分配到对应的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进行配置,例如关联用户到默认ProxySQL组中,或者添加查询路由规则。
An extra table has also been added for monitoring:
另外,ProxySQL比之前多了一个监控MySQL实例的表,具体信息如下面所示:
以上关于MySQL中ProxySQL新版本如何实现对MGR的原生支持详细内容,对大家有帮助吗?如果想要了解更多相关,可以继续关注我们的行业资讯板块。