MySQL高可用解决方案（6）数据库中间件之ProxySQL

一、ProxySQL特性介绍

1、连接池控制功能，避免连接数异常增长导致数据库异常

2、可以基于端口、用户甚至具体SQL实现读写分离

3、优化MySQL的QUERY CACHE缓存，可以针对某一语句缓存，不会因为一条数据导致表所有缓存失效

4、提供SQL改写功能

5、提供节点监控诊断系统

6、SQL防火墙功能，根据预定义的规则来决定SQL是否可以执行

7、通过sqlLite实现动态加载配置

二、安装ProxySQL

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.1.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF


yum install proxysql

三、启动ProxySQL

启动后会监听6032（管理端口）和6033（对外服务端口）两个端口

systemctl start proxysql
proxysql --version

四、ProxySQL配置概念

1、服务启动后通过管理端口连接ProxySQL，默认管理账号密码都是admin。连接后可以看到main、disk、stats 、monitor 和 stats_history五个初始化数据库

· main库：ProxySQL memory配置数据库，存放了后端数据库实例、用户验证、路由规则等信息。主要关注以下三张表

selecct * from main.mysql_servers   #存放了后端MySQL服务器信息 
selecct * from main.mysql_users   #存放了后端数据库的账号和proxysql自身账号信息 
selecct * from main.mysql_query_rules    #存放了路由规则信息
#其它以runtime_开头的都是ProxySQL当前运行的配置内容，不能通过DML语句修改，只能修改对应的不以runtime开头的表，然后LOAD使其生效，SAVE使其存到硬盘以供重启后的加载

· disk ：ProxySQL配置的持久化数据存放文件 
· stats： ProxySQL统计信息汇总 
· monitor：ProxySQL监控信息，比如数据库的健康状态等 
· stats_history: ProxySQL 收集的有关其内部功能的历史指标

mysql -u admin -p"admin" -P6032 -h 127.0.0.1
show databases;

2、ProxySQL在运行时的当前配置称为runtime，而平时修改后的配置是保存在memory，为了将memory部分持久化还会保存到disk。一般会在memory层进行修改 ,然后保存到runtime，最后写入disk进行保存。

#一般在内存那层修改 ,然后保存到运行系统，保存到磁盘数据库系统。配置文件里分了users、servers、query rules、mysql variables、admin variables多个模块
LOAD MYSQL USERS TO RUNTIME;    #将内存数据库中的配置加载到 runtime 数据结构
SAVE MYSQL USERS TO DISK;       #将内存数据库中的 MySQL 用户持久化到磁盘数据库中
LOAD MYSQL SERVERS TO RUNTIME;  #将MySQL server 从内存数据库中加载到 runtime
SAVE MYSQL SERVERS TO DISK;     #从内存数据库中将 MySQL server 持久化到磁盘数据库中。
LOAD MYSQL QUERY RULES TO RUNTIME;  #将 MySQL query rules 从内存数据库加载到 runtime 数据结构
SAVE MYSQL QUERY RULES TO DISK;     #将 MySQL query rules 从内存数据库中持久化到磁盘数据库中
LOAD MYSQL VARIABLES TO RUNTIME;    #将 MySQL variables 从内存数据库加载到 runtime 数据结构
SAVE MYSQL VARIABLES TO DISK;       #将 MySQL variables 从内存数据库中持久化到磁盘数据库中
LOAD ADMIN VARIABLES TO RUNTIME;    #将 admin variables 从内存数据库加载到 runtime 数据结构
SAVE ADMIN VARIABLES TO DISK;       #将 admin variables 从内存数据库中持久化到磁盘数据库

3、ProxySQL配置文件默认在/etc/proxysql.cnf，该文件几乎不用手动配置，而是采用命令行动态加载

· 在启动ProxySQL时，首先通过/etc/proxysql.conf找到datadir，如果datadir存在proxysql.db（该文件为sqlLite文件），就将proxysql.db中的配置加载至memory和runtime

· 如果启动ProxySQL时带有--inital选项，则使用/etc/proxysql.conf中的配置把proxysql.db、memory和runtime全部初始化

· 如果启动ProxySQL时带有--reload选项，会把/etc/proxysql.conf和disk中的配置进行合并，使用disk覆盖config（如果存在冲突需要先人工处理）

五、ProxySQL节点配置

1、创建分组——main.mysql_replication_hostgroups表

在ProxySQL中通过main.mysql_replication_hostgroups表配置主从分组，其中writer_hostgroup字段为写组，reader_hostgroup字段为读组，这2个组在定义时值必须大于0且不能相同，比如写组为10，读组为20。ProxySQL会根据数据库各个节点read _only的值对节点进行分组。如果read_only=0就被分到写组，read_only=1则被分到读组

insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy');
#将配置写入到各个环境
load mysql servers to runtime;
save mysql servers to disk;
#验证各环境配置
select * from main.runtime_mysql_replication_hostgroups;
select * from main.mysql_replication_hostgroups;

该表结构如下

2、添加节点到分组——main.mysql_servers

· max_replication_lag字段：设置从库延迟的阈值，高于该值则不再路由，建议把main.global_variables中的mysql-monitor_slave_lag_when_null也进行设置，当mysql-monitor_slave_lag_when_null > mysql_servers.max_replication_lag时，如果从库线程停止工作就不会再被路由

