本篇文章給大家主要講的是關(guān)于MySQL中ProxySQL新版本如何實(shí)現(xiàn)對(duì)MGR的原生支持的內(nèi)容，感興趣的話一起來(lái)看看這篇文章吧，相信看完MySQL中ProxySQL新版本如何實(shí)現(xiàn)對(duì)MGR的原生支持對(duì)大家多少有點(diǎn)參考價(jià)值吧。
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

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              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
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))

Column Name	Description
writer_hostgroup	the id of the hostgroup that will contain all the members that are writer MGR寫節(jié)點(diǎn)都應(yīng)被包含在這個(gè)組中
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多寫的模式下，如果可以提供寫屬性的節(jié)點(diǎn)超過(guò)實(shí)際使用的寫節(jié)點(diǎn)數(shù)，剩下的節(jié)點(diǎn)將在這個(gè)備用寫節(jié)點(diǎn)組中存放。
reader_hostgroup	the id of the hostgroup that will contain all the members in read_only 該組將會(huì)包含所有具有只讀屬性的MGR節(jié)點(diǎn)
offline_hostgroup	the id of the hostgroup that will contain the host not being online or not being part of the Group 改組將會(huì)包含所有無(wú)法提供服務(wù)或者不處于online情況下的節(jié)點(diǎn)
active	when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups 當(dāng)該列屬性啟動(dòng)時(shí)，ProxySQL將會(huì)監(jiān)察整個(gè)集權(quán)，并根據(jù)hostgroup和節(jié)點(diǎn)的屬性，進(jìn)行匹配。
max_writers	limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode 控制MGR多寫模式下實(shí)際對(duì)外提供寫服務(wù)的節(jié)點(diǎn)數(shù)量
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，當(dāng)啟動(dòng)時(shí)寫節(jié)點(diǎn)組中的節(jié)點(diǎn)會(huì)同時(shí)出現(xiàn)在讀組中
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 定義節(jié)點(diǎn)最大落后整個(gè)集群的事務(wù)數(shù)量（ProxySQL內(nèi)部，非MGR中的）

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);
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)
# mysql -p < addition_to_sys.sql
mysql> select * from gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | YES       |                  40 |                    0 |
+------------------+-----------+---------------------+----------------------+
mysql> GRANT SELECT on sys.* to 'monitor'@'%' identified by 'monitor';
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 |
+--------------+--------------+--------+
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  |
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+

創(chuàng)新互聯(lián)建站為客戶提供專業(yè)的成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、程序、域名、空間一條龍服務(wù)，提供基于WEB的系統(tǒng)開發(fā). 服務(wù)項(xiàng)目涵蓋了網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站程序開發(fā)、WEB系統(tǒng)開發(fā)、微信二次開發(fā)、成都做手機(jī)網(wǎng)站等網(wǎng)站方面業(yè)務(wù)。

ProxySQL在MySQL的代理和負(fù)載均衡中一直處于領(lǐng)先地位。其中包含了諸如緩存查詢，多路復(fù)用，流量鏡像，讀寫分離，路由等等的強(qiáng)力功能。在最新的功能性增強(qiáng)中，包含了對(duì)MGR的原生支持，不在需要使用第三方腳本進(jìn)行適配。

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.

最新的增強(qiáng)中，提供了對(duì)單寫和多寫集群組的支持，甚至可以在多寫組上指定只由某個(gè)成員進(jìn)行寫入操作。

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的主要開發(fā)者René，更進(jìn)一步的可以（利用ProxySQL）做到例如在一個(gè)七個(gè)節(jié)點(diǎn)的多寫集群中，指定2組寫節(jié)點(diǎn)，2組備用寫節(jié)點(diǎn)，3個(gè)只讀節(jié)點(diǎn)的操作。即ProxySQL雖然識(shí)別出來(lái)所有的節(jié)點(diǎn)皆為寫節(jié)點(diǎn)，但只路由寫操作到選定的兩個(gè)寫節(jié)點(diǎn)（通過(guò)Hostgroup的方式），同時(shí)將另外兩個(gè)寫節(jié)點(diǎn)添加到備用寫節(jié)點(diǎn)組中，最后三個(gè)讀節(jié)點(diǎn)加入讀組。（本段中的組皆為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).

