此MGR+ProxySql MySql集群架構(gòu)，目的是為了解決目前DMS系統(tǒng)數(shù)據(jù)庫(kù)單機(jī)環(huán)境對(duì)高并發(fā)支撐不足，數(shù)據(jù)庫(kù)經(jīng)常出現(xiàn)阻塞，導(dǎo)致系統(tǒng)卡頓的問(wèn)題。此次搭建可選擇在凌晨業(yè)務(wù)不繁忙時(shí)間，預(yù)計(jì)搭建時(shí)間3個(gè)小時(shí)。

軟件簡(jiǎn)介

MySQL是目前較流行的關(guān)系型開(kāi)源數(shù)據(jù)庫(kù)之一，支持完整的事務(wù)支持，支持標(biāo)準(zhǔn)的SQL以及支持跨平臺(tái)部署。Proxysql是基于MySQL的一款開(kāi)源的中間件的產(chǎn)品，是一個(gè)靈活的MySQL代理層，可以實(shí)現(xiàn)讀寫(xiě)分離，支持Query路由功能，支持動(dòng)態(tài)指定某個(gè)SQL進(jìn)行緩存，支持動(dòng)態(tài)加載（無(wú)需重啟ProxySQL服務(wù)），故障切換和SQL過(guò)濾功能。 

軟件版本

硬件環(huán)境

本次搭建共需要在生產(chǎn)環(huán)境新增主機(jī)2臺(tái)，以下為需要的具體配置信息：

端口

需要在其中proxysql主機(jī)開(kāi)放6032、6033端口供應(yīng)用程序訪問(wèn)及監(jiān)控使用。

環(huán)境檢查

環(huán)境檢查，主要檢查硬件信息是否符合申請(qǐng)的指標(biāo)以及軟件版本信息

MGR集群實(shí)現(xiàn)數(shù)據(jù)庫(kù)復(fù)制功能及高可用。Proxysql對(duì)應(yīng)用程序提供訪問(wèn)，對(duì)MGR集群進(jìn)行讀寫(xiě)分離，集群狀態(tài)檢測(cè)，實(shí)現(xiàn)故障切換。

MGR單主模式搭建

主從搭建

先將新增的一臺(tái)服務(wù)器搭建MySQL數(shù)據(jù)庫(kù)，將DMS主數(shù)據(jù)庫(kù)的備份數(shù)據(jù)傳送到服務(wù)器上，搭建主從使目前3臺(tái)數(shù)據(jù)庫(kù)的數(shù)據(jù)保持一致，為搭建MGR作準(zhǔn)備。

集群搭建

修改my.cnf配置文件

修改主機(jī)名

hostnamectl set-hostname mgr_node1

hostnamectl set-hostname mgr_node2

hostnamectl set-hostname mgr_node3

hostnamectl set-hostname proxysql

修改hosts文件

11.11.11.156 mgr_node1

11.11.11.157 mgr_node2

11.11.11.244 mgr_node3

開(kāi)啟MGR

主節(jié)點(diǎn)：

CHANGE MASTER TO MASTER_USER=repl, MASTER_PASSWORD=Rest!101 FORCHANNEL group_replication_recovery;

installplugin group_replication soname group_replication.so;  --安裝gr插件

#設(shè)置group_replication_bootstrap_group為ON是為了標(biāo)示以后加入集群的服務(wù)器以這臺(tái)服務(wù)器為基準(zhǔn)，以后加入的就不需要設(shè)置。

setglobal group_replication_bootstrap_group=on;

開(kāi)啟組復(fù)制

startgroup_replication;

setglobal group_replication_bootstrap_group=off;

節(jié)點(diǎn)1:

CHANGE MASTER TO MASTER_USER=repl, MASTER_PASSWORD=Rest!101 FORCHANNEL group_replication_recovery;

installplugin group_replication soname group_replication.so;

setglobal group_replication_allow_local_disjoint_gtids_join=ON;

startgroup_replication;

節(jié)點(diǎn)2:

CHANGE MASTER TO MASTER_USER=repl, MASTER_PASSWORD=Rest!101 FORCHANNEL group_replication_recovery;

installplugin group_replication soname group_replication.so;

setglobal group_replication_allow_local_disjoint_gtids_join=ON;

startgroup_replication;

查看是否搭建成功

#查詢組成員

select* from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

|CHANNEL_NAME              | MEMBER_ID                            |MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

|group_replication_applier | 38ed8610-aca0-11ea-8482-00505683effffd |mgr_node1   |        3306 | ONLINE       |

|group_replication_applier | 9d5c531c-b075-11ea-9d27-005056839787 |mgr_node2   |        3306 | ONLINE       |

|group_replication_applier | dc4bd6bb-b076-11ea-96f6-005056835c02 |mgr_node3   |        3306 | ONLINE       |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

#查詢主節(jié)點(diǎn)

selectvariable_value from performance_schema.global_status wherevariable_name=group_replication_primary_member;

