墨墨导读:Proxysql读写分离的中间件,支持高可用 主从\ MGR \ PXC等环境,并提供连接池、缓存、日志记录等功能。
ProxySQL是用C++语言开发的,一个轻量级开源软件,性能和功能满足读写中间件所需的绝大多数功能,其配置数据基于SQLite存储,目前已到v2.0.13版本。
功能方面如下:
特性方面:
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结构
1. 启动过程
注意:
如果找到数据库文件(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)
3. 核心配置表
4. 命令
5. 小结
这些数据库的功能实现了实用化内容:
三、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表,将用户添加到该表中。
mysql> 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:
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;
7. 开启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登录即可查看一些统计信息。
8. 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"}
9. 其他
自动回避复制延迟较大的节点
如果服务器将 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 |
+------------------+-----------------+------+------------------+-----------+---------------------+-------+
配置读写分离规则:
sql> 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
墨天轮原文链接:https://www.modb.pro/db/28841(复制到浏览器中打开或者点击“阅读原文”)