MySQL主從原理及配置詳解

2024-07-25 19:08:12

字體：大中小

來(lái)源：轉(zhuǎn)載

供稿：網(wǎng)友

MySQL主從配置及原理，供大家參考，具體內(nèi)容如下

一、環(huán)境選擇：

1.Centos 6.5

2.MySQL 5.7

二、什么是MySQL主從復(fù)制

MySQL主從復(fù)制是其最重要的功能之一。主從復(fù)制是指一臺(tái)服務(wù)器充當(dāng)主數(shù)據(jù)庫(kù)服務(wù)器，另一臺(tái)或多臺(tái)服務(wù)器充當(dāng)從數(shù)據(jù)庫(kù)服務(wù)器，主服務(wù)器中的數(shù)據(jù)自動(dòng)復(fù)制到從服務(wù)器之中。對(duì)于多級(jí)復(fù)制，數(shù)據(jù)庫(kù)服務(wù)器即可充當(dāng)主機(jī)，也可充當(dāng)從機(jī)。MySQL主從復(fù)制的基礎(chǔ)是主服務(wù)器對(duì)數(shù)據(jù)庫(kù)修改記錄二進(jìn)制日志，從服務(wù)器通過(guò)主服務(wù)器的二進(jìn)制日志自動(dòng)執(zhí)行更新。

三、MySQL主從復(fù)制的類型

1.基于語(yǔ)句的復(fù)制

主服務(wù)器上面執(zhí)行的語(yǔ)句在從服務(wù)器上面再執(zhí)行一遍，在MySQL-3.23版本以后支持。

缺點(diǎn)：時(shí)間上可能不完全同步造成偏差，執(zhí)行語(yǔ)句的用戶也可能是不同一個(gè)用戶。

2.基于行的復(fù)制

把主服務(wù)器上面改編后的內(nèi)容直接復(fù)制過(guò)去，而不關(guān)心到底改變?cè)搩?nèi)容是由哪條語(yǔ)句引發(fā)的，在MySQL-5.0版本以后引入。

缺點(diǎn)：比如一個(gè)工資表中有一萬(wàn)個(gè)用戶，我們把每個(gè)用戶的工資+1000，那么基于行的復(fù)制則要復(fù)制一萬(wàn)行的內(nèi)容，由此造成的開(kāi)銷比較大，而基于語(yǔ)句的復(fù)制僅僅一條語(yǔ)句就可以了。

3.混合類型的復(fù)制

MySQL默認(rèn)使用基于語(yǔ)句的復(fù)制，當(dāng)基于語(yǔ)句的復(fù)制會(huì)引發(fā)問(wèn)題的時(shí)候就會(huì)使用基于行的復(fù)制，MySQL會(huì)自動(dòng)進(jìn)行選擇。

在MySQL主從復(fù)制架構(gòu)中，讀操作可以在所有的服務(wù)器上面進(jìn)行，而寫(xiě)操作只能在主服務(wù)器上面進(jìn)行。主從復(fù)制架構(gòu)雖然給讀操作提供了擴(kuò)展，可如果寫(xiě)操作也比較多的話（多臺(tái)從服務(wù)器還要從主服務(wù)器上面同步數(shù)據(jù)），單主模型的復(fù)制中主服務(wù)器勢(shì)必會(huì)成為性能瓶頸。

四、原理

1.主從（master-slave）

主服務(wù)器上面的任何修改都會(huì)保存在二進(jìn)制日志Binary log里面，從服務(wù)器上面啟動(dòng)一個(gè)I/O thread（實(shí)際上就是一個(gè)主服務(wù)器的客戶端進(jìn)程），連接到主服務(wù)器上面請(qǐng)求讀取二進(jìn)制日志，然后把讀取到的二進(jìn)制日志寫(xiě)到本地的一個(gè)Realy log里面。從服務(wù)器上面開(kāi)啟一個(gè)SQL thread定時(shí)檢查Realy log，如果發(fā)現(xiàn)有更改立即把更改的內(nèi)容在本機(jī)上面執(zhí)行一遍。

mysql主從,mysql主從原理,mysql主從配置

2.主從從（master-slave-slave）

一主多從的話，這時(shí)主庫(kù)既要負(fù)責(zé)寫(xiě)又要負(fù)責(zé)為幾個(gè)從庫(kù)提供二進(jìn)制日志。此時(shí)可以稍做調(diào)整，將二進(jìn)制日志只給某一從，這一從再開(kāi)啟二進(jìn)制日志并將自己的二進(jìn)制日志再發(fā)給其它從。或者是干脆這個(gè)從不記錄只負(fù)責(zé)將二進(jìn)制日志轉(zhuǎn)發(fā)給其它從，這樣架構(gòu)起來(lái)性能可能要好得多，而且數(shù)據(jù)之間的延時(shí)應(yīng)該也稍微要好一些。

