MySQL ProxySQL介绍

原创 Hulong Cui 2020-08-10

10595

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

下面基于2.0.10版本所有操作。

安装部署

安装地址：https://github.com/sysown/proxysql/releases
官方说明：https://github.com/sysown/proxysql/wiki
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/systemd/system/proxysql.service.

卸载：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
}
......

ProxySQL结构

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进来
MEMORY层
是平时在mysql命令行修改的 main 里头配置，可以认为是SQLite数据库在内存的镜像。该层级的配置在main库中以mysql_开头的表以及global_variables表，这些表的数据可以直接修改；
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.核心配置表

4.命令

5.小结
这些数据库的功能实现了实用化内容：

允许轻松动态更新配置，便于运维管理，与MySQL兼容的管理界面可用于此目的。
允许尽可能多的配置项目动态修改，而不需要重新启动ProxySQL进程
可以毫不费力地回滚无效配置
通过多级配置系统实现的，其中设置从运行时移到内存，并根据需要持久保存到磁盘

ProxySQL读写分离配置

1.MySQL里创建账号
数据库段创建访问用户，监控用户

[root@ss30 ~]# mysql -uroot -p123456 -h127.0.0.1 -P3410
mysql>  GRANT ALL PRIVILEGES ON *.* TO 'dbadmin'@'%' identified by '123456'  WITH GRANT OPTION;

mysql> create user monitor@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication client on *.* to monitor@'%';
Query OK, 0 rows affected (0.01 sec)

2.添加MySQL节点
hostgroup_id, hostname, port 组成一个主键

[root@ss30 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL Admin Module)
mysql> use main
Database changed
mysql> insert into mysql_servers(hostgroup_id,hostname,port)  values(10,'192.168.244.130',3410);
mysql> insert into mysql_servers(hostgroup_id,hostname,port)  values(10,'192.168.244.130',3400);

mysql> select * from mysql_servers\G;
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 192.168.244.130
               port: 3410
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
1 row in set (0.00 sec)
##加载到RUNTIME，并保存到disk
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

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

3.用户配置
配置mysql_users表，将用户添加到该表中。

ysql> insert into mysql_users(username,password,default_hostgroup) values('dbadmin','123456',10);

mysql> load mysql users to runtime

mysql> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql_users\G;
*************************** 1. row ***************************
              username: dbadmin
              password: 123456
                active: 1     #active=1表示用户生效，0表示不生效
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1   # 如果设置为1，连接上ProxySQL的会话后，如果在一个
hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不论是否会匹配上其它路由规则，直到事务结束。虽然默认是0
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000  #该用户允许的最大连接数
               comment: 
1 row in set (0.00 sec)

注意两个字段:
只有active=1的用户才是有效的用户。
transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组。建议在创建完用户之后设置为1，避免发生脏读、幻读等现象.

mysql_users表有不少字段，最主要的三个字段为username、password和default_hostgroup：

username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行
select password(PASSWORD)，然后将加密结果复制到该字段。
default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认
情况下将路由到hostgroup_id=10组中的某个节点。

4.监控后端MySQL节点
添加Mysql节点之后，还需要监控这些后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整,它们是属于读组还是写组。

[root@ss30 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql>  set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql>  set mysql-monitor_password='123456';
Query OK, 1 row affected (0.00 sec)
mysql>  load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql>  save mysql variables to disk;
Query OK, 97 rows affected (0.01 sec)

加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组。
例如，此处所有节点都在id=10的写组，slave1和slave2都是slave，它们的read_only=1，这两个节点将会移动到id=20的组。如果一开始这3节点都在id=20的读组，那么移动的将是Master节点，会移动到id=10的写组。

mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+-----------------+------+--------+--------+
| hostgroup_id | hostname        | port | status | weight |
+--------------+-----------------+------+--------+--------+
| 10           | 192.168.244.130 | 3410 | ONLINE | 2      |
| 10           | 192.168.244.130 | 3400 | ONLINE | 1      |
+--------------+-----------------+------+--------+--------+
mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,check_type) values(10,20,'read_only');
Query OK, 1 row affected (0.00 sec)

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

