環(huán)境

操作系統(tǒng)：CentOS-6.5-x86_64-bin-DVD1.iso

MySQL版本：mysql-5.6.29.tar.gz

主節(jié)點IP：192.168.1.205 主機名：edu-mysql-01

從節(jié)點IP：192.168.1.206 主機名：edu-mysql-02

主機配置：4核CPU、4G內(nèi)存

依賴課程

《高可用架構(gòu)篇--第13節(jié)--MySQL源碼編譯安裝（CentOS-6.5+MySQL-5.6）》

MySQL主從復(fù)制官方文檔

http://dev.mysql.com/doc/refman/5.6/en/replication.html

MySQL主從復(fù)制的方式

MySQL5.6開始主從復(fù)制有兩種方式：基于日志（binlog）、基于GTID（全局事務(wù)標示符）乾巧。

本教程主要講基于日志（binlog）的復(fù)制怜械。

MySQL主從復(fù)制（也稱A/B復(fù)制）的原理

(1) Master將數(shù)據(jù)改變記錄到二進制日志(binary log)中汞贸，也就是配置文件log-bin指定的文件，這些記錄叫做二進制日志事件(binary log events)；

(2) Slave通過I/O線程讀取Master中的binary log events并寫入到它的中繼日志(relay log)；

(3) Slave重做中繼日志中的事件蜕猫，把中繼日志中的事件信息一條一條的在本地執(zhí)行一次，完成數(shù)據(jù)在本地的存儲哎迄，從而實現(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開啟二進制日志，Master和Slave的server_id在局域網(wǎng)內(nèi)必須唯一旨涝；

主從配置的簡要步驟

1蹬屹、Master上的配置

(1) 安裝數(shù)據(jù)庫；

(2) 修改數(shù)據(jù)庫配置文件，指明server_id哩治，開啟二進制日志(log-bin)秃踩；

(3) 啟動數(shù)據(jù)庫，查看當前是哪個日志业筏，position號是多少；

(4) 登錄數(shù)據(jù)庫鸟赫，授權(quán)數(shù)據(jù)復(fù)制用戶（IP地址為從機IP地址蒜胖，如果是雙向主從，這里的還需要授權(quán)本機的IP地址抛蚤，此時自己的IP地址就是從IP地址)台谢；

(5) 備份數(shù)據(jù)庫（記得加鎖和解鎖）；

(6) 傳送備份數(shù)據(jù)到Slave上岁经；

(7) 啟動數(shù)據(jù)庫朋沮；

以下步驟，為單向主從搭建成功缀壤，想搭建雙向主從需要的步驟：

(1) 登錄數(shù)據(jù)庫樊拓，指定Master的地址、用戶塘慕、密碼等信息（此步僅雙向主從時需要）筋夏；

(2) 開啟同步，查看狀態(tài)图呢；

2条篷、Slave上的配置

(1) 安裝數(shù)據(jù)庫；

(2) 修改數(shù)據(jù)庫配置文件蛤织，指明server_id（如果是搭建雙向主從的話赴叹，也要開啟二進制日志log-bin）；

(3) 啟動數(shù)據(jù)庫指蚜，還原備份乞巧；

(4) 查看當前是哪個日志，position號是多少（單向主從此步不需要姚炕，雙向主從需要）摊欠；

(5) 指定Master的地址、用戶柱宦、密碼等信息些椒；

(6) 開啟同步，查看狀態(tài)掸刊。

單向主從環(huán)境（也稱MySQL A/B復(fù)制）的搭建

1免糕、Master（192.168.1.205）和Slave（192.168.1.206）上都安裝了相同版本的數(shù)據(jù)庫（mysql-5.6.26.tar.gz）。

注意：兩臺數(shù)據(jù)庫服務(wù)器的的selinux都要disable（永久關(guān)閉selinux，請修改/etc/selinux/config石窑，將SELINUX改為disabled）

2牌芋、修改Master的配置文件/etc/my.cnf

[root@edu-mysql-01 ~]# vi /etc/my.cnf

## 在 [mysqld] 中增加以下配置項

## 設(shè)置server_id，一般設(shè)置為IP

