Mysql Replication系列之原理及配置
mysql的復制(replication)是一個異步的復制從一個mysql instace（master）復制到另一個mysql instace(slave)。實現整個復制操作主要有三個線程完成的，其中兩個線程在slave(SQL_thread和IO_thread)，另外一個線程在master（IO_thread）上。
Replication的原理如圖所示：
具體步驟:
1. Master接收用戶的請求，進行相磁的寫操作后生成MySQL binlog;
2.Slave會啟動兩個線程，一個IO_thread，一個SQL_thread。IO_thread連接到Master上后會告訴Master我需要那個binlog及binlog positition之后的日志;
3.Master接收到來自Slave的IO_thread進程的請求后，通過負責復制的IO_thread根據請求信息讀取指定日志指定位置之后的日志信息，返回給Slave 的IO_thread。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經到Master端的bin-log文件的名稱以及bin-log的位置;
4.Slave的IO_thread接收到信息后，為了必避免機器故障，日志丟失,將接收到的日志內容依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往后的日志內容，請發給我”;
5. Slave上SQL_thread讀取本的relay-log從中讀到了變化過程或是SQL在進行執行，執行后的結果寫入Slave上的數據文件中;
Master_Log_File，Read_Master_Log_Pos 記錄了IO thread讀到的當前master binlog文 件和位置， 對應master的binlog文件和位置。
Relay_Log_File，Relay_Log_Pos記錄了SQL thread執行到relay log的那個文件和位置，對應的是slave上的relay log文件和位置。
Relay_Master_Log_File，Exec_Master_Log_Pos記錄的是SQL thread執行到master binlog的文件和位置，對應的master上binlog的文件和位置。
Mysql Replication是基于binlog來完成的，那么我就需要認識一下binlog。
1.MySQL日志的格式(實際生產中基本都是ROW,建議你用MIXED)：
Binlog_format=STATEMENT|ROW|MIXED
STATEMENT ： 基于操作的SQL語句記錄到binlog中。
ROW： 基于行的變更情況記錄，會記錄行變更前的樣子及變更后的內容。
MIXED: 混后使用ROW和STATEMENT格式。對于DDL記錄會STATEMENT，對于TABLE
注意：如果使用Innodb表，事務級別使用了 READ COMMITTED or READ UNCOMMITTED日志級別只能使用row格式。但在使用ROW格式中DDL語句還是會記錄來STATEMENT格式。
基于SQL語句的復制(statement-based replication, SBR)，基于行的復制(row-based replication,RBR)，混合模式復制(mixed-based replication,MBR)。
ROW:
優點：
1.數據庫變更采用行模式記錄
2.slave上數據很少會不一致
3.任何情況都可以被復制，這對復制來說是最安全可靠的
4.和其他大多數數據庫系統的復制技術一樣
5.多數情況下，從服務器上的表如果有主鍵的話，復制就會快了很多
6.復制以下幾種語句時的行鎖更少：
a.INSERT ... SELECT
b. 包含 AUTO_INCREMENT 字段的 INSERT
c. 沒有附帶條件或者并沒有修改很多記錄的 UPDATE 或 DELETE 語句
7.執行 INSERT，UPDATE，DELETE 語句時鎖更少
8.從服務器上采用多線程來執行復制成為可能
缺點:
1.binlog 大了很多
2.復雜的回滾時 binlog 中會包含大量的數據
3.主服務器上執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，而 SBR 只會寫一次，這會導致頻繁發生 binlog 的并發寫問題
4.UDF 產生的大 BLOB 值會導致復制變慢
5.無法從 binlog 中看到都復制了寫什么語句
6.當在非事務表上執行一段堆積的SQL語句時，最好采用 SBR 模式，否則很容易導致主從服務器的數據不一致情況發生
STATEMENT:
優點：
1.歷史悠久，技術成熟
2.binlog文件較小
3.binlog中包含了所有數據庫更改信息，可以據此來審核數據庫的安全等情況
4.binlog可以用于實時的還原，而不僅僅用于復制
5.主從版本可以不一樣，從服務器版本可以比主服務器版本高
缺點：
1.不是所有的UPDATE語句都能被復制，尤其是包含不確定操作的時候。
2.調用具有不確定因素的 UDF 時復制也可能出問題
3.使用以下函數的語句也無法被復制：
a.LOAD_FILE()
b.UUID()
c.USER()
d.FOUND_ROWS()
e.SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)
4.INSERT ... SELECT 會產生比 RBR 更多的行級鎖
5.復制需要進行全表掃描(WHERE 語句中沒有使用到索引)的 UPDATE 時，需要比 RBR 請求更多的行級鎖
6.對于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句
7.對于一些復雜的語句，在從服務器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發生變化的記錄產生影響
8.存儲函數(不是存儲過程)在被調用的同時也會執行一次 NOW() 函數，這個可以說是壞事也可能是好事
9.確定了的 UDF 也需要在從服務器上執行
10.數據表必須幾乎和主服務器保持一致才行，否則可能會導致復制出錯
11.執行復雜語句如果出錯的話，會消耗更多資源
MIXED：
Mixed: 是以上兩種level的混合使用，一般的語句修改使用statment格式保存binlog，如一些函數，statement無法完成主從復制的操作，則采用row格式保binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了優化，并不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至于update或者delete等修改數據的語句，還是會記錄所有行的變更。
說了這么多,我們現在就來動手配置一個主從吧(只是在學習環境里用)
1.部署環境整理：
| 角色 | Ip:port | Server-id | 必備條件 | </tr>|||||||||||||||||||||||||
| Master | 192.168.11.20:3306 | 203306 | a.啟用log-bin; b.主庫上創建復制用戶(grant replication slave on *.* to 'repl'@'%' identified by 'repl';) |
</tr>
|||||||||||||||||||||||||
| Slav | 192.168.11.21:3306 | 213306 | </tr> </tbody> </table> 2.找到master上的log及position生成change master to語句： a.在主庫通過show master status，可以查看到log以及position |