Technology Sharing | ProxySQL with MySQL HA (Part 2)

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

Through the introduction in the previous chapter, we have already understood how ProxySQL builds read-write separation, failover and other functional points based on MySQL master-slave and group replication architecture, but did not cover the working details of ProxySQL-related configuration tables. This chapter provides an extended explanation of what was missed in the previous section.

Let's first understand the list of built-in databases of ProxySQL:

ytt:admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

In the databases listed above, main stands for runtime, that is, the runtime library; disk stands for persistent library; stats stands for statistical database; monitor stands for monitoring database; stats_history stands for statistical database archive.

Several configuration tables for storing MySQL master-slave, group replication, and read-write separation exist in each library, and different libraries represent different operating categories.

First, the backend host metabase table

mysql_servers : This table is the basic table for storing metadata information related to the back-end database. All subsequent operations need to access and update this table.

The main fields are as follows:

hostgroup_id, the host group identifier of the backend MySQL instance. Each instance can be set to the same value or a different value. It is recommended that a group of instances be set to the same value.

gtid_port, the port that the Proxy Binlog Reader component needs to listen to. You can keep the default if you don't use this component.

status , the instance status value.

1. online , the default option, provides services online, that is, the normal state;
2. offline_soft , non-forced offline state, that is, stop accepting new requests after processing the current session;
3. offline_hard , forcibly offline, forcibly close all sessions, and no longer accept new requests;
4. shunned , the state in which the backend instance is temporarily shut down due to too many bad connections or suspends processing new requests due to exceeding a set delay time.

weight, the priority of an instance in a group, the higher the priority, the more likely it will be selected. For example, with multiple slave instances, the priority of a node can be increased to ensure priority in traffic distribution.

compression , whether to compress the connection request. The default is not compressed, it can be set to 1 for compression.

max_connections , the limit on the maximum number of connections through the ProxySQL traffic port.

max_replication_lag, specifies the delay time for the instance state to be set to shunned. After this time, the specified instance status changes from online to shunned until the backlog of requests is processed.

For example, the following runtime level mysql_servers table records: Since these nodes are not running, the status is shunned:

ytt:admin> select hostgroup_id,hostname,port,status,max_connections from runtime_mysql_servers where hostgroup_id in (1,2);
+--------------+-----------+------+---------+-----------------+
| hostgroup_id | hostname  | port | status  | max_connections |
+--------------+-----------+------+---------+-----------------+
| 2            | 127.0.0.1 | 3341 | SHUNNED | 1000            |
| 2            | 127.0.0.1 | 3342 | SHUNNED | 1000            |
| 2            | 127.0.0.1 | 3340 | SHUNNED | 1000            |
+--------------+-----------+------+---------+-----------------+
3 rows in set (0.00 sec)

I start these three master-slave nodes, and the corresponding status is automatically updated to online:

ytt:admin> select hostgroup_id,hostname,port,status,max_connections from runtime_mysql_servers where hostgroup_id in (1,2);
+--------------+-----------+------+--------+-----------------+
| hostgroup_id | hostname  | port | status | max_connections |
+--------------+-----------+------+--------+-----------------+
| 2            | 127.0.0.1 | 3341 | ONLINE | 1000            |
| 1            | 127.0.0.1 | 3341 | ONLINE | 1000            |
| 2            | 127.0.0.1 | 3342 | ONLINE | 1000            |
| 2            | 127.0.0.1 | 3340 | ONLINE | 1000            |
+--------------+-----------+------+--------+-----------------+
4 rows in set (0.00 sec)

Similarly, start the Group Replication instance, and the status of the three nodes is as follows:

ytt:admin> select hostgroup_id,hostname,port,status from runtime_mysql_servers where hostgroup_id > 2;
+--------------+-----------+------+--------+
| hostgroup_id | hostname  | port | status |
+--------------+-----------+------+--------+
| 3            | 127.0.0.1 | 3343 | ONLINE |
| 5            | 127.0.0.1 | 3343 | ONLINE |
| 5            | 127.0.0.1 | 3344 | ONLINE |
| 5            | 127.0.0.1 | 3345 | ONLINE |
+--------------+-----------+------+--------+
4 rows in set (0.00 sec)

