快捷導(dǎo)航

解讀mysql主從配置及其原理分析(Master-Slave)

更新時間：2011年05月25日 17:57:29 作者：

在windows下配置的，后面會在Linux下配置進行測試，需要配置mysql數(shù)據(jù)庫同步的朋友可以參考下。

1.在主數(shù)據(jù)庫服務(wù)器為從服務(wù)器添加一個擁有權(quán)限訪問主庫的用戶:
GRANT REPLICATION SLAVE ON *.* TO ' test'@'%' IDENTIFIED BY 'test';
(%表示允許所有IP，可設(shè)置指定從服務(wù)器IP)
添加用戶后:
可在從服務(wù)器上用mysql -h127.0.0.1 -utest -ptest; 來測試是否有權(quán)限訪問主數(shù)據(jù)庫

2.在主據(jù)庫配置文件加上:
#master config
server-id     = 1
log-bin          = mysql-bin

3.在從服務(wù)器數(shù)據(jù)庫配置文件：
server-id        = 2
master-host      = 10.0.0.199
master-user      = test
master-password = test
replicate-do-db = test
master-port      = 3306
log-bin          = mysql-bin

如果你的一切配置順利
你在從服務(wù)器上輸入命令：show slave status\G
正常情況：Slave_IO_Running:yes
Slave_SQL_Running:yes

在主服務(wù)器上輸入show master status

那么，恭喜，主從數(shù)據(jù)庫配置OK，可以在主數(shù)據(jù)庫插入數(shù)據(jù)進行測試，是否從庫同步木有...
注：請檢查防火墻!!!
其實配置過程是很容易簡單的，不要太過激動，我們再花點時間了解其主從的原理。
下面的內(nèi)容對你理解應(yīng)該有點作用。

一 MySQL 復(fù)制的基本過程如下：（各部分學(xué)習(xí)自Google，謝謝）
1. Slave 上面的IO線程連接上 Master，并請求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容;

2. Master 接收到來自 Slave 的 IO 線程的請求后，通過負責(zé)復(fù)制的 IO線程根據(jù)請求信息讀取指定日志指定位置之后的日志信息，返回給 Slave 端的 IO線程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息在 Master 端的 Binary Log 文件的名稱以及在 BinaryLog 中的位置;
3. Slave 的 IO 線程接收到信息后，將接收到的日志內(nèi)容依次寫入到 Slave 端的RelayLog文件(mysql-relay-lin.xxxxxx)的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到 master-info文件中，以便在下一次讀取的時候能夠清楚的高速Master“我需要從某個bin-log的哪個位置開始往后的日志內(nèi)容，請發(fā)給我”

4. Slave 的 SQL 線程檢測到 Relay Log 中新增加了內(nèi)容后，會馬上解析該 Log 文件中的內(nèi)容成為在 Master
端真實執(zhí)行時候的那些可執(zhí)行的 Query 語句，并在自身執(zhí)行這些 Query。這樣，實際上就是在 Master 端和 Slave
端執(zhí)行了同樣的 Query，所以兩端的數(shù)據(jù)是完全一樣的。

二、設(shè)置mysql主從配置的優(yōu)點：
1、解決web應(yīng)用系統(tǒng)，數(shù)據(jù)庫出現(xiàn)的性能瓶頸，采用數(shù)據(jù)庫集群的方式來實現(xiàn)查詢負載；一個系統(tǒng)中數(shù)據(jù)庫的查詢操作比更新操作要多得多，通過多臺查詢服務(wù)器將數(shù)據(jù)庫的查詢分擔(dān)到不同的查詢服務(wù)器上從而提高查詢效率。
2、Mysql數(shù)據(jù)庫支持數(shù)據(jù)庫的主從復(fù)制功能，使用主數(shù)據(jù)庫進行數(shù)據(jù)的插入、刪除與更新操作，而從數(shù)據(jù)庫則專門用來進行數(shù)據(jù)查詢操作，這樣可以將更新操作和查詢操作分擔(dān)到不同的數(shù)據(jù)庫上，從而提高了查詢效率。

