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

1.在主要資料庫伺服器為從伺服器添加一個擁有許可權訪問主庫的使用者:
GRANT REPLICATION SLAVE ON *.* TO ' test'@'%' IDENTIFIED BY 'test';
(%表示允許所有IP，可設定指定從伺服器IP)
添加使用者後:
可在從伺服器上用mysql -h127.0.0.1 -utest -ptest; 來測試是否有許可權訪問主要資料庫

2.在主據庫設定檔加上:
#master config
server-id = 1
log-bin = mysql-bin

3.在從伺服器資料庫設定檔：
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

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

在主伺服器上輸入show master status

那麼，恭喜，主從資料庫配置OK，可以在主要資料庫插入資料進行測試，是否從庫同步木有...
註：請檢查防火牆!!!
其實配置過程是很容易簡單的，不要太過激動，我們再花點時間瞭解其主從的原理。
下面的內容對你理解應該有點作用。

一 MySQL 複製的基本過程如下：（各部分學習自Google，謝謝）
1. Slave 上面的IO線程串連上 Master，並請求從指定記錄檔的指定位置(或者從最開始的日誌)之後的日誌內容;

2. Master 接收到來自 Slave 的 IO 線程的請求後，通過負責複製的 IO線程根據請求資訊讀取指定日誌指定位置之後的日誌資訊，返回給 Slave 端的 IO線程。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊在 Master 端的 Binary Log 檔案的名稱以及在 BinaryLog 中的位置;
3. Slave 的 IO 線程接收到資訊後，將接收到的日誌內容依次寫入到 Slave 端的RelayLog檔案(mysql-relay-lin.xxxxxx)的最末端，並將讀取到的Master端的bin-log的檔案名稱和位置記錄到 master-info檔案中，以便在下一次讀取的時候能夠清楚的高速Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給 我”

4. Slave 的 SQL 線程檢測到 Relay Log 中新增加了內容後，會馬上解析該 Log 檔案中的內容成為在 Master
端真實執行時候的那些可執行檔 Query 語句，並在自身執行這些 Query。這樣，實際上就是在 Master 端和 Slave
端執行了同樣的 Query，所以兩端的資料是完全一樣的。

二、設定mysql主從配置的優點：
1、解決web應用系統，資料庫出現的效能瓶頸，採用資料庫叢集的方式來實現查詢負載；一個系統中資料庫的查詢操作比更新操作要多得多，通過多台查詢服務器將 資料庫的查詢分擔到不同的查詢服務器上從而提高查詢效率。
2、Mysql資料庫支援資料庫的主從複製功能，使用主要資料庫進行資料的插入、刪除與更新操作，而從資料庫則專門用來進行資料查詢操作，這樣可以將更新操作和 查詢操作分擔到不同的資料庫上，從而提高了查詢效率。

二 主從原理如下：
1.
主伺服器將更新寫入二進位記錄檔，並維護檔案的一個索引以追蹤記錄檔迴圈。這些日誌可以記錄發送到從伺服器的更新。當一個從伺服器串連主伺服器時，它通知 主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。

MySQL複製基於主伺服器在二進位日誌中跟蹤所有對資料庫的更改(更新、刪除等等)。因此，要進行複製，必須在主伺服器上啟用二進位日誌。

每個從伺服器從主伺服器接收主伺服器已經記錄到其二進位日誌的儲存的更新，以便從伺服器可以對其資料拷貝執行相同的更新。

從伺服器設定為複製主伺服器的資料後，它串連主伺服器並等待更新過程。如果主伺服器失敗，或者從伺服器失去與主伺服器之間的串連，從伺服器保持定期嘗試連 接，直到它能夠繼續幀聽更新。由--master-connect-retry選項控制稍候再試。 預設為60秒。

每個從伺服器跟蹤複製時間。主伺服器不知道有多少個從伺服器或在某一時刻有哪些被更新了。

2.主從同步過程的相關檔案

預設情況，中繼日誌使用host_name-relay-bin.nnnnnn形式的檔案名稱，其中host_name是從伺服器主機名稱，nnnnnn是序 列號。用連續序號來建立連續中繼記錄檔，從000001開始。從伺服器跟蹤索引檔案中目前正使用的中繼日誌。 預設中繼日誌索引檔案名稱為host_name-relay-bin.index。預設情況，在從伺服器的資料目錄中建立這些檔案。可以用--relay- log和--relay-log-index伺服器選項覆蓋 預設檔案名稱

中繼日誌與二進位日誌的格式相同，並且可以用mysqlbinlog讀取。SQL線程執行完中繼日誌中的所有事件並且不再需要之後，立即自動刪除它。沒有 直接的刪除中繼日誌的機制，因為SQL線程可以負責完成。然而，FLUSH LOGS可以迴圈中繼日誌，當SQL線程刪除日誌時會有影響。

從屬複製伺服器在資料目錄中另外建立兩個小檔案。這些狀態檔案預設名為主master.info和relay-log.info。它們包含SHOW SLAVE STATUS語句的輸出所顯示的資訊(關於該語句的描述參見13.6.2節，“用於控制從伺服器的SQL語句”)。狀態檔案儲存在硬碟上，從伺服器關閉時 不會丟失。下次從伺服器啟動時，讀取這些檔案以確定它已經從主伺服器讀取了多少二進位日誌，以及處理自己的中繼日誌的程度。

由I/O線程更新master.info檔案。檔案中的行和SHOW SLAVE STATUS顯示的列的對應關係為：

複製代碼 代碼如下:行 描述
1 檔案中的行號
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6 密碼(不由SHOW SLAVE STATUS顯示)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key

