MySQL主從復(fù)制搭建蕴潦，基于日志（binlog）

什么是MySQL主從復(fù)制

簡單來說就是保證主SQL（Master）和從SQL（Slave）的數(shù)據(jù)是一致性的研侣，向Master插入數(shù)據(jù)后喻括，Slave會(huì)自動(dòng)從Master把修改的數(shù)據(jù)同步過來（有一定的延遲），通過這種方式來保證數(shù)據(jù)的一致性领追，就是主從復(fù)制

MySQL主從能解決什么問題

一他膳、高可用

因?yàn)閿?shù)據(jù)都是相同的，所以當(dāng)Master掛掉后绒窑，可以指定一臺(tái)Slave充當(dāng)Master繼續(xù)保證服務(wù)運(yùn)行棕孙，因?yàn)閿?shù)據(jù)是一致性的（如果當(dāng)插入Master就掛掉，可能不一致些膨，因?yàn)橥揭残枰獣r(shí)間）蟀俊，當(dāng)然這種配置不是簡單的把一臺(tái)Slave充當(dāng)Master，畢竟還要考慮后續(xù)的Salve同步Master订雾，當(dāng)然本文并不是將高可用的配置肢预，所以這里就不多講了

二、負(fù)載均衡

因?yàn)樽x寫分離也算是負(fù)載均衡的一種洼哎，所以就不單獨(dú)寫了烫映，因?yàn)橐话愣际怯卸嗯_(tái)Slave的沼本，所以可以將讀操作指定到Slave服務(wù)器上（需要代碼控制），然后再用負(fù)載均衡來選擇那臺(tái)Slave來提供服務(wù)窑邦，同時(shí)也可以吧一些大量計(jì)算的查詢指定到某臺(tái)Slave擅威，這樣就不會(huì)影響Master的寫入以及其他查詢

三、數(shù)據(jù)備份

一般我們都會(huì)做數(shù)據(jù)備份冈钦，可能是寫定時(shí)任務(wù)，一些特殊行業(yè)可能還需要手動(dòng)備份李请，有些行業(yè)要求備份和原數(shù)據(jù)不能在同一個(gè)地方瞧筛，所以主從就能很好的解決這個(gè)問題，不僅備份及時(shí)导盅，而且還可以多地備份较幌，保證數(shù)據(jù)的安全

四、業(yè)務(wù)模塊化

可以一個(gè)業(yè)務(wù)模塊讀取一個(gè)Slave白翻，再針對不同的業(yè)務(wù)場景進(jìn)行數(shù)據(jù)庫的索引創(chuàng)建和根據(jù)業(yè)務(wù)選擇MySQL存儲(chǔ)引擎

五乍炉、高擴(kuò)展（硬件擴(kuò)展）

主從復(fù)制支持2種擴(kuò)展方式

1、scale-up

向上擴(kuò)展或者縱向擴(kuò)展滤馍，主要是提供比現(xiàn)在服務(wù)器更好性能的服務(wù)器岛琼，比如增加CPU和內(nèi)存以及磁盤陣列等，因?yàn)橛卸嗯_(tái)服務(wù)器巢株，所以可擴(kuò)展性比單臺(tái)更大

2槐瑞、scale-out

向外擴(kuò)展或者橫向擴(kuò)展，是指增加服務(wù)器數(shù)量的擴(kuò)展阁苞，這樣主要能分散各個(gè)服務(wù)器的壓力

主從復(fù)制的缺點(diǎn)

一困檩、成本增加

無可厚非的是搭建主從肯定會(huì)增加成本，畢竟一臺(tái)服務(wù)器和兩臺(tái)服務(wù)器的成本完全不同那槽，另外由于主從必須要開啟二進(jìn)制日志悼沿，所以也會(huì)造成額外的性能消耗

二、數(shù)據(jù)延遲

Slave從Master復(fù)制過來肯定是會(huì)有一定的數(shù)據(jù)延遲的骚灸，所以當(dāng)剛插入就出現(xiàn)查詢的情況糟趾，可能查詢不出來，當(dāng)然如果是插入者自己查詢逢唤，那么可以直接從Master中查詢出來拉讯，當(dāng)然這個(gè)也是需要用代碼來控制的

三、寫入更慢

