一、主從復(fù)制

MySQL主從復(fù)制原理

MySQL的主從復(fù)制和MySQL的讀寫分離兩者有著緊密聯(lián)系，首先要部署主從復(fù)制，只有主從復(fù)制完成了，才能在此基礎(chǔ)上進(jìn)行數(shù)據(jù)的讀寫分離。

(1)MySQL 支持的復(fù)制類型

①基于語句的復(fù)制。在主服務(wù)器上執(zhí)行的 SQL 語句，在從服務(wù)器上執(zhí)行同樣的語句，MySQL 默認(rèn)采用基于語句的復(fù)制，效率比較高。

②基于行的復(fù)制。把改變的內(nèi)容復(fù)制過去,而不是把命令在從服務(wù)器上執(zhí)行一遍

③混合類型的復(fù)制。默認(rèn)采用基于語句的復(fù)制，一旦發(fā)現(xiàn)基于語句無法精確復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制。

（2）復(fù)制工作過程

①在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，Master 將這些改變記錄進(jìn)二進(jìn)制日志。寫入二進(jìn)制日志完成后，Master 通知存儲(chǔ)引擎提交事務(wù)。

②Slave 將 Master 的 Binary log 復(fù)制到其中繼日志(Relay log)。首先，Slave 開始一個(gè)工作線程–I/0 線程,I/0 線程在 Master 上打開一個(gè)普通的連接，然后開始 Binlog dump process。Binlog dump process 從 Master 的二進(jìn)制日志中讀取事件，如果已經(jīng)跟上 Master，它會(huì)睡眠并等待 Master 產(chǎn)生新的事件。I/0 線程將這些事件寫入中繼日志。

③ SQL slave thread(SQL 從線程)處理該過程的最后一步。SQL 線程從中繼日志讀取事件，并重放其中的事件而更新 Slave 數(shù)據(jù)，使其與 Master 中的數(shù)據(jù)保持一致。只要該線程與 I/0線程保持一致，中繼日志通常會(huì)位于OS 的緩存中，所以中繼日志的開銷很小。復(fù)制過程有一個(gè)很重要的限制，即復(fù)制在Slave 上是串行化的，也就是說 Master 上的并行更新操作不能在 Slave 上并行操作。

實(shí)驗(yàn)案例

1.主從復(fù)制實(shí)驗(yàn)本實(shí)驗(yàn)用1臺(tái)主mysql主機(jī)，ip：192.168.10.101

2臺(tái)從mysql主機(jī)，ip：192.168.10.102和192.168.10.103

2.配置master主服務(wù)器

（1）在/etc/my.cnf 中修改或者增加下面內(nèi)容

重啟服務(wù)

登錄MySQL程序給從服務(wù)器授權(quán)

MySQL 8.0 默認(rèn)使用 caching_sha2_password 認(rèn)證插件,將mysql_native_password 替換為舊版認(rèn)證插件，確保從庫能兼容

其中 File 列顯示日志名，Position 列顯示偏移量，這兩個(gè)值在后面配置從服務(wù)器的時(shí)候需要。Slave 應(yīng)從該點(diǎn)上進(jìn)行新的更新。

3.配置從服務(wù)器

在 Slave1、Slave2 服務(wù)器上面分別執(zhí)行下面步驟

(1)在/etc/my.cnf 中修改或者增加下面內(nèi)容，這里要注意 server-id 不能相同。

重啟服務(wù)

登錄 MySQL，配置同步

開啟同步

查看 Slave 狀態(tài)，確保以下兩個(gè)值為 YES

驗(yàn)證主從復(fù)制效果

在主、從服務(wù)器上登錄MySQL

在主上創(chuàng)建庫，表，數(shù)據(jù)

在從服務(wù)器102上查看

二、讀寫分離

簡單來說，讀寫分離就是只在主服務(wù)器上寫，只在從服務(wù)器上讀?；镜脑硎亲屩鲾?shù)據(jù)庫處理事務(wù)性查詢，而從數(shù)據(jù)庫處理 select 查詢。數(shù)據(jù)庫復(fù)制被用來把主數(shù)據(jù)庫上事務(wù)性查詢導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫。

目前較為常見的 MySQL 讀寫分離分為兩種

(1)基于程序代碼內(nèi)部實(shí)現(xiàn)

在代碼中根據(jù) select、insert 進(jìn)行路由分類，這類方法也是目前生產(chǎn)環(huán)境應(yīng)用最廣泛的。優(yōu)點(diǎn)是性能較好，因?yàn)樵诔绦虼a中實(shí)現(xiàn)，不需要增加額外的設(shè)備作為硬件開支;缺點(diǎn)是需要開發(fā)人員來實(shí)現(xiàn)，運(yùn)維人員無從下手。

(2)基于中間代理層實(shí)現(xiàn)

代理一般位于客戶端和服務(wù)器之間，代理服務(wù)器接到客戶端請求后通過判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫，有兩個(gè)代表性程序。