UPDATE main.global_variables SET variable_value='300'  WHERE variable_name='mysql-monitor_slave_lag_when_null';
load mysql variables to runtime;
save mysql variables to disk;

· weight字段：配置权重

#查看表结构
show create table main.mysql_servers \G
CREATE TABLE mysql_servers (
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
    compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) )

#插入数据
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values (10,'172.20.1.172',3306,1,1000,'master');  
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values (20,'172.20.1.171',3306,1,1000,'slave'); 
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values (20,'172.20.1.184',3306,1,1000,'slave'); 

load mysql servers to runtime;
save mysql servers to disk;

3、配置ProxySQL监控账号用于监控后端节点状态（以下SQL配置的用户名密码都是proxysql默认的，如果没有修改需求的话可以不进行操作）

UPDATE main.global_variables SET variable_value='monitor'  WHERE variable_name='mysql-monitor_username';
UPDATE main.global_variables SET variable_value='monitor'  WHERE variable_name='mysql-monitor_password';

load mysql variables to runtime;
save mysql variables to disk;

4、在后端数据库各节点中也创建出监控账号。这些账号密码保持和main.mysql-monitor_username和main.mysql-monitor_password中一致

create user 'monitor'@'%' identified by 'monitor';
grant replication client on *.* to  'monitor'@'%' ;

5、ProxySQL的状态查看，监控模块产生的日志保存在monitor库下各张表里

· 查看后端节点状态，如果有节点没启动或者不满足路由条件的话status将会是SHUNNED

select * from main.runtime_mysql_servers;
select * from main.mysql_servers ;

· 查看节点连接状态，正常的话connect_error值为NULL，如果节点异常后，会自动请求正常的节点

select * from monitor.mysql_server_connect_log;  # 查看各节点连接是否正常

· 查看节点心跳信息，正常情况为NULL，关闭节点测试可以看到报错

select * from monitor.mysql_server_ping_log

· 查看只读状态，ProxySQL会根据数据库各个节点read _only的值对节点进行自动分组，如果read_only=0就被分到写组，read_only=1则被分到读组

select * from mysql_server_read_only_log;  #查看节点read_only状态

六、ProxySQL对外账号配置——mysql_users表

1、在各个数据库节点上配置对外账号，ProxySQL会调用该账号处理数据库操作

create user 'proxysql'@'%' identified by 'proxysql';
grant SELECT,INSERT,UPDATE,DELETE on *.* to 'proxysql'@'%';

#如果通过账号读写分离可以建立多个账号
create user 'proxysql_read'@'%' identified by 'proxysql_read';
grant SELECT on *.* to 'proxysql_read'@'%';

create user 'proxysql_dml'@'%' identified by 'proxysql_dml';
grant SELECT,INSERT,UPDATE,DELETE on *.* to 'proxysql_dml'@'%';

create user 'proxysql_ddl'@'%' identified by 'proxysql_ddl';
grant ALL PRIVILEGES on *.* to 'proxysql_ddl'@'%';

2、在ProxySQL中配置对外账号

main.mysql_users表最主要的三个字段是username、password、default_hostgroup，前面两个就是账号密码，而default_hostgroup是默认路由，如果没有能够匹配的规则就通过默认组进行数据库请求。例如proxysql用户的该字段值为10，代表该用户发送的SQL语句默认情况下将路由到hostgroup_id=10组中的某个节点。

insert into main.mysql_users (username,password,default_hostgroup) values ('proxysql','proxysql',10); 

#如果通过账号读写分离可以建立多个账号
insert into main.mysql_users (username,password,default_hostgroup) values ('proxysql_dml','proxysql_dml',10); 
insert into main.mysql_users (username,password,default_hostgroup) values ('proxysql_ddl','proxysql_ddl',10);
insert into main.mysql_users (username,password,default_hostgroup) values ('proxysql_read','proxysql_read',20);

load mysql users to runtime;
save mysql users to disk;

七、ProxySQL路由规则——mysql_query_rules表

ProxySQL根据配置的路由规则实现读写分离，而且规则配置非常灵活，可以基于用户、基于schema以及基于SQL语句来实现。如果是简单的路由规则，一般就按照读写操作进行分离，复杂场景下就要考虑根据慢日志各项指标来对执行频繁的慢语句单独写规则、做缓存等。通过mysql_query_rules和mysql_query_rules_fast_routing（mysql_query_rules_fast_routing是mysql_query_rules的扩展）表来配置规则

mysql_query_rules表关键字段： 

· rule_id：规则id号

· active：是否启用规则，1表示启用，0表示禁用 
· match_pattern：指定具体规则 
· destination_hostgroup：指定规则所作用的分组 
· apply：代表真正执行应用规则

2、创建规则实例，需要注意ProxySQL是根据rule_id的顺序进行匹配，如果满足条件后就不再继续匹配后面的规则

# select开头的语句全部分配到读组中，读组编号是20
insert into main.mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select',20,1);
insert into main.mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^create|^insert',10,1);

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

八、访问测试规则

1、通过ProxySQL的对外服务端口连接数据库进行访问，读请求和写请求应该是到不同节点

2、如果要查看ProxySQL的路由统计信息可以查看stats.stats_mysql_query_digest表，也可以根据digest列给出的值来只指定的SQL进行路由转发

select hostgroup,schemaname,username,digest,digest_text,count_star from  stats.stats_mysql_query_digest;