server_id=205

## 復(fù)制過濾：需要備份的數(shù)據(jù)庫松逊，輸出binlog

#binlog-do-db=roncoo

## 復(fù)制過濾：不需要備份的數(shù)據(jù)庫躺屁，不輸出（mysql庫一般不同步）

binlog-ignore-db=mysql

## 開啟二進制日志功能，可以隨便取经宏，最好有含義

log-bin=edu-mysql-bin

## 為每個session 分配的內(nèi)存犀暑，在事務(wù)過程中用來存儲二進制日志的緩存

binlog_cache_size=1M

## 主從復(fù)制的格式（mixed,statement,row，默認格式是statement）

binlog_format=mixed

## 二進制日志自動刪除/過期的天數(shù)烁兰。默認值為0耐亏，表示不自動刪除。

expire_logs_days=7

## 跳過主從復(fù)制中遇到的所有錯誤或指定類型的錯誤沪斟，避免slave端復(fù)制中斷广辰。

## 如：1062錯誤是指一些主鍵重復(fù)，1032錯誤是因為主從數(shù)據(jù)庫數(shù)據(jù)不一致

slave_skip_errors=1062

（如想了解以上參數(shù)的更多詳細解析主之，大家可以直接百度參數(shù)名）

2.1 復(fù)制過濾可以讓你只復(fù)制服務(wù)器中的一部分數(shù)據(jù)择吊，有兩種復(fù)制過濾：

(1)在Master上過濾二進制日志中的事件；

(2)在Slave上過濾中繼日志中的事件杀餐。如下：

2.2 MySQL對于二進制日志 (binlog)的復(fù)制類型

(1) 基于語句的復(fù)制：在Master上執(zhí)行的SQL語句干发，在Slave上執(zhí)行同樣的語句。MySQL默認采用基于語句的復(fù)制史翘，效率比較高枉长。一旦發(fā)現(xiàn)沒法精確復(fù)制時，會自動選著基于行的復(fù)制琼讽。

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

(3) 混合類型的復(fù)制：默認采用基于語句的復(fù)制吼蚁，一旦發(fā)現(xiàn)基于語句的無法精確的復(fù)制時，就會采用基于行的復(fù)制问欠。

3肝匆、啟動/重啟Master數(shù)據(jù)庫服務(wù)，登錄數(shù)據(jù)庫顺献，創(chuàng)建數(shù)據(jù)同步用戶旗国，并授予相應(yīng)的權(quán)限

[root@edu-mysql-01 ~]# service mysql restart

Shutting down MySQL..[ OK ]

Starting MySQL..[ OK ]

[root@edu-mysql-01 ~]# mysql -uroot -p

Enter password:

##創(chuàng)建數(shù)據(jù)同步用戶，并授予相應(yīng)的權(quán)限

mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.206' identified by 'roncoo.123';

Query OK, 0 rows affected (0.00 sec)

## 刷新授權(quán)表信息

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

## 查看position號注整，記下position號（從機上需要用到這個position號和現(xiàn)在的日志文件)

mysql> show master status;

4能曾、創(chuàng)建roncoo庫度硝、表，并寫入一定量的數(shù)據(jù)寿冕，用于模擬現(xiàn)有的業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫

create database if not exists roncoo default charset utf8 collate utf8_general_ci;

use roncoo;

DROP TABLE IF EXISTS `edu_user`;

CREATE TABLE `edu_user` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`userName` varchar(255) NOT NULL DEFAULT '' COMMENT '用戶名',

`pwd` varchar(255) NOT NULL DEFAULT '' COMMENT '密碼',