二主從原理如下：
1.
主服務(wù)器將更新寫入二進制日志文件，并維護文件的一個索引以跟蹤日志循環(huán)。這些日志可以記錄發(fā)送到從服務(wù)器的更新。當(dāng)一個從服務(wù)器連接主服務(wù)器時，它通知主服務(wù)器從服務(wù)器在日志中讀取的最后一次成功更新的位置。從服務(wù)器接收從那時起發(fā)生的任何更新，然后封鎖并等待主服務(wù)器通知新的更新。

MySQL復(fù)制基于主服務(wù)器在二進制日志中跟蹤所有對數(shù)據(jù)庫的更改(更新、刪除等等)。因此，要進行復(fù)制，必須在主服務(wù)器上啟用二進制日志。

每個從服務(wù)器從主服務(wù)器接收主服務(wù)器已經(jīng)記錄到其二進制日志的保存的更新，以便從服務(wù)器可以對其數(shù)據(jù)拷貝執(zhí)行相同的更新。

從服務(wù)器設(shè)置為復(fù)制主服務(wù)器的數(shù)據(jù)后，它連接主服務(wù)器并等待更新過程。如果主服務(wù)器失敗，或者從服務(wù)器失去與主服務(wù)器之間的連接，從服務(wù)器保持定期嘗試連接，直到它能夠繼續(xù)幀聽更新。由--master-connect-retry選項控制重試間隔。默認為60秒。

每個從服務(wù)器跟蹤復(fù)制時間。主服務(wù)器不知道有多少個從服務(wù)器或在某一時刻有哪些被更新了。

2.主從同步過程的相關(guān)文件

默認情況，中繼日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是從服務(wù)器主機名，nnnnnn是序列號。用連續(xù)序列號來創(chuàng)建連續(xù)中繼日志文件，從000001開始。從服務(wù)器跟蹤索引文件中目前正使用的中繼日志。默認中繼日志索引文件名為host_name-relay-bin.index。默認情況，在從服務(wù)器的數(shù)據(jù)目錄中創(chuàng)建這些文件?？梢杂?-relay- log和--relay-log-index服務(wù)器選項覆蓋默認文件名

中繼日志與二進制日志的格式相同，并且可以用mysqlbinlog讀取。SQL線程執(zhí)行完中繼日志中的所有事件并且不再需要之后，立即自動刪除它。沒有直接的刪除中繼日志的機制，因為SQL線程可以負責(zé)完成。然而，F(xiàn)LUSH LOGS可以循環(huán)中繼日志，當(dāng)SQL線程刪除日志時會有影響。

從屬復(fù)制服務(wù)器在數(shù)據(jù)目錄中另外創(chuàng)建兩個小文件。這些狀態(tài)文件默認名為主master.info和relay-log.info。它們包含SHOW SLAVE STATUS語句的輸出所顯示的信息(關(guān)于該語句的描述參見13.6.2節(jié)，“用于控制從服務(wù)器的SQL語句”)。狀態(tài)文件保存在硬盤上，從服務(wù)器關(guān)閉時不會丟失。下次從服務(wù)器啟動時，讀取這些文件以確定它已經(jīng)從主服務(wù)器讀取了多少二進制日志，以及處理自己的中繼日志的程度。

由I/O線程更新master.info文件。文件中的行和SHOW SLAVE STATUS顯示的列的對應(yīng)關(guān)系為：

復(fù)制代碼代碼如下:

 
行 描述 
文件中的行號 
Master_Log_File 
Read_Master_Log_Pos 
Master_Host 
Master_User 
密碼(不由SHOW SLAVE STATUS顯示) 
Master_Port 
Connect_Retry 
Master_SSL_Allowed 
Master_SSL_CA_File 
Master_SSL_CA_Path 
Master_SSL_Cert 
Master_SSL_Cipher 
Master_SSL_Key 

由SQL線程更新relay-log.info文件。文件中的行和SHOW SLAVE STATUS顯示的列的對應(yīng)關(guān)系為：

復(fù)制代碼代碼如下:

 
行 描述 
Relay_Log_File 
Relay_Log_Pos 
Relay_Master_Log_File 
Exec_Master_Log_Pos 

四：主從同步過程的相關(guān)文件和MySQL語句的關(guān)系
由I/O線程更新master.info文件。文件中的行和SHOW SLAVE STATUS顯示的列的對應(yīng)關(guān)系為：

