【技術(shù)方案分享】CCE proxysql+mysql 實(shí)現(xiàn)MySQL主從讀寫(xiě)分離

目錄

1.部署架構(gòu)圖

2.組件簡(jiǎn)介

3.部署前提

4.部署MySQL主從

4.1 通過(guò)配置項(xiàng)configmap創(chuàng)建MySQL master的配置文件

4.2 通過(guò)配置項(xiàng)configmap創(chuàng)建MySQL slave的配置文件

4.3 創(chuàng)建MySQL master工作負(fù)載

4.4 創(chuàng)建MySQL slave工作負(fù)載

4.5 MySQL master配置

5.部署proxySQL

5.1 通過(guò)配置項(xiàng)configmap創(chuàng)建proxysql的配置文件

5.2 創(chuàng)建proxySQL 工作負(fù)載

5.3 proxySQL配置數(shù)據(jù)庫(kù)讀寫(xiě)分離

5.4 proxySQL另一實(shí)例配置

6.驗(yàn)證

6.1 驗(yàn)證MySQL讀寫(xiě)分離

6.2 驗(yàn)證proxysql 負(fù)載均衡

7.FAQ

7.1 通過(guò)負(fù)載均衡數(shù)據(jù)庫(kù)后，SQL語(yǔ)句執(zhí)行報(bào)錯(cuò)

7.2 數(shù)據(jù)庫(kù)連接報(bào) 1251 錯(cuò)誤

7.3 ELB 負(fù)載均衡后連接失敗

1.部署架構(gòu)圖

2.組件簡(jiǎn)介

MySQL：關(guān)系型數(shù)據(jù)庫(kù)，按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)

proxySQL：proxySQL是靈活強(qiáng)大的MySQL代理層, 是一個(gè)能實(shí)實(shí)在在用在生產(chǎn)環(huán)境的MySQL中間件，可以實(shí)現(xiàn)讀寫(xiě)分離，支持 Query 路由功能，支持動(dòng)態(tài)指定某個(gè) SQL 進(jìn)行 cache，支持動(dòng)態(tài)加載配置、故障切換和一些 SQL的過(guò)濾功能。默認(rèn)管理連接端口6032，數(shù)據(jù)庫(kù)連接端口 3306。

3. 部署前提

具備可使用的CCE集群以及CCE節(jié)點(diǎn)

CCE購(gòu)買(mǎi)參考：https://support.huaweicloud.com/usermanual-cce/cce_01_0028.html

4. 部署MySQL主從

4.1 通過(guò)配置項(xiàng)configmap創(chuàng)建MySQL master的配置文件

MySQL主my.cnf文件

CCE控制臺(tái)，進(jìn)入配置中心下的配置項(xiàng)ConfigMap，點(diǎn)擊創(chuàng)建配置項(xiàng)，如下圖所示

my.cnf mysql-master配置內(nèi)容如下：

[mysqld] pid-file=/var/run/mysqld/mysqld.pid socket=/var/run/mysqld/mysqld.sock datadir=/var/lib/mysql secure-file-priv= NULL server-id=101 ##用于高可用區(qū)分服務(wù)的ID，MySQL主從ID不一致即可 log-bin=master-binlog

4.2 通過(guò)配置項(xiàng)configmap創(chuàng)建MySQL slave的配置文件

MySQL主my.cnf文件

CCE控制臺(tái)，進(jìn)入配置中心下的配置項(xiàng)ConfigMap，點(diǎn)擊創(chuàng)建配置項(xiàng)，如下圖所示

my.cnf mysql-slave配置內(nèi)容如下：

[mysqld] pid-file=/var/run/mysqld/mysqld.pid socket=/var/run/mysqld/mysqld.sock datadir=/var/lib/mysql secure-file-priv= NULL server-id=102 ##用于高可用區(qū)分服務(wù)的ID，MySQL主從ID不一致即可 log-bin=master-binlog

說(shuō)明：配置文件內(nèi)的其他參數(shù)可自行設(shè)置，當(dāng)前僅為演示，未進(jìn)行其他配置。

4.3 創(chuàng)建MySQL master工作負(fù)載

進(jìn)入華為云CCE控制臺(tái)，工作負(fù)載下的有狀態(tài)工作負(fù)載界面，點(diǎn)擊創(chuàng)建有狀態(tài)工作負(fù)載

創(chuàng)建MySQL master負(fù)載-工作負(fù)載基本信息

創(chuàng)建MySQL master負(fù)載-容器設(shè)置-step1-開(kāi)源鏡像中心：MySQL 8.0

創(chuàng)建MySQL master負(fù)載-容器設(shè)置-step2-環(huán)境變量：設(shè)置MySQL密碼

創(chuàng)建MySQL master負(fù)載-容器設(shè)置-step3-數(shù)據(jù)存儲(chǔ)：mysql-master-cnf掛載