PRIMARY KEY (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用戶信息表';

INSERT INTO `edu_user` VALUES (1,'吳水成','123456'),(2,'清風(fēng)','123456'),(3,'龍果','roncoo.com');

5蕊程、為保證Master和Slave的數(shù)據(jù)一致，我們采用主備份驼唱，從還原來實現(xiàn)初始數(shù)據(jù)一致

## 先臨時鎖表

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

## 這里我們實行全庫備份藻茂，在實際中，我們可能只同步某一個庫玫恳，那也可以只備份一個庫

[root@edu-mysql-01 ~]# mysqldump -p3306 -uroot -p --add-drop-table roncoo > /tmp/edu-master-roncoo.sql;

Warning: Using a password on the command line interface can be insecure.

Enter password:

[root@edu-mysql-01 ~]# cd /tmp

[root@edu-mysql-01 tmp]# ll

total 644

-rw-r--r-- 1 root root 644266 Dec 20 04:10 edu-master-roncoo.sql

## 注意：實際生產(chǎn)環(huán)境中大數(shù)據(jù)量（超2G數(shù)據(jù)）的備份捌治，建議不要使用mysqldump進行比分，因為會非常慢纽窟。此時推薦使用XtraBackup 進行備份。

## 解鎖表

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

將Master上備份的數(shù)據(jù)遠程傳送到Slave上兼吓，以用于Slave配置時恢復(fù)數(shù)據(jù)

[root@edu-mysql-01 ~]# scp /tmp/edu-master-roncoo.sql root@192.168.1.206:/tmp/

root@192.168.1.206's password:

edu-master-roncoo.sql 100% 629KB 629.2KB/s 00:00

[root@edu-mysql-01 ~]#

6臂港、接下來處理Slave（192.168.1.206），配置文件只需修改一項视搏，其余配置用命令來操作

[root@edu-mysql-02 ~]# vi /etc/my.cnf

## 在 [mysqld] 中增加以下配置項

## 設(shè)置server_id审孽，一般設(shè)置為IP

server_id=206

## 復(fù)制過濾：需要備份的數(shù)據(jù)庫，輸出binlog

#binlog-do-db=roncoo

##復(fù)制過濾：不需要備份的數(shù)據(jù)庫浑娜，不輸出（mysql庫一般不同步）

binlog-ignore-db=mysql

## 開啟二進制日志佑力，以備Slave作為其它Slave的Master時使用

log-bin=edu-mysql-slave1-bin

## 為每個session 分配的內(nèi)存，在事務(wù)過程中用來存儲二進制日志的緩存

binlog_cache_size = 1M

## 主從復(fù)制的格式（mixed,statement,row筋遭，默認格式是statement）

binlog_format=mixed

## 二進制日志自動刪除/過期的天數(shù)打颤。默認值為0，表示不自動刪除漓滔。

expire_logs_days=7

## 跳過主從復(fù)制中遇到的所有錯誤或指定類型的錯誤编饺，避免slave端復(fù)制中斷。

## 如：1062錯誤是指一些主鍵重復(fù)响驴，1032錯誤是因為主從數(shù)據(jù)庫數(shù)據(jù)不一致

slave_skip_errors=1062

## relay_log配置中繼日志

relay_log=edu-mysql-relay-bin

## log_slave_updates表示slave將復(fù)制事件寫進自己的二進制日志

log_slave_updates=1

## 防止改變數(shù)據(jù)(除了特殊的線程)

read_only=1

如果Slave為其它Slave的Master時透且，必須設(shè)置bin_log。在這里豁鲤，我們開啟了二進制日志秽誊，而且顯式的命名(默認名稱為hostname，但是琳骡，如果hostname改變則會出現(xiàn)問題)锅论。

relay_log配置中繼日志，log_slave_updates表示slave將復(fù)制事件寫進自己的二進制日志日熬。

當設(shè)置log_slave_updates時棍厌，你可以讓slave扮演其它slave的master肾胯。此時，slave把SQL線程執(zhí)行的事件寫進行自己的二進制日志(binary

log)耘纱，然后敬肚，它的slave可以獲取這些事件并執(zhí)行它。如下圖所示（發(fā)送復(fù)制事件到其它Slave）：

7束析、保存后重啟MySQL服務(wù)艳馒，還原備份數(shù)據(jù)

[root@edu-mysql-02 ~]# service mysql restart

Shutting down MySQL..[ OK ]

Starting MySQL..[ OK ]

Slave上創(chuàng)建相同庫：

create database if not exists roncoo default charset utf8 collate utf8_general_ci;

use roncoo;

導(dǎo)入數(shù)據(jù)

[root@edu-mysql-02 ~]# mysql -uroot -p roncoo < /tmp/edu-master-roncoo.sql

Enter password:

[root@edu-mysql-02 ~]#

8、登錄Slave數(shù)據(jù)庫员寇，添加相關(guān)參數(shù)

（Master的IP弄慰、端口、同步用戶蝶锋、密碼陆爽、position號、讀取哪個日志文件)

