MySQL數(shù)據(jù)庫(kù)的主從復(fù)制方案，和使用scp/rsync等命令進(jìn)行的文件級(jí)別復(fù)制類似，都是數(shù)據(jù)的遠(yuǎn)程傳輸，只不過(guò)MySQL的主從復(fù)制是其自帶的功能，無(wú)需借助第三方工具，而且，MySQL的主從復(fù)制并不是數(shù)據(jù)庫(kù)磁盤(pán)上的文件直接拷貝，而是通過(guò)邏輯的binlog日志復(fù)制到要同步的服務(wù)器本地，然后由本地的線程讀取日志里面的SQL語(yǔ)句重新應(yīng)用到MySQL數(shù)據(jù)庫(kù)中。

1.1.1 MySQL主從復(fù)制介紹

MySQL數(shù)據(jù)庫(kù)支持單向、雙向、鏈?zhǔn)郊?jí)聯(lián)、環(huán)狀等不同業(yè)務(wù)場(chǎng)景的復(fù)制。在復(fù)制過(guò)程中，一臺(tái)服務(wù)器充當(dāng)主服務(wù)器（Master），接收來(lái)自用戶的內(nèi)容更新，而一個(gè)或多個(gè)其他的服務(wù)器充當(dāng)從服務(wù)器（Slave），接收來(lái)自主服務(wù)器binlog文件的日志內(nèi)容，解析出SQL重新更新到從服務(wù)器，使得主從服務(wù)器數(shù)據(jù)達(dá)到一致。

如果設(shè)置了鏈?zhǔn)郊?jí)聯(lián)復(fù)制，那么，從（slave）服務(wù)器本身除了充當(dāng)從服務(wù)器外，也會(huì)同時(shí)充當(dāng)其下面從服務(wù)器的主服務(wù)器。鏈?zhǔn)郊?jí)復(fù)制類似A→B→C的復(fù)制形式。

1.1.2 MySQL主從復(fù)制的企業(yè)應(yīng)用場(chǎng)景

MySQL主從復(fù)制集群功能使得MySQL數(shù)據(jù)庫(kù)支持大規(guī)模高并發(fā)讀寫(xiě)稱為可能，同時(shí)有效地保護(hù)了物理服務(wù)器宕機(jī)場(chǎng)景的數(shù)據(jù)備份。

應(yīng)用場(chǎng)景1：從服務(wù)器作為主服務(wù)器的實(shí)時(shí)數(shù)據(jù)備份

主從服務(wù)器架構(gòu)的設(shè)置，可以大大加強(qiáng)MySQL數(shù)據(jù)庫(kù)架構(gòu)的健壯性。例如：當(dāng)主服務(wù)器出現(xiàn)問(wèn)題時(shí)，我們可以人工或設(shè)置自動(dòng)切換到從服務(wù)器繼續(xù)提供服務(wù)，此時(shí)從服務(wù)器的數(shù)據(jù)和宕機(jī)時(shí)的主數(shù)據(jù)庫(kù)幾乎是一致的。

這類似NFS存儲(chǔ)數(shù)據(jù)通過(guò)inotify+rsync同步到備份的NFS服務(wù)器，只不過(guò)MySQL的復(fù)制方案是其自帶的工具。

利用MySQL的復(fù)制功能做備份時(shí)，在硬件故障、軟件故障的場(chǎng)景下，該數(shù)據(jù)備份是有效的，但對(duì)于人為地執(zhí)行drop、delete等語(yǔ)句刪除數(shù)據(jù)的情況，從庫(kù)的備份功能就沒(méi)有用了，因?yàn)閺姆?wù)器也會(huì)執(zhí)行刪除的語(yǔ)句。

應(yīng)用場(chǎng)景2：主從服務(wù)器實(shí)時(shí)讀寫(xiě)分離，從服務(wù)器實(shí)現(xiàn)負(fù)載均衡

