1. 配置后端节点前的说明
为了让ProxySQL能够找到后端的MySQL节点, 需要将后端的MySQL Server加入到ProxySQL中。ProxySQL的一切配置行为都是在修改main库中的对应的表, 所以添加节点到ProxySQL中实际上也是通过修改相关表来实现的。
管理后端节点有几个过程 :
将后端
MySQL Server的主从复制结构配置好。将后端
MySQL Server节点加入到ProxySQL中。相关的表为mysql_servers。监控后端节点。相关的表是全局变量表
global_vairbles, 相关的变量为mysql-monitor_开头的变量。配置读、写组。相关的表为
mysql_replication_hostgroups。配置
MySQL用户, 用于发送SQL语句。相关的表为mysql_users。
几个注意点 :
ProxySQL是通过监控后端节点的read_only值来自动调整节点所属组的, 例如read_only=1的节点会移动到读组,read_only=0的节点会移动到写组。所以, 在配置读、写组之前, 需要先监控后端节点。ProxySQL也支持手动管理后端节点, 这种模式不会根据read_only的值自动调整, 在后面的文章中会介绍这种模式。对于传统的主从复制, 默认的
read_only=0, 所以在第一步中, 各slave节点的配置文件中需要加上read_only=1。对于组复制、Galera, 因为会自动强制设置非写节点的read_only=1, 所以无需额外配置该属性。ProxySQL支持传统主从复制结构(即异步、半同步、gtid复制)的后端, 读、写组相关的表是mysql_replication_hostgroups。还支持MySQL组复制结构的后端, 相关的表是mysql_group_replication_hostgroups, 还支持Galera(如percona XtraDB cluster)结构的后端, 不过ProxySQL是通过scheduler调度proxysql_galera_checker.sh脚本来支持Galera的, 而且目前还没有mysql_galera_hostgroups(ProxySQL 2.0才新增该表)。
本文暂时只解释mysql_servers和mysql_replication_hostgroups。
完成了上面的过程后, 节点就一切正常了, 然后就可以去配置ProxySQL的路由规则、查询缓存、SQL语句重写等功能。
2. 添加新节点: mysql_servers表
假如后端有3个节点, 使用的是传统的异步复制(1 master, 2 slave)。这3个节点的IP地址为 :
192.168.71.7
192.168.71.11
192.168.71.29要加入3个后端MySQL节点, 只需向mysql_servers表插入3行对应的记录即可。以下是使用了一大堆默认值的insert语句 :
insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.71.7',3306);
insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.71.11',3306);
insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.71.29',3306);
load mysql servers to runtime;
save mysql servers to disk;上面的insert语句表示将后端MySQL节点192.168.71.xx:3306加入到hostgroup_id=10的组中。
其实mysql_servers表有很多字段, 很多字段都有默认值, 正如上面的insert语句, 除了3个字段, 其它使用的全是字段的默认值。
以下是mysql_servers表的字段属性。
| 字段 | 数据类型 | 允许null | 默认值 |
|---------------------|----------|-----------|--------|
| hostgroup_id (pk) | int | not null | 0 |
| hostname (pk) | varchar | not null | 无 |
| port (pk) | int | not null | 3306 |
| status | varchar | not null | online |
| weight | int | not null | 1 |
| compression | int | not null | 0 |
| max_connections | int | not null | 1000 |
| max_replication_lag | int | not null | 0 |
| use_ssl | int | not null | 0 |
| max_latency_ms | int | not null | 0 |
| comment | varchar | not null | '' |可见, 新添加一个节点时, 唯一必须指定的字段就是hostname值。如果不指定hostgroup_id, 那么节点将自动加入到hostgroup_id=0的组中。
各字段的意义如下 :
hostgroup_id: 该后端MySQL实例所在的主机组。注意, 同一MySQL节点可属于多个主机组。hostname,port: 后端MySQL监听的地址和端口。就是默认3306端口的意义status:ONLINE: 该后端MySQL节点完全正常。SHUNNED: 该后端MySQL节点将暂时被ProxySQL自动避开(忽略), 原因可能是一个较短时间内发生了大量连接错误, 也可能是该slave与master的数据延迟太大(replication lag)。OFFLINE_SOFT: 当某后端MySQL被设置为OFFLINE_SOFT时,ProxySQL将不会向它发送新的请求, 但该节点正在执行的事务会继续执行, 直到所有事务执行完毕后会进入非激活状态。也就是说, 和该后端的连接会保持到事务执行完毕。这可以实现后端节点的graceful停止、重启。OFFLINE_HARD: 当某后端MySQL被设置为OFFLINE_HARD时,ProxySQL将不会向它发送新的请求, 该节点正在执行的事务也会立即中断。也就是直接将该后端杀掉。等价于删除该节点, 或临时将其移除出组(例如出于维护的目的)。
weight: 节点在组中的权重值越高,ProxySQL会发送越多请求给它们。compression: 如果该字段的值设置为大于0,ProxySQL和该后端新建的连接中,ProxySQL将会先压缩数据再传输。max_connections: 和该后端允许建立的最大连接数。当达到最大数量时, 即使该后端的权重很大, 也不会和它新建连接。默认值为1000, 表示每个后端最多能同时接受1000个连接。请确保该后端的max_connections值是合理的, 以避免MySQL超负荷时ProxySQL继续向其发送请求。max_replication_lag: 如果值大于0,ProxySQL的Monitor模块将会定期检查该slave的复制是否延后于master, 如果延迟的值大于该字段的值,ProxySQL将会暂时避开该节点, 直到该slave赶上master。use_ssl: 如果设置为1, 则和该后端MySQL建立SSL连接。max_latency_ms:Monitory模块定期向该后端发起ping检查, 如果该节点的ping时间大于该字段的值, 则将其排除在连接池之外(尽管该节点仍处于ONLINE状态)。comment: 该表的说明信息, 可随便定义。
以下是上面插入数据成功后的结果:
admin> select * from mysql_servers\G
*************************** 1. row ***************************
hostgroup_id: 10
hostname: 192.168.71.7
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 10
hostname: 192.168.71.11
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 10
hostname: 192.168.71.29
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
3 rows in set (0.00 sec)
需要注意的是 , 同一个节点是可以同时存在于多个组中的。最典型的情形是master既充当写节点 , 也充当读节点 , 这时它就同时存在于写组和读组。
3. 监控后端节点
ProxySQL通过Monitor模块监控后端MySQL Server的read_only值来自动调整节点所属的组。所以 , 在配置读、写组之前 , 必须先配置好监控。
本文只简单介绍该模块的监控类型 , 以及如何配置监控
3.1 Monitor模块可以监控什么东西
首先看下Monitor库中的表:
admin> show tables from monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.00 sec)
Monitor监控4种指标 : connect、ping、read_only和replication lag。
connect监控
ProxySQL连接到各后端是否成功 , 成功/失败的连接将记录到表mysql_server_connect_log中。ping监控
这是一种心跳检测。
Monitor模块向所有后端MySQL节点发起ping检查 ,ping成功/失败的情况将记录到表mysql_server_ping_log中。当ping某后端的失败次数达到了mysql-monitor_ping_max_failures时表示失去心跳 , 将发送一个信号给MySQL的主机组管理器来杀掉和该后端节点的所有连接。请和
connect监控区分开 ,connect监控是通过建立连接来检测和后端节点连接的连通性。ping监控是心跳检测 ,ProxySQL通过MySQL的一个ping API发送给后端MySQL服务上 , 然后等待ping回复 , 虽然是ping检测 , 但也是需要建立连接的。所以 , 有两个确定连接不可用公式 :
mysql-monitor_ping_max_failures * mysql-monitor_connect_timeoutmysql-monitor_ping_max_failures * mysql-monitor_ping_timeout
read_only监控
检查
mysql_replication_hostgroups表中所有节点的read_only值 , 并记录到mysql_server_read_only_log。如果read_only=1, 表示只读 , 是一个slave, 这样的节点将会自动移入reader_hostgroup中 , 如果read_only=0, 表示可写 , 可能是master, 这样的节点将会自动移入writer_hostgroup中。replication lag监控对
mysql_servers表中所有配置了max_replication_lag的后端slave节点都检查复制延迟 , 通过show slave status返回结果中的Seconds_Behind_Master字段 , 判断slave和master之间的延迟程度 , 并记录到mysql_server_replication_lag_log表中。
如果Seconds_Behind_Master > max_replication_lag , 表示该slave延迟很严重 , ProxySQL会自动避开这种slave节点 , 直到Seconds_Behind_Master < max_replication_lag。
Monitor监控上述指标时 , 会使用MySQL节点上的用户连接到后端节点 , 所以 , 需要先在后端节点上创建负责监控的用户。监控connect、ping和read_only这3项指标时 , 该用户只需具有USAGE权限 , 如果要监控replication lag指标 , 则需要replication client权限。
3.2 配置connect 和ping监控
首先 , 在后端节点上创建用于监控的用户 , 顺便为其授予replication client权限。只需在一个写节点(例如master)上创建即可 , 它会复制到其它节点上。
create user monitor@'192.168.71.%' identified by 'P@ssword1!';
grant replication client on *.* to monitor@'192.168.71.%';然后 , 在ProxySQL上配置这个监控用户 , 配置方式是修改全局变量。
set mysql-monitor_username='monitor';
set mysql-monitor_password='P@ssword1!';由于ProxySQL上所有的配置修改都是在修改main库中对应的表 , 上面的变量在main.global_variables中 , 所以上面两个set语句和下面两个update语句是等价的。
update global_variables set variable_value='monitor'
where variable_name='mysql-monitor_username';
update global_variables set variable_value='P@ssword1!'
where variable_name='mysql-monitor_password';在将配置load到runtime之前 , 可以先查看下connect和ping对应的log表。
admin> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.71.29 | 3306 | 1742735084321772 | 1682 | NULL |
| 192.168.71.11 | 3306 | 1742735085048648 | 1440 | NULL |
| 192.168.71.7 | 3306 | 1742735085775185 | 1636 | NULL |
| 192.168.71.11 | 3306 | 1742735144322754 | 1822 | NULL |
| 192.168.71.29 | 3306 | 1742735144777628 | 1844 | NULL |
| 192.168.71.7 | 3306 | 1742735145232841 | 2410 | NULL |
| 192.168.71.7 | 3306 | 1742735204323278 | 1499 | NULL |
...
admin> select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------+
| 192.168.71.29 | 3306 | 1742735130172592 | 743 | NULL |
| 192.168.71.11 | 3306 | 1742735130172783 | 656 | NULL |
| 192.168.71.7 | 3306 | 1742735130172794 | 675 | NULL |
| 192.168.71.29 | 3306 | 1742735140173101 | 624 | NULL |
| 192.168.71.7 | 3306 | 1742735140173232 | 562 | NULL |
| 192.168.71.11 | 3306 | 1742735140173215 | 600 | NULL |
| 192.168.71.29 | 3306 | 1742735150173748 | 625 | NULL |
...不难发现 , 监控操作在load到runtime之前就已经生效了。这是有意为之的 : 通过这种方式 , 可以在节点添加到生产环境之前执行一些基本的健康检查。
监控的节点一切正常后(error=NULL) , 将配置load到runtime。
load mysql variables to runtime;
save mysql variables to disk;3.3 配置read_only监控和读/写组
目前read_only和replication_lag的监控日志还是空。
admin> select * from mysql_server_read_only_log;
Empty set (0.00 sec)
admin> select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)这是因为还没有对ProxySQL中的节点分组 : writer_hostgroup、reader_hostgroup。
设置分组信息 , 需要修改的是main库中的mysql_replication_hostgroups表(组复制则是mysql_group_replication_hostgroups) , 该表只有3个字段 : 第一个字段名为writer_hostgroup , 第二个字段为reader_hostgroup , 第三个字段为注释字段 , 可随意写。
例如 , 指定写组的id为10 , 读组的id为20。
insert into mysql_replication_hostgroups values(10,20);
admin> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10 | 20 | read_only | |
+------------------+------------------+------------+---------+
1 row in set (0.07 sec)在该配置加载到RUNTIME生效之前 , 先查看下各mysql server所在的组。
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.29 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)3个节点都在hostgroup_id=10的组中。
现在 , 将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。
load mysql servers to runtime;
save mysql servers to disk;一加载 , Monitor模块就会开始监控后端的read_only值 , 当监控到read_only值后 , 就会按照read_only的值将某些节点自动移动到读/写组。
例如 , 此处所有节点都在id=10的写组 , slave1和slave2都是slave , 它们的read_only=1 , 这两个节点将会移动到id=20的组。如果一开始这3节点都在id=20的读组 , 那么移动的将是Master节点 , 会移动到id=10的写组。
这里安装配置的是3.0版本。mysql_servers库中 , 在配置到这里的时候, 不会有什么改变。
mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.29 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)在安装proxysql-2.0.2版本的时候,这里会直接显示为以下内容。主节点会同时进去10、20两个组
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.7 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
4 rows in set (0.00 sec)
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.7 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
4 rows in set (0.00 sec)在proxysql-1.4.9版本中, 会直接将mysql_servers库中的内容修改
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.01 sec)
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)每个版本只要runtime_mysql_servers运行时的库中能按照组划分即可。只是mysql_servers库不一样
这里看结果:
admin> select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.43 sec)配置好read_only的监控后 , Monitor模块会每隔一段时间监控一次read_only值。
admin> select * from mysql_server_read_only_log;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.168.71.29 | 3306 | 1742735971948477 | 916 | 1 | NULL |
| 192.168.71.11 | 3306 | 1742735971948731 | 742 | 1 | NULL |
| 192.168.71.7 | 3306 | 1742735971948868 | 651 | 0 | NULL |
| 192.168.71.29 | 3306 | 1742735973448700 | 919 | 1 | NULL |
| 192.168.71.11 | 3306 | 1742735973449062 | 617 | 1 | NULL |
| 192.168.71.7 | 3306 | 1742735973448961 | 747 | 0 | NULL |
| 192.168.71.29 | 3306 | 1742735974948835 | 1019 | 1 | NULL |
| 192.168.71.11 | 3306 | 1742735974949284 | 641 | 1 | NULL |
| 192.168.71.7 | 3306 | 1742735974949122 | 837 | 0 | NULL |3.4 配置replication lag监控
Monitor模块会监控后端主机组中各slave的数据是否延迟于master , 这个延迟行为称为replication lag , 俗称拖后腿。
如果某个slave节点上的数据比master落后很多(临界值见下文) , 表示这个slave节点处理速度慢 , 数据较旧。ProxySQL采用一种称为自动避开(automatic shunned)的方式 , 临时避开这个落后的节点。当ProxySQL避开某节点后 , ProxySQL不会把SQL语句路由给这个节点。
ProxySQL有几种情况可能会触发自动避开节点的行为 :
和后端的连接断开。
slave落后于master过多。和后端建立连接时 , 错误次数过多。
second_behind_master=null时 , 即slave的SQL线程未运行 , 或者slave未连接到master。(不过这种自动避开的情况是可控的 , 见全局变量mysql-monitor_slave_lag_when_null
本文介绍关于replication lag的内容。
Monitor模块会每隔一段时间(mysql-monitor_replication_lag_interval)去检查一次拖后腿情况 , 检测的方式是获取show slave status中的Seconds_Behind_Master字段值 , 然后和mysql_servers表中max_replication_lag字段的值比较 :
Seconds_Behind_Master < max_replication_lag: 表示落后程度尚在允许范围内。Seconds_Behind_Master > max_replication_lag: 表示落后太多 , 这样的节点应该避开。
只有传统复制结构的slave节点才需要设置max_replication_lag字段 , master无需设置 , 组复制和galera也无需设置 , 因为这两种复制结构中show slave status的结果为空。例如 , 将读组中的所有节点都设置最多落后于master 10秒钟。
update mysql_servers set max_replication_lag=10 where hostgroup_id=20;
load mysql servers to runtime;
save mysql servers to disk;需要注意的是 , Seconds_Behind_Master的值并不总是可靠的 , 见 https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html 。
4.关于主机组和read_only
前面为了将主机组分为读组和写组 , 特地开启了monitor模块的read_only监控功能 , 根据read_only的值以及mysql_replication_hostgroup表中定义的读、写组自动调整节点。
在简单的环境下 , 这没什么问题。但是想要实现复杂一点的读、写分离 , 比如写操作路由到hostgroup_id=10的组 , 读操作分多种情况路由 , select1语句路由到hostgroup_id=20的读组 , select2语句路由到hostgroup_id=30的组 , ...。这种情况 , 想通过monitor模块和mysql_replication_hostgroup表来实现是不可能的 , 因为mysql_replication_hostgroup表的writer_hostgroup字段是主键 , reader_hostgroup字段具有唯一性 , 它们都是int类型 , 且不可重复。
admin> show create table main.mysql_replication_hostgroups\G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.01 sec)换句话说 , 每一个写组只能且必须对应单个读组(经测试 , reader_hostgroup字段可以为空) , 无法实现hg_id=10是写组 , 而hg_id=20、hg_id=30同时作为读组。
显然ProxySQL不可能限制的这么死。实际上 , 完全可以不用定义mysql_replication_hostgroup表 , 也不用定义monitor模块的read_only监控(如果没有定义mysql_replication_hostgroup , read_only监控会自动停止) , 这时只能人为控制不要把写操作路由到后端的slave上。
例如 :
delete from mysql_replication_hostgroup;
delete from mysql_servers;
insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.71.7',3306),
(20,'192.168.71.11',3306),
(30,'192.168.71.29',3306);
load mysql servers to runtime;
save mysql servers to disk;事实上 , 在实现ProxySQL复杂、高级的路由功能时 , 都不会通过monitor模块去自动控制读、写组。
5.向ProxySQL添加MySQL用户 : mysql_users表
配置好后端MySQL节点、监控、分组后 , 接下来要配置的是MySQL的用户。
需要注意 , ProxySQL中有3套用户 :
连接
Admin管理接口(默认6032端口)使用一套用户名/密码。分为管理员和普通用户 , 管理员用户具有写权限 , 能配置
ProxySQL, 普通用户只有几个库的读权限。管理员用户/密码通过变量
admin-admin_credentials设置 , 普通用户/密码通过变量admin-stats_credentials设置。这部分用户名不能出现在
mysql_users表中。
Monitor模块监控后端时 , 需要使用一个MySQL用户连接后端进行监控。需要先在后端
MySQL组中创建好这个用户监控的用户。监控
connect、ping和read_only时 , 只需USAGE权限 , 监控replication lag时需要replication client权限。通过变量
mysql-monitor_username和mysql-monitor_password将监控用户加入到ProxySQL中。
前端连接
ProxySQL(默认6033端口)、ProxySQL连接后端MySQL时使用一套用户名/密码。当前端
app发送SQL请求时 , 需要使用这套用户。然后ProxySQL将SQL语句路由给某后端节点 , 需要使用同一个用户和后端建立连接并将SQL语句发送出去。需要现在后端
MySQL组中创建好这套用户 , 因为ProxySQL需要连接后端 , 所以一般授权所有权限 , 例如root。通过
mysql_users表将这些用户加入到ProxySQL中 , 每一行对应一个用户。
5.1 添加MySQL users到ProxySQL
例如 , 使用root用户来处理SQL请求。先在后端的写节点(如master节点)上授权root , 该操作会复制给其它节点。
grant all on *.* to root@'192.168.71.%' identified by 'P@ssword1!';然后 , 向ProxySQL的mysql_users插入这个用户即可。这个表的字段很多 , 大多数字段都有默认值 , 以下是大部分使用默认值的插入语句 :
insert into mysql_users(username,password,default_hostgroup)
values ('root','P@ssword1!',10);
load mysql users to runtime;
save mysql users to disk;上面指定了root用户的用户名、密码以及该用户默认的路由目标组。
ProxySQL有多种粒度的路由规则 , 每个用户都有默认的路由目标组 , 当使用该用户发送的SQL语句没有能够匹配的语句级路由规则 , 则会将该SQL语句路由给该用户默认的路由目标组。
例如 , navicat工具使用root用户连接到了ProxySQL , 发送了一个select语句 , 如果没有配置select语句的路由规则 , 那么这个select语句将默认路由给root用户的默认组。
下面先介绍一下mysql_users表中的密码相关内容 , 然后再详细介绍mysql_users表。
5.2 mysql_users表中用户的密码管理
ProxySQL向mysql_users表添加用户时 , 支持明文密码和hash加密密码。这个hash密码和mysql的password()的算法是一样的。
但是 , ProxySQL内部使用的是SQLite3引擎 , 不支持password()。所以 , 想要向ProxySQL中使用hash加密密码 , 可以先通过mysql的password()函数创建一个hash密码 , 然后copy到mysql_users表中。
例如 :
[root@s4 ~]# mysql -uroot -pP@ssword1! -e 'select password("P@ssword1!");'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------------------+
| password("P@ssword1!") |
+-------------------------------------------+
| *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 |
+-------------------------------------------+然后插入到ProxySQL的mysql_users表 :
insert into mysql_users(username,password,default_hostgroup)
values ('root','*50572A5FABC7DA9CEE5EB5977EDDE59E38967422',10);ProxySQL和MySQL对密码的处理方式都是 : 以"*"开头的密码 , 表示是hash加密密码。
在MySQL和ProxySQL中 , 使用SHA1(SHA1('clear_password'))对clear_password进行加密。无法根据加密的hash密码推导回原始的明文密码。
当客户端连接到ProxySQL时 , ProxySQL将使用该加密的密码对用户进行认证。如果该客户端是第一次认证 , 则ProxySQL会推导出一部分的hash密码SHA1('clear_password')。推导出的信息会存储到runtime的内部数据结构中 , 以便ProxySQL连接后端MySQL时使用。
从1.2.3版本开始 , 引入了一个布尔类型的全局变量admin-hash_passwords , 默认为true。该变量表示 , 内存数据库的mysql_users表中的明文密码 , 在load mysql users to runtime时 , 对明文密码进行hash加密 , 并保存到runtime数据结构中。有了这个特性 , 可以以另一种方式保存加密密码 : 只需将其刷回内存数据库即可。
例如 :
Admin> SELECT username,password FROM mysql_users;
+----------+-----------+
| username | password |
+----------+-----------+
| user1 | password1 | # 明文密码
| user2 | password2 |
+----------+-----------+
Admin> LOAD MYSQL USERS TO RUNTIME; # 加载到runtime数据结构
Admin> SELECT username,password FROM mysql_users;
+----------+-----------+
| username | password |
+----------+-----------+
| user1 | password1 | # 还是明文密码
| user2 | password2 |
+----------+-----------+这个时候 , runtime数据结构中的密码是加密密码 , 而内存数据库中的密码是明文密码。
将runtime数据结构数据刷回内存数据库 , 即可将加密密码保存到内存数据库中 , 然后还可以将加密的密码持久化到disk。
Admin> save mysql users to memory;
Admin> SELECT username,password FROM mysql_users;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | *668425423DB5193AF921380129F465A6425216D0 |
| user2 | *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9 |
+----------+-------------------------------------------+
Admin> save mysql users to disk;唯一需要注意的是 , admin-hash-passwords变量是admin-变量而非mysql-变量 , 这意味着修改了这个变量后(虽然基本不会去修改) , load/save操作的目标是"admin variables" , 而非"mysql variables"。
load admin variables to runtime;
save admin variables to disk;5.3 mysql_users表详细解释
以下是mysql_users表的属性。
| 字段名 | 数据类型 | 可为空? | 默认值 |
|-----------------------|---------|----------|-------|
|username (pk,uk) | VARCHAR | NOT NULL | |
|password | VARCHAR | NULL | |
|active | INT | NOT NULL | 1 |
|use_ssl | INT | NOT NULL | 0 |
|default_hostgroup | INT | NOT NULL | 0 |
|default_schema | VARCHAR | NULL | |
|schema_locked | INT | NOT NULL | 0 |
|transaction_persistent | INT | NOT NULL | 1 |
|fast_forward | INT | NOT NULL | 0 |
|backend (pk) | INT | NOT NULL | 1 |
|frontend (uk) | INT | NOT NULL | 1 |
|max_connections | INT | NOT NULL | 10000 |各字段的意义 :
username, password: 前端连接到ProxySQL以及ProxySQL连接到后端时使用的用户凭据。active :
active=0的用户会保留在库中 , 但不会加载到runtime数据结构中 , 只有active=1用户才是有效用户。该字段默认值为1。default_hostgroup: 如果该用户发送的查询语句无法匹配任何规则 , 则该查询会路由到该字段指定的默认组中。default_schema: 建立连接时默认将切换到该schema。schema_locked: 目前还不支持该功能。transaction_persistent: 如果正在和建立连接的客户端使用的用户设置了该字段 , 那么当该用户开启一个事务后 , 该客户端后续的请求都将路由到同一主机组中(无视查询规则) , 使得事务在这个组中是持久的 , 避免同一事务分散到其它组 , 直到事务结束才可能会路由到其它主机组。注意 , 有些老版本中 , 这个字段默认值为0, 强烈建议修改为1。fast_forward: 如果设置了该字段 ,ProxySQL将绕过查询处理层(重写语句、缓存) , 直接将原请求语句转发给后端节点。frontend: 如果设置为1, 前端将可以使用该用户(username,password)连接到ProxySQL。backend: 如果设置为1,ProxySQL将可以使用该用户(username,password)连接到后端节点。max_connections: 使用该用户"建立到ProxySQL的连接"的最大数量。默认值为10000, 所以每个用户最多和ProxySQL建立10000个连接。注意 , 这是前端到ProxySQL的连接限制 ,ProxySQL和某个后端节点的最大连接数量是通过mysql_servers中的max_connections字段控制的。
注意 , 当前版本的ProxySQL要求所有的用户均设置frontend和backend为1(即所有用户都可以进行frontend --> ProxySQL以及ProxySQL --> backend的连接认证)。将来版本中 , ProxySQL将分离这两部分连接的用户凭据。这样前端将永远不知道后端的用户凭据 , 它们只能通过中间的ProxySQL发送请求 , 无法直接和后端节点建立连接 , 从而提高安全性。
关于快速转发fast_forward :
不要求用一个不同的端口 : 正常的
ProxySQL逻辑和"fast forward"的逻辑使用的是完全相同的代码/模块。fast forward是基于每用户的 : 根据连接到ProxySQL用户的设置 , 决定该用户是否启用、禁用fast forward功能。fast forward算法的启用是在用户认证之后 :ProxySQL仍然需要先对客户端使用的用户进行认证 , 尽管客户端的请求会直接原样转发给后端 , 但ProxySQL仍然会和前端先建立好连接。这意味着 , 如果前端和ProxySQL的连接发生错误 , 也会被处理。fast forward不支持SSL连接。如果使用压缩功能 , 必须在两端都启用压缩。
5.4 事务持久化 : transaction_persistent
mysql_users表中的transaction_persistent字段 , 当它的值为1时 , 表示事务持久化 : 当某连接使用该用户开启了一个事务后 , 那么在事务提交/回滚之前 , 所有的语句都路由到同一个组中 , 避免语句分散到不同组。在以前的版本中 , 默认值为0 , 不知道从哪个版本开始 , 它的默认值为1。我们期望的值为1 , 所以强烈建议插入用户后先检查下这个字段的值是否为1 , 如果为0 , 则执行下面的语句修改为1。
update mysql_users set transaction_persistent=1 where username='root';
update mysql_users set transaction_persistent=1 where username='sqlsender';
load mysql users to runtime;
save mysql users to disk;6.总结
添加后端节点、监控后端节点、添加MySQL用户是使用ProxySQL所必须完成的步骤。这3个步骤虽然需要操作的过程不多 , 但是涉及的内容还是比较多的。
强烈建议将mysql_servers、mysql_users、mysql_replication_hostgroups这3个表的所有字段都了解一遍。不仅如此 , 要熟练使用ProxySQL , 还应该对main库中的表的各个字段都比较熟悉 , 至少要知道它们什么意思。
下面 , 将添加后端节点、监控后端节点、添加MySQL用户的操作过程抽取出来 , 算是个步骤总结 :
######### 1.添加后端节点
#
#
insert into mysql_servers(hostgroup_id,hostname,port) values
(10,'192.168.71.7',3306),
(10,'192.168.71.11',3306),
(10,'192.168.71.29',3306);
load mysql servers to runtime;
save mysql servers to disk;
# 查看下各节点是否都是 ONLINE
select * from mysql_servers\G
######### 2.在后端MySQL上创建监控用户和处理SQL语句的用户
#
# 在后端master节点上执行以下语句
#
create user monitor@'192.168.71.%' identified by 'P@ssword1!';
grant replication client on *.* to monitor@'192.168.71.%';
grant all on *.* to root@'192.168.71.%' identified by 'P@ssword1!';
######### 3.在ProxySQL中配置监控用户
#
#
set mysql-monitor_username='monitor';
set mysql-monitor_password='P@ssword1!';
# 以上两个set语句等价于下面两个update :
update global_variables set variable_value='monitor'
where variable_name='mysql-monitor_username';
update global_variables set variable_value='P@ssword1!'
where variable_name='mysql-monitor_password';
load mysql variables to runtime;
save mysql variables to disk;
# 查看下connect和ping的监控是否正常
select * from mysql_server_connect_log order by time_start_us desc limit 6;
select * from mysql_server_ping_log order by time_start_us desc limit 6;
######### 4.配置读、写组
#
#
insert into mysql_replication_hostgroups values(10,20);
load mysql servers to runtime;
save mysql servers to disk;
######### 5.在ProxySQL中配置MySQL用户
#
#
insert into mysql_users(username,password,default_hostgroup)
values ('root','P@ssword1!',10);
load mysql users to runtime;
save mysql users to memory;
save mysql users to disk;
update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;
save mysql users to disk;参考链接
MySQL中间件之ProxySQL(6) : 管理后端节点 - 骏马金龙 - 博客园