創(chuàng)建MySQL master負(fù)載-容器設(shè)置-step4-數(shù)據(jù)存儲(chǔ)：掛載SFS存儲(chǔ)MySQL數(shù)據(jù)

創(chuàng)建MySQL master負(fù)載-工作負(fù)載訪問(wèn)設(shè)置-實(shí)例間發(fā)現(xiàn)服務(wù)：訪問(wèn)端口3306

創(chuàng)建MySQL master負(fù)載-工作負(fù)載訪問(wèn)設(shè)置-服務(wù)：節(jié)點(diǎn)訪問(wèn)，節(jié)點(diǎn)模式，訪問(wèn)端口3306，訪問(wèn)端口自動(dòng)生成

創(chuàng)建MySQL master負(fù)載-高級(jí)設(shè)置保持默認(rèn)即可，點(diǎn)擊創(chuàng)建

4.4 創(chuàng)建MySQL slave工作負(fù)載

進(jìn)入華為云CCE控制臺(tái)，工作負(fù)載下的有狀態(tài)工作負(fù)載界面，點(diǎn)擊創(chuàng)建有狀態(tài)工作負(fù)載

創(chuàng)建MySQL slave負(fù)載-工作負(fù)載基本信息

創(chuàng)建MySQL slave負(fù)載-容器設(shè)置-step1-開(kāi)源鏡像中心：MySQL 8.0

創(chuàng)建MySQL slave負(fù)載-容器設(shè)置-step2-環(huán)境變量：設(shè)置MySQL密碼

創(chuàng)建MySQL slave負(fù)載-容器設(shè)置-step3-數(shù)據(jù)存儲(chǔ)：mysql-slave-cnf掛載

創(chuàng)建MySQL slave負(fù)載-容器設(shè)置-step4-數(shù)據(jù)存儲(chǔ)：掛載SFS存儲(chǔ)MySQL數(shù)據(jù)

創(chuàng)建MySQL slave負(fù)載-工作負(fù)載訪問(wèn)設(shè)置-實(shí)例間發(fā)現(xiàn)服務(wù)：訪問(wèn)端口3306

創(chuàng)建MySQL slave負(fù)載-工作負(fù)載訪問(wèn)設(shè)置-服務(wù)：節(jié)點(diǎn)訪問(wèn)，節(jié)點(diǎn)模式，訪問(wèn)端口3306，訪問(wèn)端口自動(dòng)生成

創(chuàng)建MySQL slave負(fù)載-高級(jí)設(shè)置:設(shè)置與MySQL master負(fù)載的反親和性

創(chuàng)建負(fù)載

4.5 MySQL master配置

登錄MySQL master數(shù)據(jù)庫(kù)，查看配置項(xiàng)是否生效

show variables like '%server%'; show variables like '%log_bin%';

新建主庫(kù)的復(fù)制賬號(hào)并授權(quán)

CREATE USER 'backup'@'%' IDENTIFIED BY 'backupmima'； ## 8.0數(shù)據(jù)庫(kù)請(qǐng)使用 CREATE USER 'backup'@'%' IDENTIFIED WITH mysql_native_password BY 'backupmima'； GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%'; SHOW GRANTS FOR 'backup'@'%';

主數(shù)據(jù)庫(kù) master status

登錄MySQL slave數(shù)據(jù)庫(kù)，查看配置項(xiàng)是否生效

show variables like '%server%'; show variables like '%log_bin%';

MySQL slave增加MySQL master連接配置信息。

CHANGE master to master_host='mysql-master.default.svc.cluster.local', master_user='backup', master_password='backupmima', master_port=3306, master_log_file='master-binlog.0000003', master_log_pos=1186, master_connect_retry=30;

說(shuō)明

master_host='mysql-master.default.svc.cluster.local' 為MySQL master服務(wù)集群內(nèi)網(wǎng)IP

master_user='backup',master_password='backupmima' 為復(fù)制數(shù)據(jù)庫(kù)的賬號(hào)和密碼

master_log_file='master-binlog.0000003', 為MySQL master的binlog文件

master_log_pos=1186, 為MySQL master的Posistion值

master_connect_retry=30 主節(jié)點(diǎn)宕機(jī)，從服務(wù)器的嘗試連接時(shí)間

MySQL slave開(kāi)啟設(shè)置復(fù)制

start slave; show slave status \G;

測(cè)試數(shù)據(jù)復(fù)制

登錄MySQL master執(zhí)行以下命令：

CREATE databases test; USE test; CREATE table test(name char(10),age int(3));

登錄MySQL slave執(zhí)行以下命令：

SHOW databases; USE test; SHOW tables;

5. 部署proxySQL

5.1 通過(guò)配置項(xiàng)configmap創(chuàng)建proxysql的配置文件

proxysql proxysql.cnf文件

