mysql 主從復(fù)制原理與實(shí)現(xiàn)

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

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

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

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

5、復(fù)制的步驟 修改主從的配置修改Master的配置信息mysqld#設(shè)置服務(wù)器IDserver-id=1#設(shè)置需要寫(xiě)日志的數(shù)據(jù)庫(kù)binlog-do-db=test#設(shè)置不需要寫(xiě)日志的數(shù)據(jù)庫(kù)binlog-ignore-db=mysql#日志基于行模式binlog_format=row#二進(jìn)制日志文件存放位置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è)置需要寫(xiě)日志的數(shù)據(jù)庫(kù)replicate-do-db=test#設(shè)置不需要寫(xiě)日志的數(shù)據(jù)庫(kù)replicate-ignore-db=mysql#開(kāi)啟slave 的二進(jìn)制日志log_slave_updates = 1#日志基于行模式

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

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