一、Proxysql（Centos7）下载安装
1. 下载
github proxysql-2.0.10-1-centos7.x86_64.rpm
阿里云 proxysql-2.0.10-1-centos7.x86_64.rpm
2. 把下载好的rpm文件移动到主目录（home）
3. 打开命令终端窗口
[root@mn home]# rpm -i proxysql-2.0.10-1-centos7.x86_64.rpm
# 警告：群组proxysql 不存在 - 使用root
# 警告：群组proxysql 不存在 - 使用root
# Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.
#设置proxysql服务开机自启
systemctl enable proxysql
#取消proxysql服务开机自启
systemctl disable proxysql
# 查询proxysql是否开机启动 enabled 代表开机自启
systemctl list-unit-files | grep proxy
# proxysql.service enabled
4.安装依赖包
# 错误：依赖检测失败：
/usr/bin/perl 被 proxysql-2.0.10-1.x86_64 需要
libgnutls.so.28()(64bit) 被 proxysql-2.0.10- 1.x86_64 需要
libgnutls.so.28(GNUTLS_1_4)(64bit) 被 proxysql- 2.0.10-1.x86_64 需要
libgnutls.so.28(GNUTLS_3_0_0)(64bit) 被 proxysql- 2.0.10-1.x86_64 需要
libgnutls.so.28(GNUTLS_3_1_0)(64bit) 被 proxysql- 2.0.10-1.x86_64 需要
perl(DBD::mysql) 被 proxysql-2.0.10-1.x86_64 需要
perl(DBI) 被 proxysql-2.0.10-1.x86_64 需要
perl(constant) 被 proxysql-2.0.10-1.x86_64 需要
perl(strict) 被 proxysql-2.0.10-1.x86_64 需要
perl(vars) 被 proxysql-2.0.10-1.x86_64 需要
perl(warnings) 被 proxysql-2.0.10-1.x86_64 需要
# 安装依赖包
yum install gnutls;
yum install perl;
yum install perl-DBD-MySQL;
二、开启ProxySQL，并连接
# 启动proxysql
[root@mn ~]# systemctl start proxysql
# 连接proxysql的管理
mysql -uadmin -padmin -h127.0.0.1 -P6032
# Welcome to the MariaDB monitor. Commands end with ; or \g.
# Your MySQL connection id is 1
# Server version: 5.5.30 (ProxySQL Admin Module)
# Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
# Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# MySQL [(none)]>
# 查询一下管理数据库
MySQL [(none)]> 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.01 sec)
其中：
main库是ProxySQL最主要的库，是需要修改配置时使用的库，它其实是一个内存数据库系统。所以，修改main库中的配置后，必须将其持久化到disk上才能永久保存
disk库是磁盘数据库，该数据库结构和内存数据库完全一致。当持久化内存数据库中的配置时，其实就是写入到disk库中。磁盘数据库的默认路径为 $DATADIR/proxysql.db
stats库是统计信息库。这个库中的数据一般是在检索其内数据时临时填充的，它保存在内存中。因为没有相关的配置项，所以无需持久化
monitor库是监控后端MySQL节点相关的库，该库中只有几个log类的表，监控模块收集到的监控信息全都存放到对应的log表中
stats_history库是1.4.4版新增的库，用于存放历史统计数据。默认路径为 $DATADIR/proxysql_stats.db
由于 ProxySQL 的配置全部保存在几个自带的库中，所以通过管理接口，可以非常方便地通过发送一些SQL命令去修改 ProxySQL 的配置。 ProxySQL 会解析通过该接口发送的某些对ProxySQL 有效的特定命令，并将其合理转换后发送给内嵌的 SQLite3 数据库引擎去运行
ProxySQL 的配置几乎都是通过管理接口来操作的，通过 Admin 管理接口，可以在线修改几乎所有的配置并使其生效。只有两个变量的配置是必须重启 ProxySQL 才能生效的，它们是：
**mysql-threads 和 mysql-stacksize**
三、添加管理员帐户
admin-admin_credentials 变量控制的是admin管理接口的管理员账户。默认的管理员账户和密码为admin:admin，但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL，例如用windows上的navicat连接Linux上的ProxySQL管理接口，必须自定义一个管理员账户。
# 查询现有的管理员账户
MySQL [(none)]> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin; |
+---------------------------+
1 row in set (0.01 sec)
# 添加新的mn账户，此账户可以用IP远程连接，admin只能在本机
# 可以使用命令或者小绿叶连接：
# mysql -uadmin -padmin -h127.0.0.1 -P6032
MySQL [(none)]> set admin-admin_credentials='admin:admin;mn:123456';
Query OK, 1 row affected (0.01 sec)
# 重新查询，已添加mn账户
MySQL [(none)]> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin;mn:123456 |
+---------------------------+
1 row in set (0.01 sec)
# 使修改立即生效
MySQL [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
# 是修改持久化到磁盘
MySQL [(none)]> save admin variables to disk;
Query OK, 35 rows affected (0.02 sec)
# **所有的配置操作都是在修改main库中对应的表**
MySQL [(none)]> select * from global_variables where variable_name='admin-admin_credentials';
+-------------------------+-----------------------+
| variable_name | variable_value |
+-------------------------+-----------------------+
| admin-admin_credentials | admin:admin;mn:123456 |
+-------------------------+-----------------------+
1 row in set (0.01 sec)
必须要区分admin管理接口的用户名和mysql_users中的用户名
admin管理接口的用户是连接到管理接口(默认端口6032)上用来管理、配置ProxySQL的
mysql_users表中的用户名是应用程序连接ProxySQL(默认端口6033)，以及ProxySQL连接后端MySQL Servers使用的用户。它的作用是发送、路由SQL语句，类似于MySQL Server的3306端口。所以，这个表中的用户必须已经在后端MySQL Server上存在且授权了
admin管理接口的用户必须不能存在于mysql_users中，这是出于安全的考虑，防止通过admin管理接口用户猜出mysql_users中的用户
四、普通管理帐户（主要原因是admin账户权限太大，如果需要建几个普通账户）
- admin-stats_credentials 变量控制admin管理接口的普通用户，这个变量中的用户没有超级管理员权限，只能查看monitor库和main库中关于统计的数据，其它库都是不可见的，且没有任何写权限
默认的普通用户名和密码均为 stats ，与admin一样，它默认也只能用于本地登录，若想让人远程查看则要添加查看的专有用户
MySQL [(none)]> select @@admin-stats_credentials;
+---------------------------+
| @@admin-stats_credentials |
+---------------------------+
| stats:stats; |
+---------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> set admin-stats_credentials='stats:stats;zhtstats:123456';
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> select @@admin-stats_credentials;
+---------------------------+
| @@admin-stats_credentials |
+---------------------------+
| stats:stats; |
+---------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> set admin-stats_credentials='stats:stats;zhtstats:123456';
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save admin variables to disk;
Query OK, 35 rows affected (0.01 sec)
MySQL [(none)]> select @@admin-stats_credentials;
+-----------------------------+
| @@admin-stats_credentials |
+-----------------------------+
| stats:stats;zhtstats:123456 |
+-----------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save admin variables to disk;
Query OK, 35 rows affected (0.01 sec)
############################################
# 用zhtstats账户登录，查询状态
[root@mn ~]# mysql -uzhtstats -p123456 -h192.168.16.248 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> 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_errors |
| stats_mysql_errors_reset |
| stats_mysql_free_connections |
| stats_mysql_global |
| stats_mysql_gtid_executed |
| 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 |
+--------------------------------------+
19 rows in set (0.00 sec)
五、Proxysql管理端（端口6032）添加读写分离操作数据库的用户，此用户必须已经在数据库中添加了，并且分配了权限了
-- 先在mysql数据库（三个物理机器）中添加rp root 用户
-- 给 write read1 read2 三台数据库授权，以下语句三台数据库都要执行
-- ---------------------------------------------------------
-- 修改'root'@'localhost'用户的密码为dmjjy1710
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dmjjy1710';
-- ---------------------------------------------------------
-- 授权所有用户远程访问权限
CREATE USER 'root'@'%' IDENTIFIED BY 'dmjjy1710';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;
-- ---------------------------------------------------------
-- 创建'rp'@'%' 用户并授权，用于主从复制
CREATE USER 'rp'@'%' IDENTIFIED BY 'dmjjy1710';
GRANT ALL PRIVILEGES ON *.* TO 'rp'@'%' WITH GRANT OPTION;
flush privileges;
-- 把已经授权的rp、root用户添加到proxysql的用户里面，这个要在管理接口 6032 中添加
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('root','dmjjy1710',1,0);
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('rp','dmjjy1710',1,0);
MySQL [(none)]> select * from mysql_users\G;
*************************** 1. row ***************************
username: root # 后端mysql实例的用户名
password: dmjjy1710 # 后端mysql实例的密码
active: 1 # active=1表示用户生效，0表示不生效
use_ssl: 0
default_hostgroup: 1 # 用户默认登录到哪个hostgroup_id下的实例
default_schema: NULL # 用户默认登录后端mysql实例时连接的数据库，这个地方为NULL的话，则由全局变量mysql-default_schema决定，默认是information_schema
schema_locked: 0
transaction_persistent: 1 # 如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不论是否会匹配上其它路由规则，直到事务结束。虽然默认是0
fast_forward: 0 # 忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .* 就行了
backend: 1
frontend: 1
max_connections: 10000 # 该用户允许的最大连接数
comment:
*************************** 2. row ***************************
username: rp
password: dmjjy1710
active: 1
use_ssl: 0
default_hostgroup: 1
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
comment:
2 rows in set (0.00 sec)
# 立即加载生效
MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
# 持久化到磁盘
MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)
-- 在master机器上创建'monitor'@'%' 用户并授权只读权限，用于监控proxysql的监控情况
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT ON *.* TO 'monitor'@'%' WITH GRANT OPTION;
flush privileges;
-- 在proxysql的管理接口6032上设置monitor用户
MySQL [(none)]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save mysql variables to disk;
Query OK, 143 rows affected (0.03 sec)
六、Proxysql管理端（端口6032）添加mysql主机
把读写分离的机器插入到mysql_servers表
使用 insert 语句添加 mysql 主机到 mysql_servers 表中，其中：hostgroup_id 1 表示写组，2表示读组
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment)values(1,'192.168.16.253',17100,1,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment)values(2,'192.168.16.252',17100,1,'Read Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment)values(2,'192.168.16.250',17100,1,'Read Group');
# 查询mysql主机
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight,max_connections,comment from mysql_servers;
+--------------+----------------+-------+--------+--------+-----------------+-------------+
| hostgroup_id | hostname | port | status | weight | max_connections | comment |
+--------------+----------------+-------+--------+--------+-----------------+-------------+
| 1 | 192.168.16.253 | 17100 | ONLINE | 1 | 1000 | Write Group |
| 2 | 192.168.16.252 | 17100 | ONLINE | 1 | 1000 | Read Group |
| 2 | 192.168.16.250 | 17100 | ONLINE | 1 | 1000 | Read Group |
+--------------+----------------+-------+--------+--------+-----------------+-------------+
3 rows in set (0.00 sec)
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.09 sec)
-- 设置最大连接数
-- 查询最大连接数
select @@mysql-max_connections;
-- 设置最大连接数
set mysql-max_connections=6000;
load mysql servers to runtime;
save mysql servers to disk;
七、Proxysql管理端（端口6032）添加读写分离的路由规则
delete from mysql_query_rules;
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1000,1,'^SELECT.*FOR UPDATE$',1,1);
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (2000,1,"\/\*\s*shard_host_HG=.*Write\s*\*.",1,1);
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (3000,1,"\/\*\s*shard_host_HG=.*Read\s*\*.",2,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(4000,1,'^SELECT',2,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(5000,1,'^SHOW',2,1);
select rule_id,active,match_digest,match_pattern,destination_hostgroup,apply from mysql_query_rules;
load mysql query rules to runtime;
load admin variables to runtime;
save mysql query rules to disk;
save admin variables to disk;
MySQL [(none)]> select rule_id,active,match_digest,match_pattern,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+------------------------------------+-----------------------+-------+
| rule_id | active | match_digest | match_pattern | destination_hostgroup | apply |
+---------+--------+----------------------+------------------------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | NULL | 1 | 1 |
| 2 | 1 | NULL | \/\*\s*shard_host_HG=.*Write\s*\*. | 1 | 1 |
| 3 | 1 | NULL | \/\*\s*shard_host_HG=.*Read\s*\*. | 2 | 1 |
| 4 | 1 | ^SELECT | NULL | 2 | 1 |
| 5 | 1 | ^SHOW | NULL | 2 | 1 |
+---------+--------+----------------------+------------------------------------+-----------------------+-------+
八、Proxysql验证读写分离是否生效
-- proxysql有个类似审计的功能，可以查看各类SQL的执行情况，其需要在proxysql管理端执行
select * from stats_mysql_query_digest;
-- 查询语句可以加hostgroup过滤 或者 sql 关键字like过滤 ，查一下走的是哪个节点
九、排错
ProxySQL 排错 Max connect timeout reached while reaching hostgroup 10 after 10000ms
检查proxysql.log 日志 /var/lib/proxysql/proxysql.log
monitor_ping(): [ERROR] Server
解决方案：
① 检查ping命令是否通，在不同的windows机器上执行
netsh firewall set icmpsetting 8 命令开启ping功能
② 检查数据库端口防火墙是否开放
③ 在各个mysql数据库中my.ini[mysqld]节点下加上 skip_name_resolve=1
十、部署SQL汇总
-- mysql -admin -padmin -P6032 -h127.0.0.1 进入mysql，执行下面的sql
-- 添加新的mn（admin管理）、zhtstats（状态查询，权限低）账户，此账户可以用IP远程连接，admin、stats只能在本机
-- 可以使用命令或者小绿叶连接：
set admin-admin_credentials='admin:admin;mn:123456';
set admin-stats_credentials='stats:stats;zhtstats:123456';
-- 使修改立即生效
load admin variables to runtime;
-- 持久化到磁盘
save admin variables to disk;
-- 插入mysql用户，此用户已经在mysql的各数据库添加并分配了权限
-- 修改'root'@'localhost'用户的密码为dmjjy1710
-- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dmjjy1710';
-- ---------------------------------------------------------
-- 授权所有用户远程访问权限
-- CREATE USER 'root'@'%' IDENTIFIED BY 'dmjjy1710';
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
-- flush privileges;
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('root','dmjjy1710',1,0);
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('rp','dmjjy1710',1,0);
load mysql users to runtime;
save mysql users to disk;
-- 设置最大连接数
-- 查询最大连接数
select @@mysql-max_connections;
-- 设置最大连接数
set mysql-max_connections=6000;
load mysql servers to runtime;
save mysql servers to disk;
-- 设置monitor用户，此用户已经在mysql的写数据库添加并分配了只读权限，可以不配置
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT ON *.* TO 'monitor'@'%' WITH GRANT OPTION;
flush privileges;
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;
-- 把读写分离的机器插入到mysql_servers表，其中：hostgroup_id 1 表示写组，2表示读组
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,comment)values(1,'192.168.16.253',17100,1,2000,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,comment)values(2,'192.168.16.252',17100,1,2000,'Read Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,comment)values(2,'192.168.16.250',17100,1,2000,'Read Group');
select hostgroup_id,hostname,port,status,weight,max_connections,comment from mysql_servers;
load mysql servers to runtime;
save mysql servers to disk;
-- 添加读写分离的路由规则
delete from mysql_query_rules;
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (2,1,"\/\*\s*shard_host_HG=.*Write\s*\*.",1,1);
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (3,1,"\/\*\s*shard_host_HG=.*Read\s*\*.",2,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(4,1,'^SELECT',2,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(5,1,'^SHOW',2,1);
select rule_id,active,match_digest,match_pattern,destination_hostgroup,apply from mysql_query_rules;
load mysql query rules to runtime;
load admin variables to runtime;
save mysql query rules to disk;
save admin variables to disk;
-- 验证是否成功
-- proxysql有个类似审计的功能，可以查看各类SQL的执行情况，其需要在proxysql管理端执行
select * from stats_mysql_query_digest;
-- 查询语句可以加hostgroup过滤 或者 sql 关键字like过滤 ，查一下走的是哪个节点