mysql> 
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.04 sec)
mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+-----------------+------+--------+--------+
| hostgroup_id | hostname        | port | status | weight |
+--------------+-----------------+------+--------+--------+
| 10           | 192.168.244.130 | 3410 | ONLINE | 2      |
| 20           | 192.168.244.130 | 3400 | ONLINE | 1      |
+--------------+-----------------+------+--------+--------+

5.配置路由规则
ProxySQL的路由规则非常灵活，可以基于用户、基于schema以及基于每个语句实现路由规则的定制。

mysql> insert into mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply) 
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)

mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

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

验证

[root@ss30 ~]# mysql -udbadmin -p123456 -P6033 -h127.0.0.1 -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|   3400 |
+--------+
[root@ss30 ~]# 
[root@ss30 ~]# 
[root@ss30 ~]# 
[root@ss30 ~]# mysql -udbadmin -p123456 -h127.0.0.1 -P6033 -e "start transaction;select @@port;commit;select @@port;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|   3410 |
+--------+
+--------+
| @@port |
+--------+
|   3400 |
+--------+

6.信息查询
#read_only日志此时也为空(正常来说，新环境配置时，这个只读日志是为空的)

MySQL [(none)]> select * from mysql_server_read_only_log;

#replication_lag的监控日志为空

MySQL [(none)]> select * from mysql_server_replication_lag_log;

#注意：可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常。

MySQL [(none)]> select * from mysql_server_connect_log;

#以下是对心跳信息的监控(对ping指标的监控)

MySQL [(none)]> select * from mysql_server_ping_log;

查看路由的信息，可查询stats库中的stats_mysql_query_digest表。

mysql> select* from stats_mysql_query_digest;
mysql>SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

开启ProxySQL的Web统计功能
查看路由的信息，可查询stats库中的stats_mysql_query_digest表。

mysql> update global_variables set variable_value='true' where variable_name='admin-web_enabled';
Query OK, 1 row affected (0.01 sec)

mysql> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.03 sec)

mysql> SAVE ADMIN VARIABLES TO DISK;
Query OK, 35 rows affected (0.01 sec)

查看端口和登录web界面的用户名和密码，用户名和密码与stat账户一致：

mysql> select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%';
+----------------------------------------+----------------+
| variable_name                          | variable_value |
+----------------------------------------+----------------+
| admin-stats_credentials                | stats:stats    |
| admin-stats_mysql_connections          | 60             |
| admin-stats_mysql_connection_pool      | 60             |
| admin-stats_mysql_query_cache          | 60             |
| admin-stats_mysql_query_digest_to_disk | 0              |
| admin-stats_system_cpu                 | 60             |
| admin-stats_system_memory              | 60             |
| admin-web_enabled                      | true           |
| admin-web_port                         | 6080           |
+----------------------------------------+----------------+

查看web端口是否正常打开

[root@ss30 ~]# lsof -i:6080
COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
proxysql 1295 proxysql   29u  IPv4 470544      0t0  TCP *:6080 (LISTEN)

访问http://192.168.244.130:6080并使用stats:stats登录即可查看一些统计信息。

scheduler打印状态到日志
编辑脚本和目录

[root@ss30 opt]# mkdir -p /opt/proxysql/log
[root@ss30 opt]# vim /opt/proxysql/log/status.sh
#!/bin/bash
DATE=`date "+%Y-%m-%d %H:%M:%S"`
echo "{\"dateTime\":\"$DATE\",\"status\":\"running\"}" >> /opt/proxysql/log/status_log

[root@ss30 opt]# chmod 777 /opt/proxysql/log/status.sh

输入scheduler信息

mysql> insert into scheduler(active,interval_ms,filename) values (1,60000,'/opt/proxysql/log/status.sh');
Query OK, 1 row affected (0.00 sec)

mysql> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.01 sec)

mysql>  select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%';
+----------------------------------------+----------------+
| variable_name                          | variable_value |
+----------------------------------------+----------------+
| admin-stats_credentials                | stats:stats    |
| admin-stats_mysql_connections          | 60             |
| admin-stats_mysql_connection_pool      | 60             |
| admin-stats_mysql_query_cache          | 60             |
| admin-stats_mysql_query_digest_to_disk | 0              |
| admin-stats_system_cpu                 | 60             |
| admin-stats_system_memory              | 60             |
| admin-web_enabled                      | true           |
| admin-web_port                         | 6080           |
+----------------------------------------+----------------+
9 rows in set (0.01 sec)

