環(huán)境節(jié)點如下：

192.168.2.238 proxysql
192.168.2.240 mysql master
192.168.2.239 mysql slave
192.168.2.241 mysql slave

實現(xiàn)讀寫分離前，先實現(xiàn)主從復制（主從步驟這里不再詳述，可參考使用MHA實現(xiàn)Mysql高可用中的操作方法http://www.itdecent.cn/p/4ee738a6a787）

1.安裝并啟動proxysql服務

wget https://github.com/sysown/proxysql/releases/download/v2.0.10/proxysql-2.0.10-1-centos7.x86_64.rpm
yum -y install proxysql-2.0.10-1-centos7.x86_64.rpm
啟動proxysql服務
systemctl start proxysql
啟動后會監(jiān)聽兩個默認端口
6032：ProxySQL的管理端口
6033：ProxySQL對外提供服務的端口

image.png

使用mysql客戶端連接到ProxySQL的管理接口6032，默認管理員用戶和密碼都
是admin：
mysql -uadmin -padmin -P6032 -h127.0.0.1

proxysql相關(guān)庫的介紹

main 是默認的”數(shù)據(jù)庫”名，表里存放后端db實例、用戶驗證、路由規(guī)則等信息。
表名以 runtime_開頭的表示proxysql當前運行的配置內(nèi)容，不能通過dml語句修改，
只能修改對應的不以 runtime_ 開頭的（在內(nèi)存）里的表，然后 LOAD 使其生效，
SAVE 使其存到硬盤以供下次重啟加載
disk 是持久化到硬盤的配置，sqlite數(shù)據(jù)文件
stats 是proxysql運行抓取的統(tǒng)計信息，包括到后端各命令的執(zhí)行次數(shù)、流量、
processlist、查詢種類匯總/執(zhí)行時間，等等
monitor 庫存儲 monitor 模塊收集的信息，主要是對后端db的健康/延遲檢查

在main和monitor數(shù)據(jù)庫中的表， runtime_開頭的是運行時的配置，不能修改，
只能修改非runtime_表
show databases;

image.png

2.向proxysql中添加MySQL節(jié)點

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.2.239',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.2.240',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.2.241',3306);

添加監(jiān)控后端節(jié)點的用戶。ProxySQL通過每個節(jié)點的read_only值來自動調(diào)整
它們是屬于讀組還是寫組
?在mysql master上執(zhí)行
grant replication client on . to monitor@'192.168.2.%'
identified by 'monitor';

3.ProxySQL上配置監(jiān)控

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';

加載到RUNTIME，并將配置保存到disk
load mysql variables to runtime;
save mysql variables to disk;

監(jiān)控模塊的指標保存在monitor庫的log表中
查看監(jiān)控連接是否正常的 (對connect指標的監(jiān)控)：(如果connect_error的結(jié)果
為NULL則表示正常)
MySQL> select * from mysql_server_connect_log;

image.png

查看監(jiān)控心跳信息 (對ping指標的監(jiān)控)：
MySQL> select * from mysql_server_ping_log;

image.png

查看read_only和replication_lag的監(jiān)控日志
MySQL> select * from mysql_server_read_only_log;
MySQL> select * from mysql_server_replication_lag_log;

4.設置proxysql分組信息

需要修改的是main庫中的mysql_replication_hostgroups表，該表有3個字段：
writer_hostgroup，reader_hostgroup，comment, 指定寫組的id為10，讀組的id為20
insert into mysql_replication_hostgroups values(10,20,"read_only|innodb_read_only","test");

將mysql_replication_hostgroups表的修改加載到RUNTIME生效
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;

Monitor模塊監(jiān)控后端的read_only值，按照read_only的值將節(jié)點自動移動到讀/寫組
select hostgroup_id,hostname,port,status,weight from mysql_servers;

image.png

5.配置發(fā)送SQL語句的用戶

在mysql master節(jié)點上創(chuàng)建訪問用戶
grant all on . to proxysqluser@'192.168.2.%' identified by 'proxysqluser';

在ProxySQL配置，將用戶proxysqluser添加到mysql_users表中， default_hostgroup默認組設置為寫組10，當讀寫分離的路由規(guī)則不符合時，會訪問默認組的數(shù)據(jù)庫
insert into mysql_users(username,password,default_hostgroup)
values('proxysqluser','proxysqluser',10);
load mysql users to runtime;
save mysql users to disk;

6.使用sqluser用戶測試是否能路由到默認的10寫組實現(xiàn)讀、寫數(shù)據(jù)

mysql -uproxysqluser -pproxysqluser -P6033 -h127.0.0.1 -e 'select @@server_id'

image.png

mysql -uproxysqluser -pproxysqluser -P6033 -h127.0.0.1 -e 'create database proxydb'
mysql -uproxysqluser -pproxysqluser proxydb -P6033 -h127.0.0.1 -e 'create table t1(id int)'
查看創(chuàng)建庫和表的執(zhí)行結(jié)果
mysql -uproxysqluser -pproxysqluser -P6033 -h127.0.0.1 -e 'show databases'

image.png

7.在proxysql上配置路由規(guī)則，實現(xiàn)讀寫分離

與規(guī)則有關(guān)的表：mysql_query_rules和mysql_query_rules_fast_routing，后
者是前者的擴展表，1.4.7之后支持
插入路由規(guī)則：將select語句分離到20的讀組，select語句中有一個特殊語句
SELECT...FOR UPDATE它會申請寫鎖，應路由到10的寫組
insert into mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
因ProxySQL根據(jù)rule_id順序進行規(guī)則匹配，select ... for update規(guī)則的
rule_id必須要小于普通的select規(guī)則的rule_id

測試讀操作是否路由給20的讀組
mysql -uproxysqluser -pproxysqluser -P6033 -h127.0.0.1 -e 'select @@server_id'

image.png

測試寫操作，以事務方式進行測試
mysql -uproxysqluser -pproxysqluser -P6033 -h127.0.0.1
-e 'start transaction;select @@server_id;commit;select @@server_id'
mysql -uproxysqluser -pproxysqluser -P6033 -h127.0.0.1 -e 'insert proxydb.t1 values (1)'
mysql -uproxysqluser -pproxysqluser -P6033 -h127.0.0.1 -e 'select id from proxydb.t1'

image.png

路由的信息：查詢stats庫中的stats_mysql_query_digest表
SELECT hostgroup hg,sum_time, count_star, digest_text
FROM stats_mysql_query_digest ORDER BY sum_time DESC;

image.png

到此基于proxysql的讀寫分離搭建完成。