詳解MySQL主從復(fù)制及讀寫分離
前言
在企業(yè)實際應(yīng)用中,成熟的業(yè)務(wù)通常數(shù)據(jù)量都比較大,而單臺MySQL服務(wù)器在安全性、高可用性和高并發(fā)方面都無法滿足實際的需求,我們可以在多臺MySQL服務(wù)器(Master-Slave)部署 主從復(fù)制來實現(xiàn)同步數(shù)據(jù),再通過 讀寫分離來提升數(shù)據(jù)庫的并發(fā)負(fù)載能力。有點類似于rsync,但是不同的是rsync是對磁盤文件做備份,而mysql主從復(fù)制是對數(shù)據(jù)庫中的數(shù)據(jù)、語句做備份。
一、相關(guān)概述
主從復(fù)制:主數(shù)據(jù)庫(Master)發(fā)送更新事件到從數(shù)據(jù)庫(Slave),從數(shù)據(jù)庫讀取更新記錄,并執(zhí)行更新記錄,使得從數(shù)據(jù)庫的內(nèi)容與主數(shù)據(jù)庫保持一致。
(一)MySQL 支持的復(fù)制類型
- 基于語句的復(fù)制(STATEMENT)。在主庫上執(zhí)行的 SQL 語句,在從庫上執(zhí)行同樣的語句。MySQL 默認(rèn)采用基于語句的復(fù)制,效率比較高。
- 基于行的復(fù)制(ROW)。把改變的內(nèi)容復(fù)制過去,而不是把命令在從庫上執(zhí)行一遍。
- 混合類型的復(fù)制(MIXED)。默認(rèn)采用基于語句的復(fù)制,一旦發(fā)現(xiàn)基于語句無法精確復(fù)制時,就會采用基于行的復(fù)制。
(二)MySQL主從復(fù)制的工作過程
1.Master 服務(wù)器保存記錄到二進制日志
- MySQL主庫上進行的增、刪、改的數(shù)據(jù)更新,都會按順序?qū)懭氲阶约旱?mark>二進制日志(Binary log)當(dāng)中
2.Slave 服務(wù)器復(fù)制Master 服務(wù)器的日志
- 然后MySQL從庫開始一個I/O線程連接主庫,讀取主庫的二進制日志,備份到從服務(wù)器的==中繼日志(Relay log)==當(dāng)中。如果已經(jīng)跟上主庫,它會睡眠并等待Master 產(chǎn)生新的事件,I/O線程將這些事件寫入中繼日志
3.Slave 服務(wù)器重放復(fù)制過來的日志
- 然后從庫打開SQL線程,SQL線程讀取I/O線程寫入的中繼日志,并且根據(jù)中繼日志的內(nèi)容更新從庫的數(shù)據(jù),使其與主庫中的數(shù)據(jù)一致
重點:復(fù)制過程有一個很重要的限制,即復(fù)制在 Slave 上是串行化的,也就是說Master 上的并行更新操作不能在 Slave 上并行操作
二、讀寫分離
(1)讀寫分離的概念
讀寫分離:讀寫分離就是只在主服務(wù)器上寫,只在從服務(wù)器上讀?;镜脑硎亲屩鲾?shù)據(jù)庫處理事務(wù)性增、改、刪操作(INSERT、UPDATE、DELETE),而從數(shù)據(jù)庫處理SELECT查詢操作。數(shù)據(jù)庫復(fù)制被用來把事務(wù)性操作導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫。
(2)讀寫分離的作用
- 因為數(shù)據(jù)庫的"寫"(寫10000條數(shù)據(jù)可能要3分鐘)操作是比較耗時的。但是數(shù)據(jù)庫的"讀"(讀10000條數(shù)據(jù)可能只要5秒鐘)。
- 所以讀寫分離,解決的是,數(shù)據(jù)庫的寫入,影響了查詢的效率。注意:數(shù)據(jù)庫不一定要讀寫分離,如果程序使用數(shù)據(jù)庫較多時,而更新少,查詢多的情況下會考慮使用。利用數(shù)據(jù)庫主從同步,再通過讀寫分離可以分擔(dān)數(shù)據(jù)庫壓力,提高性能
(3)MySQL 讀寫分離原理
- 讀寫分離就是只在主服務(wù)器上寫,只在從服務(wù)器上讀
- 基本的原理是讓主數(shù)據(jù)庫處理事務(wù)性查詢,而從數(shù)據(jù)庫處理 select 查詢
- 數(shù)據(jù)庫復(fù)制被用來把主數(shù)據(jù)庫上事務(wù)性查詢導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫
(4)常見的 MySQL 讀寫分離
1)基于程序代碼內(nèi)部實現(xiàn)
1.在代碼中根據(jù) select、insert 進行路由分類,這類方法也是目前生產(chǎn)環(huán)境應(yīng)用最廣泛的。
2.優(yōu)缺點:
- 優(yōu)點是性能較好,因為在程序代碼中實現(xiàn),不需要增加額外的設(shè)備為硬件開支;
- 缺點是需要開發(fā)人員來實現(xiàn),運維人員無從下手。
3.并不是所有的應(yīng)用都適合在程序代碼中實現(xiàn)讀寫分離,像一些大型復(fù)雜的Java應(yīng)用,如果在程序代碼中實現(xiàn)讀寫分離對代碼改動就較大。
2)基于中間代理層實現(xiàn)
1.代理一般位于客戶端和服務(wù)器之間,代理服務(wù)器接到客戶端請求后通過判斷后轉(zhuǎn)發(fā)到后端數(shù)據(jù)庫,有以下代表性程序:
- MySQL-Proxy:MySQL-Proxy 為 MySQL 開源項目,通過其自帶的 lua 腳本進行SQL 判斷。
- Atlas:是由奇虎360的Web平臺部基礎(chǔ)架構(gòu)團隊開發(fā)維護的一個基于MySQL協(xié)議的數(shù)據(jù)中間層項目。它是在mysql-proxy 0.8.2版本的基礎(chǔ)上,對其進行了優(yōu)化,增加了一些新的功能特性。360內(nèi)部使用Atlas運行的mysql業(yè)務(wù),每天承載的讀寫請求數(shù)達幾十億條。支持事物以及存儲過程。
- Amoeba:由陳思儒開發(fā),作者曾就職于阿里巴巴。該程序由Java語言進行開發(fā),阿里巴巴將其用于生產(chǎn)環(huán)境。但是它不支持事務(wù)和存儲過程。
2.由于使用MySQL Proxy需要寫大量的Lua腳本,這些Lua腳本不是現(xiàn)成的,而需要自己編寫,這對于并不熟悉MySQL Proxy內(nèi)置變量和MySQL Protocol的人來說是非常困難的。
3.Amoeba是一個非常容易使用,可移植性非常強的軟件,因此它在生產(chǎn)環(huán)境中被廣泛用于數(shù)據(jù)庫的代理層。
三、MySQL主從復(fù)制實驗部署
需要的相關(guān)軟降包
amoeba-mysql-binary-2.2.0.tar.gz
jdk-6u14-linux-x64.bin
mysql壓縮包
(1)主從復(fù)制實驗步驟及準(zhǔn)備
實驗步驟
第一步:客戶端client訪問代理服務(wù)器amoeba
第二步:代理服務(wù)器讀寫判斷
寫操作:寫入到主服務(wù)器
第三步:主服務(wù)器將增刪改寫入自己二進制日志
第四步:從服務(wù)器將主服務(wù)器的二進制日志同步至自己中繼日志
第五步:從服務(wù)器重放中繼日志到數(shù)據(jù)庫中
讀操作:直接訪問從服務(wù)器
最終結(jié)果:降低負(fù)載,起到負(fù)載均衡作用
主機 操作系統(tǒng) IP地址 所需工具/軟件/安裝包
Amoeba CentOS7 | 192.168.71.10 | jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz |
Master CentOS7 | 192.168.71.12 | ntp 、 mysql-boost-5.7.20.tar.gz |
Slave1 CentOS7 | 192.168.71.13 | ntp 、ntpdate 、 mysql-boost-5.7.20.tar.gz |
Slave2 CentOS7 | 192.168.71.14 | ntp 、ntpdate 、mysql-boost-5.7.20.tar.gz |
客戶端 CentOS7 | 192.168.71.15 |
1.關(guān)閉防火墻及安全機制
四臺服務(wù)器都要關(guān)閉
systemctl stop firewalld systemctl disable firewalld setenforce 0
2.安裝時間同步服務(wù)ntp
(1)主服務(wù)器
yum -y install ntp vim /etc/ntp.conf ##---------末尾添加--------- server 127.127.71.0 #設(shè)置本地是時鐘源,注意修改網(wǎng)段 fudge 127.127.71.0 stratum 8 #設(shè)置時間層級為8(限制在15內(nèi)) service ntpd start
(2)兩個從服務(wù)器
yum -y install ntp ntpdate service ntpd start /usr/sbin/ntpdate 192.168.71.12 #進行時間同步,指向Master服務(wù)器IP crontab -e #設(shè)置計劃任務(wù)每個半個小時同步一次時間 */30 * * * * /usr/sbin/ntpdate 192.168.71.12
3.主服務(wù)器的mysql配置
vim /etc/my.cnf server-id = 1 log-bin=master-bin #添加,主服務(wù)器開啟二進制日志 log-slave-updates=true #添加,允許從服務(wù)器更新二進制日志 systemctl restart mysqld mysql -u root -p grant replication slave on *.* to 'myslave'@'192.168.71.%' identified by '123'; #對從服務(wù)器提權(quán) #grant 授權(quán) #replication 復(fù)制 #允許網(wǎng)段為192.168.71的使用密碼123對所有表庫都可以復(fù)制 flush privileges; show master status; #File 列顯示日志名,F(xiàn)osition 列顯示偏移量
4.從服務(wù)器的mysql配置
Slave1服務(wù)器:192.168.163.13
Slave2服務(wù)器:192.168.163.14
1)修改配置文件
vim /etc/my.cnf #修改,注意id與Master的不同,兩個Slave的id也要不同 server-id = 2 #添加,開啟中繼日志,從主服務(wù)器上同步日志文件記錄到本地 relay-log=relay-log-bin #添加,定義中繼日志文件的位置和名稱 relay-log-index=slave-relay-bin.index systemctl restart mysqld
slave1
slave2
2)從服務(wù)器 對數(shù)據(jù)庫進行操作
mysql -u root - p123 change master to master_host='192.168.71.12',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=306; #show master status;所輸出的二進制文件和位置點一樣 #配置同步,注意master_log_file 和master_log_pos的值要與Master查詢的一致 start slave; #啟動同步,如有報錯執(zhí)行reset slave; show slave status\G; #查看Slave 狀態(tài) //確保 IO 和 SQL 線程都是Yes,代表同步正常。 Slave_IO_Running:Yes #負(fù)責(zé)與主機的io通信 Slave_SQL_Running:Yes #負(fù)責(zé)自己的slave mysql進程
5.驗證結(jié)果
主服務(wù)器上創(chuàng)建一個庫驗,在從服務(wù)器上查看
四、MySQL讀寫分離實驗
Amoeba服務(wù)器(192.168.153.30)
安裝Java 環(huán)境
因為Amoeba 基于是jdk1.5 開發(fā)的,所以官方推薦使用jdk1.5或1.6版本,高版本不建議使用。
1)創(chuàng)建jdk java環(huán)境
cd /opt/ #將jdk-6u14-linux-x64.bin 和 amoeba-mysql-binary-2.2.0.tar.gz上傳到/opt目錄下 cp jdk-6u14-linux-x64.bin /usr/local/ cd /usr/local/ chmod +x jdk-6u14-linux-x64.bin ./jdk-6u14-linux-x64.bin //按yes,按enter mv jdk1.6.0_14/ /usr/1ocal/jdk1.6 vim /etc/profile export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=SCLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA_HOME/bin source /etc/profile java -version
2)安裝 Amoeba軟件
mkdir /usr/local/amoeba tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ chmod -R 755 /usr/local/amoeba/ /usr/local/amoeba/bin/amoeba //如顯示amoeba start|stop 說明安裝成功
3)在主從服務(wù)器的mysql上授權(quán)
Master服務(wù)器:192.168.71.12
Slave1服務(wù)器:192.168.71.13
Slave2服務(wù)器:192.168.71.14
先在Master、Slave1、Slave2 的mysql上開放權(quán)限給 Amoeba 訪問
grant all on *.* to 'test'@'192.168.163.%' identified by 'abc123';
4)配置amoeba服務(wù)在阿米巴上配置
cd /usr/local/amoeba/conf/ cp amoeba.xml amoeba.xml.bak #對配置文件備份 vim amoeba.xml #修改amoeba配置文件 <property name="user">amoeba</property> #30行修改 <property name="password">abc123</property> #32行修改 客戶端連接amoeba服務(wù)器的密碼 <property.name="defaultPool">master</property> #115行修改 設(shè)置默認(rèn)池 <property name="writePool">master</property> #117行去掉注釋 設(shè)置master為寫池 <property name="readPool">slaves</property> #slaves 為讀池
cp dbServers.xml dbServers.xml.bak vim dbServers.xml #修改數(shù)據(jù)庫配置文件 <!-- <property name="schema"> test</property> --> #23行,注釋掉 作用: 默認(rèn)進入test庫以防m(xù)ysql 中沒有test庫時,會報錯 <property name="user">test</property> #26行,修改主從服務(wù)器上提權(quán)的用戶和密碼 <property.name-"password">123</property> #28-30行,去掉注釋 <dbServer name= "master" parent="abstractServer"> #45行,修改,設(shè)置主服務(wù)器的名Master <property name= "ipAddress">192.168.71.12</property> #48行,修改,設(shè)置主服務(wù)器的地址 <dbServer name="slave1" parent="abstractServer"> #52行,修改,設(shè)置從服務(wù)器的名slave1 <property.name="ipAddress">192.168.71.13</property> #55行,修改,設(shè)置從服務(wù)器1的地址 <dbServer name="slave2 " parent="abstractserver"> #59行,復(fù)制上面6行粘貼,設(shè)置從服務(wù)器2的名slave2和地址 <property, name="ipAddress">192.168.71.14</property> <dbServer name="slaves" virtual="true"> #65行,修改 <property name="poolNames">slave1,slave2</property> #71行,修改 /usr/local/amoeba/bin/amoeba start& #啟動Amoeba軟件,按ctrl+c 返回 netstat -anpt | grep java #查看8066端口是否開啟,默認(rèn)端口為TCP 8066
5)測試讀寫分離
在客戶端服務(wù)器上測試
yum install -y mysql mysql-server #快速安裝mysql,也可以選擇編譯安裝 mysql -u amoeba -pabc123 -h 192.168.71.20 -P8066
主從服務(wù) v器上都可以看到該表
通過amoeba服務(wù)器代理訪問mysql ,在通過客戶端連接mysql后寫入的數(shù)據(jù)只有主服務(wù)會記錄,然后同步給從服務(wù)器
stop slave; #關(guān)閉同步寫入數(shù)據(jù) use school; //在slave1上: insert into test1 values('1','slave1'); //在slave2上: insert into test1 values('2','slave2');
在主服務(wù)器上插入數(shù)據(jù)
insert into test1 values('3','master');
在客戶端中向表插入數(shù)據(jù)
//在客戶端服務(wù)器上: use school; select * from test1; //客戶端會分別向slave1和s1ave2讀取數(shù)據(jù),顯示的只有在兩個從服務(wù)器上添加的數(shù)據(jù),沒有在主服務(wù)器上添加的數(shù)據(jù) insert into test1 values('4','climet'); //只有主服務(wù)器上有此數(shù)據(jù)
在主服務(wù)器上查看數(shù)據(jù)
同時開啟兩個從服務(wù)器,查看表格記錄
到此這篇關(guān)于詳解MySQL主從復(fù)制及讀寫分離的文章就介紹到這了,更多相關(guān)MySQL主從復(fù)制及讀寫分離內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Can''t connect to MySQL server的解決辦法
ERROR 2003 (HY000): Can't connect to MySQL server on '*.*.*.*' (113)的解決辦法2010-06-06Can’t open file:''[Table]mytable.MYI''
也許很多人遇到過類似Can’t open file: ‘[Table]mytable.MYI’ 這樣的錯誤信息,卻不知道怎么解決他,下面我們做個介紹,2011-01-01MySQL Lock wait timeout exceeded錯誤
“Lock wait timeout exceeded” 是一個常見的MySQL錯誤,指示了潛在的性能問題或死鎖,本文就來介紹一下如何解決,感興趣的可以了解一下2024-05-05debian6配置mysql允許遠(yuǎn)程連接的方法(圖)
這篇文章主要介紹了debian6配置mysql允許遠(yuǎn)程連接的方法,大家可以參考,最后可看圖2013-11-11mysql "too many connections" 錯誤 之 mysql解決方法
解決方法是修改/etc/mysql/my.cnf,添加以下一行2009-06-06