① MySQL-Proxy。MySQL-Proxy 為 MySQL 開源項(xiàng)目，通過其自帶的 lua 腳本進(jìn)行 SQL 判斷，雖然是 MySQL 官方產(chǎn)品，但是 MySQL 官方并不建議將 MySQL-Proxy 用到生產(chǎn)環(huán)境。

②Amoeba。由陳思儒開發(fā)，作者曾就職于阿里巴巴。該程序由 Java 語言進(jìn)行開發(fā)，阿里巴巴將其用于生產(chǎn)環(huán)境。它不支持事務(wù)和存儲(chǔ)過程。經(jīng)過上述簡單的比較，通過程序代碼實(shí)現(xiàn) MySQL 讀寫分離自然是一個(gè)不錯(cuò)的選擇，但是并不是所有的應(yīng)用都適合在程序代碼中實(shí)現(xiàn)讀寫分離，像一些大型復(fù)雜的Java應(yīng)用，如果在程序代碼中實(shí)現(xiàn)讀寫分離對代碼改動(dòng)就較大。所以，像這種應(yīng)用一般會(huì)考慮使用代理層來實(shí)現(xiàn)。本章后續(xù)案例通過 Amoeba 實(shí)現(xiàn)。

③ MyCAT是一款開源的分布式關(guān)系型數(shù)據(jù)庫中間件，主要用于解決大規(guī)模數(shù)據(jù)存儲(chǔ)和高效查詢的需求。它支持分布式 SQL 查詢，兼容 MySQL通信協(xié)議，能夠通過數(shù)據(jù)分片提高數(shù)據(jù)查詢處理能力。MyCAT的前端用戶可以將其視為一個(gè)數(shù)據(jù)庫代理，使用 MySQL 客戶端工具和命令行訪問，而后端則可以通過 MySQL 原生協(xié)議與多個(gè) MySQL 服務(wù)器通信，或者使用JDBC協(xié)議與大多數(shù)主流數(shù)據(jù)庫服務(wù)器通信

• MyCAT是目前最流行的分布式數(shù)據(jù)庫中間插件，是一個(gè)開源的分布式數(shù)據(jù)庫系統(tǒng)，是一個(gè)實(shí)現(xiàn)了 MySQL協(xié)議的服務(wù)器。前端用戶可以把它看作一個(gè)數(shù)據(jù)庫代理，用 MySQL 客戶端工具和命令行訪問，其后端可以用
• MySQL 原生協(xié)議與多個(gè)MySQL服務(wù)器通信，也可以用 JDBC協(xié)議與大多數(shù)主流數(shù)據(jù)庫服務(wù)器通信，其核心功能是分表分庫，即將一個(gè)大表水平分割為N個(gè)小表，存儲(chǔ)在后端 MySQL服務(wù)器里或者其他數(shù)據(jù)庫里。
• MyCAT 發(fā)展到目前，已經(jīng)不是一個(gè)單純的 MySQL 代理了，它的后端可以支持MySQL、SQL Server、0racle、DB2、PostgreSqL 等主流數(shù)據(jù)庫，也支持 MongoDB這種新型 NoSQL方式的存儲(chǔ)。未來，它還會(huì)支持更多類型的存儲(chǔ)。

不過，無論是哪種存儲(chǔ)方式，在最終用戶看，MyCAT里都是一個(gè)傳統(tǒng)的數(shù)據(jù)庫表，支持標(biāo)準(zhǔn)的SQL語句進(jìn)行數(shù)據(jù)的操作，這樣一來，對前端業(yè)務(wù)系統(tǒng)來說，可以大幅降低開發(fā)難度，提升開發(fā)速度。

實(shí)驗(yàn)案例

在基于讀寫分離的基礎(chǔ)上做

在第四臺(tái)客戶機(jī)充當(dāng)中間代理mycat主機(jī)：192.168.10.104 

安裝mycat2

Mycat 需要依賴于JAVA，因此需要在讀寫分離代理所在的系統(tǒng)預(yù)先安裝JAVA環(huán)境

安裝并配置mycat 軟件

unzip 解開后 mycat2 安裝包，將其移動(dòng)到目錄“/usr/local”

把依賴包 mycat2-1.21-release-jar-with-dependencies.jar 和mysql-connector-java-8.0.18.jar,原樣移動(dòng)或者復(fù)制到目錄"/usr/local/mycat/lib’

為 Mycat 命令添加執(zhí)行權(quán)限

到目前為止，安裝的步驟基本上算是完成了，任意命令行下執(zhí)行指令“mycat -h”驗(yàn)證安裝的正確性

關(guān)閉防火墻

設(shè)置mycat讀寫分離

Mycat2讀寫分離配置可分為:創(chuàng)建數(shù)據(jù)庫連接賬號(hào)、啟動(dòng)Mycat2與讀寫分離配置等幾個(gè)步驟

1)創(chuàng)建 Mycat2工作所必須的賬號(hào)啟動(dòng) Mycat2 服務(wù)，需要有真實(shí)的數(shù)據(jù)庫服務(wù)器支撐才能運(yùn)行，因此，需要在 MySQL 服務(wù)器(其它被 Mycat2 支持的數(shù)據(jù)庫也如此)創(chuàng)建賬號(hào)并給賬號(hào)授權(quán)，然后在 Mycat2 所在的宿主系統(tǒng)用 MySQL,客戶端用創(chuàng)建好的賬號(hào)遠(yuǎn)程進(jìn)行連接，驗(yàn)證賬號(hào)的有效性和正確性。

