MySQL中ProxySQL新版本如何實(shí)現(xiàn)對MGR的原生支持

本篇文章給大家主要講的是關(guān)于MySQL中ProxySQL新版本如何實(shí)現(xiàn)對MGR的原生支持的內(nèi)容，感興趣的話一起來看看這篇文章吧，相信看完MySQL中ProxySQL新版本如何實(shí)現(xiàn)對MGR的原生支持對大家多少有點(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)被包含在這個組中
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)超過實(shí)際使用的寫節(jié)點(diǎn)數(shù)，剩下的節(jié)點(diǎn)將在這個備用寫節(jié)點(diǎn)組中存放。
reader_hostgroup	the id of the hostgroup that will contain all the members in read_only 該組將會包含所有具有只讀屬性的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 改組將會包含所有無法提供服務(wù)或者不處于online情況下的節(jié)點(diǎn)
active	when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups 當(dāng)該列屬性啟動時(shí)，ProxySQL將會監(jiān)察整個集權(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í)際對外提供寫服務(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)啟動時(shí)寫節(jié)點(diǎn)組中的節(jié)點(diǎn)會同時(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)最大落后整個集群的事務(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  |
+--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+

ProxySQL在MySQL的代理和負(fù)載均衡中一直處于領(lǐng)先地位。其中包含了諸如緩存查詢，多路復(fù)用，流量鏡像，讀寫分離，路由等等的強(qiáng)力功能。在最新的功能性增強(qiáng)中，包含了對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)中，提供了對單寫和多寫集群組的支持，甚至可以在多寫組上指定只由某個成員進(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）做到例如在一個七個節(jié)點(diǎn)的多寫集群中，指定2組寫節(jié)點(diǎn)，2組備用寫節(jié)點(diǎn)，3個只讀節(jié)點(diǎn)的操作。即ProxySQL雖然識別出來所有的節(jié)點(diǎn)皆為寫節(jié)點(diǎn)，但只路由寫操作到選定的兩個寫節(jié)點(diǎn)（通過Hostgroup的方式），同時(shí)將另外兩個寫節(jié)點(diǎn)添加到備用寫節(jié)點(diǎn)組中，最后三個讀節(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).

除此之外，還可以限制連接訪問集群中超出最大設(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版本開始增加對MGR的原生支持，若發(fā)行版中沒有，可以從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

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

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

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

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

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

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

首先，我們照舊插入三個節(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:

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

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

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

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中添加如下的視圖，及其依賴的存儲過程。

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

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

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

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

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

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

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.

這樣，我們就省掉了通過定時(shí)器去調(diào)用第三方復(fù)雜定義的腳本將MGR節(jié)點(diǎn)匹配并分配到對應(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.

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

An extra table has also been added for monitoring:

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

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