mysql 主從復制原理與實現(xiàn)

1、2016-04-14weixin: dragonflyyi 為何要做MySQL 主從復制？ MySQL主從復制的原理是什么？ MySQL二進制日志的形式有哪些？ MySQL主從復制有哪些形態(tài)？ MySQL主從復制如何來實現(xiàn)？ 如何驗證主從數(shù)據(jù)的質(zhì)量? 遇到的相關(guān)問題與解決方案？ 相關(guān)參考資料主要議題 備份數(shù)據(jù)(Data Backup) 負載均衡(Load Balance) 數(shù)據(jù)分發(fā) (Data Distribution) 高可用和數(shù)據(jù)容錯(High Availability and Failover)只是簡單的對數(shù)據(jù)進行備份，降低數(shù)據(jù)丟失的風險，有事也用于報表等對數(shù)據(jù)時效性要求相對較低的場合。

2、主要用于MySQL 集群，解決單點故障或者故障切換;降低單臺服務(wù)的負載, 使得訪問均衡或者讀寫分離.主要用于多數(shù)據(jù)中心的，數(shù)據(jù)分發(fā)與同步為什么要做MySQL主從復制MySQL 自帶的健康監(jiān)控和檢測，(根據(jù)配置的時間間隔)可以檢測主庫是否正常工作，一旦發(fā)現(xiàn)主庫宕機或者無法工作，就會選擇到最好的一個備庫上. 主要基于MySQL 二進制 日志 主要包括三個進程（2個I/O進程，1個SQL進程）1. Master 將數(shù)據(jù)變化記錄到二進制日志中2. Slave將Master的二進制日志，拷貝到 Slave 的中繼日志中3. Slave將中繼日志中的事件再做一次， 將數(shù)據(jù)變化，反應到自身（Slave)數(shù)據(jù)

3、庫MySQL 的日志分類為:二進制日志、中繼日志、錯誤日志、查詢?nèi)罩?、慢查詢?nèi)罩?、事?wù)日志MySQL主從復制的原理 基于行的復制(Row-Based Replication)在主服務(wù)器上執(zhí)行的SQL語句，（將語句寫入二進制日志），在從服務(wù)器上執(zhí)行同樣的語句。MySQL默認采用基于語句的復制，效率比較高。 把改變的內(nèi)容（數(shù)據(jù)本身）復制過去，而不是把命令在從服務(wù)器上執(zhí)行一遍. 從mysql5.0開始支持默認采用基于語句的復制，一旦發(fā)現(xiàn)基于語句的無法精確的復制時，就會采用基于行的復制。MySQL 二進制日志的復制類型 基于語句的復制(Statement-Based Replication) 混合型的

4、復制(Mix-Based Replication) 主從類型（Master-Slave) 主主類型（Master-Master) 級聯(lián)類型（Master-Slave-Slave)MySQL復制的常用拓撲結(jié)構(gòu) 安裝MySQL服務(wù)器分別安裝兩臺MySQL 服務(wù)器：(MySQL 版本最好一致）192.168.0.1 (Master)192.168.0.2 (Slave) 創(chuàng)建備份賬戶并授權(quán)（在Master上） grant replication slave on *.* to copyer% identified by 12345;/* 用戶名： copyer; 密碼：12345 */MySQL主從

5、復制的步驟 修改主從的配置修改Master的配置信息mysqld#設(shè)置服務(wù)器IDserver-id=1#設(shè)置需要寫日志的數(shù)據(jù)庫binlog-do-db=test#設(shè)置不需要寫日志的數(shù)據(jù)庫binlog-ignore-db=mysql#日志基于行模式binlog_format=row#二進制日志文件存放位置log-bin=d:/log/mysql/mysql_log_bind:/log/mysql/mysql_log_bin.000001d:/log/mysql/mysql_log_bin.000002d:/log/mysql/mysql_log_bin.000003d:/log/mysql/my

6、sql_log_bin.000004d:/log/mysql/mysql_log_bin.000005d:/log/mysql/mysql_log_bin.000006d:/log/mysql/mysql_log_bin.000007d:/log/mysql/mysql_log_bin.index修改Slave的配置信息mysqld#設(shè)置服務(wù)器IDserver-id=2#設(shè)置需要寫日志的數(shù)據(jù)庫replicate-do-db=test#設(shè)置不需要寫日志的數(shù)據(jù)庫replicate-ignore-db=mysql#開啟slave 的二進制日志log_slave_updates = 1#日志基于行模式

7、binlog_format=row#二進制日志文件存放位置log-bin=d:/log/mysql/mysql_log_bin MySQL主從復制的步驟MySQL主從復制的步驟 啟動Master并查看狀況 關(guān)聯(lián)Slave(在Slave操作) 啟動主從復制（在Slave操作）在啟動復制前后，可以執(zhí)行show slave 命令查詢相關(guān)參數(shù)Slave_IO_Running=YesSlave_SQL_Running=Yes參數(shù)非常多，具體的可以參考MySQL官方文檔A. 事務(wù)的語句先緩存到事務(wù)緩存，直到提交指令，才寫入binlogB. 事務(wù)是串行寫入binlog，依賴于事務(wù)發(fā)生的次序MySQL事務(wù)與b

8、inlog日志的關(guān)系MySQL主從復制的狀況監(jiān)測 主從狀況監(jiān)測主要參數(shù)Slave_IO_Running IO線程是否打開：YES/NO/NULLSlave_SQL_Running SQL線程是否運行：YES/NO/NULLSeconds_Behind_Master落后主庫的時間（秒） 可能導致主從延時的因素主從時鐘是否一致網(wǎng)絡(luò)通信是否存在延時是否和日志類型，數(shù)據(jù)過大有關(guān)從庫性能，有沒有開啟binlog從庫查詢是否優(yōu)化（解決慢查詢）MySQL主從數(shù)據(jù)一致性檢測 pt-table-checksum工具檢測檢測數(shù)據(jù)的散列值是否一致對表數(shù)據(jù)進行分塊檢測（以避免導致從庫的延時）必須是居于語句的日志模式才能檢測（計算散列的函數(shù)需要在從庫上重做）檢測要求表、表結(jié)構(gòu)相同（否則檢測行為將終止）安全選項：check-replication-filters 是否檢查復制過濾規(guī)則 check-slave-tables 檢查是否所有從庫都有被檢查的表和列chunk-size-limit 每個chunk最大不能超過這個大小，超過就忽略它限速選項：check-interval