主從復(fù)制主要是針對讀遠(yuǎn)大于寫或者對數(shù)據(jù)備份實(shí)時(shí)性要求較高的系統(tǒng)中鳖藕，因?yàn)镸aster在寫中需要更多操作魔慷，而且只有一臺(tái)寫入的Master（因?yàn)槲夷壳爸粫?huì)配置一臺(tái)寫入Master，最多就是有從Master的Slave著恩，用來在Master掛掉后替換成Master院尔，平時(shí)不對外進(jìn)行服務(wù)）蜻展，所以寫入的壓力并不能被分散，當(dāng)然如果直接怎么解決這個(gè)問題的話邀摆，歡迎留言指教

復(fù)制方式

MySQL5.6開始主從復(fù)制有兩種方式：基于日志（binlog）纵顾、基于GTID（全局事務(wù)標(biāo)示符）。
本文只涉及基于日志binlog的主從配置

復(fù)制原理

1栋盹、Master將數(shù)據(jù)改變記錄到二進(jìn)制日志(binary log)中施逾，也就是配置文件log-bin指定的文件，這些記錄叫做二進(jìn)制日志事件(binary log events)
2例获、Slave通過I/O線程讀取Master中的binary log events并寫入到它的中繼日志(relay log)
3汉额、Slave重做中繼日志中的事件，把中繼日志中的事件信息一條一條的在本地執(zhí)行一次榨汤，完成數(shù)據(jù)在本地的存儲(chǔ)蠕搜，從而實(shí)現(xiàn)將改變反映到它自己的數(shù)據(jù)(數(shù)據(jù)重放)

要求

1、主從服務(wù)器操作系統(tǒng)版本和位數(shù)一致
2收壕、Master和Slave數(shù)據(jù)庫的版本要一致
3妓灌、Master和Slave數(shù)據(jù)庫中的數(shù)據(jù)要一致
4、Master開啟二進(jìn)制日志蜜宪，Master和Slave的server_id在局域網(wǎng)內(nèi)必須唯一

具體配置

硬件需求

兩臺(tái)或兩臺(tái)以上安裝了相同版本的MySQL（我沒有試過不同版本會(huì)不會(huì)有問題虫埂，有興趣的可以試試），當(dāng)然這個(gè)可以用虛擬機(jī)或者Docker代替端壳，個(gè)人推薦用Docker告丢，比虛擬機(jī)消耗少太多了，當(dāng)然用起來可能沒有虛擬機(jī)那么方便损谦，但是卻不用挨個(gè)環(huán)境配置了

配置Master

一岖免、安裝數(shù)據(jù)庫

二、配置my.cnf

不同的系統(tǒng)my.cnf路徑不同照捡，所以我們只講解牽扯修改的地方颅湘。添加配置

[mysqld]
## 設(shè)置server_id，一般設(shè)置為IP,注意要唯一
server_id=100
## 復(fù)制過濾：也就是指定哪個(gè)數(shù)據(jù)庫不用同步（mysql庫一般不同步）
binlog-ignore-db=mysql
## 開啟二進(jìn)制日志功能栗精，可以隨便取闯参，最好有含義（關(guān)鍵就是這里了）
log-bin=edu-mysql-bin
## 為每個(gè)session 分配的內(nèi)存，在事務(wù)過程中用來存儲(chǔ)二進(jìn)制日志的緩存
binlog_cache_size=1M
## 主從復(fù)制的格式（mixed,statement,row悲立，默認(rèn)格式是statement）
binlog_format=mixed
## 二進(jìn)制日志自動(dòng)刪除/過期的天數(shù)鹿寨。默認(rèn)值為0，表示不自動(dòng)刪除薪夕。
expire_logs_days=7
## 跳過主從復(fù)制中遇到的所有錯(cuò)誤或指定類型的錯(cuò)誤脚草，避免slave端復(fù)制中斷。
## 如：1062錯(cuò)誤是指一些主鍵重復(fù)原献，1032錯(cuò)誤是因?yàn)橹鲝臄?shù)據(jù)庫數(shù)據(jù)不一致
slave_skip_errors=1062

配置完成后重啟mysql

關(guān)于復(fù)制過濾

復(fù)制過濾可以讓你只復(fù)制服務(wù)器中的一部分?jǐn)?shù)據(jù)馏慨，有兩種復(fù)制過濾：

1埂淮、在Master上過濾二進(jìn)制日志中的事件
2、在Slave上過濾中繼日志中的事件写隶。

復(fù)制類型

1倔撞、基于語句的復(fù)制

在Master上執(zhí)行的SQL語句，在Slave上執(zhí)行同樣的語句慕趴。MySQL默認(rèn)采用基于語句的復(fù)制痪蝇，效率比較高。一旦發(fā)現(xiàn)沒法精確復(fù)制時(shí)冕房，會(huì)自動(dòng)選著基于行的復(fù)制