Second, the user metadata table

mysql_users : This table stores authorization data for traffic users. There are several main fields:

transaction_persistent , which is used to specify whether the transaction as a whole is shunted. If set to 1, it means that the traffic is distributed to the default host group at the granularity of the transaction; if it is 0, it means that the traffic is divided according to the granularity of the internal SQL of the transaction. Except for read-only transactions, other transactions should be used as a whole to maintain the original transaction logic.

default_hostgroup , the default host group, the SQL without the configured query rules will be distributed to the default host group uniformly.

frontend , the front-end user, for the ProxySQL instance.

backend , the backend user, for the MySQL instance.

These two fields are both 1 by default. Usually, a back-end MySQL instance user is defined, which will be automatically mapped to the front-end ProxySQL instance.

For example, the following master-slave traffic users: automatically map from the mysql_users table to the runtime_mysql_users table, and one user is the front-end and back-end at the same time.

ytt:admin> select username,active,default_hostgroup,frontend,backend from mysql_users where username = 'dev_user';
+----------+--------+-------------------+----------+---------+
| username | active | default_hostgroup | frontend | backend |
+----------+--------+-------------------+----------+---------+
| dev_user | 1      | 1                 | 1        | 1       |
+----------+--------+-------------------+----------+---------+
1 row in set (0.00 sec)

ytt:admin> select username,active,default_hostgroup,frontend,backend from runtime_mysql_users where username = 'dev_user';
+----------+--------+-------------------+----------+---------+
| username | active | default_hostgroup | frontend | backend |
+----------+--------+-------------------+----------+---------+
| dev_user | 1      | 1                 | 0        | 1       |
| dev_user | 1      | 1                 | 1        | 0       |
+----------+--------+-------------------+----------+---------+
2 rows in set (0.00 sec)

Third, the master-slave metadata table

mysql_replication_hostgroups : This table configures host group information for master and slave instances.

ProxySQL distributes front-end requests according to the content of this table, and cooperates with the mysql_servers table to achieve the goal of automatic master-slave failover.

writer_hostgroup, write host group ID. For example, in our example, it is set to 1, which means that the host group ID is 1 to process write requests.

reader_hostgroup , read host group ID. For example, in our example, it is set to 2, which means that the read request with the host group ID of 2 is processed.

check_type , checks the value of a MySQL read-only variable. Choose among the variables read_only, innodb_read_only, super_read_only.

For example, super_read_only needs to be detected. If it is 1, it means reading; if it is 0, it means writing.

ytt:admin> select * from mysql_replication_hostgroups;
+------------------+------------------+-----------------+---------------------------------+
| writer_hostgroup | reader_hostgroup | check_type      | comment                         |
+------------------+------------------+-----------------+---------------------------------+
| 1                | 2                | super_read_only | MySQL Replication failover test |
+------------------+------------------+-----------------+---------------------------------+
1 row in set (0.00 sec)

Fourth, group replication metadata table

mysql_group_replication_hostgroups : This table configures the group replication host group information, and also cooperates with the mysql_servers table to complete the non-aware fault tolerance function of the group replication node, similar to the table mysql_replication_hostgroups.

writer_hostgroup, reader_hostgroup, these two represent write and read traffic groups respectively.

offline_hostgroup , the offline host group, and nodes with abnormal status are put into this group.

max_writers, backup_writer_hostgroup, these two are used in multi-write mode, if the number of write instances exceeds the max_writers setting, they are put into the host group backup_writer_hostgroup.

max_transactions_behind , similar to the master-slave delay traffic deactivation function. Set the number of transactions that a node is behind. After reaching this number, the node state is set to shunned, and after it is completely processed, it changes to the normal state.

