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 |
+-------------------------+---------------------------+
