MySQL中间件ProxySQL2.x安装及测试

陶老师运维笔记 2019-12-17

3604

MySQL中间件ProxySQL2.x安装及测试

1.简介

官方网站:https://proxysql.com/
Doc : https://github.com/sysown/proxysql/wiki#getting-started

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流入流量功能"的全局变量。
mysqlservers：包含管理接口中mysqlservers表的行。
mysqlusers：包含管理接口中mysqlusers表的行。它们定义了谁可以连接到ProxySQL，以及ProxySQL用哪个用户连接后端服务器。
mysqlqueryrules：包含管理接口中mysqlqueryrules表的行。它们根据各种标准(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=mysqlnativepassword。

库说明:

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)

4.管理接口

4.1 管理接口介绍

当ProxySQL启动后，将监听两个端口：(1).admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL。(2).接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口。

$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节点

https://github.com/sysown/proxysql/wiki/MySQL-Server-Configuration
https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

启动后会监听两个端口，默认为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指标的监控)：(在前面可能会有很多connecterror，这是因为没有配置监控信息时的错误，配置后如果connecterror的结果为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                                                                    |

#

但是，readonly和replicationlag的监控日志都为空。

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中的节点分组：writerhostgroup、readerhostgroup。

设置分组信息，需要修改的是main库中的mysqlreplicationhostgroups表，该表只有4个字段：第一个字段名为writerhostgroup，第二个字段为readerhostgroup，第三个字段为checktype,有superreadonly,readonly等，默认为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      |
+--------------+---------------+------+--------+--------+

将刚才mysqlreplicationhostgroups表的修改加载到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模块就会开始监控后端的readonly值，当监控到readonly值后，就会按照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;

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

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

说明：实际的路由规则绝不应该仅根据所谓的读、写操作进行分离，而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。

和查询规则有关的表有两个：mysqlqueryrules和mysqlqueryrulesfastrouting，后者是前者的扩展表。

插入两个规则，目的是将select语句分离到hostgroupid=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroupid=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规则的ruleid必须要小于普通的select规则的ruleid，因为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库中的statsmysqlquery_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://juejin.im/post/5df83974e51d4557e76a657c
官方网站: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

mysql

最后修改时间：2019-12-18 15:01:57

文章转载自陶老师运维笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL中间件ProxySQL2.x安装及测试

MySQL中间件ProxySQL2.x安装及测试

1.简介

2.安装

3. 配置

3.1 用配置文件配置

3.2 用admin interface配置

3.3 配置生效

4.管理接口

4.1 管理接口介绍

4.2 管理接口相关的变量

5.读写分离

5.1 环境准备

5.2 ProxySQL添加DB节点

5.3 监控后端MySQL节点

5.4 配置mysql_users

5.5 读写分离测试

6. 读写分离

6.1 配置路由规则

参考:

评论