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
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面試的一個加分項。