ProxySQL 實(shí)戰(zhàn)

上一小節(jié)我們介紹了 MGR 環(huán)境的部署，接下來我們從實(shí)戰(zhàn)角度，繼續(xù)學(xué)習(xí)這套高可用架構(gòu)的 ProxySQL部分。

1. ProxySQL 介紹

ProxySQL 是基于 MySQL 的一款開源的中間件產(chǎn)品，是一個(gè)輕量級(jí)的 MySQL 代理層，可以實(shí)現(xiàn)讀寫分離，故障切換，集群部署，監(jiān)控后端節(jié)點(diǎn)，可緩存查詢結(jié)果，支持 Query 路由功能，支持動(dòng)態(tài)指定某個(gè)SQL 進(jìn)行緩存，支持動(dòng)態(tài)加載。

2. ProxySQL 部署

下面從實(shí)戰(zhàn)的角度一步步搭建 ProxySQL 環(huán)境。

2.1 基本環(huán)境

2.2 安裝配置

安裝 proxysql：

--新安裝
rpm -ivh proxysql-2.0.5-1-centos7.x86_64.rpm
--升級(jí)
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

--啟動(dòng)proxysql
service proxysql start
--檢查版本
proxysql –version
--proxysql路徑
/var/lib/proxysql

配置后端 MySQL 機(jī)器：

登入 ProxySQL，把 MySQL 主從的信息添加進(jìn)去。將主庫 master 也就是做寫入的節(jié)點(diǎn)放到 HG 100中，salve 節(jié)點(diǎn)做讀放到 HG 1000。

--配置后端MySQL機(jī)器（在ProxySQL中執(zhí)行）
--登錄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中執(zhí)行）
save mysql servers to disk;

-- 加載到線上（在ProxySQL中執(zhí)行）
load mysql servers to runtime;

配置后端 MySQL 用戶：

這個(gè)用戶需要先在后端 MySQL 里真實(shí)存在，一個(gè)是監(jiān)控賬號(hào)，一個(gè)是程序賬號(hào)：

--監(jiān)控賬號(hào)（在MySQL中執(zhí)行）
GRANT USAGE ON *.* TO 'monitor_user'@'192.%' IDENTIFIED BY '123456';
--程序賬號(hào)（在MySQL中執(zhí)行）
GRANT SELECT, INSERT, UPDATE, DELETE ON center.* TO 'app_user'@'192.%' identified by '123456';

在每個(gè) ProxySQL 中執(zhí)行，配置后端 MySQL 監(jiān)控用戶：

--登錄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中執(zhí)行）
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent, max_connections) values('app_user','123456',1,100,1,500)
 
-- 持久化（在ProxySQL中執(zhí)行）
save mysql users to disk;
-- 加載到線上（在ProxySQL中執(zhí)行）
load mysql users to runtime;

加載配置和變量

-- 持久化（在ProxySQL中執(zhí)行）
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
save mysql query rules to disk;

-- 加載到線上（在ProxySQL中執(zhí)行）
load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
load mysql query rules to runtime;

定義路由規(guī)則

-- 發(fā)送到主庫（在ProxySQL中執(zhí)行）
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);

-- 發(fā)送到從庫（在ProxySQL中執(zhí)行）
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);

-- 加載（在ProxySQL中執(zhí)行）
save mysql query rules to disk;
load mysql query rules to runtime;

MGR配置

--配置MGR信息（在每個(gè)ProxySQL中執(zhí)行）
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;

-- 加載（在每個(gè)ProxySQL中執(zhí)行）
save mysql servers to disk;
load mysql servers to runtime;

--導(dǎo)入sys視圖（在MySQL主庫中執(zhí)行）
source /software/addition_to_sys.sql
addition_to_sys.sql可以從http://lefred.be/content/mysql-group-replication-native-support-in-proxysql下載

--授權(quán)sys庫（在MySQL主庫中執(zhí)行）
GRANT SELECT on sys.* to 'monitor_user'@'192.%';

--查看MGR成員基本信息（在MySQL中執(zhí)行），監(jiān)測(cè)節(jié)點(diǎn)的健康與落后情況
select * from sys.gr_member_routing_candidate_status;

--查看MGR各節(jié)點(diǎn)狀態(tài)（在ProxySQL中執(zhí)行）
select hostgroup_id, hostname, status from runtime_mysql_servers;

--查看MGR各節(jié)點(diǎn)日志信息（在ProxySQL中執(zhí)行）
select * from mysql_server_group_replication_log order by time_start_us desc limit 5;

2.3 基礎(chǔ)維護(hù)

啟動(dòng) ProxySQL：

service proxysql start

停止 ProxySQL：

service proxysql stop

連接 ProxySQL 管理端口：

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

連接 ProxySQL 服務(wù)端口：

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

sql 執(zhí)行統(tǒng)計(jì)：

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

3. 小結(jié)

本小節(jié)主要從實(shí)戰(zhàn)角度介紹如何搭建 ProxySQL 環(huán)境。

ProxySQL 是非常輕量級(jí)的一款 MySQL 中間件，對(duì) CPU 內(nèi)存等服務(wù)器資源的消耗基本小于 5%，而且功能強(qiáng)大，不僅僅是純粹的 MySQL 讀寫分離。掌握 ProxySQL 這門技術(shù)，也變得越來越重要，是DBA面試的一個(gè)加分項(xiàng)。