復(fù)制代碼代碼如下:

 
行 描述 
文件中的行號 
Master_Log_File 
Read_Master_Log_Pos 
Master_Host 
Master_User 
密碼(不由SHOW SLAVE STATUS顯示) 
Master_Port 
Connect_Retry 
Master_SSL_Allowed 
Master_SSL_CA_File 
Master_SSL_CA_Path 
Master_SSL_Cert 
Master_SSL_Cipher 
Master_SSL_Key 

由SQL線程更新relay-log.info文件。文件中的行和SHOW SLAVE STATUS顯示的列的對應(yīng)關(guān)系為：

復(fù)制代碼代碼如下:

 
行 描述 
Relay_Log_File 
Relay_Log_Pos 
Relay_Master_Log_File 
Exec_Master_Log_Pos 

當(dāng)備份從服務(wù)器的數(shù)據(jù)時，你還應(yīng)備份這兩個小文件以及中繼日志文件。它們用來在恢復(fù)從服務(wù)器的數(shù)據(jù)后繼續(xù)進行復(fù)制。如果丟失了中繼日志但仍然有 relay-log.info文件，你可以通過檢查該文件來確定SQL線程已經(jīng)執(zhí)行的主服務(wù)器中二進制日志的程度。然后可以用 Master_Log_File和Master_LOG_POS選項執(zhí)行CHANGE MASTER TO來告訴從服務(wù)器重新從該點讀取二進制日志。當(dāng)然，要求二進制日志仍然在主服務(wù)器上。
如果從服務(wù)器正復(fù)制LOAD DATA INFILE語句，你應(yīng)也備份該目錄內(nèi)從服務(wù)器用于該目的的任何SQL_LOAD-*文件。從服務(wù)器需要這些文件繼續(xù)復(fù)制任何中斷的LOAD DATA INFILE操作。用--slave-load-tmpdir選項來指定目錄的位置。如果未指定，默認值為tmpdir變量的值。
五：主從同步起點的說明
master.info的內(nèi)容會覆蓋命令行或in my.cnf中指定的部分選項。
如果從服務(wù)器啟動時master.info文件不存在，選項采用選項文件或命令行中指定的值。首次將服務(wù)器作為從服務(wù)器啟動時，或者已經(jīng)運行RESET SLAVE然后已經(jīng)關(guān)閉并重啟從服務(wù)器時會發(fā)生。
如果從服務(wù)器啟動時master.info文件存在，服務(wù)器忽略那些選項。使用master.info文件中發(fā)現(xiàn)的值。
如果你使用與master.info文件中相對應(yīng)的啟動選項的不同的值重啟從服務(wù)器，啟動選項的不同的值不會生效，因為服務(wù)器繼續(xù)使用 master.info文件。要想使用啟動選項的不同的值，必須刪除master.info文件并重啟從服務(wù)器，或(最好是)在從服務(wù)器運行時使用 CHANGE MASTER TO語句重新設(shè)置值。
六：如何確保所有從服務(wù)器已經(jīng)處理了中繼日志中的所有語句
在每個從服務(wù)器上，發(fā)出STOP SLAVE IO_THREAD語句，然后檢查SHOW PROCESSLIST語句的輸出，直到你看到Has read all relay log。當(dāng)所有從服務(wù)器都執(zhí)行完這些，它們可以被重新配置為一個新的設(shè)置。在被提升為主服務(wù)器的從服務(wù)器S1上，發(fā)出STOP SLAVE和RESET MASTER語句。
七：如果你確定可以跳過來自主服務(wù)器的下一個語句，可以執(zhí)行下面的語句

復(fù)制代碼代碼如下:

 
mysql> SET GLOBAL SQL_slave_SKIP_COUNTER = n； 
mysql> START SLAVE； 