CCE控制臺(tái)，進(jìn)入配置中心下的配置項(xiàng)ConfigMap，點(diǎn)擊創(chuàng)建配置項(xiàng)，如下圖所示

proxysql.cnf 配置內(nèi)容如下：

datadir="/var/lib/proxysql" admin_variables = { admin_credentials="admin:admin" mysql_ifaces="0.0.0.0:6032" refresh_interval=2000 cluster_username="admin" cluster_password="admin" cluster_check_interval_ms=200 cluster_check_status_frequency=100 cluster_mysql_query_rules_save_to_disk=true cluster_mysql_servers_save_to_disk=true cluster_mysql_users_save_to_disk=true cluster_proxysql_servers_save_to_disk=true cluster_mysql_query_rules_diffs_before_sync=1 cluster_mysql_servers_diffs_before_sync=1 cluster_mysql_users_diffs_before_sync=1 cluster_proxysql_servers_diffs_before_sync=1 } mysql_variables= { monitor_password="monitor" monitor_galera_healthcheck_interval=1000 threads=2 max_connections=2048 default_query_delay=0 default_query_timeout=10000 poll_timeout=2000 interfaces="0.0.0.0:3306;0.0.0.0:33062" default_schema="information_schema" stacksize=1048576 connect_timeout_server=10000 monitor_history=60000 monitor_connect_interval=20000 monitor_ping_interval=10000 ping_timeout_server=200 commands_stats=true sessions_sort=true have_ssl=false ssl_p2s_ca="" ssl_p2s_cert="" ssl_p2s_key="" ssl_p2s_cipher="ECDHE-RSA-AES128-GCM-SHA256" }

5.2 創(chuàng)建proxySQL 工作負(fù)載

進(jìn)入華為云CCE控制臺(tái)，工作負(fù)載下的有狀態(tài)工作負(fù)載界面，點(diǎn)擊創(chuàng)建有狀態(tài)工作負(fù)載

創(chuàng)建proxysql負(fù)載-工作負(fù)載基本信息

創(chuàng)建proxysql負(fù)載-容器設(shè)置-step1-我的鏡像：proxysql:V2.0

說(shuō)明?鏡像來(lái)源?https://registry.hub.docker.com/r/percona/proxysql?推送到華為云鏡像倉(cāng)庫(kù)

創(chuàng)建proxysql負(fù)載-容器設(shè)置-step2-數(shù)據(jù)存儲(chǔ)：掛載Configmap 配置文件

創(chuàng)建proxysql負(fù)載-容器設(shè)置-step2-數(shù)據(jù)存儲(chǔ)：掛載SFS存儲(chǔ)proxysql數(shù)據(jù)

創(chuàng)建proxysql負(fù)載-工作負(fù)載訪問(wèn)設(shè)置-實(shí)例間發(fā)現(xiàn)服務(wù)：訪問(wèn)端口3306

創(chuàng)建proxysql負(fù)載-工作負(fù)載訪問(wèn)設(shè)置-服務(wù)：負(fù)載均衡，節(jié)點(diǎn)級(jí)別，容器端口3306、訪問(wèn)端口3306

說(shuō)明：負(fù)載均衡配置完成后，服務(wù)器安全組需要開(kāi)放 100.125.0.0/16 網(wǎng)段的安全組入方向規(guī)則

創(chuàng)建proxysql負(fù)載-高級(jí)設(shè)置保持默認(rèn)即可，點(diǎn)擊創(chuàng)建

5.3 proxySQL配置數(shù)據(jù)庫(kù)讀寫(xiě)分離

5.3.1 MySQL master創(chuàng)建用于proxysql連接的用戶(用戶數(shù)據(jù)會(huì)同步到從庫(kù))

CREATE USER 'proxysql'@'%' IDENTIFIED with mysql_native_password BY 'proxysqlmima'; GRANT ALL ON *.* TO 'proxysql'@'%'; flush privilrges;

5.3.2 MySQL master創(chuàng)建用于proxysql健康監(jiān)測(cè)的用戶(用戶數(shù)據(jù)會(huì)同步到從庫(kù))

CREATE USER 'monitor'@'%' IDENTIFIED with mysql_native_password BY 'monitormima'; GRANT SELECT ON *.* TO 'monitor'@'%'; flush privilrges;

5.3.3 登錄proxysql 配置mysql_server 信息

說(shuō)明：分別插入mysql的節(jié)點(diǎn)信息，10表示master（寫(xiě)），20表示slave（讀）

mysql -h 127.0.0.1 -P 6032 -u admin -p insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'mysql-master.default.svc.cluster.local',3306,1,'master'); insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'mysql-slave.default.svc.cluster.local',3306,1,'slave');

字段說(shuō)明：

hostgroup_id：一個(gè)組ID，組內(nèi)可包含多個(gè)MySQL地址