+--------------------------------------+

| variable_value                       |

+--------------------------------------+

| 38ed8610-aca0-11ea-8482-00505683effffd |

+--------------------------------------+

ProxySql讀寫(xiě)分離搭建及驗(yàn)證

在mgr集群上創(chuàng)建proxysql所需的賬號(hào)

#proxysql的監(jiān)控賬戶

createuser monitor@% identified by Monitor@123;

grantall privileges on *.* to monitor@% with grant option;

#proxysql的對(duì)外訪問(wèn)賬戶

createuser proxysql@% identified by Proxysql@123;

grantall privileges on *.* to proxysql@% with grant option;

搭建yum環(huán)境，安裝proxysql軟件

搭建yum源

cat<

[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

EOF

安裝依賴

yum-y install perl.x86_64

yuminstall -y libaio.x86_64

yum-y install net-tools.x86_64

yuminstall perl-DBD-MySQL -y

安裝Proxysql

Yuminstall -y proxysql

啟動(dòng)proxySql

systemctlstart proxysql

netstat-anlp | grep proxysql

6032是ProxySQL的管理端口號(hào)，6033是對(duì)外服務(wù)的端口號(hào)

ProxySQL的用戶名和密碼都是默認(rèn)的admin

配置proxySql：配置訪問(wèn)賬號(hào)及監(jiān)控監(jiān)控，在mgr主節(jié)點(diǎn)執(zhí)行監(jiān)控腳本。

管理員登錄ProxySQL

/usr/local/mysql/bin/mysql-uadmin -padmin -h 127.0.0.1 -P 6032

#配置監(jiān)控賬號(hào)

setmysql-monitor_username=monitor;

setmysql-monitor_password=Monitor@123;

#配置默認(rèn)組信息

insertintomysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader)values(10,20,30,40,1,1);

#配置用戶（主要是添加程序端的這個(gè)用戶，也就是run，將其設(shè)置到寫(xiě)組10里面）

insertinto mysql_users(username,password,default_hostgroup)values(proxysql,Proxysql@123,10);

主節(jié)點(diǎn)定義為寫(xiě)組10,從節(jié)點(diǎn)定義為只讀組30

insertintomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (10,10.2.159.35,3306,1,3000,10,mgr_node1);

insertintomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,10.2.159.36,3306,2,3000,10,mgr_node2);

insertintomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,10.2.159.46,3306,2,3000,10,mgr_node3);

insertintomysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,10.2.159.35,3306,1,3000,10,mgr_node1);

規(guī)劃讀寫(xiě)組，添加節(jié)點(diǎn)設(shè)置讀寫(xiě)分離規(guī)則，查看節(jié)點(diǎn)狀態(tài)。

#配置讀寫(xiě)分離參數(shù)

insertintomysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,^SELECT.*FORUPDATE$,10,1);

insertintomysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,^SELECT,30,1);

save使內(nèi)存數(shù)據(jù)永久存儲(chǔ)到磁盤，load使內(nèi)存數(shù)據(jù)加載到runtime生效：

savemysql users to disk;

savemysql servers to disk;

savemysql query rules to disk;

savemysql variables to disk;

saveadmin variables to disk;

loadmysql users to runtime;

loadmysql servers to runtime;

loadmysql query rules to runtime;

loadmysql variables to runtime;

loadadmin variables to runtime;

在MGR主節(jié)點(diǎn)執(zhí)行監(jiān)控腳本

查看各節(jié)點(diǎn)狀態(tài)

SELECT* FROM sys.gr_member_routing_candidate_status;

selecthostname,port,viable_candidate,read_only,transactions_behind,errorfrom mysql_server_group_replication_log order by time_start_us desclimit 6;

SELECT* FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESCLIMIT 10 ;

SELECT* FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESCLIMIT 10;

在應(yīng)用端驗(yàn)證讀寫(xiě)分離是否可用；驗(yàn)證mgr故障轉(zhuǎn)移是否可用

測(cè)試讀負(fù)載均衡

fori in `seq 1 10`; do /usr/local/mysql/bin/mysql -uproxysql-pProxysql@123 -h127.0.0.1 -P6033 -e "select * fromperformance_schema.global_variables where variable_name=server_id;"; done  | grep server

fori in `seq 1 10`; do /usr/local/mysql/bin/mysql -uproxysql-pProxysql@123 -h10.2.159.47 -P6033 -e "select * fromperformance_schema.global_variables where variable_name=server_id;"; done  | grep server

驗(yàn)證系統(tǒng)各功能是否正常

本次集群搭建中若出現(xiàn)問(wèn)題，或搭建成功后系統(tǒng)功能不可用，可采用應(yīng)用端保持連接原始數(shù)據(jù)庫(kù)，或切換回原始數(shù)據(jù)庫(kù)鏈接，以此確保架構(gòu)改造失敗的回退安全性。