如果來自主服務(wù)器的下一個語句不使用AUTO_INCREMENT或LAST_INSERT_ID()，n 值應(yīng)為1。否則，值應(yīng)為2。使用AUTO_INCREMENT或LAST_INSERT_ID()的語句使用值2的原因是它們從主服務(wù)器的二進制日志中取兩個事件。
七：兩個重要的選項：
1）：· --logs-slave-updates
這個是在my.cnf文件配置的
通常情況，從服務(wù)器從主服務(wù)器接收到的更新不記入它的二進制日志。該選項告訴從服務(wù)器將其SQL線程執(zhí)行的更新記入到從服務(wù)器自己的二進制日志。為了使該選項生效，還必須用--logs-bin選項啟動從服務(wù)器以啟用二進制日志。如果想要應(yīng)用鏈式復(fù)制服務(wù)器，應(yīng)使用--logs-slave- updates。例如，可能你想要這樣設(shè)置：
A -> B -> C
也就是說，A為從服務(wù)器B的主服務(wù)器，B為從服務(wù)器C的主服務(wù)器。為了能工作，B必須既為主服務(wù)器又為從服務(wù)器。你必須用--logs-bin啟動A和B以啟用二進制日志，并且用--logs-slave-updates選項啟動B。
2）：· --slave-skip-errors=[err_code1,err_code2,... | all]
這個是在mysql啟動時的選項
通常情況，當(dāng)出現(xiàn)錯誤時復(fù)制停止，這樣給你一個機會手動解決數(shù)據(jù)中的不一致性問題。該選項告訴從服務(wù)器SQL線程當(dāng)語句返回任何選項值中所列的錯誤時繼續(xù)復(fù)制。
如果你不能完全理解為什么發(fā)生錯誤，則不要使用該選項。如果復(fù)制設(shè)置和客戶程序中沒有bug，并且MySQL自身也沒有bug，應(yīng)不會發(fā)生停止復(fù)制的錯誤。濫用該選項會使從服務(wù)器與主服務(wù)器不能保存同步，并且你找不到原因。
對于錯誤代碼，你應(yīng)使用從服務(wù)器錯誤日志中錯誤消息提供的編號和SHOW SLAVE STATUS的輸出。服務(wù)器錯誤代碼列于附錄B：錯誤代碼和消息。
你也可以(但不應(yīng))使用不推薦的all值忽略所有錯誤消息，不考慮所發(fā)生的錯誤。無需而言，如果使用該值，我們不能保證數(shù)據(jù)的完整性。在這種情況下，如果從服務(wù)器的數(shù)據(jù)與主服務(wù)器上的不相近請不要抱怨(或編寫bug報告)。已經(jīng)警告你了。
例如：

復(fù)制代碼代碼如下:

 
--slave-skip-errors=1062,1053 
--slave-skip-errors=all 

八：二個有用的問與答：
1）Q：如果主服務(wù)器正在運行并且不想停止主服務(wù)器，怎樣配置一個從服務(wù)器？
A：有多種方法。如果你在某時間點做過主服務(wù)器備份并且記錄了相應(yīng)快照的二進制日志名和偏移量(通過SHOW MASTER STATUS命令的輸出)，采用下面的步驟：

復(fù)制代碼代碼如下:

 
1. 確保從服務(wù)器分配了一個唯一的服務(wù)器ID號。 
2. 在從服務(wù)器上執(zhí)行下面的語句，為每個選項填入適當(dāng)?shù)闹担?
mysql> CHANGE MASTER TO 
-> MASTER_HOST='master_host_name', 
-> MASTER_USER='master_user_name', 
-> MASTER_PASSWORD='master_pass', 
-> MASTER_LOG_FILE='recorded_log_file_name', 
-> MASTER_LOG_POS=recorded_log_position; 

3. 在從服務(wù)器上執(zhí)行START SLAVE語句。
如果你沒有備份主服務(wù)器，這里是一個創(chuàng)建備份的快速程序。所有步驟都應(yīng)該在主服務(wù)器主機上執(zhí)行。