查看日志：

[root@ss30 log]# tail -f status_log
{"dateTime":"2020-04-05 00:07:40","status":"running"}
{"dateTime":"2020-04-05 00:08:41","status":"running"}
{"dateTime":"2020-04-05 00:10:52","status":"running"}

其他
自动回避复制延迟较大的节点
如果服务器将 max_replication_lag 设置为非零值，则 Monitor 模块会定期检查复制延迟。
下图中，当172.16.0.3的复制延迟超过了30秒会自动回避，设置max_replication_lag = 0，代表不检查复制延迟。

mysql> select hostgroup_id,hostname,port,max_replication_lag from mysql_servers;
+--------------+-----------------+------+---------------------+
| hostgroup_id | hostname        | port | max_replication_lag |
+--------------+-----------------+------+---------------------+
| 20           | 192.168.244.130 | 3400 | 0                   |
| 10           | 192.168.244.130 | 3410 | 0                   |
+--------------+-----------------+------+---------------------+
2 rows in set (0.00 sec)

mysql> update  mysql_servers set max_replication_lag=30 where hostgroup_id=10;
Query OK, 1 row affected (0.00 sec)

mysql> select hostgroup_id,hostname,port,max_replication_lag from mysql_servers;
+--------------+-----------------+------+---------------------+
| hostgroup_id | hostname        | port | max_replication_lag |
+--------------+-----------------+------+---------------------+
| 20           | 192.168.244.130 | 3400 | 0                   |
| 10           | 192.168.244.130 | 3410 | 30                  |
+--------------+-----------------+------+---------------------+
2 rows in set (0.00 sec)

注意：
1.max_replication_lag主要来源Seconds_Behind_Master，该参数判断延迟准确性不高，顾个人建议为参考功能。
2.max_replication_lag 仅适用于从节点。如果服务器未启用复制，则 Monitor 不会执行任何操作。

#强制关闭与假死主库的连接，避免数据被写入的假死的老主库。Admin> update runtime_mysql_servers set status=“HARD_OFFLINE” where hostname=‘192.168.20.31’ and port=‘3306’

#将假死的旧主库提出集群，避免后续数据被写入到老主库Admin> delete from mysql_servers where hostname=‘192.168.20.31’ and port='3306’Admin> load mysql servers to runtimeAdmin> save mysql serbers to disk

MGR配置

1.MySQL 配置
创建proxysql 监控mysql用户monitor 并赋予权限
sys库的读权限赋给ProxySQL配置的监控MySQL的账户

[root@ens8 ~]# mysql -uroot -p -S /opt/data8.0/mysql/mysql.sock
mysql> CREATE USER 'monitor'@'%'   IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT SELECT on sys.* to 'monitor'@'%';
Query OK, 0 rows affected (0.03 sec)

官方提供监控视图版本是5.7的，8.0的如下：
5.7版本参考：https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_group_replication_hostgroups

USE sys;
 
DELIMITER $$

DROP FUNCTION IF EXISTS my_id;
CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$
 
CREATE FUNCTION gr_member_in_primary_partition()
    RETURNS VARCHAR(3)
    DETERMINISTIC
    BEGIN
      RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
    performance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=
    ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
    'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
    performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
END$$


DROP VIEW IF EXISTS gr_member_routing_candidate_status;
CREATE VIEW gr_member_routing_candidate_status AS
    SELECT 
        sys.gr_member_in_primary_partition() AS viable_candidate,
        IF((SELECT 
                    (SELECT 
                                GROUP_CONCAT(variable_value)
                            FROM
                                performance_schema.global_variables
                            WHERE
                                variable_name IN ('read_only' , 'super_read_only')) != 'OFF,OFF'
                ),
            'YES',
            'NO') AS read_only,
        Count_Transactions_Remote_In_Applier_Queue AS transactions_behind,
        Count_Transactions_in_queue AS 'transactions_to_cert'
    FROM
        performance_schema.replication_group_member_stats
    WHERE
        member_id = MY_ID();$$