主從服務(wù)器架構(gòu)可通過(guò)程序（PHP、Java等）或代理軟件（mysql-proxy、Amoeba）實(shí)現(xiàn)對(duì)用戶（客戶端）的請(qǐng)求讀寫(xiě)分離，即讓從服務(wù)器僅僅處理用戶的select查詢請(qǐng)求，降低用戶查詢響應(yīng)時(shí)間及讀寫(xiě)同時(shí)在主服務(wù)器上帶來(lái)的訪問(wèn)壓力。對(duì)于更新的數(shù)據(jù)（例如update、insert、delete語(yǔ)句）仍然交給主服務(wù)器處理，確保主服務(wù)器和從服務(wù)器保持實(shí)時(shí)同步。

應(yīng)用場(chǎng)景3：把多個(gè)從服務(wù)器根據(jù)業(yè)務(wù)重要性進(jìn)行拆分訪問(wèn)

可以把幾個(gè)不同的從服務(wù)器，根據(jù)公司的業(yè)務(wù)進(jìn)行拆分。例如：有為外部用戶提供查詢服務(wù)的從服務(wù)器，有內(nèi)部DBA用來(lái)數(shù)據(jù)備份的從服務(wù)器，還有為公司內(nèi)部人員提供訪問(wèn)的后臺(tái)、腳本、日志分析及供開(kāi)發(fā)人員查詢使用的從服務(wù)器。這樣的拆分除了減輕主服務(wù)器的壓力外，還可以使數(shù)據(jù)庫(kù)對(duì)外部用戶瀏覽、內(nèi)部用戶業(yè)務(wù)處理及DBA人員的備份等互不影響。具體可以用下圖說(shuō)明：

2.1 實(shí)現(xiàn)MySQL主從讀寫(xiě)分離的方案

（1）通過(guò)程序?qū)崿F(xiàn)讀寫(xiě)分離（性能和效率***，推薦）

PHP和Java程序都可以通過(guò)設(shè)置多個(gè)連接文件輕松地實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的讀寫(xiě)分離，即當(dāng)語(yǔ)句關(guān)鍵字為select時(shí)，就去連接讀庫(kù)的連接文件，若為update、insert、delete時(shí)，則連接寫(xiě)庫(kù)的連接文件。

通過(guò)程序?qū)崿F(xiàn)讀寫(xiě)分離的缺點(diǎn)就是需要開(kāi)發(fā)人員對(duì)程序進(jìn)行改造，使其對(duì)下層透明，但這種方式更容易開(kāi)發(fā)和實(shí)現(xiàn)，適合互聯(lián)網(wǎng)業(yè)務(wù)場(chǎng)景。

（2）通過(guò)開(kāi)源軟件實(shí)現(xiàn)讀寫(xiě)分離

MySQL-proxy、Amoda、Mycat、Altas等代理軟件也可以實(shí)現(xiàn)讀寫(xiě)分離功能，這些軟件的穩(wěn)定性和功能一般，不建議生產(chǎn)使用。絕大多數(shù)公司常用的還是應(yīng)用端開(kāi)發(fā)程序?qū)崿F(xiàn)讀寫(xiě)分離。

（3）大型門(mén)戶獨(dú)立開(kāi)發(fā)DAL層綜合軟件

百度、阿里等大型門(mén)戶都有開(kāi)發(fā)牛人，會(huì)花大力氣開(kāi)發(fā)適合自己業(yè)務(wù)的讀寫(xiě)分離、負(fù)載均衡、監(jiān)控報(bào)警、自動(dòng)擴(kuò)容，自動(dòng)收縮等一系列功能的DAL層軟件。

3.1 MySQL主從復(fù)制原理介紹

