ProxySql是MySQL的一款中間件產(chǎn)品，是靈活強(qiáng)大的mysql代理層，可以實(shí)現(xiàn)讀寫(xiě)分離，支持query路由功能，支持動(dòng)態(tài)指定某個(gè)sql進(jìn)行緩存，支持動(dòng)態(tài)加載配置。故障切換和一些sql過(guò)濾功能。
實(shí)戰(zhàn)演練
環(huán)境
centos7.5 mysql5.7
ip：10.106.145.181（從） 10.106.145.182（主） 10.106.145.183（從）
注意：另個(gè)從庫(kù)設(shè)置為read_only=on
我們可以將proxysql單獨(dú)安裝在一臺(tái)機(jī)器上，這里因?yàn)闄C(jī)器不夠我們就先將它安裝在10.106.145.183上，下面我們開(kāi)始操作
1. 安裝并配置好mysq主從
從庫(kù)的配置文件中要加入read_only=1，不然在啟動(dòng)之后查詢mysql_servers表會(huì)出現(xiàn)從庫(kù)也會(huì)被自動(dòng)加入寫(xiě)入的組中。proxysql區(qū)別主從的唯一方式就是read_only狀態(tài)值。 如果是測(cè)試則先將從庫(kù)臨時(shí)設(shè)置為只讀
vi /etc/my.cnf
添加 read_only=1
臨時(shí)設(shè)置：
set global read_only=on
2. 安裝ProxySql
vim /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
3.安裝ProxySQL和mariadb客戶端(如果有該機(jī)器上有mysql客戶端則不需要安裝mariadb)
ProxySQL內(nèi)置了一個(gè)輕量級(jí)的數(shù)據(jù)庫(kù)，所以需要有MySQL客戶端連上去對(duì)其進(jìn)行配置
yum install proxysql mariadb -y
這里因?yàn)樵摍C(jī)器上有mysql客戶端了，所有我使用指令如下
yum install proxysql -y
4.啟動(dòng)ProxySQL服務(wù)
systemctl start proxysql
systemctl status proxysql
設(shè)置開(kāi)機(jī)啟動(dòng)
system enable proxysql
netstat -anlp| grep proxysql
proxysql --version
5.連接管理端口
6032是proxysql的管理端口號(hào)6033是對(duì)外服務(wù)的端口，用戶名密碼都是admin
登錄命令
/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032
show databases;
6.四個(gè)庫(kù)的說(shuō)明
main：內(nèi)存配置數(shù)據(jù)庫(kù)，即memory，表里存放后端db實(shí)例 用戶驗(yàn)證 路由規(guī)則等信息，該庫(kù)信息如 下
其中主要表
mysql_servers：后端可以連接mysql的服務(wù)器列表
mysql_users:后端數(shù)據(jù)庫(kù)賬戶和監(jiān)控賬戶
mysql_query_rules: query路由到后端不同服務(wù)器的規(guī)則列表
disk庫(kù)：持久化磁盤(pán)的配置
stats庫(kù)：統(tǒng)計(jì)信息的匯總
monitor庫(kù)：一些監(jiān)控收集的信息，包括數(shù)據(jù)庫(kù)的健康狀態(tài)等
7.在主庫(kù)上配置監(jiān)控賬戶和對(duì)外訪問(wèn)賬戶
create user monitor@'10.106.145.%' identified by 'monitor';
grant all privileges on *.* to monitor@'10.106.145.%' with grant option;
create user zsprox@'10.106.145.%' identified by 'zsprox';
grant all privileges on *.* to zsprox@'10.106.145.%' with grant option;
主庫(kù)的賬戶會(huì)被同步到另外兩個(gè)從庫(kù)上，如果屏蔽了則需要分別到從庫(kù)上創(chuàng)建上面兩個(gè)賬戶
8.Proxysql的多層配置系統(tǒng)
proxysql有一套完整的配置系統(tǒng) ，方便dba對(duì)線上操作，整套配置系統(tǒng)分為三層，頂層為runtime，中間層為memory，底層也就是持久層為disk 和config file，配置結(jié)構(gòu)如下
runtime:代表當(dāng)前生效的正在使用的配置，無(wú)法直接修改這里的配置，必須從下一層load進(jìn)來(lái)
memory：上層連runtime下面連接持久化，這層是可以正常操作proxysql的配置，隨便修改，不會(huì)影響生產(chǎn)環(huán)境，修改這個(gè)配置一般是先在memory中完成的，確認(rèn)正常后在加載到runtime和持久化到磁盤(pán)上
disk 和config file：持久化信息，重啟后內(nèi)存的信息會(huì)丟失，所有需要持久化
9. 添加主從服務(wù)器列表
使用超管用戶登錄
/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032
use main；
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.106.145.181',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.106.145.182',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.106.145.183',3306);
load mysql servers to runtime;
save mysql servers to disk;
查詢狀態(tài)
select *from mysql_servers;
10. 添加監(jiān)控賬戶
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;
驗(yàn)證
select *from monitor.mysql_server_connect_log;
監(jiān)控正常，沒(méi)有報(bào)錯(cuò)信息
11.配置主從分組信息
show create table mysql_replication_hostgroups;
里面write_hostgroup 是寫(xiě)入組編號(hào)，reader_hostgroup是讀取組編號(hào)，我們這里使用10 作為寫(xiě)入組，20為讀取組編號(hào)
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;
select *from mysql_replication_hostgroups;
proxysql 會(huì)根據(jù)server的read_only的取值來(lái)將服務(wù)器進(jìn)行分組， 只讀的分配到20（讀取組），不是只讀的分配到10（寫(xiě)入組）
12.配置對(duì)外訪問(wèn)賬戶
對(duì)方訪問(wèn)賬戶默認(rèn)指定主庫(kù)，并對(duì)該用戶開(kāi)啟事務(wù)持久化保護(hù)
mysql_users表中的transaction_persistent 的字段默認(rèn)是0，建議在創(chuàng)建完用戶之后設(shè)置為1，避免發(fā)生臟讀幻讀現(xiàn)象，命令如下
insert into mysql_users(username,password,default_hostgroup) values('zsprox','zsprox',10);
update mysql_users set transaction_persistent=1 where username='zsprox';
load mysql users to runtime;
save mysql users to disk;
驗(yàn)證登錄的服務(wù)器就是主庫(kù)（這里登錄ip就是我們安裝proxysql的服務(wù)器地址，一般建議單獨(dú)安裝一臺(tái) ，不要和mysql集群中的機(jī)器使用一臺(tái)）
/usr/local/mysql/bin/mysql -uzsprox -pzsprox -h 10.106.145.183 -P 6033 -e "select @@hostname"
注意對(duì)外端口是6033
13 配置讀寫(xiě)分離策略
配置讀寫(xiě)分離需要使用mysql_query_rules 表，表中的match_pattern字段就是代表設(shè)置的規(guī)則，destination_hostgroup字段代表默認(rèn)指定的分組，Apply代表真正執(zhí)行的應(yīng)用規(guī)則。
把所有以select開(kāi)頭的語(yǔ)句全部分配到20的讀組中，其他都分配到寫(xiě)組10中去
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',10,1);
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
14 測(cè)試讀寫(xiě)分離
通過(guò)創(chuàng)建對(duì)外賬戶zsproxy連接proxysql登錄數(shù)據(jù)庫(kù)
命令如下
/usr/local/mysql/bin/mysql -u zsprox -p -h 10.106.145.183 -P 6033
查詢一個(gè)表
select * from test.b
在登錄管理端口 查詢監(jiān)控狀態(tài)
/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032
select *from stats_mysql_query_digest;
在6033端口執(zhí)行插入操作
insert into b values(20);
再次查詢狀態(tài)
15 調(diào)整權(quán)重
讀寫(xiě)分離成功后，我們可以調(diào)整權(quán)重，讓某臺(tái)機(jī)器承受更多的讀操作，在表mysql_servers上進(jìn)行設(shè)置
update mysql_servers set weight=10 where hostname='10.106.145.181';
load mysql servers to runtime;
save mysql servers to disk;
到此讀寫(xiě)分離正式配置完畢了
