本文详细介绍了ProxySQL中的三种用户类型，包括管理接口用户、监控用户和应用端普通用户，重点讲解了如何在ProxySQL中管理这些用户的密码、权限和配置，以及mysql_users表的使用和密码加密策略。
ProxySQL–基础–3.2–用户–管理用户
1、ProxySQL中的3套用户
1.1、连接Admin管理接口(默认6032端口)使用一套用户名/密码(管理接口用户)
- 分为管理员和普通用户
- 管理员用户：具有写权限，能配置ProxySQL
- 普通用户：只有几个库的读权限。
- 管理员用户/密码 通过变量
admin-admin_credentials设置 - 普通用户/密码通 过变量
admin-stats_credentials设置。 - 这部分用户名不能出现在
mysql_users表中。
1.2、Monitor模块监控后端时，需要使用一个MySQL用户连接后端进行监控(监控用户)
- 需要先在后端MySQL组中创建好这个监控用户。
- 监控connect、ping和read_only时，只需USAGE权限
- 监控replication lag时需要replication client权限。
- 通过变量
mysql-monitor_username和mysql-monitor_password将监控用户加入到ProxySQL中。
1.3、应用端 连接ProxySQL(默认6033端口)、ProxySQL连接 后端MySQL时使用一套用户名/密码(普通用户)
- 当前端app发送SQL请求时，需要使用这套用户。然后ProxySQL将SQL语句路由给某后端节点，需要使用同一个用户和后端建立连接并将SQL语句发送出去。
- 需要现在后端MySQL组中创建好这套用户，因为ProxySQL需要连接后端，所以一般授权所有权限，例如root。
- 通过
mysql_users表将这些用户加入到ProxySQL中，每一行对应一个用户。
2、添加 管理接口管理接口的用户
2.1、自定义一个管理员账户
添加一个myuser:myuser的用户密码对。
2.1.1、配置
# 查看当前用户名和密码
mysql> select @@admin-admin_credentials; # 当前用户名和密码
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin |
+---------------------------+
1 row in set (0.01 sec)
# 新增管理员账户：myuser:myuser
mysql> set admin-admin_credentials='admin:admin;myuser:myuser';
Query OK, 1 row affected (0.00 sec)
# 使修改永久保存到磁盘
mysql> load admin variables to runtime;
mysql> save admin variables to disk;
# 查看
mysql> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin;myuser:myuser |
+---------------------------+
1 row in set (0.00 sec)
2.1.2、验证
修改后，就可以使用该用户名和密码连接管理接口。
[root@zhoufei ~]# mysql -umyuser -pmyuser -P6032 -h192.168.187.88
mysql>
2.1.3、扩展
上面所有的配置操作都是在修改main库中对应的表。
mysql> select * from global_variables where variable_name='admin-admin_credentials';
+-------------------------+---------------------------+
| variable_name | variable_value |
+-------------------------+---------------------------+
| admin-admin_credentials | admin:admin;myuser:myuser |
+-------------------------+---------------------------+
1 row in set (0.00 sec)
所以，前面的set语句和下面的update语句是等价的：
update global_variables set variable_value='admin:admin;myuser:myuser'
where variable_name='admin-admin_credentials';
2.2、自定义一个管理接口的普通账户
没有超级管理员权限，只能查看monitor库和main库中关于统计的数据，其它库都是不可见的，且没有任何写权限。
2.1、配置
# 查看当前的普通账户
mysql> select @@admin-stats_credentials;
+---------------------------+
| @@admin-stats_credentials |
+---------------------------+
| stats:stats |
+---------------------------+
1 row in set (0.00 sec)
# 新增普通账户：mystats:mystats
mysql> set admin-stats_credentials='stats:stats;mystats:mystats';
Query OK, 1 row affected (0.01 sec)
# 使修改立即生效
# 使修改永久保存到磁盘
mysql> load admin variables to runtime;
mysql> save admin variables to disk;
2.2、使用普通账户 查询
# 登录
mysql -ustats -pstats -P6032 -h127.0.0.1
# 查看库
mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | monitor | |
| 3 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
3 rows in set (0.00 sec)
# 查看库中的表
mysql> show tables from main;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_global |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+--------------------------------------+
15 rows in set (0.00 sec)
同样，这个变量中的用户必须不能存在于mysql_users表中。
3、添加 普通用户
- 涉及 ProxySQL 的 mysql_users表
- 应用端 连接ProxySQL(默认6033端口)、ProxySQL连接 后端MySQL时使用一套用户名
3.1、msyql 添加用户
我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例。
# 创建用户
create user sqlsender@'192.168.187.%' identified by '123456';
# 授权
grant all on *.* to root@'192.168.187.%' identified by '123456';
grant all on *.* to sqlsender@'192.168.187.%' identified by '123456';
3.2、ProxySQL 添加用户
配置mysql_users表，将刚才的两个用户添加到该表中。
这个表的字段很多，大多数字段都有默认值，以下是大部分使用默认值的插入语句
insert into mysql_users(username,password,default_hostgroup)values('root','123456',10);
insert into mysql_users(username,password,default_hostgroup)values('sqlsender','123456',10);
# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。
load mysql users to runtime;
save mysql users to disk;
mysql_users表有不少字段，最主要的以下3个字段为
- username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
- password：
- 用户名对应的密码
- 可以是明文密码
- 可以是hash密码：
- 想使用hash密码，可以先在某个MySQL节点上执行select password(PASSWORD)，然后将加密结果复制到该字段。
- 用户名对应的密码
- default_hostgroup：
- 该用户名默认的路由目标组。
- ProxySQL有多种粒度的路由规则，每个用户都有默认的路由目标组，当使用该用户发送的SQL语句没有能够匹配的语句级路由规则，则会将该SQL语句路由给该用户默认的路由目标组。
- 例如：navicat工具使用root用户连接到了ProxySQL，发送了一个select语句，如果没有配置select语句的路由规则，那么这个select语句将路由到默认的路由目标组。
- 该用户名默认的路由目标组。
3.3、测试
3.3.1、通过连接来验证
3.3.2、通过查看mysql_users表来验证
select * from mysql_users\G
要保证上面的值为1
3.3.2.1、active
只有active=1的用户才是有效的用户。
3.3.2.2、transaction_persistent(事务持久化)
- 值为1时：
- 表示事务持久化
- 如果正在和ProxySQL建立连接的客户端使用的用户设置了该字段，那么当该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组(它会自动禁用multiplexing，让同一个事务的语句从同一个连接路由出去，保证路由到同一个组的同一个节点)。
- 表示事务持久化
- 默认值：1。强烈建议修改为1
- 注意：有些老版本中，这个字段默认值为0，强烈建议修改为1。
我们期望的值为1，如果为0，则执行下面的语句修改为1。
update mysql_users set transaction_persistent=1 where username='root';
update mysql_users set transaction_persistent=1 where username='sqlsender';
# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。
load mysql users to runtime;
save mysql users to disk;
4、添加 监控用户
监控用户 用于监控后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL通过Monitor模块监控后端MySQL Server的read_only值来自动调整节点所属的组。所以，在配置读、写组之前，必须先配置好监控。
4.1、创建一个用于监控的用户和对应的权限
- 首先在后端mysql的 master节点上创建一个用于监控的用户名，这个用户名只需具有USAGE权限即可。
- 只需在master上创建即可，因为会复制到slave上
- 如果还需要监控复制结构中slave是否严重延迟于master(先混个眼熟：这个俗语叫做
拖后腿，术语叫做replication lag)，则还需具备replication client权限。这里直接赋予这个权限
4.1.1、在mysql的master上操作
创建一个监控用户
# 进入容器
docker exec -it M1 bash ;
# 进入mysql
mysql -uroot -proot ;
# 创建用户和权限
create user monitor@'192.168.187.%' identified by '123456';
grant replication client on *.* to monitor@'192.168.187.%';
4.1.2、在ProxySQL上配置监控用户，配置方式是修改全局变量。
# 连接到ProxySQL的管理接口
mysql -uadmin -padmin -P6032 -h127.0.0.1;
set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';
以上设置实际上是在修改global_variables表，它和下面两个语句是等价的：
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123456'
WHERE variable_name='mysql-monitor_password';
# 可以通过下面语句查询
select * from global_variables WHERE variable_name='mysql-monitor_username'\G
4.1.3、ProxySQL上配置监控用户后，需要加载到RUNTIME，并保存到disk。
在ProxySQL上操作
load mysql variables to runtime;
save mysql variables to disk;
4.2、验证监控结果
- ProxySQL监控模块的指标都保存在monitor库的log表中
- 在ProxySQL上操作
4.2.1、验证：连接是否正常(对connect指标的监控)
在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常
select * from mysql_server_connect_log;
mysql> select * from mysql_server_connect_log;
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| 192.168.187.88 | 3307 | 1693479837327821 | 0 | Access denied for user 'monitor'@'192.168.187.88' (using password: YES) |
| 192.168.187.88 | 3307 | 1693480317996720 | 0 | Access denied for user 'monitor'@'192.168.187.88' (using password: YES) |
| 192.168.187.88 | 3309 | 1693480318663673 | 0 | Access denied for user 'monitor'@'192.168.187.88' (using password: YES) |
| 192.168.187.88 | 3309 | 1693480326834127 | 1972 | NULL |
| 192.168.187.88 | 3307 | 1693480327390364 | 1743 | NULL |
| 192.168.187.88 | 3308 | 1693480327946619 | 3763 | NULL |
| 192.168.187.88 | 3308 | 1693480386834006 | 1278 | NULL |
| 192.168.187.88 | 3307 | 1693480387561339 | 1784 | NULL |
| 192.168.187.88 | 3309 | 1693480388288688 | 2812 | NULL |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
4.2.2、验证：心跳信息的监控(对ping指标的监控)
select * from mysql_server_ping_log;
mysql> select * from mysql_server_ping_log;
+----------------+------+------------------+----------------------+-------------------------------------------------------------------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+-------------------------------------------------------------------------+
| 192.168.187.88 | 3308 | 1693479994119722 | 0 | Access denied for user 'monitor'@'192.168.187.88' (using password: YES) |
| 192.168.187.88 | 3307 | 1693479994243661 | 0 | Access denied for user 'monitor'@'192.168.187.88' (using password: YES) |
| 192.168.187.88 | 3309 | 1693480324380010 | 0 | Access denied for user 'monitor'@'192.168.187.88' (using password: YES) |
| 192.168.187.88 | 3307 | 1693480327030572 | 1665 | NULL |
| 192.168.187.88 | 3308 | 1693480327154397 | 1222 | NULL |
| 192.168.187.88 | 3309 | 1693480327278349 | 148 | NULL |
| 192.168.187.88 | 3307 | 1693480337009422 | 155 | NULL |
| 192.168.187.88 | 3309 | 1693480337084814 | 487 | NULL |
5、mysql_users表中用户的密码管理
ProxySQL向mysql_users表添加用户时，支持明文密码和hash加密密码。这个hash密码和mysql的password()的算法是一样的。
但是，ProxySQL内部使用的是SQLite3引擎，不支持password()。所以，想要向ProxySQL中使用hash加密密码，可以先通过mysql的password()函数创建一个hash密码，然后copy到mysql_users表中。
5.1、案例
5.1.1、获取密码
# 进入容器
docker exec -it M1 bash ;
# 进入mysql
mysql -uroot -proot ;
mysql> select password("123456");
+-------------------------------------------+
| password("123456") |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
5.1.2、插入到ProxySQL的mysql_users表：
insert into mysql_users(username,password,default_hostgroup)
values('root','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9',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.4、mysql_users表详细解释
ProxySQL--基础--04--库和表
3、mysql_users表
扫一扫
647
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