MySQL的主從復(fù)制是一個(gè)異步的復(fù)制過(guò)程（雖然一般情況下感覺(jué)是實(shí)時(shí)的），數(shù)據(jù)將將從一個(gè)MySQL數(shù)據(jù)庫(kù)（我們稱之為Master）復(fù)制到另一個(gè)MySQL數(shù)據(jù)庫(kù)（我們稱之為Slave），在Master于Slave之間實(shí)現(xiàn)整個(gè)主從復(fù)制的過(guò)程是由三個(gè)線程參與完成的。其中有兩個(gè)線程（SQL和IO線程）在Slave端，另外一個(gè)線程（I/O線程）在Master端。

要實(shí)現(xiàn)MySQL的主從復(fù)制，首先必須打開(kāi)Master端的Binlog記錄功能，否則就無(wú)法實(shí)現(xiàn)。因?yàn)檎麄€(gè)復(fù)制過(guò)程實(shí)際上就是Slave從Master端獲取BInlog日志，然后再在Slave上以相同順序執(zhí)行獲取的binlog日志中記錄的各種SQL操作。

要打開(kāi)MySQL的BInlog記錄功能，可通過(guò)在MySQL的配置文件my.cnf中的mysqld模塊增加“log-bin”參數(shù)選項(xiàng)來(lái)實(shí)現(xiàn)，具體信息如下：

 

[mysqld]  
log-bin 

 

3.2 MySQL主從復(fù)制原理過(guò)程詳細(xì)描述

下面簡(jiǎn)單描述下MySQL Replication的復(fù)制原理過(guò)程。

1）在Slave服務(wù)器上執(zhí)行start slave命令開(kāi)啟主從復(fù)制開(kāi)關(guān)，主從復(fù)制開(kāi)始進(jìn)行。

2）此時(shí)，Slave服務(wù)器的I/O線程會(huì)通過(guò)在Master上己經(jīng)授權(quán)的復(fù)制用戶權(quán)限請(qǐng)求連接Master服務(wù)器，并請(qǐng)求從指定Binlog日志文件的指定位罝（日志文件名和位置就是在配罝主從復(fù)制服務(wù)時(shí)執(zhí)行change master命令指定的）之后開(kāi)始發(fā)送Binlog日志內(nèi)容。

3）Master服務(wù)器接收到來(lái)自Slave服務(wù)器的I/O線程的請(qǐng)求后，其上負(fù)責(zé)復(fù)制的I/O線程會(huì)根據(jù)Slave服務(wù)器的I/O線程請(qǐng)求的信息分批讀取指定Binlog日志文件指定位置之后的Binlog日志信息，然后返回給Slave端的I/O線程。返回的信息中除了Binlog日志內(nèi)容外，還有在Master服務(wù)器端記錄的新的Binlog文件名稱以及在新的Binlog中的下一個(gè) 指定更新位置。

4）當(dāng)Slave服務(wù)器的I/O線程獲取到Master服務(wù)器上I/O線程發(fā)送的日志內(nèi)容及日志文件及位置點(diǎn)后，會(huì)將Binlog日志內(nèi)容依次寫(xiě)入到Slave端自身的Relay Log（即中繼日志） 文件(MySQL-relay-bin.xxxxxx)的最末端，并將新的Binlog文件名和位置記錄到master-info文件中，以便下一次讀取Master端新Binlog日志時(shí)能夠告訴Master服務(wù)器需要從新Binlog 日志的指定文件及位置開(kāi)始請(qǐng)求新的Binlog日志內(nèi)容。

5）Slave服務(wù)器端的SQL線程會(huì)實(shí)時(shí)地檢測(cè)本地Relay Log中I/O線程新增加的日志內(nèi)容，然后及時(shí)地把Relay Log文件中的內(nèi)容解析成SQL語(yǔ)句，并在自身Slave服務(wù)器上按解析SQL語(yǔ)句的位置順序執(zhí)行應(yīng)用這些SQL語(yǔ)句，并記錄當(dāng)前應(yīng)用中繼日志的文件名及位置點(diǎn)在relay-log.info中。

