MySQL中间件ProxySQL2.x安装及测试
文章来源: 陶老师运维笔记- 微信公众号
ProxySQL.jpg
1.简介
ProxySQL 是一个较轻量但功能强大的MySQL中间件。可以很好的支持 Master Slave， MGR, PXC等MySQL架构，并提供连接池、读写分离、日志记录等功能。
ProxySQL当前最新版本是2.0.x,已可以支持MySQL8.0了。
2.安装
下载软件： https://github.com/sysown/proxysql/releases
#yum源
$cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
#或下载
$wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos67.x86_64.rpm
#安装
$rpm -ivh proxysql-2.0.8-1-centos67.x86_64.rpm
#查看版本
$proxysql --version
ProxySQL version 2.0.8-67-g877cab1, codename Truls
启停/状态
#查看版本
$proxysql --version
#存储目录
#ls /var/lib/proxysql/
#启动/停止
$service proxysql start
$service proxysql stop
$service proxysql status # 查看proxysql状态
ProxySQL is running (20761).
测试
成功启动后，就使用原始账号admin/admin可以登录proxy管理接口6032。
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
3. 配置
可以用配置文件或管理接口进行配置。
配置ProxySQL的推荐方式是通过它的管理接口(admin interface)。
这样可以通过SQL查询它的管理数据库实现在线配置(无需重启ProxySQL)。这种配置方式非常有效。
3.1 用配置文件配置
配置文件: /etc/proxysql.cnf 说明如下：
顶级sections：
- admin_variables：包含控制管理接口功能的全局变量。
- mysql_variables：包含控制"处理MySQL流入流量功能"的全局变量。
- mysql_servers：包含管理接口中mysql_servers表的行。
- mysql_users：包含管理接口中mysql_users表的行。它们定义了谁可以连接到ProxySQL，以及ProxySQL用哪个用户连接后端服务器。
- mysql_query_rules：包含管理接口中mysql_query_rules表的行。它们根据各种标准(pattern匹配，执行查询的用户身份，等等)定义了对MySQL流量的分类和路由规则。
#修改配置
vim /etc/proxysql.cnf
cat /etc/proxysql.cnf |grep -v '#'|grep -v '^$'
3.2 用admin interface配置
用admin interface配置ProxySQL，ProxySQL的admin管理接口是一个使用MySQL协议的接口，所以，可以直接使用mysql客户端、navicat等工具去连接这个管理接口。
例如，使用mysql客户端去连接ProxySQL的管理接口。下面使用的是管理接口的默认用户名和密码admin:admin。
使用admin凭据连接到本地6032端口的管理接口：
$mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
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)
Admin> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.00 sec)
注意：如果你的MySQL客户端版本为8.04或更高版本，需要在上面连接到管理接口的命令行上加上--default-auth=mysql_native_password。
库说明:
- main库是ProxySQL最主要的库，是需要修改配置时使用的库，它其实是一个内存数据库系统。所以，修改main库中的配置后，必须将其持久化到disk上才能永久保存。
- disk库是磁盘数据库，该数据库结构和内存数据库完全一致。当持久化内存数据库中的配置时，其实就是写入到disk库中。
- stats库是统计信息库。这个库中的数据一般是在检索其内数据时临时填充的，它保存在内存中。因为没有相关的配置项，所以无需持久化。
- monitor库是监控后端MySQL节点相关的库，该库中只有几个log类的表，监控模块收集到的监控信息全都存放到对应的log表中。
- stats_history库是1.4.4版新增的库，用于存放历史统计数据。
3.3 配置生效
ProxySQL的配置几乎都是通过管理接口来操作的，通过Admin管理接口，可以在线修改几乎所有的配置并使其生效。
生效方法示例：
admin> load xxx to runtime; # 使修改立即生效
admin> save xxx to disk; # 使修改永久保存到磁盘
上面的XXX是什么？这表示要加载/保存的是哪类配置。目前的ProxySQL支持以下几种：
[x] mysql users
[x] mysql servers
[x] mysql variables
[x] mysql query rules
[x] admin variables
[x] scheduler
[ ] proxysql_servers：
这些从main库或disk库中就可以查看到。
Admin> show tables from main;
Admin> show tables from disk;
+------------------------------------+
| tables |
+------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| scheduler |
+------------------------------------+
10 rows in set (0.00 sec)
image.png
4.管理接口
4.1 管理接口介绍
当ProxySQL启动后，将监听两个端口：
(1).admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL。
(2).接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口。
image.png
$mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
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)
Admin> show tables from disk;
请注意，ProxySQL数据是存储在SQLite3中，目录/var/lib/proxy下。SQLite3和MySQL使用的SQL语法有所不同，因此能在MySQL上正常运行的命令不一定能在SQLite3上运行。例如，尽管admin管理接口接收USE命令，但不会切换默认的schema，因为SQLite3不支持USE功能。
ProxySQL会尽量将MySQL语法自动调整为对应的SQLite3的语法，例如show databases;会转换为SQLite3上等价的语句。
4.2 管理接口相关的变量
ProxySQL的配置几乎都是通过管理接口来操作的，通过Admin管理接口，可以在线修改几乎所有的配置并使其生效。只有两个变量的配置是必须重启ProxySQL才能生效的，它们是：mysql-threads和mysql-stacksize。
1. admin-admin_credentials
默认的管理员账户和密码为admin:admin，但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL，例如用windows上的navicat连接Linux上的ProxySQL管理接口，必须自定义一个管理员账户。
admin> select @@admin-admin_credentials; # 当前用户名和密码
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin |
+---------------------------+
1 row in set (0.01 sec)
admin> set admin-admin_credentials='admin:admin;myuser:myuser';
admin> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin;myuser:myuser |
+---------------------------+
admin> load admin variables to runtime; # 使修改立即生效
admin> save admin variables to disk; # 使修改永久保存到磁盘
所有的配置操作都是在修改main库中对应的表。
admin> select * from global_variables ;
admin> 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. admin-stats_credentials
该变量控制admin管理接口的普通用户，这个变量中的用户没有超级管理员权限，只能查看monitor库和main库中关于统计的数据，其它库都是不可见的，且没有任何写权限。
默认的普通用户名和密码为stats:stats。
admin> select @@admin-stats_credentials;
+---------------------------+
| @@admin-stats_credentials |
+---------------------------+
| stats:stats |
+---------------------------+
$mysql -ustats -pstats -P6032 -h127.0.0.1 -e 'show databases'
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | monitor | |
| 3 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
3. 用户账号
必须要区分admin,stat管理接口的用户名和mysql_users中的用户名。
- admin管理接口的用户: 是连接到管理接口(默认端口6032)上用来管理、配置ProxySQL的。
- mysql_users表中的用户: 是应用程序连接ProxySQL(默认端口6033)，以及ProxySQL连接后端MySQL Servers使用的用户。它的作用是发送、路由SQL语句，类似于MySQL Server的3306端口。所以，这个表中的用户必须已经在后端MySQL Server上存在且授权了!
#MySQL Master
#grant select,insert,update,delete on *.* to 'mydev'@'%' identified by 'devpass';
#proxy
admin> insert into mysql_users(username,password,active,max_connections) values('mydev','devpass',1,10000);
admin> select username,password,active,default_hostgroup,max_connections from mysql_users;
+----------+----------+--------+-------------------+-----------------+
| username | password | active | default_hostgroup | max_connections |
+----------+----------+--------+-------------------+-----------------+
| mydev | devpass | 1 | 0 | 10000 |
+----------+----------+--------+-------------------+-----------------+
4 . admin-mysql_ifaces
该变量指定admin接口的监听地址，格式为分号分隔的hostname:port列表。默认监听在0.0.0.0:6032。
注意，允许使用UNIX的domain socket进行监听，这样本主机内的应用程序就可以直接被处理。
admin> select @@admin-mysql_ifaces;
+----------------------+
| @@admin-mysql_ifaces |
+----------------------+
| 0.0.0.0:6032 |
+----------------------+
admin> SET admin-mysql_ifaces='0.0.0.0:6032;/tmp/proxysql_admin.sock' ;
Query OK, 1 row affected (0.00 sec)
admin> select @@admin-mysql_ifaces;
5.读写分离
5.1 环境准备
| 角色 | IP | port | server-id |
|---|---|---|---|
| Proxy | 192.110.103.40 | - | - |
| MySQL-Master | 192.110.103.41 | 3306 | 103413306 |
| MySQL-Slave | 192.110.103.42 | 3306 | 103423306 |
| MySQL-Slave | 192.110.103.43 | 3306 | 103433306 |
1)MySQL安装 (略)
2)MySQL授权
为了简便，对大网段192.%授权，密码也相同。线上不能这样做！
#主库授权
alter user root@localhost identified by 'P@ssword1!';
grant replication slave on *.* to repl@'192.%' identified by 'P@ssword1!';
grant replication client on *.* to monitor@'192.% ' identified by 'P@ssword1!';
3)主从同步
#在主库中
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 151 |
+------------------+-----------+
# 以下在slave节点上都执行
>change master to
master_host='192.110.103.41',
master_port=3306,
master_user='repl',
master_password='P@ssword1!',
master_log_file='mysql-bin.000001',
master_log_pos=151;
> start slave;
> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Checking master version
Master_Host: 192.110.103.41
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 151
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.2 ProxySQL添加DB节点
启动后会监听两个端口，默认为6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口。
$service proxysql start
$netstat -tnlp |grep proxysql -i
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 20763/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 20763/proxysql
主要修改main和monitor数据库中的表。
$mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '
$show databases;
admin> show tables from monitor;
admin> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
runtime_开头的是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘。具体操作见后文。
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.110.103.41',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.110.103.42',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.110.103.43',3306);
Admin> select hostgroup_id,hostname,port,status,weight,max_connections from mysql_servers;
+--------------+---------------+------+--------+--------+-----------------+
| hostgroup_id | hostname | port | status | weight | max_connections |
+--------------+---------------+------+--------+--------+-----------------+
| 10 | 192.110.103.41 | 3306 | ONLINE | 1 | 1000 |
| 20 | 192.110.103.43 | 3306 | ONLINE | 1 | 1000 |
| 20 | 192.110.103.42 | 3306 | ONLINE | 1 | 1000 |
+--------------+---------------+------+--------+--------+-----------------+
3 rows in set (0.00 sec)
#修改后，加载到RUNTIME，并保存到disk
admin> load mysql servers to runtime;
admin> save mysql servers to disk;
5.3 监控后端MySQL节点
添加节点之后，还需要监控后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。
首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master，则还需具备replication client权限。这里直接赋予这个权限。
#master
grant replication client on *.* to monitor@'192.% ' identified by 'P@ssword1!';
然后回到ProxySQL上配置监控。
set mysql-monitor_username='monitor'; set mysql-monitor_password='P@ssword1!';
或者:
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';
#检查
admin> select * from global_variables where variable_name like 'mysql-monitor%';
修改后，加载到RUNTIME，并保存到disk。
admin> select * from global_variables where variable_name like 'mysql-monitor%';
+-----------------------------------------------------+----------------+
| variable_name | variable_value |
+-----------------------------------------------------+----------------+
| mysql-monitor_enabled | true |
...
| mysql-monitor_username | monitor |
| mysql-monitor_password | P@ssword1! |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_read_only_timeout | 500 |
+-----------------------------------------------------+----------------+
22 rows in set (0.00 sec)
admin> load mysql variables to runtime;
admin> save mysql variables to disk;
验证监控结果:
ProxySQL监控模块的指标都保存在monitor库的log表中。
以下是连接是否正常的监控(对connect指标的监控)：(在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常)
##
admin> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.110.103.42 | 3306 | 1571329677556524 | 2819 | NULL |
| 192.110.103.41 | 3306 | 1571329678496044 | 2392 | NULL |
| 192.110.103.41 | 3306 | 1571329738369098 | 2707 | NULL |
+---------------+------+------------------+-------------------------+---------------+
#心跳信息的监控(对ping指标的监控)：监控
admin> select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+-------------------------------------------------------------------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+-------------------------------------------------------------------------+
| 192.110.103.42 | 3306 | 1571329127917343 | 0 | Access denied for user 'monitor'@'192.110.103.40' (using password: YES) |
| 192.110.103.41 | 3306 | 1571329137776787 | 786 | NULL |
| 192.110.103.42 | 3306 | 1571329137954593 | 805 | NULL |
| 192.110.103.41 | 3306 | 1571329147776863 | 810 | NULL |
#
但是，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表，该表只有4个字段：第一个字段名为writer_hostgroup，第二个字段为reader_hostgroup，第三个字段为check_type,有super_read_only,read_only等，默认为read_only, 第四字段为注释字段，可随意写。
注意: 1.4x版本和2.x版本相比，少一个check_type字段!
如下，咱们指定写组的id为10，读组的id为20。
#insert into mysql_replication_hostgroups values(10,20,'-'); #1.4x版本
insert into mysql_replication_hostgroups values(10,20,'read_only','-'); #2.x版本
#2.0.x表结构变了。
Admin> select * from sqlite_master where name = "mysql_replication_hostgroups"\G;
Admin> SHOW CREATE TABLE 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))
Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10 | 20 | read_only | - |
+------------------+------------------+------------+---------+
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.110.103.41 | 3306 | ONLINE | 1 |
| 10 | 192.110.103.42 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。
admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
admin> save mysql servers to disk;
Query OK, 0 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的写组。
结果：
insert into mysql_replication_hostgroups values(10,20,'read_only','-');
select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10 | 20 | read_only | - |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)
select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.110.103.41 | 3306 | ONLINE | 1 |
| 10 | 192.110.103.42 | 3306 | ONLINE | 1 |
| 10 | 192.110.103.43 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)
#生效
load mysql servers to runtime;
save mysql servers to disk;
#会依read only自动修改分组
select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.110.103.41 | 3306 | ONLINE | 1 |
| 20 | 192.110.103.43 | 3306 | ONLINE | 1 |
| 20 | 192.110.103.42 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
Admin> select * from mysql_server_read_only_log limit 5;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.110.103.43 | 3306 | 1576595040808568 | 364 | 1 | NULL |
| 192.110.103.41 | 3306 | 1576595040821383 | 247 | 0 | NULL |
| 192.110.103.42 | 3306 | 1576595040834224 | 203 | 1 | NULL |
| 192.110.103.41 | 3306 | 1576595042308511 | 237 | 0 | NULL |
| 192.110.103.43 | 3306 | 1576595042321244 | 305 | 1 | NULL |
+---------------+------+------------------+-----------------+-----------+-------+
5.4 配置mysql_users
mysql_users表中的用户名是应用程序连接ProxySQL(默认端口6033)，以及ProxySQL连接后端MySQL Servers使用的用户。
mysql_users配置都是关于后端MySQL节点的，现在可以配置：发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。
本小节是SQL请求所使用的用户配置，例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例。
#在master节点上执行
grant all on *.* to root@'192.%' identified by 'P@ssword1!';
grant all on *.* to sqlsender@'192.%' identified by 'P@ssword1!';
#然后回到ProxySQL，配置mysql_users表，将刚才的两个用户添加到该表中。
insert into mysql_users(username,password,default_hostgroup) values('root','P@ssword1!',10);
insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
load mysql users to runtime;
save mysql users to disk;
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组中的某个节点。
admin> select * from mysql_users;
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| mydev | devpass | 1 | 0 | 0 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| root | P@ssword1! | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| sqlsender | P@ssword1! | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
3 rows in set (0.00 sec)
注意transaction_persistent，active 字段状态
- 只有active=1的用户才是有效的用户。
- transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组(更进一步的，它会自动禁用multiplexing，让同一个事务的语句从同一个连接路由出去，保证路由到同一个组的同一个节点)。我们期望的值为1。
#若不是1就可修改为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;
5.5 读写分离测试
另开一个终端，分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 103413306 |
+-------------+
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "create database proxy_test"
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| proxy_test |
| test |
+--------------------+
mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 proxy_test -e 'create table t(id int);'
mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 proxy_test -e "show tables;select @@server_id;"
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t |
+----------------------+
+-------------+
| @@server_id |
+-------------+
| 103413306 |
+-------------+
6. 读写分离
6.1 配置路由规则
ProxySQL的路由规则非常灵活，可以基于用户、基于schema以及基于每个语句实现路由规则的定制。
说明： 实际的路由规则绝不应该仅根据所谓的读、写操作进行分离，而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。
和查询规则有关的表有两个：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表。
插入两个规则，目的是将select语句分离到hostgroup_id=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组。
mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 proxy_test -e 'create table t(id int);'
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);
load mysql query rules to runtime;
save mysql query rules to disk;
admin> select * from runtime_mysql_query_rules\G
select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配。
再来测试下，读操作是否路由给了hostgroup_id=20的读组。
#测试发现会读从库. serverid=103423306
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
| 103423306 |
+-------------+
读操作已经路由给读组，再看看写操作。这里以事务持久化进行测试。
#测试写
mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit; select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 103413306 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 103423306 |
+-------------+
最后，如果想查看路由的信息，可查询stats库中的stats_mysql_query_digest表。
admin> select * from stats_mysql_query_digest ORDER BY sum_time DESC limit 10;
+-----------+--------------------+-----------+--------------------+----------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+-----------+--------------------+----------------------------+------------+------------+------------+----------+----------+----------+
| 10 | information_schema | root | 0xDA65260DF35B8D13 | select @@server_id | 5 | 1571367177 | 1571380746 | 10598 | 999 | 4438 |
| 10 | proxy_test | sqlsender | 0x4E6025FB1E51A2E5 | create table t(id int) | 1 | 1571367516 | 1571367516 | 8773 | 8773 | 8773 |
| 20 | information_schema | root | 0xC5C9CBEB12CE8379 | select now() | 1 | 1571380799 | 1571380799 | 8461 | 8461 | 8461 |
| 10 | proxy_test | sqlsender | 0x99531AEFF718C501 | show tables | 3 | 1571367527 | 1571380007 | 5740 | 1215 | 3290 |
| 20 | information_schema | root | 0xDA65260DF35B8D13 | select @@server_id | 4 | 1571380562 | 1571380799 | 5473 | 714 | 3142 |
| 10 | information_schema | root | 0x02033E45904D3DF0 | show databases | 2 | 1571367466 | 1571379999 | 4969 | 1363 | 3606 |
| 10 | information_schema | root | 0x326F4F2B935EC266 | start transaction | 2 | 1571380704 | 1571380746 | 2162 | 1058 | 1104 |
| 10 | proxy_test | sqlsender | 0xDA65260DF35B8D13 | select @@server_id | 2 | 1571367546 | 1571380007 | 2123 | 1054 | 1069 |
| 10 | information_schema | root | 0xDB3A841EF5443C35 | commit | 2 | 1571380704 | 1571380746 | 1994 | 973 | 1021 |
| 10 | information_schema | root | 0x871B1AB1144F72C8 | create database proxy_test | 1 | 1571367436 | 1571367436 | 1233 | 1233 | 1233 |
+-----------+--------------------+-----------+--------------------+----------------------------+------------+------------+------------+----------+----------+----------+
10 rows in set (0.00 sec)
参考:
- 官方网站:https://proxysql.com/
- https://github.com/sysown/proxysql/wiki#getting-started
- https://github.com/sysown/proxysql/wiki
- 中文文档 https://github.com/malongshuai/proxysql/wiki
- 骏马金龙 https://www.cnblogs.com/f-ck-need-u/p/7586194.html#middleware
陶老师运维笔记