[root@edu-mysql-02 ~]# mysql -uroot -p

Enter password:

mysql>

change master to master_host='192.168.1.205', master_user='repl',

master_password='roncoo.123', master_port=3306,

master_log_file='edu-mysql-bin.000001', master_log_pos=1389,

master_connect_retry=30;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

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

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

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

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

master_log_file='edu-mysql-bin.000001'##指定Slave從哪個日志文件開始讀復(fù)制數(shù)據(jù)（可在Master上使用show master status查看到日志文件名）

master_log_pos=429## 從哪個POSITION號開始讀

master_connect_retry=30##當重新建立主從連接時扳缕，如果連接建立失敗慌闭，間隔多久后重試。單位為秒躯舔，默認設(shè)置為60秒驴剔，同步延遲調(diào)優(yōu)參數(shù)。

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

mysql> show slave status\G;

可看到Slave_IO_State為空粥庄， Slave_IO_Running和Slave_SQL_Running是No丧失，表明Slave還沒有開始復(fù)制過程。

## 開啟主從同步

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

## 再查看主從同步狀態(tài)

mysql> show slave status\G;

主要看以下兩個參數(shù)惜互，這兩個參數(shù)如果是Yes就表示主從同步正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

由截圖中的主從同步狀態(tài)信息可以看出布讹，我們配置的主從同步是正常的。

可查看master和slave上線程的狀態(tài)载佳。在master上炒事，可以看到slave的I/O線程創(chuàng)建的連接：

Master : mysql> show processlist\G;

1.row 為處理slave的I/O線程的連接。

2.row 為處理MySQL客戶端連接線程蔫慧。

3.row 為處理本地命令行的線程挠乳。

Slave : mysql> show processlist\G;

1.row為I/O線程狀態(tài)。

2.row 為SQL線程狀態(tài)姑躲。

3.row 為處理本地命令行的線程睡扬。

9、主從數(shù)據(jù)復(fù)制同步測試

(1) 在Master中的roncoo庫上變更數(shù)據(jù)的同步測試黍析；

mysql> INSERT INTO `edu_user` VALUES (4,'同步測試1','123456'),(5,'同步測試2','123456');

Master中添加完之后卖怜，登錄Slave中查看數(shù)據(jù)是否已同步。

(2) 在Master上新建一個ron庫

mysql> create database if not exists ron default charset utf8 collate utf8_general_ci;

在Slave中查看數(shù)據(jù)庫

mysql> show databases;

最終的測試結(jié)果是阐枣，在Master中的操作马靠，都成功同步到了Slave奄抽。

10、測試過程中甩鳄，如果遇到同步出錯逞度，可在Slave上重置主從復(fù)制設(shè)置（選操作）：

(1) mysql> reset slave;

(2) mysql> change master to master_host='192.168.1.205',

master_user='repl',

master_password='roncoo.123',

master_port=3306,

master_log_file='edu-mysql-bin.00000x',

master_log_pos=xx,

master_connect_retry=30;

(此時，master_log_file和master_log_pos要在Master中用show master status 命令查看)

注意：如果在Slave沒做只讀控制的情況下妙啃，千萬不要在Slave中手動插入數(shù)據(jù)档泽，那樣數(shù)據(jù)就會不一致，主從就會斷開揖赴，就需要重新配置了馆匿。

11、上面所搭建的是單向復(fù)制的主從燥滑，也是用的比較多的渐北，而雙向主從其實就是Master和Slave都開啟日志功能，然后在Master執(zhí)行授權(quán)用戶（這里授權(quán)的是自己作為從服務(wù)器铭拧，也就是這里的IP地址是Master的IP地址）腔稀，然后再在Master上進行chang

master操作。

