关于mysql:技术分享-ProxySQL-搭配-MySQL-HA-下

作者：杨涛涛

资深数据库专家，专研 MySQL 十余年。善于 MySQL、PostgreSQL、MongoDB 等开源数据库相干的备份复原、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相干技术支持、MySQL 相干课程培训等工作。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

通过上一章节的介绍，咱们曾经理解 ProxySQL 如何基于 MySQL 主从以及组复制架构来构建读写拆散、故障转移等性能点，但没有涵盖 ProxySQL 相干配置表的工作细节。那本章就对上节脱漏的内容进行一个延长解说。

先来理解下 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)

以上所列数据库中，main 代表 runtime，也即运行时库；disk 代表长久化库；stats 代表统计数据库；monitor 代表监控数据库；stats_history 代表统计数据库归档。

对于贮存 MySQL 主从、组复制、读写拆散的几张配置表，在每个库里都存在，不同的库代表不同的运行领域。

第一，后端主机元数据库表

mysql_servers：该表为存储后端数据库相干元数据信息的根底表，所有的后续操作都须要拜访并且更新这张表。

其中次要几个字段如下：

hostgroup_id，后端 MySQL 实例的主机组标记，每个实例能够设置为一样的数值或者设置为不雷同的数值，举荐一组实例设置为雷同。

gtid_port，Proxy Binlog Reader 组件须要监听的端口。不应用此组件能够放弃默认即可。

status，实例状态值。

online，默认选项，在线提供服务，也即失常状态；
offline_soft，非强制下线状态，也即解决完以后会话后进行承受新申请；
offline_hard，强制下线，强制敞开目所有会话，并且不再承受新的申请；
shunned，后端实例因为太多谬误连贯而临时敞开的状态或者说因为超过设定的延迟时间而暂停解决新申请。

weight，一个组里的实例优先级，优先级越高的越有几率被选中。比方多个从实例，能够晋升一个节点的优先级来保障流量调配优先。

compression，是否压缩连贯申请。默认不压缩，能够设置为 1 示意压缩。

max_connections，通过 ProxySQL 流量端口的最大连接数限度。

max_replication_lag，指定实例状态被设置为 shunned 的延迟时间。超过这个工夫后，指定实例状态由 online 变为 shunned，直到积压的申请解决实现。

比方上面 runtime 级别的 mysql_servers 表记录：因为这几个节点都没有运行，状态都为 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)

我启动这三个主从节点，对应状态自动更新为 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)

同样，启动组复制实例，三个节点的状态如下：

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)

第二，用户元数据表

mysql_users: 此表存储流量用户的受权数据。有几个次要字段：

transaction_persistent，用来指定事务整体是否被分流。设置为 1 则代表以事务为粒度分流到到默认主机组；为 0 则代表依照事务外部 SQL 为粒度来分流。除了只读事务，其余事务都应该作为一个整体，放弃原有事务逻辑。

default_hostgroup，默认主机组，没有配置查问规定的 SQL 对立分流到默认主机组。

frontend , 前端用户，针对 ProxySQL 实例。

backend , 后端用户，针对 MySQL 实例。

这两个字段默认都为 1，通常定义一个后端 MySQL 实例用户，会主动映射到前端 ProxySQL 实例。

比方上面主从流量用户：从 mysql_users 表主动映射到 runtime_mysql_users 表，一个用户同时为前后端。

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)

第三，主从元数据表

mysql_replication_hostgroups：此表配置主从实例主机组信息。

ProxySQL 依据这张表的内容来分流前端申请，并且配合 mysql_servers 表来达成主从主动故障转移指标。

writer_hostgroup，写主机组 ID。比方咱们的例子里设置为 1，示意主机组 ID 为 1 的解决写申请。

reader_hostgroup，读主机组 ID。比方咱们的例子里设置为 2，示意主机组 ID 为 2 的解决读申请。

check_type，查看 MySQL 只读变量的值。在 read_only , innodb_read_only , super_read_only 这几个变量里选。

比方须要检测 super_read_only，如果为 1，代表读；为 0，则为写。

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)

第四，组复制元数据表

mysql_group_replication_hostgroups：此表配置组复制主机组信息，同样配合 mysql_servers 表来实现组复制节点无感知容错性能，相似表 mysql_replication_hostgroups。

writer_hostgroup，reader_hostgroup，这两个别离代表写和读流量组。

offline_hostgroup，下线主机组，状态不失常的节点被放入这个组。

max_writers，backup_writer_hostgroup，这两个用于多写模式，如果写实例数量多过 max_writers 设置，则被放入主机组 backup_writer_hostgroup。

max_transactions_behind，相似主从提早流量停用性能。设置一个节点落后的事务数量，达到这个数量后，节点状态被设置为 shunned，被齐全解决完后，再变更为失常状态。

目前组复制环境的配置表如下：

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)

第五，读写拆散元数据表

mysql_query_rules：用来配置读写拆散模式，非常灵活，能够配置对立端口匹配正则表达式或者依据不同端口来分流。（正则表达式根据的规范由参数 mysql-query_processor_regex 设置决定）几个次要的字段如下：

active，是否激活这个匹配模式。

username，流量用户名。

schemaname，数据库名。

match_pattern，具体的匹配模式。

除了上一章节介绍的依赖正则表达式来分流读写流量到同一端口外，还能够设置多个端口来辨别不同的实例组。比方主从流量走端口 6401，组复制流量走 6402，那么能够间接这样适配：

先把 ProxySQL 要监听的端口增加到变量 mysql-interfaces 里，完了重启 ProxySQL 服务：

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)

再把这两个端口插入到这张表：

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)

这张体现在内容如下：除了依照正则分流外，额定有两条记录来依照指定端口（6401 为主从分流端口，6402 为组复制分流端口）分流。

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)

来验证下这个分流策略: 别离应用用户 dev_user 连贯端口 6401，用户 dev_user_mgr 连贯端口 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 |
+----------+

进入 ProxySQL 治理端，查看审计表：以上不同用户、不同端口分流到具体的主机组里。

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)

到这里，对于 ProxySQL 来讲，如何与 MySQL HA 进行搭配，置信曾经有了肯定的理解。