Shulou(Shulou.com)06/01 Report--

This article is mainly about how the new version of ProxySQL in MySQL implements native support for MGR. If you are interested, let's take a look at this article. I believe it is of some reference value to you after reading how the new version of ProxySQL in MySQL implements native support for 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)

Nameipmysql1192.168.90.2mysql2192.168.90.3mysql3192.168.90.4mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (192.168.90.2)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (2meme 192.168.90.3)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (2meme 192.168.90.4)

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_hostgroupwriter_hostgroup) NOT NULL

Reader_hostgroup INT NOT NULL CHECK (reader_hostgroupwriter_hostgroup AND backup_writer_hostgroupreader_hostgroup AND reader_hostgroup > 0)

Offline_hostgroup INT NOT NULL CHECK (offline_hostgroupwriter_hostgroup AND offline_hostgroupreader_hostgroup AND backup_writer_hostgroupoffline_hostgroup AND offline_hostgroup > = 0)

Active INT CHECK (active IN (0Pol 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 (0Pol 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 NameDescriptionwriter_hostgroupthe id of the hostgroup that will contain all the members that are writer MGR write nodes should be included in this group backup_writer_hostgroupif 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 in MGR overwrite mode, if the nodes that can provide write attributes exceed the number of write nodes actually used The remaining nodes will be stored in this alternate write node group. Reader_hostgroupthe id of the hostgroup that will contain all the members in read_only this group will contain all MGR nodes with read-only attributes the offline_hostgroupthe id of the hostgroup that will contain the host not being online or not being part of the Group group will contain all nodes activewhen enabled that cannot provide services or are not in the case of online, ProxySQL monitors the Group and move the server according in the appropriate hostgroups when the column attribute is started, ProxySQL will monitor the entire centralization and will monitor the entire centralization according to the hostgroup and node attributes To match. Max_writerslimit the amount of nodes in the writer hostgroup in case of group in multi-primary mode controls the number of nodes that actually provide write services in MGR multi-write mode writer_is_also_readerboolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup Boolean value 0 or 1 When starting, the nodes in the write node group will appear in the read group at the same time. Max_transactions_behindif 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 define the maximum number of transactions that the node lags behind the entire cluster (internal ProxySQL, non-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, 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

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 has always been in the leading position in the proxy and load balancing of MySQL. It includes powerful functions such as cache query, multiplexing, traffic mirroring, read-write separation, routing and so on. The latest functional enhancements include native support for MGR, eliminating the need for third-party scripts for adaptation.

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.

In the latest enhancements, support for single-write and multi-write cluster groups is provided, and it is even possible to specify that only one member writes on the multi-write group.

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.

Ren é, the main developer of ProxySQL, can go a step further (using ProxySQL) to specify 2 groups of write nodes, 2 sets of backup write nodes, and 3 read-only nodes in a seven-node multi-write cluster. That is, although ProxySQL recognizes that all nodes are write nodes, it only routes write operations to the two selected write nodes (through Hostgroup). At the same time, the other two write nodes are added to the standby write node group, and the last three read nodes join the read group. The groups in this paragraph all have the meaning of hostgroup in ProxySQL.

It's also possible to limit the access to a member that is slower in applying the replicated transactions (applying queue reaching a threshold).

In addition, you can restrict connection access to slow nodes in the cluster that exceed the maximum set transaction value.

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 has added native support for MGR since version 1.4.0. If it is not available in the distribution, it can be compiled from 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

Let's take a look at the obvious changes for users. After starting the admin port connection, we will find that there is one more mysql_group_replication_ hosts groups table than before.

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

We will set the hostgroup of the node in this table.

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

To illustrate how ProxySQL supports MGR, I'll use a three-node cluster below.

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

First, we insert the information of the three nodes into the mysql_ servers table as usual.

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:

Before setting the behavior of the MGR node in ProxySQL, look at the DDL of the newly added mysql_group_replication_hostgroups table.

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

Take a look at the meaning of new columns that have not appeared before.

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

Once you are familiar with the definition of the table, the whole topology will look like this:

So let's add this:

Let's write the grouping definition and key parameters of the MGR cluster into the mysql_group_replication_hostgroups table.

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

The newly changed configuration is then saved to disk and loaded into the runtime environment.

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:

At the same time, we need to add the following view to the MGR and its dependent stored procedures.

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

In this way, we can get the basic information of the MGR members by executing the following statement from any node in the MGR cluster, and ProxySQL also monitors the health and backwardness of the nodes according to this method.

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

At the same time, we need to assign the read permission of the sys library to the monitoring MySQL account configured by ProxySQL:

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

Next, let's take a look at how ProxySQL distributes MGR nodes to ProxySQL groups:

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

The write node is assigned to the previously defined write group with ID 2, and all other nodes are assigned to the read-only group with ID 3. (single write mode)

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.

In this way, we do not need to use a timer to invoke a third-party complex defined script to match and assign MGR nodes to the corresponding ProxySQL group.

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

Next, you can configure ProxySQL as before, such as associating users to the default ProxySQL group, or adding query routing rules.

An extra table has also been added for monitoring:

In addition, ProxySQL has one more table to monitor MySQL instances than before, as shown below:

Is the above details about how the new version of ProxySQL in MySQL can support MGR natively? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.