The configuration table of the current group replication environment is as follows:

ytt:admin> select writer_hostgroup,backup_writer_hostgroup,reader_hostgroup from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup |
+------------------+-------------------------+------------------+
| 3                | 4                       | 5                |
+------------------+-------------------------+------------------+
1 row in set (0.00 sec)

Fifth, read and write separation metadata table

mysql_query_rules : It is used to configure the read-write separation mode, which is very flexible. You can configure the unified port to match the regular expression or divert traffic according to different ports. (The standard on which the regular expression is based is determined by the parameter mysql-query_processor_regex setting) The main fields are as follows:

active , whether to activate this matching mode.

username , the traffic username.

schemaname , the database name.

match_pattern , the specific matching pattern.

In addition to relying on regular expressions to divert read and write traffic to the same port as described in the previous chapter, you can also set multiple ports to distinguish different instance groups. For example, if the master-slave traffic goes to port 6401, and the group replication traffic goes to 6402, it can be directly adapted like this:

First add the port that ProxySQL will listen on to the variable mysql-interfaces, and then restart the ProxySQL service:

ytt:admin> SET mysql-interfaces='0.0.0.0:6033;0.0.0.0:6401;0.0.0.0:6402';
Query OK, 1 row affected (0.00 sec)

ytt:admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 140 rows affected (0.02 sec)

Then insert these two ports into this table:

ytt:admin> INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
    -> VALUES (1,1,6401,1,1), (2,1,6402,3,1);
Query OK, 2 rows affected (0.00 sec)

ytt:admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

ytt:admin> SAVE MYSQL QUERY RULES TO DISK; 
Query OK, 0 rows affected (0.08 sec)

The content of this table is as follows: In addition to the regular diversion, there are two additional records to divert traffic according to the specified port (6401 is the master-slave diversion port, 6402 is the group replication diversion port).

ytt:admin> select rule_id,active,username,schemaname,match_pattern,destination_hostgroup,proxy_port from runtime_mysql_query_rules;
+---------+--------+--------------+------------+---------------+-----------------------+------------+
| rule_id | active | username     | schemaname | match_pattern | destination_hostgroup | proxy_port |
+---------+--------+--------------+------------+---------------+-----------------------+------------+
| 1       | 1      | NULL         | NULL       | NULL          | 1                     | 6401       |
| 2       | 1      | NULL         | NULL       | NULL          | 3                     | 6402       |
| 13      | 1      | dev_user     | ytt        | ^select       | 2                     | NULL       |
| 14      | 1      | dev_user_mgr | ytt        | ^select       | 4                     | NULL       |
+---------+--------+--------------+------------+---------------+-----------------------+------------+
4 rows in set (0.00 sec)

To verify this shunt strategy: use user dev_user to connect to port 6401 and user dev_user_mgr to connect to port 6402.

root@ytt-ubuntu:~# mysql -udev_user -pdev_user -P6401 -h ytt-ubuntu -e "use ytt;select count(*) from t1";
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        5 |
+----------+
root@ytt-ubuntu:~# mysql -udev_user_mgr -pdev_user_mgr -P6402 -h ytt-ubuntu -e "use ytt;select count(*) from t1";
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        1 |
+----------+

Enter the ProxySQL management terminal and view the audit table: The above different users and different ports are distributed to specific host groups.

ytt:admin> select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest where schemaname = 'ytt';
+-----------+------------+--------------+-------------------------+------------+
| hostgroup | schemaname | username     | digest_text             | count_star |
+-----------+------------+--------------+-------------------------+------------+
| 3         | ytt        | dev_user_mgr | select count(*) from t1 | 1          |
| 1         | ytt        | dev_user     | select count(*) from t1 | 1          |
+-----------+------------+--------------+-------------------------+------------+
2 rows in set (0.00 sec)

At this point, for ProxySQL, I believe I already have a certain understanding of how to match it with MySQL HA.