ProxySQL 入门

ProxySQL是用C++语言开发的，一个轻量级开源软件，性能和功能满足读写中间件所需的绝大多数功能，其配置数据基于SQLite存储，目前已到v2.0.13版本。

功能方面如下：

最基本的读/写分离，且方式有多种。
可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding。
不支持分表，可以分库，但利用规则配置实现分表。
可缓存查询结果。基本ProxySQL的缓存策略实现了基本的缓存功能，绝大多数时候够用。
1.支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。
2.支持query cache。
3.支持对query的路由，可以针对某个语句进行分配执行实例。
监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性(replication lag)。


特性方面：

连接池，而且是 multiplexing；
主机和用户的最大连接数限制；
自动下线后端DB；
- 延迟超过阀值
- ping 延迟超过阀值
- 网络不通或宕机
强大的规则路由引擎；
- 实现读写分离
- 查询重写
- sql流量镜像
支持prepared statement；
支持Query Cache；
支持负载均衡，与gelera结合自动failover；
将所有配置保存写入到SQLit表中。
支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。
支持query cache。
支持对query的路由。
不支持分表，可以分库，但是利用规则配置实现分表。


ProxySQL：
官方站点： https://www.proxysql.com/
官方github： https://github.com/sysown/proxysql/wiki
percona ProxySQL手册：https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html

ProxySQL 是一个使用非常广泛并且较稳定的中间件，有很多功能点。 比如查询缓存，查询重写，读写分离，数据分片等等。

要介绍的是 ProxySQL 和 MySQL Replication 以及 MySQL MGR 的初步结合，初步读写分离以及 failover 功能的体验。

在本机安装一个 ProxySQL 实例，六个 MySQL 实例；ProxySQL 和 MySQL 版本均是最新版。

ProxySQL: 管理端口6032，流量端口6033。

MySQL Replication：流量端口分别为：3340、3341、3342

MySQL MGR： 流量端口分别为：3343、3344、3345

第一，ProxySQL 以及六个 MySQL 部署。

ProxySQL 安装比较简单，官网 apt/yum ，或者自己下载安装。装好六个 MySQL 实例，并且配置好 MySQL 主从以及组复制环境。
proxysql服务器上安装mysql客户端，用于在本机连接到ProxySQL的管理接口

1.tar.gz安装

