ProxySQL搭建和使用

作者: YSoSeriS | 来源:发表于2019-11-15 10:11 被阅读0次

ProxySQL搭建和使用
Mysql实战之主从复制的读写分离
ProxySQL简单实现MGR读写分离
ProxySQL手册_06密码管理
mysql主从复制读写分离
ProxySQL企业实战应用
MySQL课程-ProxySQL企业实战应用
ProxySQL手册_01安装使用
基于1主1从搭建ProxySQL
2021.09.20 [MySQL 读写分离]使用中间件Prox

1、ProxySQL简介

ProxySQL是用C++语言开发的，虽然也是一个轻量级产品，但性能很好(据测试，能处理千亿级的数据)，功能也足够，能满足中间件所需的绝大多数功能，包括：
最基本的读/写分离，且方式有多种。

可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding。
可缓存查询结果。虽然ProxySQL的缓存策略比较简陋，但实现了基本的缓存功能，绝大多数时候也够用了。此外，作者已经打算实现更丰富的缓存策略。
监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性(replication lag)。

2、ProxySQL安装

rpm包地址：https://github.com/sysown/proxysql/releases

rpm -ivh proxysql-2.0.8-1-centos7.x86_64.rpm

安装过程可能会报错，按要求去安装所需要的依赖包即可

service proxysql start      # 启动proxysql
service proxysql stop       # 停止proxysql
service proxysql status     # 查看proxysql状态
netstat -nltp               # 查看主机端口占用情况

3、实现读写分离

角色	主机IP	server_id
proxysql	172.16.66.131	null
master	172.16.66.129	1721666129
slave	172.16.66.130	1721666130

slave节点的read_only值设为1，主从环境搭建这边就不演示了，proxysql的主机也是需要安装mysql客户端的

service proxysql start

启动后会监听两个端口，6032和6033，6032是管理端口，6033是对外提供服务的端口

[root@localhost app]# netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      16831/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      16831/proxysql      
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      7017/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      7264/master         
tcp6       0      0 :::3306                 :::*                    LISTEN      17536/mysqld        
tcp6       0      0 :::22                   :::*                    LISTEN      7017/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      7264/master

使用mysql客户端连接到proxysql的管理端口，该端口的默认账号密码为admin。

[root@localhost app]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --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)

监控后端MySQL节点

首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master，则还需具备replication client权限。这里直接赋予这个权限。

# 在master节点上执行
grant replication client on *.* to monitor@'%' identified by '123456';

回到proxysql节点配置监控

set mysql-monitor_username='monitor';  
set mysql-monitor_password='123456';

修改完成后，加载到runtime，保存到磁盘
注意：要修改ProxySQL的配置，需要修改了非runtime_表，修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘

load mysql variables to run;    # runtime可简写为run
save mysql variables to disk;

插入数据到mysql_replication_hostgroups表，设置读组和写组（我这边设置200为写组，100为读组，可自行定义，验证方法为查看数据库read_only的参数）

insert into mysql_replication_hostgroups values(200,100);
load mysql servers to run;
save mysql servers to disk;

Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 200              | 100              | read_only  |         |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

将主机信息插入到mysql_servers(在main库下)表：

