ProxySQL 实战

上一小节我们介绍了 MGR 环境的部署，接下来我们从实战角度，继续学习这套高可用架构的 ProxySQL部分。

1. ProxySQL 介绍

ProxySQL 是基于 MySQL 的一款开源的中间件产品，是一个轻量级的 MySQL 代理层，可以实现读写分离，故障切换，集群部署，监控后端节点，可缓存查询结果，支持 Query 路由功能，支持动态指定某个SQL 进行缓存，支持动态加载。

2. ProxySQL 部署

下面从实战的角度一步步搭建 ProxySQL 环境。

2.1 基本环境

| ProxySQL-1 | ProxySQL-2 | ProxySQL-3
---|---|---|---
MySQL版本 | proxysql-2.0.5 | proxysql-2.0.5 | proxysql-2.0.5
操作系统 | CentOS 7.8 | CentOS 7.8 | CentOS 7.8
服务器IP | 192.168.0.1 | 192.168.0.2 | 192.168.0.3
端口 | 管理端口-6032
服务端口-6033 | 管理端口-6032
服务端口-6033 | 管理端口-6032
服务端口-6033
服务器配置 | 2c4g | 2c4g | 2c4g

2.2 安装配置

安装 proxysql：

--新安装
rpm -ivh proxysql-2.0.5-1-centos7.x86_64.rpm
--升级
rpm -Uvh proxysql-2.0.5-1-centos7.x86_64.rpm
--删除
rpm -qa | grep proxysql
rpm -e proxysql-2.0.5-1-centos7.x86_64

--启动proxysql
service proxysql start
--检查版本
proxysql –version
--proxysql路径
/var/lib/proxysql

配置后端 MySQL 机器：

登入 ProxySQL，把 MySQL 主从的信息添加进去。将主库 master 也就是做写入的节点放到 HG 100中，salve 节点做读放到 HG 1000。

--配置后端MySQL机器（在ProxySQL中执行）
--登录proxysql管理端口
mysql -uadmin -padmin -h127.0.0.1 -P6032;
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) 
values(100,'192.168.0.1',3306,1,500,10,'mysql-1'),
(1000,'192.168.0.1',3306,10,500,10,'mysql-1'),
(1000,'192.168.0.2',3306,45,500,10,'mysql-2'),
(1000,'192.168.0.3',3306,45,500,10,'mysql-3');
select * from mysql_servers;

-- 持久化（在ProxySQL中执行）
save mysql servers to disk;

-- 加载到线上（在ProxySQL中执行）
load mysql servers to runtime;

配置后端 MySQL 用户：

这个用户需要先在后端 MySQL 里真实存在，一个是监控账号，一个是程序账号：

--监控账号（在MySQL中执行）
GRANT USAGE ON *.* TO 'monitor_user'@'192.%' IDENTIFIED BY '123456';
--程序账号（在MySQL中执行）
GRANT SELECT, INSERT, UPDATE, DELETE ON center.* TO 'app_user'@'192.%' identified by '123456';

在每个 ProxySQL 中执行，配置后端 MySQL 监控用户：

--登录proxysql管理端口
mysql -uadmin -padmin -h127.0.0.1 -P6032;
UPDATE global_variables SET variable_value='monitor_user' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';
--加载和持久化
save mysql variables to disk;
load mysql variables to runtime;

--配置后端MySQL程序用户（在ProxySQL中执行）
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent, max_connections) values('app_user','123456',1,100,1,500)

-- 持久化（在ProxySQL中执行）
save mysql users to disk;
-- 加载到线上（在ProxySQL中执行）
load mysql users to runtime;

加载配置和变量

-- 持久化（在ProxySQL中执行）
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql query rules to disk;

-- 加载到线上（在ProxySQL中执行）
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
load mysql query rules to runtime;

定义路由规则

-- 发送到主库（在ProxySQL中执行）
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);

-- 发送到从库（在ProxySQL中执行）
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);

-- 加载（在ProxySQL中执行）
save mysql query rules to disk;
load mysql query rules to runtime;

MGR配置

--配置MGR信息（在每个ProxySQL中执行）
insert into mysql_group_replication_hostgroups (writer_hostgroup,reader_hostgroup,backup_writer_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values (100,1000,101,102,1,1,1,100);
select * from mysql_group_replication_hostgroups;

-- 加载（在每个ProxySQL中执行）
save mysql servers to disk;
load mysql servers to runtime;

--导入sys视图（在MySQL主库中执行）
source /software/addition_to_sys.sql
addition_to_sys.sql可以从http://lefred.be/content/mysql-group-replication-native-support-in-proxysql下载

--授权sys库（在MySQL主库中执行）
GRANT SELECT on sys.* to 'monitor_user'@'192.%';

--查看MGR成员基本信息（在MySQL中执行），监测节点的健康与落后情况
select * from sys.gr_member_routing_candidate_status;

--查看MGR各节点状态（在ProxySQL中执行）
select hostgroup_id, hostname, status from runtime_mysql_servers;

--查看MGR各节点日志信息（在ProxySQL中执行）
select * from mysql_server_group_replication_log order by time_start_us desc limit 5;

2.3 基础维护

启动 ProxySQL：

service proxysql start

停止 ProxySQL：

service proxysql stop

连接 ProxySQL 管理端口：

mysql -uadmin -padmin -h127.0.0.1 -P6032;

连接 ProxySQL 服务端口：

mysql -uadmin_user -padmin_user -h127.0.0.1 -P6033;

sql 执行统计：

select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;

3. 小结

本小节主要从实战角度介绍如何搭建 ProxySQL 环境。

ProxySQL 是非常轻量级的一款 MySQL 中间件，对 CPU 内存等服务器资源的消耗基本小于 5%，而且功能强大，不仅仅是纯粹的 MySQL 读写分离。掌握 ProxySQL 这门技术，也变得越来越重要，是DBA面试的一个加分项。