mysql主從,mysql主從原理,mysql主從配置

【注意】

1.在老版本的MySQL中，主從復(fù)制的slave段并不是由兩個(gè)進(jìn)程完成的，而是由一個(gè)進(jìn)程完成的，之后就出現(xiàn)了很多風(fēng)險(xiǎn)和性能的相關(guān)問(wèn)題。具體有以下問(wèn)題：

　　1.一個(gè)進(jìn)程會(huì)使復(fù)制bin-log日志和解析日志并在自身執(zhí)行的過(guò)程成為一個(gè)串行的過(guò)程，性能受到了一定的限制，異步復(fù)制的延遲也會(huì)比較長(zhǎng)。

2. Slave端從Master端獲取bin-log過(guò)來(lái)之后，需要接著解析日志內(nèi)容，然后在自身執(zhí)行。在這個(gè)過(guò)程中，Master端可能又產(chǎn)生了大量變化并新增了大量的日志。如果在這個(gè)階段Master端的存儲(chǔ)出現(xiàn)了無(wú)法修復(fù)的錯(cuò)誤，那么在這個(gè)階段所產(chǎn)生的所有變更都將永遠(yuǎn)無(wú)法找回。如果在Slave端的壓力比較大的時(shí)候，這個(gè)過(guò)程的時(shí)間可能會(huì)比較長(zhǎng)。

五、MySQL主從復(fù)制的過(guò)程

1.兩種情況：同步復(fù)制和異步復(fù)制，生產(chǎn)環(huán)境中大多數(shù)采用異步復(fù)制。

2. 復(fù)制的基本過(guò)程：

　　1.slave上面的I/O進(jìn)程連接上master，并請(qǐng)求從指定文件的指定位置（或者從最開(kāi)始的日志）之后的日志內(nèi)容。

　　2.Master接收到來(lái)自Slave的IO進(jìn)程的請(qǐng)求后，負(fù)責(zé)復(fù)制的IO進(jìn)程會(huì)根據(jù)請(qǐng)求信息讀取日志指定位置之后的日志信息，返回給Slave的IO進(jìn)程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經(jīng)到Master端的bin-log文件的名稱以及bin-log的位置。

3.Slave的IO進(jìn)程接收到信息后，將接收到的日志內(nèi)容依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時(shí)候能夠清楚的告訴Master“我需要從某個(gè)bin-log的哪個(gè)位置開(kāi)始往后的日志內(nèi)容，請(qǐng)發(fā)給我”。

4.Slave的Sql進(jìn)程檢測(cè)到relay-log中新增加了內(nèi)容后，會(huì)馬上解析relay-log的內(nèi)容成為在Master端真實(shí)執(zhí)行時(shí)候的那些可執(zhí)行的內(nèi)容，并在自身執(zhí)行。

六、配置：

1.首先明確兩種情況：

　　1.兩臺(tái)相互獨(dú)立的服務(wù)器或虛擬機(jī)。

　　2.兩臺(tái)通過(guò)模板創(chuàng)建的虛擬機(jī)。

2.下面對(duì)兩臺(tái)服務(wù)器進(jìn)行命名，分別為：MySQL主服務(wù)器，MySQL從服務(wù)器。

3.主從服務(wù)器分別進(jìn)行如下操作：

　　1.MySQL版本一致

　　2.初始化表

4.修改MySQL主服務(wù)器和MySQL從服務(wù)器：

vim /etc/my.cnf [mysqld] log-bin=取任意名稱 //啟用二進(jìn)制日志server-id=任意取數(shù)字 //服務(wù)器唯一ID，默認(rèn)值是1，一般設(shè)置為IP地址的最后一段數(shù)字

5.若是第一種情況，直接重啟兩臺(tái)服務(wù)器；若是第二種情況，還需將兩臺(tái)服務(wù)器的auto.cnf文件重命名為auto.cnf.bak，然后重啟MySQL服務(wù)。

6.在MySQL主服務(wù)器上建立賬戶并授權(quán)slave

grant replication slave on *.* to '用戶名'@'%' identified by '任意密碼';

7.查詢MySQL主服務(wù)器的狀態(tài)

show master status;

8.配置MySQL從服務(wù)器

change master to master_host='MySQL主服務(wù)器IP地址', master_user='之前在MySQL主服務(wù)器上面創(chuàng)建的用戶名'， master_password='之前創(chuàng)建的密碼', master_log_file='MySQL主服務(wù)器狀態(tài)中的二進(jìn)制文件名', master_log_pos='MySQL主服務(wù)器狀態(tài)中的position值';

9.啟用slave