由SQL線程更新relay-log.info檔案。檔案中的行和SHOW SLAVE STATUS顯示的列的對應關係為： 複製代碼 代碼如下:行 描述
1 Relay_Log_File
2 Relay_Log_Pos
3 Relay_Master_Log_File
4 Exec_Master_Log_Pos

四：主從同步過程的相關檔案和MySQL語句的關係
由I/O線程更新master.info檔案。檔案中的行和SHOW SLAVE STATUS顯示的列的對應關係為： 複製代碼 代碼如下:行 描述
1 檔案中的行號
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6 密碼(不由SHOW SLAVE STATUS顯示)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key

由SQL線程更新relay-log.info檔案。檔案中的行和SHOW SLAVE STATUS顯示的列的對應關係為： 複製代碼 代碼如下:行 描述
1 Relay_Log_File
2 Relay_Log_Pos
3 Relay_Master_Log_File
4 Exec_Master_Log_Pos

當備份從伺服器的資料時，你還應備份這兩個小檔案以及中繼記錄檔。它們用來在恢複從伺服器的資料後繼續進行複製。如果丟失了中繼日誌但仍然有 relay-log.info檔案，你可以通過檢查該檔案來確定SQL線程已經執行的主伺服器中二進位日誌的程度。然後可以用 Master_Log_File和Master_LOG_POS選項執行CHANGE MASTER TO來告訴從伺服器重新從該點讀取二進位日誌。當然，要求二進位日誌仍然在主伺服器上。
如果從伺服器正複製LOAD DATA INFILE語句，你應也備份該目錄內從伺服器用於該目的的任何SQL_LOAD-*檔案。從伺服器需要這些檔案繼續複製任何中斷的LOAD DATA INFILE操作。用--slave-load-tmpdir選項來指定目錄的位置。如果未指定， 預設值為tmpdir變數的值。
五：主從同步起點的說明
master.info的內容會覆蓋命令列或in my.cnf中指定的部分選項。
如果從伺服器啟動時master.info檔案不存在，選項採用選項檔案或命令列中指定的值。首次將伺服器作為從伺服器啟動時，或者已經運行RESET SLAVE然後已經關閉並重啟從伺服器時會發生。
如果從伺服器啟動時master.info檔案存在，伺服器忽略那些選項。使用master.info檔案中發現的值。
如果你使用與master.info檔案中相對應的啟動選項的不同的值重啟從伺服器，啟動選項的不同的值不會生效，因為伺服器繼續使用 master.info檔案。要想使用啟動選項的不同的值，必須刪除master.info檔案並重啟從伺服器，或(最好是)在從伺服器運行時使用 CHANGE MASTER TO語句重新設定值。
六：如何確保所有從伺服器已經處理了中繼日誌中的所有語句
在每個從伺服器上，發出STOP SLAVE IO_THREAD語句，然後檢查SHOW PROCESSLIST語句的輸出，直到你看到Has read all relay log。當所有從伺服器都執行完這些，它們可以被重新設定為一個新的設定。在被提升為主伺服器的從伺服器S1上，發出STOP SLAVE和RESET MASTER語句。
七：如果你確定可以跳過來自主伺服器的下一個語句，可以執行下面的語句 複製代碼 代碼如下:mysql> SET GLOBAL SQL_slave_SKIP_COUNTER = n；
mysql> START SLAVE；

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

八：二個有用的問答：
1）Q：如果主伺服器正在運行並且不想停止主伺服器，怎樣配置一個從伺服器？
A：有多種方法。如果你在某時間點做過主伺服器備份並且記錄了相應快照的二進位日誌名和位移量(通過SHOW MASTER STATUS命令的輸出)，採用下面的步驟： 複製代碼 代碼如下:1. 確保從伺服器分配了一個唯一的伺服器ID號。
2. 在從伺服器上執行下面的語句，為每個選項填入適當的值：
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. 在從伺服器上執行START SLAVE語句。
如果你沒有備份主伺服器，這裡是一個建立備份的快速程式。所有步驟都應該在主伺服器主機上執行。

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

當備份從伺服器的資料時，你還應備份這兩個小檔案以及中繼記錄檔。它們用來在恢複從伺服器的資料後繼續進行複製。如果丟失了中繼日誌但仍然有 relay-log.info檔案，你可以通過檢查該檔案來確定SQL線程已經執行的主伺服器中二進位日誌的程度。然後可以用 Master_Log_File和Master_LOG_POS選項執行CHANGE MASTER TO來告訴從伺服器重新從該點讀取二進位日誌。當然，要求二進位日誌仍然在主伺服器上。
如果從伺服器正複製LOAD DATA INFILE語句，你應也備份該目錄內從伺服器用於該目的的任何SQL_LOAD-*檔案。從伺服器需要這些檔案繼續複製任何中斷的LOAD DATA INFILE操作。用--slave-load-tmpdir選項來指定目錄的位置。如果未指定， 預設值為tmpdir變數的值

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

要實現 MySQL 的 Replication ，首先必須開啟 Master 端的BinaryLog(mysql-bin.xxxxxx)功能，否則無法實現。因為整個複製過程實際上就是Slave從Master端擷取該日誌然後 再在自己身上完全順序的執行日誌中所記錄的各種操作。開啟 MySQL 的 Binary Log 可以通過在啟動 MySQL Server 的過程中使用“—log-bin” 參數選項，或者在 my.cnf 設定檔中的 mysqld 參數組([mysqld]標識後的參數部分)增加“log-bin” 參數項。