[root@ss30 proxysql-2.0.10]# tar -xvf proxysql-2.0.10.tar.gz.gz
[root@ss30 proxysql-2.0.10]# cd proxysql-2.0.10/
[root@ss30 proxysql-2.0.10]# make -j 4
[root@ss30 proxysql-2.0.10]# make install DESTDIR=/opt/idc/proxysql-2.0.10
make[1]: Leaving directory `/opt/idc/softwares/proxysql-2.0.10/src'
[root@ss30 proxysql-2.0.10]# make install DESTDIR=/opt/idc/proxysql-2.0.10
install -m 0755 src/proxysql /usr/bin
install -m 0600 etc/proxysql.cnf /etc
if [ ! -d /var/lib/proxysql ]; then mkdir /var/lib/proxysql ; fi
Creating proxysql user and group
useradd -r -U -s /bin/false proxysql
install -m 0644 systemd/system/proxysql.service /usr/lib/systemd/system/
systemctl enable proxysql.service
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /usr/lib/syst


卸载：make uninstall

2.rpm包安装

rpm解压即可，会在/usr/local/proxysql 生成文件

[root@ss30 softwares]# rpm -ivh proxysql-2.0.10-1-centos7.x86_64.rpm
Preparing… ################################# [100%]
Updating / installing…
1:proxysql-2.0.10-1 warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.

3.启动服务

[root@ss30 softwares]# systemctl start proxysql
[root@ss30 softwares]# ps -ef | grep proxy
proxysql 3964 1 0 21:01 ? 00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf
proxysql 3965 3964 7 21:01 ? 00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf
root 3989 77420 0 21:01 pts/2 00:00:00 grep --color=auto proxy

服务命令：systemctl start | stop | restart | statu proxysql

4.配置信息

ProxySQL很少停止或重启，因为绝大多数配置都可以在线修改。
服务启动配置文件

[root@ss30 proxysql-2.0.10]# vim /etc/systemd/system/proxysql.service
[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target

[Service]
Type=forking
RuntimeDirectory=proxysql
#PermissionsStartOnly=true
#ExecStartPre=/usr/bin/mkdir -p /var/run/proxysql /var/run/proxysql
#ExecStartPre=/usr/bin/chown -R proxysql: /var/run/proxysql/
ExecStart=/usr/bin/proxysql -c /etc/proxysql.cnf
#PIDFile=/var/lib/proxysql/proxysql.pid
#StandardError=null # all output is in stderr
SyslogIdentifier=proxysql
Restart=no
User=proxysql
Group=proxysql
PermissionsStartOnly=true
UMask=0007
LimitNOFILE=102400
LimitCORE=1073741824
ProtectHome=yes
NoNewPrivileges=true
CapabilityBoundingSet=CAP_SETGID CAP_SETUID CAP_SYS_RESOURCE
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX AF_ALG
ProtectSystem=full
PrivateDevices=yes

[Install]
WantedBy=multi-user.target

5.参数配置文件

[root@ss30 softwares]# vim /etc/proxysql.cnf
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
}
......


Qurey Processor 用于匹配查询规则并根据规则决定是否缓存查询或者将查询加入黑名单或者重新路由、重写查询或者镜像查询到其他hostgroup。
User Auth 为底层后端数据库认证提供了用户凭证。
Hostgroup manager – 负责管理发送SQL请求都后端数据库并跟踪SQL请求状态。
Connection pool – 负责管理后端数据库连接，连接池中建立的连接被所有的前端应用程序共享。
Monitoring – 负责监控后端数据库健康状态主从复制延时并临时下线不正常的数据库实例。


1.启动过程

RUNTIME层
代表的是ProxySQL当前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来

DISK|CONFIG FILR层
持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口，还是需要在管理命令行里修改，再 save 到硬盘。


注意：
如果找到数据库文件（proxysql.db），ProxySQL 将从 proxysql.db 初始化其内存中配置。因此，磁盘被加载到 MEMORY 中，然后加载到 RUNTIME 中。
如果找不到数据库文件（proxysql.db）且存在配置文件（proxysql.cfg），则解析配置文件并将其内容加载到内存数据库中，然后将其保存在 proxysql.db 中并在加载到 RUNTIME。
请务必注意，如果找到 proxysql.db，则不会解析配置文件。也就是说，在正常启动期间，ProxySQL 仅从持久存储的磁盘数据库初始化其内存配置。

2.数据库结构

ProxySQL自身共有5个 库，分别为3个保存在内存中的库，和三个保存在磁盘的SQLite库。
通过6032管理端口登入后，默认就是main库，所有的配置更改都必须在这个库中进行，disk存档库不会直接受到影响。接下来看下

[root@ss30 proxysql-2.0.10]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
mysql> 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：内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
disk：是持久化到硬盘的配置，sqlite数据文件。SQLite3 数据库，默认位置为 $（DATADIR）/proxysql.db,在重新启动时，未保留的内存中配置将丢失。因此，将配置保留在 DISK 中非常重要。(SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎)
stats：proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
stats_history:统计信息历史库


3.核心配置表

image.png
image.png
image.png
image.png

4.命令

image.png

第二，ProxySQL 记录 MySQL 实例相关信息。

进入 ProxySQL 管理端，把以上六个 MySQL 实例信息依次插入到表 mysql_servers ：主从实例的 hostgroup_id 统一设置为1， 为了不破坏后续 failover 相关 hostgroup_id 的连续性，组复制实例的 hostgroup_id 统一设置为3。

Admin> select hostgroup_id, hostname,port,status from mysql_servers;
+--------------+-----------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------+------+--------+
| 1 | 127.0.0.1 | 3340 | ONLINE |
| 1 | 127.0.0.1 | 3341 | ONLINE |
| 1 | 127.0.0.1 | 3342 | ONLINE |
| 3 | 127.0.0.1 | 3343 | ONLINE |
| 3 | 127.0.0.1 | 3344 | ONLINE |
| 3 | 127.0.0.1 | 3345 | ONLINE |
+--------------+-----------+------+--------+
6 rows in set (0.00 sec)


第三，MySQL 端创建 ProxySQL 所需的两类用户

ProxySQL 对 MySQL 来讲，有需要两类用户，这两类用户需要同时在主从和组复制环境创建。

1， 监控用户：

为了免去后面重复设置监控用户变量的工作，两种架构用户名和密码保持一致，用户名和密码都是 proxysql_monitor 。proxysql_monitor 需要 以下权限：
client,session_variables_admin,system_variables_admin,select

在 MySQL 主从以及组复制环境里分别执行下面SQL：

MySQL localhost:3343 ssl SQL > create user proxysql_monitor@'127.0.0.1' identified by 'proxysql_monitor';
Query OK, 0 rows affected (0.0596 sec)
MySQL localhost:3343 ssl SQL > grant replication client,session_variables_admin,system_variables_admin,select on *.* to proxysql_monitor@'127.0.0.1';
Query OK, 0 rows affected (0.0103 sec)


进入 ProxySQL 管理端，设置监控用户：

Admin> set mysql-monitor_username='proxysql_monitor';
Query OK, 1 row affected (0.00 sec)

Admin> set mysql-monitor_password='proxysql_monitor';
Query OK, 1 row affected (0.00 sec)


2， 开发用户：对于主从和组复制环境，分别创建此类用户。

主从环境用户创建：用户名 dev_user

MySQL localhost:3343 ssl SQL > create user dev_user@'127.0.0.1' identified by 'dev_user';
Query OK, 0 rows affected (0.1221 sec)
MySQL localhost:3343 ssl SQL > grant insert,delete,update,select,create on ytt.* to dev_user@'127.0.0.1';
Query OK, 0 rows affected (0.0359 sec)


组复制环境用户创建：用户名 dev_user_mgr

MySQL localhost:3343 ssl SQL > create user dev_user_mgr@'127.0.0.1' identified by 'dev_user_mgr';
Query OK, 0 rows affected (0.1221 sec)
MySQL localhost:3343 ssl SQL > grant insert,delete,update,select,create on ytt.* to dev_user_mgr@'127.0.0.1';
Query OK, 0 rows affected (0.0359 sec)


进入 ProxySQL 管理端，分别插入主从以及组复制对应的开发用户到表 mysql_users 。字段 transaction_persistent 为1代表事务不拆分，统一去主库检索。

Admin> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)
values
('dev_user','dev_user',1,1,1),
('dev_user_mgr','dev_user_mgr',1,3,1);
Query OK, 1 row affected (0.00 sec)

Admin> select username,active,default_hostgroup from mysql_users;
+--------------+--------+-------------------+
| username | active | default_hostgroup |
+--------------+--------+-------------------+
| dev_user | 1 | 1 |
| dev_user_mgr | 1 | 3 |
+--------------+--------+-------------------+
2 rows in set (0.00 sec)


第四，配置读写分离

进入 ProxySQL 管理端，插入之前创建的两个开发用户到表 mysql_query_rules ，定义最基本的读写分离策略，只要是 select 开头的语句都分流到从库。

Admin> INSERT INTO mysql_query_rules(username,schemaname,active,match_pattern,destination_hostgroup,apply) VALUES
('dev_user','ytt',1,'^select',2,1),
('dev_user_mgr','ytt',1,'^select',4,1);
Query OK, 2 rows affected (0.00 sec)

Admin> select username,schemaname,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;
+--------------+------------+--------+---------------+-----------------------+-------+
| username | schemaname | active | match_pattern | destination_hostgroup | apply |
+--------------+------------+--------+---------------+-----------------------+-------+
| dev_user | ytt | 1 | ^select | 2 | 1 |
| dev_user_mgr | ytt | 1 | ^select | 4 | 1 |
+--------------+------------+--------+---------------+-----------------------+-------+
2 rows in set (0.00 sec)


设置好相关信息后把以上所有更改加载到内存，并且持久化到磁盘。

Admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

Admin> save mysql query rules to disk;
Query OK, 0 rows affected (0.11 sec)


分别测试下主从和组复制两种架构的读写分离效果：开发用户dev_user/dev_user_mgr连接端口6033，创建一张表t1，插入一条记录，并且简单查询一次。

– 主从环境：

root@ytt-ubuntu:/home/ytt/scripts# mysql -u dev_user -p -h 127.0.0.1 -P 6033 -e "
> use ytt;
> create table t1 (id int primary key,str1 varchar(100));
> insert t1 values (1,'replication');
> select * from t1;
> ";


Enter password:
– 组复制环境把用户dev_user和密码替换为dev_user_mgr和对应密码重复执行一次。

进入 ProxySQL 管理端，检索审计表 stats_mysql_query_digest ：写入请求和读取请求根据不同的用户被成功分发到 mysql_query_rules 表里对应的 hostgroup 上。

Admin> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest where schemaname = 'ytt';
+-----------+--------------+------------------------------------------------------+------------+
| hostgroup | username | digest_text | count_star |
+-----------+--------------+------------------------------------------------------+------------+
| 4 | dev_user_mgr | select * from t1 | 1 |
| 3 | dev_user_mgr | insert t1 values (?,?) | 1 |
| 3 | dev_user_mgr | create table t1 (id int primary key,str1 varchar(?)) | 1 |
| 2 | dev_user | select * from t1 | 1 |
| 1 | dev_user | insert t1 values (?,?) | 1 |
| 1 | dev_user | create table t1 (id int primary key,str1 varchar(?)) | 1 |
+-----------+--------------+------------------------------------------------------+------------+
6 rows in set (0.00 sec)


第五，配置主从自动 failover 功能：

进入 RroxySQL 管理端，把主从相关实例信息插入到表 mysql_replication_hostgroups 即可。

ProxySQL 通过实时监控 MySQL 系统变量（’read_only’,’innodb_read_only’,’super_read_only’ ）开关与否来探测对应的 MySQL 实例是主库还是从库，完了自动更新 mysql_server 表主库对应的IP和端口来达到 failover 的目的。

Admin> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment)values(1,2,'super_read_only','MySQL Replication fa
ailover test');
Query OK, 1 row affected (0.00 sec)

Admin> save mysql servers to disk;
Query OK, 0 rows affected (0.18 sec)


我用 MySQL Shell 操作副本集来手动进行主从切换，设置主库为端口3342。

MySQL localhost:3340 ssl Py > rs = dba.get_replica_set()
You are connected to a member of replicaset ‘rs1’.

MySQL localhost:3340 ssl Py > rs.set_primary_instance(‘root@localhost:3342’)
127.0.0.1:3342 will be promoted to PRIMARY of ‘rs1’.
The current PRIMARY is 127.0.0.1:3340.
…

127.0.0.1:3342 was promoted to PRIMARY.

查看 ProxySQL 日志，已经感知到主从切换，新的主库自动变为127.0.0.1:3342

2021-12-15 16:02:08 [INFO] Regenerating read_only_set1 with 1 servers
2021-12-15 16:02:08 [INFO] read_only_action() detected RO=0 on server 127.0.0.1:3342 for the first time after commit(), but no need to reconfigure

也可以进入 ProxySQL 管理端来查询表 mysql_servers 的字段 hostgroup_id=1 的匹配记录是否已经变更为新主库：

Admin> select hostname,port from mysql_servers where hostgroup_id = 1;
+-----------+------+
| hostname | port |
+-----------+------+
| 127.0.0.1 | 3342 |
+-----------+------+
1 row in set (0.00 sec)


第六，配置组复制自动 failover 功能：

和主从配置类似，把组复制实例相关信息插入到表 mysql_replication_hostgroups 即可。

这里和主从有点不一样的地方：writer_hostgroup,backup_writer_hostgroup, reader_hostgroup, offline_hostgroup 这四个字段代表不同职责的 Hostgroup ，最好是设置不一样。

Admin> 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 (3,4,5,6,1,1,1,1000);
Query OK, 1 row affected (0.00 sec)

Admin> save mysql servers to disk;
Query OK, 0 rows affected (0.19 sec)


进入 ProxySQL 管理端，查询组复制日志表 mysql_server_group_replication_log ，可以看到当前的组复制对应的实例数据，其中主库为：127.0.0.1:3343。

Admin> select hostname, port,viable_candidate,read_only, transactions_behind, error from mysql_server_group_replication_log where port in (3343,3344,3345) order by time_start_us desc limit 3;
+-----------+------+------------------+-----------+---------------------+-------+
| hostname | port | viable_candidate | read_only | transactions_behind | error |
+-----------+------+------------------+-----------+---------------------+-------+
| 127.0.0.1 | 3345 | YES | YES | 0 | NULL |
| 127.0.0.1 | 3344 | YES | YES | 0 | NULL |
| 127.0.0.1 | 3343 | YES | NO | 0 | NULL |
+-----------+------+------------------+-----------+---------------------+-------+
3 rows in set (0.00 sec)


同样用 MySQL Shell 来验证下组复制的主备角色切换后 ProxySQL 是否会自动感知： 把实例 127.0.0.1：3344 提升为主库。

MySQL localhost:3343 ssl sys Py > rc = dba.get_cluster();
MySQL localhost:3343 ssl sys Py > rc.set_primary_instance(‘root@localhost:3344’);
Setting instance ‘localhost:3344’ as the primary instance of cluster ‘ytt_mgr’…

Instance ‘127.0.0.1:3343’ was switched from PRIMARY to SECONDARY.
Instance ‘127.0.0.1:3344’ was switched from SECONDARY to PRIMARY.
Instance ‘127.0.0.1:3345’ remains SECONDARY.

WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.get_cluster().

The instance ‘localhost:3344’ was successfully elected as primary.
MySQL localhost:3343 ssl sys Py >

进入 ProxySQL 管理端，查询组复制日志：127.0.0.1：3344 自动变更为主库。

Admin> select hostname, port,viable_candidate,read_only, transactions_behind, error from mysql_server_group_replication_log where port in (3343,3344,3345) order by time_start_us desc limit 3;
+-----------+------+------------------+-----------+---------------------+-------+
| hostname | port | viable_candidate | read_only | transactions_behind | error |
+-----------+------+------------------+-----------+---------------------+-------+
| 127.0.0.1 | 3345 | YES | YES | 0 | NULL |
| 127.0.0.1 | 3344 | YES | NO | 0 | NULL |
| 127.0.0.1 | 3343 | YES | YES | 0 | NULL |
+-----------+------+------------------+-----------+---------------------+-------+
3 rows in set (0.00 sec)


或者查询mysql_server表，查找hostgroup_id 为3的记录是否变更为新主库。

Admin> select port from mysql_servers where hostgroup_id = 3;
+------+
| port |
+------+
| 3344 |
+------+
1 row in set (0.00 sec)


先来了解下 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> 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 进行搭配，相信已经有了一定的了解。