hostname：MySQL訪問(wèn)地址

port：訪問(wèn)端口

weight：訪問(wèn)權(quán)重

comment：文本，用于備注節(jié)點(diǎn)信息

生效配置并把配置保存到磁盤(pán)

load mysql servers to runtime; save mysql servers to disk;

5.3.4 登錄proxysql 配置mysql_users 信息

說(shuō)明：proxysql 通過(guò)6032鏈接管理接口，默認(rèn)賬號(hào)密碼為 admin/admin

mysql -h 127.0.0.1 -P 6032 -u admin -p insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','proxysqlmima',10,1);

字段說(shuō)明

sername、password：proxysql連接MySQL的用戶和密碼

default_hostgroup：沒(méi)有匹配到規(guī)則的SQL直接訪問(wèn)這個(gè)ID內(nèi)的數(shù)據(jù)庫(kù)

transaction_persistent：一個(gè)事務(wù)內(nèi)的多條 SQL，只會(huì)路由到一個(gè)主機(jī)組中

生效配置并把配置保存到磁盤(pán)

load mysql users to runtime; save mysql users to disk;

5.3.5 登錄proxysql 配置MySQL路由規(guī)則

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(1,1,'^SELECT.*FOR UPDATE$',10,1); insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(2,1,'^SELECT',20,1); insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(3,1,'^SHOW',20,1);

字段說(shuō)明

rule_id：規(guī)則ID

active：設(shè)置為1時(shí)，為啟用規(guī)則

match_digest：SQL匹配的規(guī)則

destination_hostgroup：將匹配的規(guī)則路由到這個(gè)主機(jī)組

apply：設(shè)置為1時(shí)，在匹配和處理此規(guī)則后，將不再評(píng)估進(jìn)一步的查詢

load mysql query rules to runtime; save mysql query rules to disk;

5.3.6 配置數(shù)據(jù)庫(kù)健康監(jiān)測(cè)賬號(hào)

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

生效配置并把配置保存到磁盤(pán).

load mysql variables to runtime; save mysql variables to disk;

5.4 proxySQL另一實(shí)例配置

按照同樣的方法進(jìn)行另一實(shí)例proxysql的配置

說(shuō)明：也可通過(guò)proxySQL集群的方式進(jìn)行部署。實(shí)現(xiàn)各個(gè)proxysql之間的數(shù)據(jù)同步

6. 驗(yàn)證

6.1 驗(yàn)證MySQL讀寫(xiě)分離

proxysql連接數(shù)據(jù)并執(zhí)行以下命令

mysql -h 127.0.0.1 -P 3306 -u proxysql -p ##使用proxysql 連接數(shù)據(jù)庫(kù) show databases; use test; show tables; DESC test; insert into test(name,age) values('caichunfu',10);

proxysql 連接管理接口，查看路由記錄；

stats_mysql_query_digest：通過(guò)ProxySQL路由出去的各類(lèi)查詢相關(guān)統(tǒng)計(jì)數(shù)據(jù)

6.2 驗(yàn)證proxysql 負(fù)載均衡

查看Proxysql的訪問(wèn)方式

訪問(wèn)方式為 124.71.75.74:3306

使用navicat 連接查看數(shù)據(jù)庫(kù)

驗(yàn)證負(fù)載均衡能力-step1 連接進(jìn)入數(shù)據(jù)

驗(yàn)證負(fù)載均衡能力-step2 輪流刪除實(shí)例，驗(yàn)證Proxysql負(fù)載均衡能力

刪除一個(gè)實(shí)例

驗(yàn)證

刪除另一個(gè)實(shí)例

驗(yàn)證

7 FAQ

7.1 通過(guò)負(fù)載均衡數(shù)據(jù)庫(kù)后，SQL語(yǔ)句執(zhí)行報(bào)錯(cuò)。

[Error] 9006 - ProxySQL Error: connection is locked to ......

解決辦法：登錄proxysql管理端，執(zhí)行以下命令

set mysql-set_query_lock_on_hostgroup=0; load mysql variables to runtime; save mysql variables to disk;

7.2 數(shù)據(jù)庫(kù)連接報(bào) 1251 錯(cuò)誤

[Error] 1251 Client does not....

解決辦法：MySQL8.0的密碼加密規(guī)則修改為mysql_native_password

ALTER USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password BY 'proxysqlmima'; #修改加密規(guī)則 ALTER USER 'proxysql'@'%' IDENTIFIED BY 'proxysqlmima' PASSWORD EXPIRE NEVER; #更新一下用戶的密碼 FLUSH PRIVILEGES;

7.3 ELB 負(fù)載均衡后連接失敗

解決辦法：安全組入方向放通 100.125.0.0/16 網(wǎng)段的安全組

MySQL 云容器引擎 CCE

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。