Keepalived LVSDR MariaDB 主主業(yè)務(wù)場(chǎng)景與數(shù)據(jù)庫(kù)核心訴求在企業(yè)級(jí)應(yīng)用中數(shù)據(jù)庫(kù)作為業(yè)務(wù)數(shù)據(jù)的 “核心載體”其高可用性、讀寫性能、數(shù)據(jù)一致性直接決定業(yè) 務(wù)能否穩(wěn)定運(yùn)行。無論是電商交易系統(tǒng)訂單生成、庫(kù)存扣減、金融支付平臺(tái)交易對(duì)賬、資金流 轉(zhuǎn)還是政務(wù)管理系統(tǒng)數(shù)據(jù)上報(bào)、業(yè)務(wù)審批均對(duì)數(shù)據(jù)庫(kù)提出以下剛性需求無間斷服務(wù)高可用數(shù)據(jù)庫(kù)需實(shí)現(xiàn) 24×7 小時(shí)不間斷運(yùn)行避免因單點(diǎn)故障如數(shù)據(jù)庫(kù)服務(wù)器宕機(jī)、磁盤損壞、網(wǎng)絡(luò)中 斷導(dǎo)致業(yè)務(wù)中斷。例如電商平臺(tái)秒殺活動(dòng)中若數(shù)據(jù)庫(kù)不可用將直接導(dǎo)致訂單無法生成每 中斷 1 分鐘可能造成數(shù)萬(wàn)元營(yíng)收損失金融系統(tǒng)中數(shù)據(jù)庫(kù)故障可能引發(fā)交易對(duì)賬異常甚至觸發(fā) 合規(guī)風(fēng)險(xiǎn)。高并發(fā)承載讀寫性能隨著用戶規(guī)模增長(zhǎng)數(shù)據(jù)庫(kù)面臨的讀寫請(qǐng)求呈指數(shù)級(jí)上升如日均 SQL 執(zhí)行量從 100 萬(wàn)次增至 1 億次。單臺(tái)數(shù)據(jù)庫(kù)服務(wù)器的 CPU、內(nèi)存、IO 能力易成為瓶頸讀請(qǐng)求過多會(huì)導(dǎo)致查詢延遲如用 戶查詢訂單列表超時(shí)寫請(qǐng)求集中會(huì)造成事務(wù)阻塞如多用戶同時(shí)提交訂單導(dǎo)致庫(kù)存更新排 隊(duì)。數(shù)據(jù)零丟失可靠性業(yè)務(wù)數(shù)據(jù)需具備 “抗丟失” 能力即使遭遇硬件故障或軟件異常也需保證數(shù)據(jù)不損壞、不丟失。例 如用戶充值記錄、訂單信息若因數(shù)據(jù)庫(kù)故障丟失將直接引發(fā)用戶投訴與信任危機(jī)同時(shí)多節(jié) 點(diǎn)間的數(shù)據(jù)需實(shí)時(shí)同步避免出現(xiàn) “主庫(kù)數(shù)據(jù)已更新從庫(kù)仍展示舊數(shù)據(jù)” 的一致性問題。靈活擴(kuò)展可擴(kuò)展性業(yè)務(wù)增長(zhǎng)過程中需支持 “按需擴(kuò)展” 數(shù)據(jù)庫(kù)能力讀壓力增大時(shí)可快速新增讀節(jié)點(diǎn)寫壓力上升時(shí) 可優(yōu)化寫分發(fā)策略避免因架構(gòu)僵化導(dǎo)致 “業(yè)務(wù)倒逼重構(gòu)” 的被動(dòng)局面。傳統(tǒng)數(shù)據(jù)庫(kù)架構(gòu)的痛點(diǎn)與局限在采用《Keepalived LVSDR MariaDB 主主》方案前多數(shù)企業(yè)曾使用 “單節(jié)點(diǎn)數(shù)據(jù)庫(kù)” 或 “簡(jiǎn)單 主從架構(gòu)”但面臨以下難以突破的瓶頸單節(jié)點(diǎn)數(shù)據(jù)庫(kù)單點(diǎn)故障風(fēng)險(xiǎn)致命問題核心數(shù)據(jù)庫(kù)僅部署在一臺(tái)服務(wù)器上一旦服務(wù)器硬件故障如電源損壞、磁盤壞道或 軟件崩潰如 MariaDB 進(jìn)程異常退出將導(dǎo)致全量業(yè)務(wù)中斷。恢復(fù)效率低依賴人工干預(yù)恢復(fù)如更換服務(wù)器、重建數(shù)據(jù)庫(kù)、恢復(fù)備份平均恢復(fù)時(shí)間 MTTR通常超過 30 分鐘遠(yuǎn)無法滿足 “秒級(jí)切換” 的業(yè)務(wù)需求若備份數(shù)據(jù)不完整還可 能導(dǎo)致部分業(yè)務(wù)數(shù)據(jù)永久丟失。簡(jiǎn)單主從架構(gòu)一主一從讀寫瓶頸與切換缺陷讀性能局限雖然通過 “主庫(kù)寫、從庫(kù)讀” 分?jǐn)傋x壓力但從庫(kù)僅能擴(kuò)展讀能力無法緩解主庫(kù) 的寫壓力如大量訂單寫入仍集中在主庫(kù)且從庫(kù)數(shù)量增多時(shí)缺乏統(tǒng)一的讀請(qǐng)求分發(fā)機(jī) 制易導(dǎo)致部分從庫(kù)過載如某臺(tái)從庫(kù)承擔(dān) 80% 讀請(qǐng)求、部分從庫(kù)空閑。高可用缺陷主庫(kù)故障時(shí)需手動(dòng)將從庫(kù)提升為新主庫(kù)再修改業(yè)務(wù)系統(tǒng)的數(shù)據(jù)庫(kù)連接地址 切換過程耗時(shí)且易出錯(cuò)如忘記同步從庫(kù)未應(yīng)用的 binlog 導(dǎo)致數(shù)據(jù)不一致同時(shí)從庫(kù)僅 作為 “備用節(jié)點(diǎn)”寫請(qǐng)求始終依賴主庫(kù)主庫(kù)寫瓶頸無法突破無負(fù)載均衡請(qǐng)求分發(fā)混亂部分企業(yè)嘗試用 “業(yè)務(wù)層硬編碼連接地址” 實(shí)現(xiàn)讀寫分離如讀請(qǐng)求連從庫(kù) IP寫請(qǐng)求連主庫(kù) IP 但存在兩大問題① 缺乏故障檢測(cè)機(jī)制若某臺(tái)從庫(kù)宕機(jī)業(yè)務(wù)層無法實(shí)時(shí)感知仍會(huì)將讀請(qǐng)求分發(fā)至故障節(jié)點(diǎn)導(dǎo) 致部分讀業(yè)務(wù)失敗② 擴(kuò)展性差新增讀節(jié)點(diǎn)時(shí)需修改業(yè)務(wù)代碼中的連接地址列表重啟服務(wù)才能生效不符合 “無 感知擴(kuò)展” 的運(yùn)維需求。**數(shù)據(jù)同步與一致性風(fēng)險(xiǎn): **傳統(tǒng)主從架構(gòu)依賴 MariaDB 原生的 binlog 同步若網(wǎng)絡(luò)延遲或主庫(kù) binlog 丟失會(huì)導(dǎo)致從庫(kù)數(shù)據(jù) 滯后或同步失敗且主庫(kù)故障時(shí)若從庫(kù)未完全同步主庫(kù)數(shù)據(jù)強(qiáng)制切換會(huì)造成 “數(shù)據(jù)斷層”如主 庫(kù)已提交的訂單從庫(kù)未記錄技術(shù)方案的選型邏輯針對(duì)上述痛點(diǎn)需構(gòu)建一套 “高可用負(fù)載均衡 雙主互備 讀寫協(xié)同” 的數(shù)據(jù)庫(kù)架構(gòu)而《Keepalived LVSDR MariaDB 主主》組合正是基于以下核心訴求選型**MariaDB 主主突破寫瓶頸與雙活備份 **采用 “雙主互備” 模式兩臺(tái) MariaDB 均為主庫(kù)可同時(shí)處理寫請(qǐng)求徹底解決傳統(tǒng)主從架 構(gòu)的 “寫依賴單主” 問題寫性能理論上提升 2 倍兩臺(tái)主庫(kù)實(shí)時(shí)同步數(shù)據(jù)通過 binlog 雙向同步任一主庫(kù)故障時(shí)另一主庫(kù)已擁有完整數(shù) 據(jù)避免數(shù)據(jù)丟失同時(shí)支持 “讀寫請(qǐng)求均分發(fā)至雙主”進(jìn)一步提升整體并發(fā)能力。**LVSDR 模式高效分發(fā)讀寫請(qǐng)求 **LVS 作為四層負(fù)載均衡器基于 IP 和端口轉(zhuǎn)發(fā)請(qǐng)求具備超高并發(fā)承載能力單機(jī)可支撐 10 萬(wàn) 并發(fā)連接遠(yuǎn)超 Nginx 等七層負(fù)載均衡器采用 DR直接路由模式請(qǐng)求僅經(jīng)過 LVS 轉(zhuǎn)發(fā)至后端 MariaDB 節(jié)點(diǎn)響應(yīng)數(shù)據(jù)直接從 MariaDB 返回給客戶端避免 “請(qǐng)求回程流量” 占用 LVS 帶寬轉(zhuǎn)發(fā)效率接近物理機(jī)直連支持 “健康檢查”實(shí)時(shí)檢測(cè) MariaDB 節(jié)點(diǎn)狀態(tài)若某臺(tái)主庫(kù)宕機(jī)LVS 自動(dòng)將請(qǐng)求分發(fā)至另 一臺(tái)健康主庫(kù)避免業(yè)務(wù)訪問故障節(jié)點(diǎn)。**Keepalived負(fù)載均衡層高可用 **LVS 作為請(qǐng)求分發(fā)核心若自身單點(diǎn)故障將導(dǎo)致全量數(shù)據(jù)庫(kù)請(qǐng)求無法轉(zhuǎn)發(fā)。通過 Keepalived 的 VRRP 協(xié)議實(shí)現(xiàn) LVS 主備高可用主 LVS 節(jié)點(diǎn)故障時(shí)備 LVS 節(jié)點(diǎn)可在 1-3 秒內(nèi)自動(dòng)接管 虛擬 IPVIP實(shí)現(xiàn) “無感知切換”徹底消除負(fù)載均衡層單點(diǎn)風(fēng)險(xiǎn)支持 “優(yōu)先級(jí)配置”可根據(jù) LVS 節(jié)點(diǎn)性能設(shè)置主備角色確保高性能節(jié)點(diǎn)優(yōu)先承擔(dān)轉(zhuǎn)發(fā)任務(wù)。項(xiàng)目?jī)r(jià)值與預(yù)期目標(biāo)通過部署《Keepalived LVSDR MariaDB 主主》架構(gòu)預(yù)期實(shí)現(xiàn)以下技術(shù)與業(yè)務(wù)價(jià)值高可用升級(jí)數(shù)據(jù)庫(kù)層可用性從 99.9% 提升至 99.99%年均故障中斷時(shí)間從 8.76 小時(shí)降至 52.56 分鐘核心業(yè)務(wù)如訂單、支付無服務(wù)中斷風(fēng)險(xiǎn)性能翻倍寫性能從單主 500 TPS 提升至雙主 1000 TPS讀性能支持通過新增從節(jié)點(diǎn)無限擴(kuò)展 LVS 統(tǒng)一分發(fā)讀請(qǐng)求95% SQL 查詢響應(yīng)時(shí)間200ms數(shù)據(jù)可靠雙主實(shí)時(shí)同步數(shù)據(jù)任一節(jié)點(diǎn)故障無數(shù)據(jù)丟失LVS 健康檢查 Keepalived 主備切換 實(shí)現(xiàn) “故障自動(dòng)轉(zhuǎn)移”無需人工干預(yù)運(yùn)維高效新增數(shù)據(jù)庫(kù)節(jié)點(diǎn)時(shí)僅需接入 LVS 集群無需修改業(yè)務(wù)代碼負(fù)載均衡與數(shù)據(jù)庫(kù)節(jié)點(diǎn)狀 態(tài)可通過監(jiān)控平臺(tái)實(shí)時(shí)查看故障定位效率提升 70%。MariaDB 主從復(fù)制原理MariaDB 主從復(fù)制是指主庫(kù)將數(shù)據(jù)變更以日志形式傳輸給從庫(kù)從庫(kù)重放日志實(shí)現(xiàn)數(shù)據(jù)一致。主庫(kù) 二進(jìn)制日志binlog主庫(kù)核心日志記錄所有修改數(shù)據(jù)的 SQL增刪改、建表等是主 從同步的 “數(shù)據(jù)源頭”從庫(kù) 中繼日志relay log從庫(kù)本地日志存儲(chǔ)從主庫(kù)獲取的 binlog 內(nèi)容避免直接通過網(wǎng)絡(luò) 讀取主庫(kù) binlog 主庫(kù) binlog dump 線程負(fù)責(zé)向從庫(kù)傳輸 binlog從庫(kù) IO 線程負(fù)責(zé)連接主庫(kù)、拉取 binlog 并寫入本地 relay log從庫(kù) SQL 線程負(fù)責(zé)讀取 relay log、執(zhí)行其中的 SQL還原主庫(kù)數(shù)據(jù)變更。主從同步完整原理流程步驟 1主庫(kù)記錄數(shù)據(jù)變更到 binlog當(dāng)主庫(kù)執(zhí)行數(shù)據(jù)變更操作如INSERT/UPDATE/DELETE 、CREATE TABLE等時(shí)操作先寫入**事務(wù)日志redo log**保證數(shù)據(jù)持久化事務(wù)提交時(shí)MySQL 會(huì)將該操作的 SQL或數(shù)據(jù)變更事件按順序?qū)懭?binlog **布驟 2從庫(kù) IO 線程連接主庫(kù)請(qǐng)求同步 binlog **從庫(kù)啟動(dòng)后IO線程 會(huì)主動(dòng)連接主庫(kù)并向主庫(kù)發(fā)送兩個(gè)關(guān)鍵信息要同步的主庫(kù) binlog 文件名稱要同步的binlog 位置position即從哪個(gè)位置開始讀取 首次同步時(shí)從庫(kù)會(huì)請(qǐng)求主庫(kù)全量數(shù)據(jù)的 binlog或先全量備份再同步增量后續(xù)僅請(qǐng)求增量 binlog。步驟 3主庫(kù) binlog dump 線程傳輸 binlog 給從庫(kù)主庫(kù)接收到從庫(kù)的同步請(qǐng)求后會(huì)創(chuàng)建binlog dump線程 響應(yīng)該線程根據(jù)從庫(kù)指定的 binlog 文件和位置讀取主庫(kù) binlog 中的增量數(shù)據(jù)將讀取到的 binlog 內(nèi)容事件通過網(wǎng)絡(luò)傳輸給從庫(kù)的 IO 線程主庫(kù)會(huì)記錄 “哪些從庫(kù)正在同步哪個(gè) binlog 位置”確保從庫(kù)斷線重連后能續(xù)傳。**步驟 4從庫(kù) IO 線程寫入 relay log **從庫(kù) IO 線程接收到主庫(kù)傳輸?shù)?binlog 內(nèi)容后不會(huì)直接執(zhí)行而是先寫入本地的relay log中繼日志避免網(wǎng)絡(luò)中斷導(dǎo)致數(shù)據(jù)丟失同時(shí)更新從庫(kù)狀態(tài)文件master.info/relay-log.info記錄已同步的主庫(kù) binlog 文件 位置本地 relay log 的文件 位置確保從庫(kù)重啟后能繼續(xù)同步不重復(fù) / 遺漏數(shù)據(jù)。**步驟 5從庫(kù) SQL 線程重放 relay log實(shí)現(xiàn)數(shù)據(jù)一致 **從庫(kù)SQL線程 會(huì)實(shí)時(shí)讀取 relay log 中的內(nèi)容按順序解析 relay log 中的 binlog 事件SQL 或行變更在從庫(kù)本地執(zhí)行這些事件對(duì)應(yīng)的操作還原主庫(kù)的數(shù)據(jù)變更執(zhí)行完成后更新relay-log.info標(biāo)記已處理的 relay log 位置避免重復(fù)執(zhí)行。主主復(fù)制只需要把主節(jié)點(diǎn)當(dāng)做從節(jié)點(diǎn)、從節(jié)點(diǎn)當(dāng)做主節(jié)點(diǎn)再做一遍項(xiàng)目實(shí)踐項(xiàng)目環(huán)境主機(jī)名IP 地址網(wǎng)關(guān)DNSVIP地址服務(wù)器角色client2.laogao.cloud10.1.1.21(vmnet1)10.1.1.20223.5.5.5無客戶端client1.laogao.cloud10.1.8.21(vmnet8)10.1.8.20223.5.5.5無客戶端router.laogao.cloud10.1.8.20(vmnet8) 10.1.1.20(vmnet1)10.1.8.2 無網(wǎng)關(guān)223.5.5.5 無DNS無路由器ha1.laogao.cloud10.1.8.13(vmnet8)10.1.8.20223.5.5.510.1.8.100LVSkeepalived 服務(wù)器ha2.laogao.cloud10.1.8.14(vmnet8)10.1.8.20223.5.5.510.1.8.100LVSkeepalived 服務(wù)器db1.laogao.cloud10.1.8.11(vmnet8)10.1.8.20223.5.5.510.1.8.100db服務(wù)器db2.laogao.cloud10.1.8.12(vmnet8)10.1.8.20223.5.5.510.1.8.100db服務(wù)器基礎(chǔ)配置主機(jī)名、IP 地址、網(wǎng)關(guān)# client2:[rootcentos7 ~14:44:04]# hostnamectl set-hostname client2.ljw.cloud[rootcentos7 ~14:44:29]# bash[rootclient2 ~14:44:31]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.1.21/24 ipv4.gateway 10.1.8.20 ipv4.dns 223.5.5.5 autoconnect yes[rootclient2 ~14:45:38]# nmcli connection up ens33# client1:[rootcentos7 ~14:39:23]# hostnamectl set-hostname client1.ljw.cloud[rootcentos7 ~14:40:11]# bash[rootclient1 ~14:40:24]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.21/24 ipv4.gateway 10.1.8.20 ipv4.dns 223.5.5.5 autoconnect yes[rootclient1 ~14:41:37]# nmcli connection up ens33# router:[rootcentos7 ~14:45:56]# hostnamectl set-hostname router.ljw.cloud[rootcentos7 ~14:46:10]# bash[rootrouter ~14:46:12]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.20/24 ipv4.gateway 10.1.8.2 ipv4.dns 223.5.5.5 autoconnect yes[rootrouter ~14:47:21]# nmcli connection up ens33[rootrouter ~14:47:35]# nmcli connection add type ethernet con-name ens35 ifname ens35 ipv4.method manual ipv4.addresses 10.1.1.20/24 autoconnect yes[rootrouter ~14:48:37]# nmcli connection up ens35# ha1:[rootcentos7 ~14:49:00]# hostnamectl set-hostname ha1.ljw.cloud[rootcentos7 ~14:49:17]# bash[rootha1 ~14:49:19]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.13/24 ipv4.gateway 10.1.8.20 ipv4.dns 223.5.5.5 autoconnect yes[rootha1 ~14:50:10]# nmcli connection up ens33# ha2:[rootcentos7 ~14:50:29]# hostnamectl set-hostname ha2.ljw.cloud[rootcentos7 ~14:50:48]# bash[rootha2 ~14:50:50]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.14/24 ipv4.gateway 10.1.8.20 ipv4.dns 223.5.5.5 autoconnect yes[rootha2 ~14:51:41]# nmcli connection up ens33# db1:[rootcentos7 ~14:51:57]# hostnamectl set-hostname db1.ljw.cloud[rootcentos7 ~14:52:07]# bash[rootdb1 ~14:52:10]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.11/24 ipv4.gateway 10.1.8.20 ipv4.dns 223.5.5.5 autoconnect yes[rootdb1 ~14:52:54]# nmcli connection up ens33# db2:[rootcentos7 ~14:53:54]# hostnamectl set-hostname db2.ljw.cloud[rootcentos7 ~14:54:09]# bash[rootdb2 ~14:54:11]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.12/24 ipv4.gateway 10.1.8.20 ipv4.dns 223.5.5.5 autoconnect yes[rootdb2 ~14:54:48]# nmcli connection up ens33配置 router# 開啟路由[rootrouter ~14:58:55]# echo net.ipv4.ip_forward1 /etc/sysctl.conf# 或者sed -i s/ip_forward0/ip_forward1/g /etc/sysctl.conf[rootrouter ~]# sysctl -p[rootrouter ~15:23:21]# systemctl enable firewalld --now[rootrouter ~15:27:10]# firewall-cmd --set-defult-zonetrustedsuccess[rootrouter ~15:27:36]# firewall-cmd --add-masqueradesuccess[rootrouter ~15:27:52]# firewall-cmd --add-masquerade --permanentsuccessMariaDB 安裝和初始化配置 db1# 安裝軟件包[rootdb1 ~15:00:16]# yum install -y mariadb-server# 開啟二進(jìn)制日志[rootdb1 ~15:43:45]# vim /etc/my.cnf.d/server.cnf[mysqld]# 在mysqld塊最后添加如下內(nèi)容server-id1log_binmysql-binrelay_logmysql-relay-bin# 啟用并啟動(dòng)服務(wù)[rootdb1 ~15:44:20]# systemctl enable mariadb --now# 安全初始化[rootdb1 ~15:44:25]# mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE!PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, well need the current password for the root user. If youve just installed MariaDB, and you havent set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from localhost. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named test that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If youve completed all of the above steps, your MariaDB installation should now be secure. Thanksforusing MariaDB!配置 db2# 安裝軟件包[rootdb2 ~15:00:35]# yum install -y mariadb-server# 開啟二進(jìn)制日志[rootdb2 ~15:49:34]# vim /etc/my.cnf.d/server.cnf# 在mysqld塊最后添加如下內(nèi)容server-id2log_binmysql-binrelay_logmysql-relay-bin# 啟用并啟動(dòng)服務(wù)[rootdb2 ~15:49:53]# systemctl enable mariadb --now# 安全初始化[rootdb2 ~15:50:09]# mysql_secure_installationMariaDB 主從db2-db1主庫(kù)db1從庫(kù)db2# 配置主數(shù)據(jù)庫(kù)MariaDB[(none)]grant replication slave, replication client on *.* torepl10.1.8.12identified by123;Query OK,0rows affected(0.00sec)MariaDB[(none)]flush privileges;Query OK,0rows affected(0.00sec)MariaDB[(none)]show master status\G;***************************1. row *************************** File: mysql-bin.000003 Position:1722Binlog_Do_DB: Binlog_Ignore_DB:1rowinset(0.00sec)ERROR: No query specified# 配置從數(shù)據(jù)庫(kù)MariaDB[(none)]change master tomaster_host10.1.8.11, -master_userrepl, -master_password123, -master_port3306, -master_log_filemysql-bin.000003, -master_log_pos1722, -master_connect_retry30;Query OK,0rows affected(0.00sec)MariaDB[(none)]show slave status\G;***************************1. row *************************** Slave_IO_State: Master_Host:10.1.8.11 Master_User: repl Master_Port:3306Connect_Retry:30Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos:1722Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos:4Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No......1rowinset(0.00sec)ERROR: No query specified# 啟動(dòng)同步再次查看狀態(tài)MariaDB[(none)]start slave;Query OK,0rows affected(0.00sec)MariaDB[(none)]show slave status\G ***************************1. row *************************** Slave_IO_State: Waitingformaster to send event Master_Host:10.1.8.11 Master_User: repl Master_Port:3306Connect_Retry:30Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos:1722Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos:529Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes........# 確保 Slave_IO_Running 和 Slave_SQL_Running 狀態(tài)為yesMariaDB 主從db1-db2主庫(kù)db2從庫(kù)db1# 配置主數(shù)據(jù)庫(kù)MariaDB[(none)]grant replication slave, replication client on *.* torepl10.1.8.11identified by123;Query OK,0rows affected(0.00sec)MariaDB[(none)]show master status\G;***************************1. row *************************** File: mysql-bin.000003 Position:1647Binlog_Do_DB: Binlog_Ignore_DB:1rowinset(0.00sec)ERROR: No query specified# 配置從數(shù)據(jù)庫(kù)MariaDB[(none)]change master tomaster_host10.1.8.12, -master_userrepl, -master_password123, -master_port3306, -master_log_filemysql-bin.000003, -master_log_pos1647, -master_connect_retry30;Query OK,0rows affected(0.01sec)MariaDB[(none)]show slave status\G;***************************1. row *************************** Slave_IO_State: Waitingformaster to send event Master_Host:10.1.8.12 Master_User: repl Master_Port:3306Connect_Retry:30Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos:1647Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos:529Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes配置 LVS-RS所有后端主機(jī)都要做相同配置。# 增加虛擬網(wǎng)卡[rootdb1 ~16:13:27]# nmcli connection add type dummy ifname dummy con-name dummy ipv4.method manual ipv4.addresses 10.1.8.100/32[rootdb1 ~16:14:20]# nmcli connection up dummy# 配置arp參數(shù)關(guān)閉arp對(duì)dummy網(wǎng)卡的解析cat/etc/sysctl.confEOF net.ipv4.conf.all.arp_ignore 1 net.ipv4.conf.all.arp_announce 2 net.ipv4.conf.dummy.arp_ignore 1 net.ipv4.conf.dummy.arp_announce 2 EOF[rootdb1 ~16:16:39]# sysctl -pnet.ipv4.conf.all.arp_ignore1net.ipv4.conf.all.arp_announce2net.ipv4.conf.dummy.arp_ignore1net.ipv4.conf.dummy.arp_announce2配置 keepalived 和 LVS-DS配置 ha1[rootha1 ~14:59:17]# yum install -y keepalived ipvsadm[rootha1 ~16:19:55]# cp /etc/keepalived/keepalived.conf{,.bak}[rootha1 ~16:20:21]# vim /etc/keepalived/keepalived.conf!Configuration Fileforkeepalived global_defs{router_id ha1}vrrp_instance db{state MASTER interface eth33 virtual_router_id51priority110advert_int1authentication{auth_type PASS auth_pass1111}virtual_ipaddress{10.1.8.100/24}}virtual_server10.1.8.1003306{delay_loop6lb_algo rr lb_kind DR persistence_timeout50protocol TCP real_server10.1.8.113306{weight1TCP_CHECK{connect_timeout3retry3delay_before_retry3}}real_server10.1.8.123306{weight1TCP_CHECK{connect_timeout3retry3delay_before_retry3}}}[rootha1 ~16:26:00]# systemctl enable keepalived --now在keepalivedLVS配置中后端 real_server 后端服務(wù)器不支持直接使用域名必須指定 IP 地 址。這是由 LVS 的工作原理和 Keepalived 的配置機(jī)制決定的。原因LVS 工作在四層傳輸層LVS 基于 IP 地址和端口進(jìn)行負(fù)載均衡轉(zhuǎn)發(fā)不涉及 DNS 解析 無法識(shí)別域名。** Keepalived 配置特性**Keepalived 的 real_server 配置項(xiàng)要求明確的 IP 地址 address 參數(shù)其語(yǔ)法解析器不支持域名格式會(huì)直接將域名視為無效配置。啟動(dòng)驗(yàn)證失敗若強(qiáng)行在 real_server 中填寫域名Keepalived 啟動(dòng)時(shí)會(huì)報(bào)錯(cuò)如 invalid IP address導(dǎo)致配置加載失敗。替代方案通過腳本動(dòng)態(tài)更新配置配置 ha2[rootha2 ~14:59:39]# yum install -y keepalived ipvsadm[rootha2 ~16:28:27]# cp /etc/keepalived/keepalived.conf{,.bak}# 將db1修改過的文件復(fù)制過來進(jìn)行修改[rootha2 ~16:31:06]# scp 10.1.8.13:/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf!Configuration Fileforkeepalived global_defs{router_id ha2}vrrp_instance db{state BACKUP interface eth33 virtual_router_id51priority100advert_int1authentication{auth_type PASS auth_pass1111}virtual_ipaddress{10.1.8.100/24}}virtual_server10.1.8.1003306{delay_loop6lb_algo rr lb_kind DR protocol TCP real_server10.1.8.113306{weight1TCP_CHECK{connect_timeout3retry3delay_before_retry3}}real_server10.1.8.123306{weight2TCP_CHECK{connect_timeout3retry3delay_before_retry3}}}[rootha2 ~16:33:54]# systemctl enable keepalived --now測(cè)試# 創(chuàng)建測(cè)試賬戶[rootdb1 ~16:17:11]# mysql -uroot -p123MariaDB[(none)]grant all privileges on *.* toljw%identified by123;Query OK,0rows affected(0.00sec)MariaDB[(none)]flush privileges;Query OK,0rows affected(0.00sec)MariaDB[(none)]quit Bye[rootclient1 ~14:56:07]# yum install -y mysql[rootclient1 ~16:52:13]# mysql -uljw -p123 -h 10.1.8.100# 停止ha1上Keepalived服務(wù)測(cè)試mysql連接[rootha1 ~]# systemctl stop keepalived.service# 啟動(dòng)ha1上Keepalived服務(wù)測(cè)試mysql連接[rootha1 ~]# systemctl start keepalived.service# 停止db1上mariadb服務(wù)測(cè)試mysql連接[rootdb1 ~]# systemctl stop mariadb