2霹俺、基于行的復(fù)制

把改變的內(nèi)容復(fù)制到Slave，而不是把命令在Slave上執(zhí)行一遍毒费。從MySQL5.0開始支持

3、混合類型的復(fù)制

默認(rèn)采用基于語句的復(fù)制愈魏，一旦發(fā)現(xiàn)基于語句的無法精確的復(fù)制時(shí)觅玻，就會(huì)采用基于行的復(fù)制

三、創(chuàng)建數(shù)據(jù)同步用戶

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

這里主要是要授予用戶REPLICATION SLAVE權(quán)限和REPLICATION CLIENT權(quán)限

配置Slave

一培漏、安裝數(shù)據(jù)庫

二溪厘、配置my.cnf

[mysqld]
## 設(shè)置server_id，一般設(shè)置為IP,注意要唯一
server_id=101
## 復(fù)制過濾：也就是指定哪個(gè)數(shù)據(jù)庫不用同步（mysql庫一般不同步）
binlog-ignore-db=mysql
## 開啟二進(jìn)制日志功能牌柄，以備Slave作為其它Slave的Master時(shí)使用
log-bin=edu-mysql-slave1-bin
## 為每個(gè)session 分配的內(nèi)存畸悬，在事務(wù)過程中用來存儲(chǔ)二進(jìn)制日志的緩存
binlog_cache_size=1M
## 主從復(fù)制的格式（mixed,statement,row，默認(rèn)格式是statement）
binlog_format=mixed
## 二進(jìn)制日志自動(dòng)刪除/過期的天數(shù)珊佣。默認(rèn)值為0蹋宦，表示不自動(dòng)刪除。
expire_logs_days=7
## 跳過主從復(fù)制中遇到的所有錯(cuò)誤或指定類型的錯(cuò)誤咒锻，避免slave端復(fù)制中斷冷冗。
## 如：1062錯(cuò)誤是指一些主鍵重復(fù)，1032錯(cuò)誤是因?yàn)橹鲝臄?shù)據(jù)庫數(shù)據(jù)不一致
slave_skip_errors=1062
## relay_log配置中繼日志
relay_log=edu-mysql-relay-bin
## log_slave_updates表示slave將復(fù)制事件寫進(jìn)自己的二進(jìn)制日志
log_slave_updates=1
## 防止改變數(shù)據(jù)(除了特殊的線程)
read_only=1

如果Slave為其它Slave的Master時(shí)惑艇，必須設(shè)置bin_log蒿辙。配置完成后重啟mysql

完成Master和Slave鏈接

一、初始化數(shù)據(jù)

保證Master和Slave除不同步的數(shù)據(jù)庫滨巴，其他庫的數(shù)據(jù)一致

二思灌、查詢Master狀態(tài)

在Master中執(zhí)行

show master status;

記錄下返回結(jié)果的File列和Position列的值

三、Slave中設(shè)置Master信息

在Slave中執(zhí)行

change master to master_host='192.168.1.100', master_user='slave', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=1389, master_connect_retry=30;

上面執(zhí)行的命令的解釋：

master_host='192.168.1.100' ## Master的IP地址

master_user='slave' ## 用于同步數(shù)據(jù)的用戶（在Master中授權(quán)的用戶）

master_password='123456' ## 同步數(shù)據(jù)用戶的密碼

master_port=3306 ## Master數(shù)據(jù)庫服務(wù)的端口

master_log_file='edu-mysql-bin.000001' ##指定Slave從哪個(gè)日志文件開始讀復(fù)制數(shù)據(jù)（Master上執(zhí)行命令的結(jié)果的File字段）

master_log_pos=429 ## 從哪個(gè)POSITION號開始讀（Master上執(zhí)行命令的結(jié)果的Position字段）

master_connect_retry=30 ##當(dāng)重新建立主從連接時(shí)恭取，如果連接建立失敗泰偿，間隔多久后重試。單位為秒秽荤，默認(rèn)設(shè)置為60秒甜奄，同步延遲調(diào)優(yōu)參數(shù)柠横。

四、查看主從同步狀態(tài)

在Slave中執(zhí)行命令

show slave status;

可看到Slave_IO_State為空课兄， Slave_IO_Running和Slave_SQL_Running是No牍氛，表明Slave還沒有開始復(fù)制過程。相反Slave_IO_Running和Slave_SQL_Running是Yes表明已經(jīng)開始工作了

五烟阐、開啟主從同步