MySQL主從數(shù)據(jù)同步延遲問題的調(diào)優(yōu)

基于局域網(wǎng)的Master/Slave機制在通常情況下已經(jīng)可以滿足“實時”備份的要求了羽历。如果延遲比較大，可以從以下幾個因素進行排查：

(1) 網(wǎng)絡(luò)延遲淡喜；

(2) Master負載過高秕磷；

(3) Slave負載過高；

一般的做法是使用多臺Slave來分攤讀請求炼团，再單獨配置一臺Slave只作為備份用澎嚣，不進行其他任何操作，就能相對最大限度地達到“實時”的要求了瘟芝。

兩個可以減少主從復(fù)制延遲的參數(shù)（按需配置）：

MySQL可以指定3個參數(shù)易桃，用于復(fù)制線程重連主庫：--master-retry-count，--master-connect-retry锌俱，--slave-net-timeout

晤郑。其中master-connect-retry 和 master-retry-count 需要在 Change Master

搭建主備復(fù)制時指定，而 slave-net-timeout 是一個全局變量贸宏，可以在 MySQL 運行時在線設(shè)置造寝。具體的重試策略為：備庫過了

slave-net-timeout 秒還沒有收到主庫來的數(shù)據(jù)，它就會開始第一次重試吭练。然后每過 master-connect-retry

秒诫龙，備庫會再次嘗試重連主庫。直到重試了 master-retry-count

次鲫咽，它才會放棄重試签赃。如果重試的過程中谷异，連上了主庫，那么它認為當前主庫是好的锦聊，又會開始 slave-net-timeout

秒的等待歹嘹。slave-net-timeout 的默認值是 3600 秒，master-connect-retry 默認為 60

秒括丁，master-retry-count 默認為 86400

次荞下。也就是說，如果主庫一個小時都沒有任何數(shù)據(jù)變更發(fā)送過來史飞，備庫才會嘗試重連主庫尖昏。這就是為什么在我們模擬的場景下，一個小時后构资，備庫才會重連主庫抽诉，繼續(xù)同步數(shù)據(jù)變更的原因。

這樣的話吐绵，如果你的主庫上變更比較頻繁迹淌，可以考慮將

slave-net-timeout 設(shè)置的小一點，避免主庫 Binlog dump 線程終止了己单，無法將最新的更新推送過來唉窃。當然

slave-net-timeout 設(shè)置的過小也有問題，這樣會導(dǎo)致如果主庫的變更確實比較少的時候纹笼，備庫頻繁的重新連接主庫纹份，造成資源浪費。

slave-net-timeout=seconds

參數(shù)說明：當Slave從Master數(shù)據(jù)庫讀取log數(shù)據(jù)失敗后廷痘，等待多久重新建立連接并獲取數(shù)據(jù)蔓涧，單位為秒，默認設(shè)置為3600秒笋额。

在做MySQL

Slave的時候經(jīng)常會遇到很多錯誤元暴，需要根據(jù)具體原因跨過錯誤繼續(xù)同步，但有時候是因為網(wǎng)絡(luò)不穩(wěn)定兄猩、網(wǎng)絡(luò)閃斷造成同步不正常茉盏，如果Slave機器非常多的情況下，一個一個登錄服務(wù)器去stop

slave枢冤、start slave變得無聊而且重復(fù)援岩。從MySQL5.1開始支持的解決方案配置：

master-connect-retry=seconds

參數(shù)說明：在主服務(wù)器宕機或連接丟失的情況下，從服務(wù)器線程重新嘗試連接主服務(wù)器之前睡眠的秒數(shù)掏导。如果主服務(wù)器.info文件中的值可以讀取則優(yōu)先使用享怀。如果未設(shè)置，默認值為60趟咆。

通常配置以上2個參數(shù)可以減少網(wǎng)絡(luò)問題導(dǎo)致的主從數(shù)據(jù)同步延遲添瓷。

一般網(wǎng)絡(luò)問題的錯誤是：

[ERROR] Error reading packet from server: Lost connection to MySQL server during query (server_errno=xxxx)

[ERROR] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘edu-mysql-bin.000256’ position 23456