MySQL高可用主从集群（二）——ProxySQL中间件部署

上一篇MySQL高可用主从集群（一）文中介绍了MySQL主从集群的部署，在这篇文章里将主要介绍ProxySQL中间件部署并实现MySQL主从集群代理和读写分离。

读写分离原理

读写分离就是用户在发送请求时，请求经过中间件，中间件将请求中的读和写操作分辨出来将读请求发送给后端的从服务器，将写请求发送给后端的主服务器，主服务器通过主从复制将数据复制给其他从服务器.

1 ProxySQL中间件作用

参考官方站点：https://proxysql.com/ 

ProxySQL是一个开源的具有高性能、高可用性、协议感知的MySQL和其分支（如Percona Server和MariaDB）的代理工具。轻量级且支持数十万并发连接。

ProxySQL部署网络拓扑

2 安装ProxySQL

2.1下载ProxySQL

官网下载地址：Download ProxySQL - Percona

本文将ProxySQL部署在centos7虚拟机上

使用wget直接在虚拟机上下载ProxySQL安装包

wget https://downloads.percona.com/downloads/proxysql2/proxysql2-2.0.18/binary/redhat/7/x86_64/proxysql2-2.0.18-1.1.el7.x86_64.rpm

2.2调整系统参数

sed -i 's/#DefaultLimitCORE=/DefaultLimitCORE=infinity/g' /etc/systemd/system.conf
sed -i 's/#DefaultLimitCORE=/DefaultLimitCORE=infinity/g' /etc/systemd/user.conf
sed -i 's/#DefaultLimitNOFILE=/DefaultLimitNOFILE=1000000/g' /etc/systemd/system.conf
sed -i 's/#DefaultLimitNOFILE=/DefaultLimitNOFILE=1000000/g' /etc/systemd/user.conf
sed -i 's/#DefaultLimitNPROC=/DefaultLimitNPROC=1000000/g'  /etc/systemd/system.conf
sed -i 's/#DefaultLimitNPROC=/DefaultLimitNPROC=1000000/g'  /etc/systemd/user.conf
sed -i 's/#DefaultLimitMEMLOCK=/DefaultLimitMEMLOCK=infinity/g'  /etc/systemd/system.conf
sed -i 's/#DefaultLimitMEMLOCK=/DefaultLimitMEMLOCK=infinity/g'  /etc/systemd/user.conf

2.3开放防火墙端口

firewall-cmd --zone=public --add-port=6033/tcp --permanent
firewall-cmd --zone=public --add-port=6032/tcp --permanent
#重启防火墙后生效
firewall-cmd --reload
#查看开放端口
sudo firewall-cmd --list-ports

ProxySQL主要使用两个端口

6032为ProxySQL管理用户登录端口

6033为ProxySQL客户端连接端口，相当于MySQL的3306端口

2.4yum安装ProxySQL

进入ProxySQL安装包存放目录

yum localinstall proxysql2-2.0.18-1.1.el7.x86_64.rpm

2.5启动ProxySQL

sudo systemctl start proxysql
sudo systemctl stop proxysql
sudo systemctl status proxysql
sudo systemctl restart proxysql
#########查看日志
tail -f -n 200 /var/lib/proxysql/proxysql.log

2.6安装MySQL客户端工具

ProxySQL 的 admin 管理接口是一个使用 MySQL 协议的接口，所以，可以直接使用 mysql 客户端、navicat 等工具去连接这个管理接口，其默认的用户名和密码均为 admin。

本文使用mariadb作为mysql客户端

sudo yum -y install mariadb

登陆ProxySQL管理端口

mysql -uadmin -h 127.0.0.1 -P6032 -padmin

3 ProxySQL内设置数据库介绍

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

• main库是ProxySQL最主要的库，是需要修改配置时使用的库，它其实是一个内存数据库系统。所以，修改main库中的配置后，必须将其持久化到disk上才能永久保存
• disk库是磁盘数据库，该数据库结构和内存数据库完全一致。当持久化内存数据库中的配置时，其实就是写入到disk库中。磁盘数据库的默认路径为 $DATADIR/proxysql.db
• stats库是统计信息库。这个库中的数据一般是在检索其内数据时临时填充的，它保存在内存中。因为没有相关的配置项，所以无需持久化
• monitor库是监控后端MySQL节点相关的库，该库中只有几个log类的表，监控模块收集到的监控信息全都存放到对应的log表中
• stats_history库是1.4.4版新增的库，用于存放历史统计数据。默认路径为 $DATADIR/proxysql_stats.db

上面描述main库的时候，只是说了内存数据库需要持久化到disk库才能永久保存配置。但实际上，修改了main库中的配置后，并不会立即生效，它还需要load到runtime的数据结构中才生效，只有在runtime数据结构中的配置才是对ProxySQL当前有效的配置。

main库中的表分为runtime开头和非runtime开头， runtime开头为运行时的设置 ，非runtime开头为需要设置的配置 ，所有的配置修改后需要执行命令才能加载到runtime生效。

LOAD ...  TO RUNTIME;  #使修改立即生效
SAVE  ...  TO DISK;    #使修改永久保存到磁盘

4 配置MySQL主从集群

登陆ProxySQL管理端口

mysql -uadmin -h 127.0.0.1 -P6032 -padmin

将mysql节点加入到proxysql中

insert into  mysql_servers (hostgroup_id,hostname,port) values (10,'10.0.0.1',3306),(10,'10.0.0.2',3306),(10,'10.0.0.3',3306);

配置读写组

insert into  mysql_replication_hostgroups(writer_hostgroup ,reader_hostgroup,comment) values(10,20,'读写分离高可用');

将提交同步到执行状态和磁盘

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

添加用户

通过proxysql连接mysql的用户必须加入到MySQL_users表

INSERT INTO MySQL_users(username,password,default_hostgroup,comment) VALUES ('root','123456',10,'root');
load mysql users to runtime; save mysql users to disk;

添加读写分离规则

可自由定制,根据实际定制不设置读写分离可略过此步骤

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',20,1);
load mysql query rules to runtime;  save mysql query rules to disk;

MySQL的master节点设置proxysql监控用户

通过ProxySQL代理MySQL要设置ProxySQL监控用户，用来检测和管理MySQL集群状态

create user 'monitor'@'%' identified by '1234563';
grant all privileges on *.* to 'monitor'@'%' with grant option;
flush privileges;
ALTER USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

proxysql管理接口修改变量设置健康检测的账号

set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';
load mysql variables to runtime;save mysql variables to disk;

修改proxysql中mysql版本

ProxySQL中默认的MySQL版本为5，需要根据实际使用的MySQL版本修改

update global_variables set variable_value="8.0.21" where variable_name='mysql-server_version';
load mysql variables to runtime;save mysql variables to disk;

设置proxysql远程连接用户

set admin-admin_credentials='admin:admin;myadmin:myadmin';
load admin variables to runtime;save admin variables to disk;

登录proxysql客户端连接mysql主从集群

mysql -uroot -h 127.0.0.1 -P6033 -p123456

通过ProxySQL连接MySQL主从集群，ProxySQL会根据上面配置的MySQL读写节点，自动将读写请求路由到相应的MySQL节点，从而实现读写分离。

下一篇：MySQL高可用主从集群（三）——Orchestrator集群部署