在前邊，我們已經(jīng)做好了 MySQL數(shù)據(jù)庫間的主從同步，因此創(chuàng)建 Mycat2 所需賬號(hào)的操作只需也只能在主數(shù)據(jù)庫上進(jìn)行，具體的指令如下:

2)啟動(dòng) Mycat2

與 Mycat1.x版本相比，Mycat2 的配置基本不需要手動(dòng)去修改配置文件，而是可以在 Mycat2 啟動(dòng)之后，登錄 Mycat 管理后臺(tái)，用 SQL 指令或者客戶端工具進(jìn)行配置。在啟動(dòng) Mycat2 之前，需要對原型庫的數(shù)據(jù)源做相應(yīng)的修改，修改的項(xiàng)主要是主數(shù)據(jù)庫的連接信息，一個(gè)完整的修改過的原型數(shù)據(jù)源文件“/usr/local/mycat/conf/datasources/prototypeDs.datasource.json”的內(nèi)容如下:

因?yàn)橐呀?jīng)對系統(tǒng)變量做了設(shè)置，所以在任意路徑執(zhí)行“mycat start”就可以啟動(dòng) Mycat2。在 Mycat2的安裝目錄“/usr/local/mycat”下，存在目錄“logs”打開此目錄中的日志文件“wrapper.log’，可了解 Mycat2 服務(wù)的運(yùn)行狀

用 MySQL, 客戶端工具連接 Mycat 的服務(wù)端口 TCP 8066、用戶名與密碼在配置文件“/usr/local/mycat/conf/users/ root.user.json’

用命令行連接 Mycat 管理后臺(tái)的指令為“mysql -uroot -p123456 -P8066 -h192.168.10.104 ”，ip指向mycat主機(jī)，進(jìn)入用戶交互界面，表明 Mycat2 運(yùn)行正常，可在此交互界面進(jìn)行讀寫分離配置

3)Mycat2配置讀寫分離

兩種配置 MySQL讀寫分離的方法，一種是直接在 Mycat 的配置目錄“/usr/1ocal/mycat/conf”的子目錄編輯相關(guān)的文本文件(Mycat1.x版本只用這種方法):另一種登錄到 Mycat 交互界面，用特殊語法的 SQL,命令進(jìn)行配置。本教程采用第二種方法，直接在 Mycat 的交互界面輸入命令。

第一步:Mycat 增加數(shù)據(jù)源

需要正確輸入的數(shù)據(jù)主要包括:MySQL 主從數(shù)據(jù)庫的 IP 地址、數(shù)據(jù)庫庫名(schema)、數(shù)據(jù)庫賬號(hào)、數(shù)據(jù)庫密碼(生產(chǎn)數(shù)據(jù)庫請使用復(fù)雜密碼)、實(shí)例類型(READ、WRITE或 READ_WRITE)。下邊是添加一個(gè)主庫源和兩個(gè)從庫源的具體指令：

增加主庫master:

增加從庫slavel和 slave2:

查看數(shù)據(jù)源信息:

如數(shù)據(jù)源配置有誤可使用” /* + mycat:resetConfig{ } */;“進(jìn)行重置

正確執(zhí)行完上面三條 SQL語句以后，在目錄“/usr/local/mycat/conf/datasources”下自動(dòng)生成三個(gè)文本文件，文件名以已經(jīng)執(zhí)行的SQL 語句中“name”的鍵值做前綴

第二步:創(chuàng)建 Mycat 集群

在本案例中，集群成員包括一個(gè)主庫與兩個(gè)從庫。根據(jù)業(yè)務(wù)場景，也可以創(chuàng)建多個(gè)集群，充分、有效的利用系統(tǒng)資源。創(chuàng)建 Mycat 集群的 SQL語句如下:

上述 SQL, 語句執(zhí)行完以后，將在目錄“/usr/local/mycat/conf/clusters自動(dòng)生成 Mycat 集群配置文件“cls01.cluster.json

查看并修改集群配置

修改負(fù)載均衡的默認(rèn)策略為輪詢

修改配置后需重啟 mycat

驗(yàn)證mycat讀寫分離

1)登錄 mycat 集群，創(chuàng)建測試庫和測試表

創(chuàng)建庫和表的操作會(huì)路由到 master 執(zhí)行，并被同步到slave 節(jié)點(diǎn)

2)停止salvel和slave2的主從同步

在102和103mysql上

3)在 master 和 slave1、slave2 創(chuàng)建測試數(shù)據(jù)

在slave1：102上添加數(shù)據(jù)4

在slave2：103上添加數(shù)據(jù)6

3)測試讀操作登錄 mycat 集群，查詢 test.t1 的數(shù)據(jù)

第一次查詢

第二次查詢

可以看到輪詢的查詢模式 

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論