MySQL主從復(fù)制與讀寫(xiě)分離的使用示例
概述:
在企業(yè)應(yīng)用中,成熟的業(yè)務(wù)通常數(shù)據(jù)量都比較大。
單臺(tái)mysql在安全性、高可用性和高并發(fā)方面都無(wú)法滿足實(shí)際的需求。
配置多臺(tái)主從數(shù)據(jù)服務(wù)器以實(shí)現(xiàn)讀寫(xiě)分離。
主從復(fù)制:解決高可用問(wèn)題
讀寫(xiě)分離:解決高并發(fā)的問(wèn)題
一、主從復(fù)制原理
1、MySql支持從復(fù)制類(lèi)型
基于語(yǔ)句的復(fù)制(statement)
在主服務(wù)器上執(zhí)行的sql語(yǔ)句,在從服務(wù)器執(zhí)行同樣的語(yǔ)句。mysql默認(rèn)采用的方式。效率高。
基于行的復(fù)制 (ROW)
把改變的內(nèi)容復(fù)制過(guò)去。
基于混合類(lèi)型復(fù)制 (MIXED)
默認(rèn)采用基于語(yǔ)句的復(fù)制,一旦發(fā)現(xiàn)基于語(yǔ)句無(wú)法精確復(fù)制時(shí),就會(huì)采用基于行的復(fù)制。
2、主從復(fù)制的原理
- 首先client端(tomcat)將數(shù)據(jù)寫(xiě)入到master節(jié)點(diǎn)的數(shù)據(jù)庫(kù)中,master節(jié)點(diǎn)會(huì)通知存儲(chǔ)引擎提交事務(wù),同時(shí)會(huì)將數(shù)據(jù)以(基于行、基于SQL、基于混合)的方式保存在二進(jìn)制文件中。
- slave節(jié)點(diǎn)會(huì)開(kāi)啟 I/O 線程,用于監(jiān)聽(tīng)master的二進(jìn)制日志的更新,一旦發(fā)生更新內(nèi)容,則向master的dump線程發(fā)出同步請(qǐng)求。
- master的dump線程在接收到salve的I/O請(qǐng)求后,會(huì)讀取二進(jìn)制日志文件中更新的數(shù)據(jù),并發(fā)送給slave的I/O線程。
- slave的I/O線程接收到數(shù)據(jù)后,會(huì)保存在slave節(jié)點(diǎn)的中繼日志中。
- 同時(shí),slave節(jié)點(diǎn)中的SQL線程,會(huì)讀取中繼日志中的數(shù)據(jù),更新在本地的mysql數(shù)據(jù)庫(kù)中
- 最終,完成slave---> 復(fù)制master數(shù)據(jù),達(dá)到主從同步的效果。
中繼日志通常會(huì)位于OS緩存中,所以中繼日志的開(kāi)銷(xiāo)很小。
復(fù)制過(guò)程有一個(gè)很重要的限制,即復(fù)制在slave上時(shí)串行化的,也就是說(shuō),master上的并行更新操作不能再slave上并行操作。
兩日志
- 二進(jìn)制文件: 記錄數(shù)據(jù)庫(kù)變動(dòng)的信息(語(yǔ)句、變動(dòng)記錄)
- 中繼日志文件: 用于臨時(shí)存放二進(jìn)制文件內(nèi)容。
三線程
- dump線程: ①監(jiān)聽(tīng)I(yíng)/O線程請(qǐng)求。②將二進(jìn)制日志文件更新的數(shù)據(jù)發(fā)送給slave的I/O線程。
- I/O線程: ①監(jiān)聽(tīng)master主機(jī)的二進(jìn)制文件。②向master的dump線程發(fā)出同步請(qǐng)求
- SQL線程: 讀取中繼日志中的文件,更新到本機(jī)的數(shù)據(jù)庫(kù)。
3、mysql主從復(fù)制延遲
- master服務(wù)器高并發(fā),形成大量事務(wù)
- 網(wǎng)絡(luò)延遲主
- 從硬件設(shè)備導(dǎo)致(cpu主頻、內(nèi)存io、硬盤(pán)io)
- mysql默認(rèn)使用異步復(fù)制。 可以?xún)?yōu)化mysql參數(shù)。 比如增大innodb_buffer_pool_size,讓更多操作在mysql內(nèi)存中完成。 從庫(kù)還可以使用高性能主機(jī) ,包括cpu強(qiáng)悍、內(nèi)存加大,避免使用虛擬云主機(jī),使用物理主機(jī),這樣升級(jí)了I/O方面性。 還可以將從庫(kù)使用SSD磁盤(pán)。 網(wǎng)絡(luò)優(yōu)化,。避免跨機(jī)房使用。
異步、同步、半同步復(fù)制:
異步復(fù)制
MySQL復(fù)制默認(rèn)是異步復(fù)制,Master將事件寫(xiě)入binlog,提交事務(wù),自身并不知道slave是否接收是否處理;
缺點(diǎn):不能保證所有事務(wù)都被所有slave接收。
同步復(fù)制
Master提交事務(wù),直到事務(wù)在所有slave都已提交,才會(huì)返回客戶(hù)端事務(wù)執(zhí)行完畢信息;
缺點(diǎn):完成一個(gè)事務(wù)可能造成延遲。
半同步復(fù)制
介于異步復(fù)制和全同步復(fù)制之間,主庫(kù)在執(zhí)行完客戶(hù)端提交的事務(wù)后不是立刻返回給客戶(hù)端,而是等待至少一個(gè)從庫(kù)接收到并寫(xiě)到relay log中才返回給客戶(hù)端。
缺點(diǎn):造成了一定程度的延遲。半同步復(fù)制最好在低延時(shí)的網(wǎng)絡(luò)中使用。
二、搭建主從復(fù)制:
實(shí)驗(yàn)前提:
關(guān)防火墻和安全機(jī)制
1.時(shí)間同步:
1.主節(jié)點(diǎn): yum -y install chrony #時(shí)間同步協(xié)議,下載資源包 vim /etc/chrony.conf server ntp.aliyun.com allow 192.168.88.0/24 30行添加 允許同步的網(wǎng)段 systemctl start chrony #重啟natp服務(wù) 2. 兩臺(tái)從節(jié)點(diǎn): yum -y install chrony systemctl start chrony /usr/sbin/ntpdate 192.168.88.100 #時(shí)間同步指向主master服務(wù)器 crontab -e */30 * * * * /usr/sbin/ntpdate 192.168.88.100 #寫(xiě)入周期計(jì)劃任務(wù),每30分鐘同步一次時(shí)間
2.master主服務(wù)設(shè)置:
vim /etc/my.cnf server-id=1 #server-id不能重復(fù) log-bin=master-bin #添加,主服務(wù)器開(kāi)啟二進(jìn)制文件 binlog_format = MIXED #指定二進(jìn)制日志的格式為MIXED log-slave-updates=true #添加,允許從服務(wù)器更新二進(jìn)制文件 systemctl restart mysqld #重啟mysqld服務(wù) mysql -uroot -p #登錄數(shù)據(jù)庫(kù) grant replication slave on *.* to 'myslave'@'192.168.88.%' identified by '123456'; #允許192.168.88.0網(wǎng)段的所有主機(jī)使用myslave賬號(hào)和123456密碼登錄并使用庫(kù)和表 flush privileges; #將用戶(hù)和權(quán)限配置保存在內(nèi)存中 show master status; #查看master服務(wù)器當(dāng)前正在執(zhí)行的二進(jìn)制日志位置,和列偏移量
3.slave從服務(wù)器的配置:
兩臺(tái)從配置都一樣,注意,server-id #修改,id不能相同
vim /etc/my.cnf server-id=2 #修改,id不能與master相同,兩個(gè)slave也不能相同 relay-log=relay-log-bin #添加,開(kāi)啟中繼日志,從服務(wù)器上同步master服務(wù)器日志文件到本地 relay-log-index=slave-relay-bin.index #添加,定義中繼日志文件的位置和名稱(chēng) relay_log_recovery=1 #選配項(xiàng) #當(dāng) slave 從庫(kù)宕機(jī)后,假如 relay-log 損壞了,導(dǎo)致一部分中繼日志沒(méi)有處理,則自動(dòng)放棄所有未執(zhí)行的 relay-log,并且重新從 master 上獲取日志,這樣就保證了relay-log 的完整性。默認(rèn)情況下該功能是關(guān)閉的,將 relay_log_recovery 的值設(shè)置為 1 時(shí), 可在 slave 從庫(kù)上開(kāi)啟該功能,建議開(kāi)啟 systemctl restart mysqld #啟動(dòng)mysqld服務(wù)器 mysql -uroot -p123456 #登錄數(shù)據(jù)庫(kù) change master to master_host='192.168.88.100',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; #配置同步,注意master_log_file 和master_log_pos的值要與master查詢(xún)的一致。 start slave; #開(kāi)啟同步,如有報(bào)錯(cuò)執(zhí)行 reset slave; show slave status\G #查看slave狀態(tài)(查看I/O和SQL都為YES)
4.驗(yàn)證:
主創(chuàng)建表,并寫(xiě)入數(shù)據(jù):
從進(jìn)行了復(fù)制:
三、讀寫(xiě)分離原理
讀寫(xiě)分離就是只在主服務(wù)器上寫(xiě),只在從服務(wù)器上讀,
基本的原理是讓主數(shù)據(jù)庫(kù)處理事務(wù)操作,而從數(shù)據(jù)庫(kù)處理select查詢(xún),數(shù)據(jù)庫(kù)復(fù)制被用來(lái)把主數(shù)據(jù)庫(kù)上事務(wù)性操作導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫(kù)。
1、讀寫(xiě)分離存在的意義
- 因?yàn)閿?shù)據(jù)庫(kù)的 “寫(xiě)” (寫(xiě)10000條數(shù)據(jù)可能要3分鐘),操作是比較耗時(shí)的。
- 但是數(shù)據(jù)庫(kù)的 “讀” (讀10000條數(shù)據(jù)可能只要5秒中)。
- 所謂讀寫(xiě)分離,解決的是,數(shù)據(jù)庫(kù)的寫(xiě)入,影響了查詢(xún)的效率。
- 還可能因?yàn)殒i表的原因,進(jìn)行讀寫(xiě)分離。
2、什么時(shí)候要讀寫(xiě)分離?
數(shù)據(jù)庫(kù)不一定要讀寫(xiě)分離,如果程序使用數(shù)據(jù)庫(kù)較多時(shí),而更新少,查詢(xún)多的情況下會(huì)考慮使用,利用數(shù)據(jù)庫(kù)主從同步,再通過(guò)讀寫(xiě)分離可以分擔(dān)數(shù)據(jù)庫(kù)的壓力,提高性能。
3、主從復(fù)制與讀寫(xiě)分離
在實(shí)際的生產(chǎn)環(huán)境中,對(duì)數(shù)據(jù)庫(kù)的讀和寫(xiě)都在同一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中,是不能滿足實(shí)際需求的。無(wú)論時(shí)在安全性,高可能性還是高并發(fā)等各個(gè)方面都不是完全不能滿足實(shí)際需求的。因此,通過(guò)主從復(fù)制的方式來(lái)同步數(shù)據(jù),再通過(guò)讀寫(xiě)分離來(lái)提升數(shù)據(jù)庫(kù)的并發(fā)負(fù)載能力。優(yōu)點(diǎn)類(lèi)似于rsync,但是不同的是rsync是對(duì)磁盤(pán)文件做備份,而mysql主從復(fù)制時(shí)對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)、語(yǔ)句做備份。
4、MySql讀寫(xiě)分離類(lèi)型
4.1 基于程序代碼內(nèi)部實(shí)現(xiàn)
在代碼中根據(jù)select、insert進(jìn)行路由分類(lèi), 這類(lèi)方法也是目前生產(chǎn)環(huán)境應(yīng)用最廣泛的。優(yōu)點(diǎn):性能較好,因?yàn)樵诔绦虼a中實(shí)現(xiàn),不需要增加額外的設(shè)備為硬件開(kāi)支。缺點(diǎn):是需要開(kāi)發(fā)人員來(lái)實(shí)現(xiàn),運(yùn)維人員無(wú)從下手。但是并不是所有的應(yīng)用都適合在程序代碼中實(shí)現(xiàn)讀寫(xiě)分離,像一些大型的java應(yīng)用,如果在程序代碼中實(shí)現(xiàn)讀寫(xiě)分離對(duì)代碼改動(dòng)就較大。
4.2 基于中間代理層實(shí)現(xiàn)
代理一般位于客戶(hù)端和服務(wù)器之間,代理服務(wù)器接收到客戶(hù)端請(qǐng)求后通過(guò)判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫(kù),有以下代表程序。
1、MySql-Proxy 、MySql-Proxy為MySQL開(kāi)源項(xiàng)目,通過(guò)其自帶的lua腳本進(jìn)行判斷。
2、Atlas 是由奇虎360的web平臺(tái)部基礎(chǔ)架構(gòu)團(tuán)隊(duì)開(kāi)發(fā)維護(hù)的一個(gè)基于mysql協(xié)議的數(shù)據(jù)中間層項(xiàng)目,它是在mysql-proxy0.8.2版本的基礎(chǔ)上。對(duì)于進(jìn)行了優(yōu)化,增加了一些新的功能特性,360內(nèi)部使用atlas運(yùn)行mysql業(yè)務(wù),每條承受的讀寫(xiě)請(qǐng)求數(shù)達(dá)幾十億條,支持事務(wù)以及存儲(chǔ)過(guò)程。
3、Amoeba。 由陳思儒開(kāi)發(fā),作者曾就職于阿里巴巴,該程序由java語(yǔ)言進(jìn)行開(kāi)發(fā),阿里巴巴將其應(yīng)用于生產(chǎn)環(huán)境,但它不支持事務(wù)和存儲(chǔ)過(guò)程。
Amoeba:是一個(gè)以mysql為底層的數(shù)據(jù)存儲(chǔ),并對(duì)應(yīng)提供mysql協(xié)議接口的proxy(代理),外號(hào)變形蟲(chóng)。
- 讀取請(qǐng)求發(fā)送給從服務(wù)器是采用輪詢(xún)調(diào)度算法。
- amoeba使用的java語(yǔ)言編寫(xiě),配置文件為xml
- amoeba主要負(fù)責(zé)對(duì)外的一個(gè)代理ip
- 訪問(wèn)這個(gè)ip時(shí),發(fā)送的請(qǐng)求為“寫(xiě)”請(qǐng)求,則會(huì)轉(zhuǎn)給主服務(wù)器。
- 當(dāng)發(fā)送的請(qǐng)求為“讀”時(shí),會(huì)通過(guò)調(diào)度轉(zhuǎn)發(fā)給從服務(wù)器,使用輪詢(xún)算法,輪流分配給兩臺(tái)從服務(wù)器。
- amoeba可以視為調(diào)度器,如果主服務(wù)器掛掉(單點(diǎn)故障),則會(huì)有HMA解決這個(gè)問(wèn)題。
四、讀寫(xiě)分離實(shí)驗(yàn)
做讀寫(xiě)分離必須要做好主從復(fù)制的的環(huán)境,因?yàn)樽x寫(xiě)分離是基于主從復(fù)制的基礎(chǔ)上去執(zhí)行的架構(gòu)。
實(shí)驗(yàn)環(huán)境:
主服務(wù)器:192.168.88.100
從服務(wù)器1:192.168.88.101
從服務(wù)器2:192.168.88.103
Amoeba 服務(wù)器:192.168.88.104
因?yàn)榍懊娴闹鞣?wù)器和兩臺(tái)從服務(wù)器在前面主從復(fù)制的時(shí)候,已經(jīng)部署好了,現(xiàn)在進(jìn)行部署Amoeba代理服務(wù)器。
1.JDK環(huán)境安裝:
Amoeba服務(wù)時(shí)基于jdk1.5開(kāi)發(fā)的,所有要使用jdk1.5或jdk1.6的版本,不建議使用高版本。
cp jdk-6u14-linux-x64.bin /usr/local #將jdk安裝包復(fù)制到/usr/local目錄下 cd /usr/local/ #進(jìn)入local目錄 chmod +x jdk-6u14-linux-x64.bin #給安裝包執(zhí)行文件 ./jdk-6u14-linux-64.bin #安裝jdk(按空格到最后一行,輸入yes按回車(chē)) mv jdk1.6.0_14 jdk1.6 #安裝好之后在本地目錄下生成jdk1.6.0_14目錄,將其該名,方便后續(xù)操作 vim /etc/profile #編輯全局配置文件 export JAVA_HOME=/usr/local/jdk1.6 #輸出定義java的工作目錄 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib #輸出指定的java類(lèi)文件 export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin #將java加入工作環(huán)境 export AMOEBA_HOME=/usr/local/amoeba #輸出定義的amoeba工作目錄 export PATH=$PATH:$AMOEBA_HOME/bin #加入路徑環(huán)境變量 source /etc/profile #執(zhí)行群居配置文件 java -version #查看java版本信息
2.安裝 Amoeba軟件:
mkdir /usr/local/amoeba #創(chuàng)建amoeba的安裝目錄 tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba #將amoeba的安裝包解壓到/usr/local/amoeba目錄中 chmod -R 775 /usr/local/amoeba #給amoeba目錄所有的權(quán)限 /usr/local/amoeba/bin/amoeba #開(kāi)啟amoeba(如果顯示amoeba start |stop,則說(shuō)明完成)
3. 三臺(tái)mysql主從服務(wù)器設(shè)置權(quán)限:
先在Master、Slave1、Slave2 的mysql上開(kāi)放權(quán)限給 Amoeba 訪問(wèn)
grant all on *.* to 'amoeba-wzw'@'192.168.88.%' identified by '123123';
4. 配置amoeba服務(wù):
vim /usr/local/amoeba/conf/amoeba.xml #修改amoeba配置文件 --30行-- <property name="user">amoeba</property> --32行-- <property name="password">123456</property> --115行-- <property name="defaultPool">master</property> --117-去掉注釋- <property name="writePool">master</property> <property name="readPool">slaves</property> vim /usr/local/amoeba/conf/dbServers.xml #修改數(shù)據(jù)庫(kù)配置文件 --23行--注釋掉 作用:默認(rèn)進(jìn)入test庫(kù) 以防m(xù)ysql中沒(méi)有test庫(kù)時(shí),會(huì)報(bào)錯(cuò) <!-- <property name="schema">test</property> --> --26--修改 <property name="user">amoeba-wzw</property> --28-30--去掉注釋 <property name="password">123123</property> --45--修改,設(shè)置主服務(wù)器的名Master <dbServer name="master" parent="abstractServer"> --48--修改,設(shè)置主服務(wù)器的地址 <property name="ipAddress">192.168.88.101</property> --52--修改,設(shè)置從服務(wù)器的名slave1 <dbServer name="slave1" parent="abstractServer"> --55--修改,設(shè)置從服務(wù)器1的地址 <property name="ipAddress">192.168.88.103</property> --58--復(fù)制上面6行粘貼,設(shè)置從服務(wù)器2的名slave2和地址 <dbServer name="slave2" parent="abstractServer"> <property name="ipAddress">192.168.80.12</property> --65行--修改 <dbServer name="slaves" virtual="true"> --71行--修改 <property name="poolNames">slave1,slave2</property> /usr/local/amoeba/bin/amoeba start& #啟動(dòng)Amoeba軟件,按ctrl+c 返回 netstat -anpt | grep java #查看8066端口是否開(kāi)啟,默認(rèn)端口為T(mén)CP 8066
5.測(cè)試:
讀節(jié)點(diǎn)關(guān)閉slave
在主服務(wù)器上寫(xiě)入一條111數(shù)據(jù):
到此這篇關(guān)于MySQL主從復(fù)制與讀寫(xiě)分離的使用示例的文章就介紹到這了,更多相關(guān)MySQL主從復(fù)制與讀寫(xiě)分離內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL中的GROUP_CONCAT()函數(shù)詳解與實(shí)戰(zhàn)應(yīng)用小結(jié)(示例詳解)
本文介紹了MySQL中的GROUP_CONCAT()函數(shù),詳細(xì)解釋了其基本語(yǔ)法、應(yīng)用示例以及ORDERBY和SEPARATOR參數(shù)的使用方法,此外,還提到了該函數(shù)的性能限制和注意事項(xiàng),感興趣的朋友一起看看吧2025-02-02Mysql 本地計(jì)算機(jī)無(wú)法啟動(dòng) mysql 服務(wù) 錯(cuò)誤 1067:進(jìn)程意外終止。
初學(xué)php接觸mysql,遇到一些問(wèn)題,卸載重裝后,無(wú)法啟動(dòng)mysql服務(wù),網(wǎng)絡(luò)上有很多種說(shuō)法,我這里將我解決這個(gè)問(wèn)題的辦法提出2009-12-12mysql函數(shù)之截取字符串的實(shí)現(xiàn)
本文主要介紹了mysql函數(shù)之截取字符串的實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-08-08mysql 主從數(shù)據(jù)不一致,提示: Slave_SQL_Running: No 的解決方法
這篇文章主要介紹了mysql 主從數(shù)據(jù)不一致,提示: Slave_SQL_Running: No 的解決方法,總結(jié)分析了MySQL主從數(shù)據(jù)不一致的原因與常見(jiàn)處理技巧,需要的朋友可以參考下2020-02-02Navicat for MySQL 15注冊(cè)激活詳細(xì)教程
這篇文章主要介紹了Navicat for MySQL 15注冊(cè)激活詳細(xì)教程,本文通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-12-12阿里云ECS centos6.8下安裝配置MySql5.7的教程
阿里云默認(rèn)yum命令下的MySQL是5.17****,安裝mysql5.7之前先卸載以前的版本。下面通過(guò)本文給大家介紹阿里云ECS centos6.8下安裝配置MySql5.7的教程,需要的的朋友參考下吧2017-07-07將MySQL查詢(xún)結(jié)果按值排序的簡(jiǎn)要教程
這篇文章主要介紹了將MySQL查詢(xún)結(jié)果按值排序的簡(jiǎn)要教程,不過(guò)同樣需要對(duì)結(jié)果進(jìn)行檢查而決定是否使用order by等其他語(yǔ)句,需要的朋友可以參考下2015-12-12快速解決MySQL服務(wù)無(wú)法啟動(dòng)顯示:系統(tǒng)出錯(cuò),發(fā)生系統(tǒng)錯(cuò)誤1067, 進(jìn)程意外終止的兩種方法
本人因?yàn)閜hpstudy的MySQL數(shù)據(jù)庫(kù)與我的電腦上的MySQL數(shù)據(jù)庫(kù)發(fā)生沖突,當(dāng)我將MySQL服務(wù)器的服務(wù)名從MySQL改為MySQL5后,啟動(dòng)MySQL5服務(wù)后就報(bào)錯(cuò):系統(tǒng)出錯(cuò), 發(fā)生系統(tǒng)錯(cuò)誤 1067, 進(jìn)程意外終止,現(xiàn)在將這個(gè)解決方法分享給大家,需要的朋友可以參考下2024-06-06