此MGR+ProxySql MySql集群架構，目的是為了解決目前DMS系統數據庫單機環境對高并發支撐不足，數據庫經常出現阻塞，導致系統卡頓的問題。此次搭建可選擇在凌晨業務不繁忙時間，預計搭建時間3個小時。

軟件簡介

MySQL是目前較流行的關系型開源數據庫之一，支持完整的事務支持，支持標準的SQL以及支持跨平臺部署。Proxysql是基于MySQL的一款開源的中間件的產品，是一個靈活的MySQL代理層，可以實現讀寫分離，支持Query路由功能，支持動態指定某個SQL進行緩存，支持動態加載（無需重啟ProxySQL服務），故障切換和SQL過濾功能。 

軟件版本

硬件環境

本次搭建共需要在生產環境新增主機2臺，以下為需要的具體配置信息：

端口

需要在其中proxysql主機開放6032、6033端口供應用程序訪問及監控使用。

環境檢查

環境檢查，主要檢查硬件信息是否符合申請的指標以及軟件版本信息

MGR集群實現數據庫復制功能及高可用。Proxysql對應用程序提供訪問，對MGR集群進行讀寫分離，集群狀態檢測，實現故障切換。

MGR單主模式搭建

主從搭建

先將新增的一臺服務器搭建MySQL數據庫，將DMS主數據庫的備份數據傳送到服務器上，搭建主從使目前3臺數據庫的數據保持一致，為搭建MGR作準備。

集群搭建

修改my.cnf配置文件

修改主機名

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

開啟MGR

主節點：

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

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

#設置group_replication_bootstrap_group為ON是為了標示以后加入集群的服務器以這臺服務器為基準，以后加入的就不需要設置。

setglobal group_replication_bootstrap_group=on;

開啟組復制

startgroup_replication;

setglobal group_replication_bootstrap_group=off;

節點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;

節點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       |

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

#查詢主節點

selectvariable_value from performance_schema.global_status wherevariable_name=group_replication_primary_member;

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

| variable_value                       |

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

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

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

ProxySql讀寫分離搭建及驗證

在mgr集群上創建proxysql所需的賬號

#proxysql的監控賬戶

createuser monitor@% identified by Monitor@123;

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

#proxysql的對外訪問賬戶

createuser proxysql@% identified by Proxysql@123;

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

搭建yum環境，安裝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

啟動proxySql

systemctlstart proxysql

netstat-anlp | grep proxysql

6032是ProxySQL的管理端口號，6033是對外服務的端口號

ProxySQL的用戶名和密碼都是默認的admin

配置proxySql：配置訪問賬號及監控監控，在mgr主節點執行監控腳本。

管理員登錄ProxySQL

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

#配置監控賬號

setmysql-monitor_username=monitor;

setmysql-monitor_password=Monitor@123;

#配置默認組信息

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);

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

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

主節點定義為寫組10,從節點定義為只讀組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);

規劃讀寫組，添加節點設置讀寫分離規則，查看節點狀態。

#配置讀寫分離參數

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使內存數據永久存儲到磁盤，load使內存數據加載到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主節點執行監控腳本

查看各節點狀態

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;

在應用端驗證讀寫分離是否可用；驗證mgr故障轉移是否可用

測試讀負載均衡

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

驗證系統各功能是否正常

本次集群搭建中若出現問題，或搭建成功后系統功能不可用，可采用應用端保持連接原始數據庫，或切換回原始數據庫鏈接，以此確保架構改造失敗的回退安全性。