經(jīng)過(guò)了上面的過(guò)程，就可以確保在Master端和Slave端執(zhí)行了同樣的SQL語(yǔ)句。當(dāng)復(fù)制狀態(tài)正常的情況下，Master端和Slave端的數(shù)據(jù)是完全一樣的。當(dāng)然，MySQL的復(fù)制機(jī)制也有一些特殊情況，具體請(qǐng)參考官方的說(shuō)明，大多數(shù)情況下，大家不用擔(dān)心。

下面針對(duì)MySQL主從復(fù)制原理的重點(diǎn)小結(jié)

• 主從復(fù)制是異步的邏輯的SQL語(yǔ)句級(jí)的復(fù)制

• 復(fù)制時(shí)，主庫(kù)有一個(gè)I/O線程，從庫(kù)有兩個(gè)線程，I/O和SQL線程。

• 作為復(fù)制的所有MySQL節(jié)點(diǎn)的server-id都不能相同。

• binlog文件只記錄對(duì)數(shù)據(jù)庫(kù)有更改的SQL語(yǔ)句（來(lái)自數(shù)據(jù)庫(kù)內(nèi)容的變更），不記錄任何查詢（select，slow）語(yǔ)句。

5.6 SQL多線程寫(xiě)入

GTID（不用找位置點(diǎn)了）

3.3主從復(fù)制實(shí)踐

1、準(zhǔn)備

主庫(kù)log_bin

主從server-id不同

2、主庫(kù)準(zhǔn)備

mysql -S /data/3306/mysql.sock  
grant replication slave on *.* to 'rep'@'172.16.1.%' identified by 'oldboy123';  
flush privileges; 

3、把數(shù)據(jù)從主庫(kù)搞到從庫(kù)。

復(fù)制前 讓從和主一致。

mysql -S /data/3306/mysql.sock  
mysql> flush table with read lock; 不能關(guān)窗口  
mysql> show master status;  
+-------------------+----------+  
| File | Position |  
+-------------------+----------+  
| oldboy-bin.000002 | 405 | 

單開(kāi)窗口

mysqldump -A -B --master-data=2 -S /data/3306/mysql.sock|gzip >/opt/3306.sql.gz 

原窗口

mysql> unlock table; 

從庫(kù)恢復(fù)：

gzip -d /opt/3306.sql.gz  
mysql -uroot -poldboy123 -S /data/3307/mysql.sock </opt/3306.sql 

5、指定主機(jī)、用戶、位置點(diǎn)同步

mysql> CHANGE MASTER TO  
MASTER_HOST='172.16.1.51',  
MASTER_PORT=3306,  
MASTER_USER='rep',  
MASTER_PASSWORD='oldboy123',  
MASTER_LOG_FILE='mysql-bin.000001',  
MASTER_LOG_POS=405;  
#查看MASTER_LOG_FILE 使用sed -n 22p /opt/3306.sql#查看主庫(kù)  
#從庫(kù) 

6、開(kāi)啟同步開(kāi)關(guān)，檢查是否同步。

mysql> start slave;  
mysql> show slave statusG  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  
Seconds_Behind_Master: 0 

從庫(kù)配置信息說(shuō)明

mysql> CHANGE MASTER TO  
MASTER_HOST='172.16.1.51', #這里是主庫(kù)的ip  
MASTER_PORT=3306, #這里是主庫(kù)的端口，從庫(kù)的端口可以和主庫(kù)不同  
MASTER_USER='rep', #這里是主庫(kù)上建立的用于復(fù)制的用戶rpo  
MASTER_PASSWORD='oldboy123', ##這里是rep用戶的密碼  
MASTER_LOG_FILE='mysql-bin.000001', ###這里是show master status時(shí)查看到的二進(jìn)制日志文件名稱，注意不能多空格  
MASTER_LOG_POS=405; ##這里是show master status時(shí)查看到的二進(jìn)制日志文件偏移量，注意不能多空格