# 这里一开始设置的hostgroup_id不对也是没关系的，proxysql会根据read_only的值，自动更改组
insert into mysql_servers(hostgroup_id,hostname,port) values(200,'172.16.66.129',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(100,'172.16.66.130',3306);

# 修改完数据记得加载到runtime，保存到disk
load mysql servers to run;
save mysql servers to disk;

到这可以先去查看监控数据是否正常（监控日志在monitor库下）

admin> select * from mysql_server_connect_log;
+---------------+------+------------------+--------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us  | connect_error |
+---------------+------+------------------+--------------------------+---------------+
| 172.16.66.129 | 3306 | 1573299207432672 | 35796                    | NULL          |
| 172.16.66.130 | 3306 | 1573299208376188 | 44216                    | NULL          |
| 172.16.66.129 | 3306 | 1573299267433619 | 30093                    | NULL          |
| 172.16.66.130 | 3306 | 1573299268194739 | 96201                    | NULL          |
| 172.16.66.129 | 3306 | 1573299327434140 | 18877                    | NULL          |
| 172.16.66.130 | 3306 | 1573299387435310 | 251356                   | NULL          |
+---------------+------+------------------+--------------------------+---------------+

Admin> select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+-----------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                          |
+---------------+------+------------------+----------------------+-----------------------------------------------------+
| 172.16.66.129 | 3306 | 1573299533515702 | 12119                | NULL                                                |
| 172.16.66.130 | 3306 | 1573299533692031 | 7846                 | NULL                                                |
| 172.16.66.129 | 3306 | 1573299543652807 | 432875               | NULL                                                |
| 172.16.66.130 | 3306 | 1573299543517355 | 432837               | NULL                                                |
| 172.16.66.129 | 3306 | 1573299553517804 | 13914                | NULL                                                |
| 172.16.66.130 | 3306 | 1573299553673869 | 248019               | NULL                                                |
| 172.16.66.129 | 3306 | 1573299563518580 | 9857                 | NULL                                                |
+---------------+------+------------------+----------------------+-----------------------------------------------------+


Admin> select * from mysql_server_read_only_log;
+---------------+------+------------------+-----------------+-----------+---------------+
| hostname      | port | time_start_us    | success_time_us | read_only | error         |
+---------------+------+------------------+-----------------+-----------+---------------+
| 172.16.66.129 | 3306 | 1573299711290468 | 11956           | 0         | NULL          |
| 172.16.66.130 | 3306 | 1573299711316458 | 8303            | 1         | NULL          |
| 172.16.66.129 | 3306 | 1573299712791164 | 14511           | 0         | NULL          |
| 172.16.66.130 | 3306 | 1573299712811574 | 286420          | 1         | NULL          |
| 172.16.66.129 | 3306 | 1573299714291969 | 6007            | 0         | NULL          |
| 172.16.66.130 | 3306 | 1573299714312120 | 9083            | 1         | NULL          |
| 172.16.66.129 | 3306 | 1573299715793505 | 13514           | 0         | NULL          |
| 172.16.66.130 | 3306 | 1573299715820894 | 4221            | 1         | NULL          |
| 172.16.66.129 | 3306 | 1573299717295711 | 14686           | 0         | NULL          |

添加mysql_users

proxysql上的添加的用户，在后端实际库上也需要有
在master节点添加用户

grant all on *.* to sqlsender@'%' identified by '123456';

到proxysql中配置mysql_users表，将该用户添加到该表中

insert into mysql_users(username,password,default_hostgroup) values('sqlsender','123456',200);
load mysql users to run;
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用户的该字段值为200时，则使用root用户发送的SQL语句默认情况下将路由到hostgroup_id=200组中的某个节点。

配置路由规则

和查询规则有关的表有两个：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后才支持该快速路由表。本文只介绍第一个表。

插入两个规则，目的是将select语句分离到hostgroup_id=100的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=200的写组。

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',200,1),(2,1,'^SELECT',100,1);

load mysql query rules to run;
save mysql query rules to disk;

测试

# 使用上面新建的用户登陆，这边我在proxysql的主机登陆，如果远程登陆，修改host
[root@localhost app]# mysql -usqlsender -p123456 -h127.0.0.1 -P6033

执行SQL验证下

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  1721666130 |
+-------------+
1 row in set (0.03 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  1721666129 |
+-------------+
1 row in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.10 sec)

proxysql中stats库的stats_mysql_query_digest表能够看到sql用哪组执行的,执行错的sql也记录了～～

+-----------+--------------------+-----------+----------------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname         | username  | client_address | digest             | digest_text                            | count_star | first_seen | last_seen  | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 200       | information_schema | sqlsender |                | 0xBA1ADF966D0B70F4 | show databses                          | 1          | 1573302271 | 1573302271 | 249218   | 249218   | 249218   | 0                 | 0             |
| 200       | information_schema | sqlsender |                | 0xDB3A841EF5443C35 | commit                                 | 3          | 1573287023 | 1573303084 | 112696   | 5156     | 100008   | 0                 | 0             |
| 200       | information_schema | sqlsender |                | 0xA592C94A099E89DC | begin                                  | 3          | 1573287013 | 1573303073 | 270854   | 8279     | 248770   | 0                 | 0             |
| 200       | information_schema | sqlsender |                | 0x22FB45EDE5889AB6 | save mysql servers to disk             | 1          | 1573286991 | 1573286991 | 18596    | 18596    | 18596    | 0                 | 0             |
| 100       | information_schema | sqlsender |                | 0xBBCAD24E589BBDFC | select @server_id                      | 1          | 1573286839 | 1573286839 | 26919    | 26919    | 26919    | 0                 | 1             |
| 200       | information_schema | sqlsender |                | 0x02033E45904D3DF0 | show databases                         | 2          | 1573286823 | 1573302276 | 48100    | 4638     | 43462    | 0                 | 10            |
| 200       | information_schema | sqlsender |                | 0x226CD90D52A2BA0B | select @@version_comment limit ?       | 4          | 1573286817 | 1573302950 | 0        | 0        | 0        | 0                 | 0             |
| 200       | information_schema | sqlsender |                | 0xDA65260DF35B8D13 | select @@server_id                     | 2          | 1573287020 | 1573303080 | 17657    | 4558     | 13099    | 0                 | 2             |
| 100       | information_schema | sqlsender |                | 0xDA65260DF35B8D13 | select @@server_id                     | 5          | 1573219445 | 1573302956 | 321986   | 12259    | 231307   | 0                 | 5             |
| 100       | information_schema | sqlsender |                | 0x86268ED4E024722E | select * from proxysql_test.t1         | 1          | 1573217634 | 1573217634 | 4509     | 4509     | 4509     | 0                 | 1             |
| 100       | information_schema | sqlsender |                | 0x70EA069B84F3D47C | select * from proxysql_test.ti         | 1          | 1573217631 | 1573217631 | 22640    | 22640    | 22640    | 0                 | 0             |
| 200       | information_schema | sqlsender |                | 0xED8D7E79F579ED8F | insert into proxysql_test.t1 values(?) | 1          | 1573217619 | 1573217619 | 26971    | 26971    | 26971    | 1                 | 0             |
| 200       | information_schema | sqlsender |                | 0x56F3F22AF40F6008 | insert into proxysql_test.ti values(?) | 1          | 1573217614 | 1573217614 | 12254    | 12254    | 12254    | 0                 | 0             |
| 200       | information_schema | sqlsender |                | 0xF730D9409A64CBB1 | create table proxysql_test.t1(id int)  | 1          | 1573217587 | 1573217587 | 83468    | 83468    | 83468    | 0                 | 0             |
| 200       | information_schema | sqlsender |                | 0xB07C8ECC77AB77C6 | create database proxysql_test          | 1          | 1573217533 | 1573217533 | 56059    | 56059    | 56059    | 1                 | 0             |
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+

admin-admin_credentials

该变量控制的是admin管理接口的管理员账户。默认的管理员账户和密码为admin:admin，但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL，例如用windows上的navicat连接Linux上的ProxySQL管理接口，必须自定义一个管理员账户。

例如，添加一个myuser:myuser的用户密码对。

admin> select @@admin-admin_credentials;    # 当前用户名和密码
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin               |
+---------------------------+

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 run;      # 使修改立即生效
admin> save admin variables to disk;     # 使修改永久保存到磁盘

修改后，就可以使用该用户名和密码连接管理接口。

mysql -umyuser -pmyuser -P6032 -h127.0.0.1 --prompt 'admin> '
所有的配置操作都是在修改main库中对应的表。

select * from global_variables 
 where variable_name='admin-admin_credentials';
+-------------------------+---------------------------+
| variable_name           | variable_value            |
+-------------------------+---------------------------+
| admin-admin_credentials | admin:admin;myuser:myuser |
+-------------------------+---------------------------+

ProxySQL搭建和使用
1、ProxySQL简介 ProxySQL是用C++语言开发的，虽然也是一个轻量级产品，但性能很好(据测试，能处理...
Mysql实战之主从复制的读写分离
ProxySQL构建主从复制的读写分离 ProxySQL官网及下载地址 http://www.proxysql.c...
ProxySQL简单实现MGR读写分离
一. ProxySQL 1.1简介 ProxySQL是MySQL、Percona Server、MariaDB的高...
ProxySQL手册_06密码管理
ProxySQL是一个协议感知的proxy。由于ProxySQL基于流量进行路由，当一个客户端连接ProxySQL...
mysql主从复制读写分离
实现主从复制的读写分离 ProxySQL官网及下载地址 http://www.proxysql.com/ 架构角色...
ProxySQL企业实战应用
安装配置 1、安装perl的软件包支持 2、下载proxySQL 3、安装 4、启动 5、ProxySQL配置...
MySQL课程-ProxySQL企业实战应用
安装配置 1、安装perl的软件包支持 2、下载proxySQL 3、安装 4、启动 5、ProxySQL配置...
ProxySQL手册_01安装使用
转载来自骏马金龙 https://www.junmajinlong.com[https://www.junmaj...
基于1主1从搭建ProxySQL
基于1主1从搭建ProxySQL1、环境2、数据库设置3、安装ProxySQL4、创建分组5、配置服务：...
2021.09.20 [MySQL 读写分离]使用中间件Prox
一、ProxySQL 简介 ProxySQL 是一个高性能、高可用性的数据库中间件，本人尝试过Mycat、Gaea...