除此之外，還可以限制連接訪問(wèn)集群中超出最大設(shè)定落后事務(wù)值的慢節(jié)點(diǎn)。

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版本開始增加對(duì)MGR的原生支持，若發(fā)行版中沒(méi)有，可以從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

下面我們看下對(duì)于用戶來(lái)說(shuō)有哪些明顯的變化，開始進(jìn)行admin端口連接后會(huì)發(fā)現(xiàn)比之前多了一個(gè)mysql_group_replication_hostgroups表

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

我們將在這個(gè)表中進(jìn)行節(jié)點(diǎn)的歸屬組（hostgroup）的設(shè)置。

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

為了闡明ProxySQL 對(duì)MGR支持的原理，下面我會(huì)用到一個(gè)三節(jié)點(diǎn)的集群。

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

首先，我們照舊插入三個(gè)節(jié)點(diǎn)的信息到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:

在設(shè)置MGR節(jié)點(diǎn)在ProxySQL中的行為之前，先查看下新加入的mysql_group_replication_hostgroups表的DDL。

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

看一下之前沒(méi)有出現(xiàn)過(guò)的新列的含義

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

熟悉了表的定義后，整個(gè)拓補(bǔ)將會(huì)如下圖所示：

So let’s add this:

下面我們將MGR集群的分組定義和關(guān)鍵參數(shù)寫入mysql_group_replication_hostgroups表中

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

然后將新更改的配置保存到磁盤上，并加載到運(yùn)行環(huán)境。

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:

同時(shí)，我們需要在MGR中添加如下的視圖，及其依賴的存儲(chǔ)過(guò)程。

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

如此，我們便可以從MGR集群中任意一個(gè)節(jié)點(diǎn)上執(zhí)行下面的語(yǔ)句獲取MGR成員的基本信息，ProxySQL 也是根據(jù)這個(gè)辦法進(jìn)行監(jiān)測(cè)節(jié)點(diǎn)的健康與落后情況。

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

同時(shí)，我們需要講sys庫(kù)的讀權(quán)限賦給ProxySQL配置的監(jiān)控MySQL的賬戶：

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

接下來(lái)，我們馬上檢查下ProxySQL是如何將MGR節(jié)點(diǎn)分發(fā)到ProxySQL各個(gè)組中：

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

寫節(jié)點(diǎn)被分配到之前定義好的ID為2的寫組中，其他所有的節(jié)點(diǎn)被分配到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.

這樣，我們就省掉了通過(guò)定時(shí)器去調(diào)用第三方復(fù)雜定義的腳本將MGR節(jié)點(diǎn)匹配并分配到對(duì)應(yīng)的ProxySQL組中的操作。

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

接下來(lái)，你就可以按照之前的做法對(duì)ProxySQL進(jìn)行配置，例如關(guān)聯(lián)用戶到默認(rèn)ProxySQL組中，或者添加查詢路由規(guī)則。

An extra table has also been added for monitoring:

另外，ProxySQL比之前多了一個(gè)監(jiān)控MySQL實(shí)例的表，具體信息如下面所示：

以上關(guān)于MySQL中ProxySQL新版本如何實(shí)現(xiàn)對(duì)MGR的原生支持詳細(xì)內(nèi)容，對(duì)大家有幫助嗎？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。

文章題目：MySQL中ProxySQL新版本如何實(shí)現(xiàn)對(duì)MGR的原生支持
本文路徑：http://www.rwnh.cn/article8/ggohip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供關(guān)鍵詞優(yōu)化、定制網(wǎng)站、靜態(tài)網(wǎng)站、面包屑導(dǎo)航、企業(yè)網(wǎng)站制作、自適應(yīng)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)