DELIMITER ;

确认信息如下：

mysql> select * from gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)

2.ProxySQL配置
添加服务器信息和用户信息
主负责写、从负责读，当MGR主库切换后，代理自动识别主从。
ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组50、备写组60、读组70、离线组80

[root@ss30 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL Admin Module)
mysql> use main
Database changed
mysql>insert into mysql_servers(hostgroup_id,hostname,port) values (50,'192.168.244.129',3380);
mysql>insert into mysql_servers(hostgroup_id,hostname,port) values (50,'192.168.244.129',3381);
mysql>insert into mysql_servers(hostgroup_id,hostname,port) values (50,'192.168.244.129',3382);

mysql> insert into mysql_users(username,password,default_hostgroup) values('mgradmin','123456',50);

mysql> 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 (50,60,70,80,1,1,0,100);

mysql>load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

查看服务器信息

mysql> select hostgroup_id, hostname,port, status  from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname        | port | status |
+--------------+-----------------+------+--------+
| 50           | 192.168.244.129 | 3380 | ONLINE |
| 70           | 192.168.244.129 | 3381 | ONLINE |
| 70           | 192.168.244.129 | 3382 | ONLINE |
+--------------+-----------------+------+--------+

验证是否正常通信

mysql> select  time_start_us, hostname,  port,  viable_candidate,  read_only, transactions_behind,  error  from mysql_server_group_replication_log  
order by time_start_us desc   limit 6;
+------------------+-----------------+------+------------------+-----------+---------------------+-------+
| time_start_us    | hostname        | port | viable_candidate | read_only | transactions_behind | error |
+------------------+-----------------+------+------------------+-----------+---------------------+-------+
| 1586094196050264 | 192.168.244.129 | 3382 | YES              | YES       | 1                   | NULL  |
| 1586094196049313 | 192.168.244.129 | 3381 | YES              | YES       | 0                   | NULL  |
| 1586094196048453 | 192.168.244.129 | 3380 | YES              | NO        | 0                   | NULL  |
| 1586094191055906 | 192.168.244.129 | 3382 | YES              | YES       | 1                   | NULL  |
| 1586094191055905 | 192.168.244.129 | 3381 | YES              | YES       | 0                   | NULL  |
| 1586094191055901 | 192.168.244.129 | 3380 | YES              | NO        | 0                   | NULL  |
+------------------+-----------------+------+------------------+-----------+---------------------+-------+

配置读写分离规则：

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
    -> VALUES (3,1,'^SELECT.*FOR UPDATE$',50,1),
    -> (4,1,'^SELECT',70,1);

验证：

[root@ss30 ~]# mysql -umgradmin -p123456 -h127.0.0.1 -P6033 -e "start transaction;select @@port;commit;select @@port;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|   3380 |
+--------+
+--------+
| @@port |
+--------+
|   3381 |
+--------+

查看路由规则：

mysql> select hostgroup,digest_text from stats_mysql_query_digest order by digest_text desc limit 5;
+-----------+----------------------------------+
| hostgroup | digest_text                      |
+-----------+----------------------------------+
| 50        | start transaction                |
| 50        | select @@version_comment limit ? |
| 70        | select @@port                    |
| 20        | select @@port                    |
| 50        | select @@port                    |
+-----------+----------------------------------+

总结

1.Proxysql整个使用上面，功能上面基本都能满足需求。
2.还有配置复杂度还是比较低的。
3.资源方面因为自己维护一套连接池，还是流量转发，配置不能太低。
4.性能损耗估计5%~18%以内。
5.autocommit=0相关bug，这样使用的场景还是比较不多。对于这种问题，等官方修复，或可以先临时插入一张表，作为事务开始的之后前奏。

https://github.com/sysown/proxysql/issues/2997

mysql

最后修改时间：2022-12-27 13:48:49

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

2人已赞赏

文章被以下合辑收录

专家专栏 | 崔虎龙（共138篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。

MySQL ProxySQL介绍

文章被以下合辑收录

评论