以下是引用片段：
1. 發(fā)出該語句：
mysql> FLUSH TABLES WITH READ LOCK；
2. 仍然加鎖時，執(zhí)行該命令（或它的變體）：
shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql
3. 發(fā)出該語句并且確保記錄了以后用到的輸出：
mysql>SHOW MASTER STATUS；
4. 釋放鎖：
mysql> UNLOCK TABLES；
一個可選擇的方法是，轉(zhuǎn)儲主服務(wù)器的SQL來代替前面步驟中的二進制復(fù)制。要這樣做，你可以在主服務(wù)器上使用mysqldump --master-data，以后裝載SQL轉(zhuǎn)儲到到你的從服務(wù)器。然而，這比進行二進制復(fù)制速度慢。
不管你使用這兩種方法中的那一個，當(dāng)你有一個快照和記錄了日志名與偏移量時，后來根據(jù)說明操作。你可以使用相同的快照建立多個從服務(wù)器。一旦你擁有主服務(wù) 器的一個快照，可以等待創(chuàng)建一個從服務(wù)器，只要主服務(wù)器的二進制日志完整。兩個能夠等待的時間實際的限制是指在主服務(wù)器上保存二進制日志的可用硬盤空間和從服務(wù)器同步所用的時間。
你也可以使用LOAD DATA FROM MASTER。這是一個方便的語句，它傳輸一個快照到從服務(wù)器并且立即調(diào)整日志名和偏移量。將來，LOAD DATA FROM MASTER將成為創(chuàng)建從服務(wù)器的推薦方法。然而需要注意，它只工作在MyISAM 表上并且可能長時間持有讀鎖定。它并不象我們希望的那樣高效率地執(zhí)行。如果你有大表，執(zhí)行FLUSH TABLES WITH READ LOCK語句后，這時首選方法仍然是在主服務(wù)器上制作二進制快照。
2）Q：從服務(wù)器需要始終連接到主服務(wù)器嗎？
A：不，不需要。從服務(wù)器可以宕機或斷開連接幾個小時甚至幾天，重新連接后獲得更新信息。例如，你可以在通過撥號的鏈接上設(shè)置主服務(wù)器/從服務(wù)器關(guān)系，其中只是偶爾短時間內(nèi)進行連接。這意味著，在任何給定時間，從服務(wù)器不能保證與主服務(wù)器同步除非你執(zhí)行某些特殊的方法。將來，我們將使用選項來阻塞主服務(wù)器直到有一個從服務(wù)器同步。

當(dāng)備份從服務(wù)器的數(shù)據(jù)時，你還應(yīng)備份這兩個小文件以及中繼日志文件。它們用來在恢復(fù)從服務(wù)器的數(shù)據(jù)后繼續(xù)進行復(fù)制。如果丟失了中繼日志但仍然有 relay-log.info文件，你可以通過檢查該文件來確定SQL線程已經(jīng)執(zhí)行的主服務(wù)器中二進制日志的程度。然后可以用 Master_Log_File和Master_LOG_POS選項執(zhí)行CHANGE MASTER TO來告訴從服務(wù)器重新從該點讀取二進制日志。當(dāng)然，要求二進制日志仍然在主服務(wù)器上。
如果從服務(wù)器正復(fù)制LOAD DATA INFILE語句，你應(yīng)也備份該目錄內(nèi)從服務(wù)器用于該目的的任何SQL_LOAD-*文件。從服務(wù)器需要這些文件繼續(xù)復(fù)制任何中斷的LOAD DATA INFILE操作。用--slave-load-tmpdir選項來指定目錄的位置。如果未指定，默認值為tmpdir變量的值

MySQL的 Replication 是一個異步的復(fù)制過程，從一個 Mysql instace(我們稱之為 Master)復(fù)制到另一個Mysql instance(我們稱之 Slave)。在 Master 與 Slave之間的實現(xiàn)整個復(fù)制過程主要由三個線程來完成，其中兩個線程(Sql線程和IO線程)在 Slave 端，另外一個線程(IO線程)在 Master端。

要實現(xiàn) MySQL 的 Replication ，首先必須打開 Master 端的BinaryLog(mysql-bin.xxxxxx)功能，否則無法實現(xiàn)。因為整個復(fù)制過程實際上就是Slave從Master端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。打開 MySQL 的 Binary Log 可以通過在啟動 MySQL Server 的過程中使用“—log-bin” 參數(shù)選項，或者在 my.cnf 配置文件中的 mysqld 參數(shù)組([mysqld]標(biāo)識后的參數(shù)部分)增加“l(fā)og-bin” 參數(shù)項。

您可能感興趣的文章: