59. MySQL高可用之 ProxySQL 实战

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

1. ProxySQL 介绍

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

2. ProxySQL 部署

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

2.1 基本环境

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

--授权 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;

addition_to_sys.sql 可以从这里下载

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 面试的一个加分项。