高可用架構-- MySQL主從複制的配置

環境

作業系統：CentOS-6.6-x86_64-bin-DVD1.iso

MySQL版本：mysql-5.6.26.tar.gz

主節點IP：192.168.1.205 主機名：edu-mysql-01

從節點IP：192.168.1.206 主機名：edu-mysql-02

主機配置：4核CPU、4G記憶體

依賴課程

《高可用架構篇--第13節--MySQL源碼編譯安裝（CentOS-6.6+MySQL-5.6）》

MySQL主從複制官方文檔

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

MySQL主從複制的方式

MySQL5.6開始主從複制有兩種方式：基于日志（binlog）、基于GTID（全局事務标示符）。

本教程主要講基于日志（binlog）的複制。

MySQL主從複制（也稱A/B複制）的原理

(1) Master将資料改變記錄到二進制日志(binary log)中，也就是配置檔案log-bin指定的檔案，這些記錄叫做二進制日志事件(binary log events)；

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

(3) Slave重做中繼日志中的事件，把中繼日志中的事件資訊一條一條的在本地執行一次，完成資料在本地的存儲，進而實作将改變反映到它自己的資料(資料重放)。

主從配置需要注意的點

(1)主從伺服器作業系統版本和位數一緻；

(2) Master和Slave資料庫的版本要一緻；

(3) Master和Slave資料庫中的資料要一緻；

(4) Master開啟二進制日志，Master和Slave的server_id在區域網路内必須唯一；

主從配置的簡要步驟

1、Master上的配置

(1) 安裝資料庫；

(2) 修改資料庫配置檔案，指明server_id，開啟二進制日志(log-bin)；

(3) 啟動資料庫，檢視目前是哪個日志，position号是多少；

(4) 登入資料庫，授權資料複制使用者（IP位址為從機IP位址，如果是雙向主從，這裡的還需要授權本機的IP位址，此時自己的IP位址就是從IP位址)；

(5) 備份資料庫（記得加鎖和解鎖）；

(6) 傳送備份資料到Slave上；

(7) 啟動資料庫；

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

(1) 登入資料庫，指定Master的位址、使用者、密碼等資訊（此步僅雙向主從時需要）；

(2) 開啟同步，檢視狀态；

2、Slave上的配置

(2) 修改資料庫配置檔案，指明server_id（如果是搭建雙向主從的話，也要開啟二進制日志log-bin）；

(3) 啟動資料庫，還原備份；

(4) 檢視目前是哪個日志，position号是多少（單向主從此步不需要，雙向主從需要）；

(5) 指定Master的位址、使用者、密碼等資訊；

(6) 開啟同步，檢視狀态。

單向主從環境（也稱MySQL A/B複制）的搭建

1、Master（192.168.1.205）和Slave（192.168.1.206）上都安裝了相同版本的資料庫（mysql-5.6.26.tar.gz），參考《高可用架構篇--第13節--MySQL源碼編譯安裝（CentOS6.6+MySQL5.6）》。

注意：兩台資料庫伺服器的的selinux都要disable（永久關閉selinux，請修改/etc/selinux/config，将SELINUX改為disabled）

2、修改Master的配置檔案/etc/my.cnf

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

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

## 設定server_id，一般設定為IP

server_id=205

## 複制過濾：需要備份的資料庫，輸出binlog

#binlog-do-db=roncoo

## 複制過濾：不需要備份的資料庫，不輸出（mysql庫一般不同步）

binlog-ignore-db=mysql

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

log-bin=edu-mysql-bin

## 為每個session 配置設定的記憶體，在事務過程中用來存儲二進制日志的緩存

binlog_cache_size=1M

## 主從複制的格式（mixed,statement,row，預設格式是statement）

binlog_format=mixed

## 二進制日志自動删除/過期的天數。預設值為0，表示不自動删除。

expire_logs_days=7

## 跳過主從複制中遇到的所有錯誤或指定類型的錯誤，避免slave端複制中斷。

## 如：1062錯誤是指一些主鍵重複，1032錯誤是因為主從資料庫資料不一緻

slave_skip_errors=1062

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

2.1 複制過濾可以讓你隻複制伺服器中的一部分資料，有兩種複制過濾：

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

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

2.2 MySQL對于二進制日志 (binlog)的複制類型

(1) 基于語句的複制：在Master上執行的SQL語句，在Slave上執行同樣的語句。MySQL預設采用基于語句的複制，效率比較高。一旦發現沒法精确複制時，會自動選着基于行的複制。

(2) 基于行的複制：把改變的内容複制到Slave，而不是把指令在Slave上執行一遍。從MySQL5.0開始支援。

(3) 混合類型的複制：預設采用基于語句的複制，一旦發現基于語句的無法精确的複制時，就會采用基于行的複制。

3、啟動/重新開機Master資料庫服務，登入資料庫，建立資料同步使用者，并授予相應的權限

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

Shutting down MySQL..[ OK ]

Starting MySQL..[ OK ]

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

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.26-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

##建立資料同步使用者，并授予相應的權限

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)

## 重新整理授權表資訊

mysql> flush privileges;

## 檢視position号，記下position号（從機上需要用到這個position号和現在的日志檔案)

mysql> show master status;

4、建立roncoo庫、表，并寫入一定量的資料，用于模拟現有的業務系統資料庫

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,'清風','123456'),(3,'龍果','roncoo.com');

5、為保證Master和Slave的資料一緻，我們采用主備份，從還原來實作初始資料一緻

## 先臨時鎖表

mysql> flush tables with read lock;

## 這裡我們實行全庫備份，在實際中，我們可能隻同步某一個庫，那也可以隻備份一個庫

[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.

[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

## 注意：實際生産環境中大資料量（超2G資料）的備份，建議不要使用mysqldump進行比分，因為會非常慢。此時推薦使用XtraBackup 進行備份。

## 解鎖表

mysql> unlock tables;

将Master上備份的資料遠端傳送到Slave上，以用于Slave配置時恢複資料

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

[email protected]'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

server_id=206

##複制過濾：不需要備份的資料庫，不輸出（mysql庫一般不同步）

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

log-bin=edu-mysql-slave1-bin

binlog_cache_size = 1M

## relay_log配置中繼日志

relay_log=edu-mysql-relay-bin

## log_slave_updates表示slave将複制事件寫進自己的二進制日志

log_slave_updates=1

## 防止改變資料(除了特殊的線程)

read_only=1

如果Slave為其它Slave的Master時，必須設定bin_log。在這裡，我們開啟了二進制日志，而且顯式的命名(預設名稱為hostname，但是，如果hostname改變則會出現問題)。

relay_log配置中繼日志，log_slave_updates表示slave将複制事件寫進自己的二進制日志。

當設定log_slave_updates時，你可以讓slave扮演其它slave的master。此時，slave把SQL線程執行的事件寫進行自己的二進制日志(binary log)，然後，它的slave可以擷取這些事件并執行它。如下圖所示（發送複制事件到其它Slave）：

7、儲存後重新開機MySQL服務，還原備份資料

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

Slave上建立相同庫：

導入資料

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

[root@edu-mysql-02 ~]#

8、登入Slave資料庫，添加相關參數

（Master的IP、端口、同步使用者、密碼、position号、讀取哪個日志檔案)

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

Your MySQL connection id is 3

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)

上面執行的指令的解釋：

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

master_user='repl' ## 用于同步資料的使用者（在Master中授權的使用者）

master_password='roncoo.123'## 同步資料使用者的密碼

master_port=3306## Master資料庫服務的端口

master_log_file='edu-mysql-bin.000001'##指定Slave從哪個日志檔案開始讀複制資料（可在Master上使用show master status檢視到日志檔案名）

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

master_connect_retry=30##當重建立立主從連接配接時，如果連接配接建立失敗，間隔多久後重試。機關為秒，預設設定為60秒，同步延遲調優參數。

## 檢視主從同步狀态

mysql> show slave status\G;

可看到Slave_IO_State為空， Slave_IO_Running和Slave_SQL_Running是No，表明Slave還沒有開始複制過程。

## 開啟主從同步

mysql> start slave;

## 再檢視主從同步狀态

主要看以下兩個參數，這兩個參數如果是Yes就表示主從同步正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

由截圖中的主從同步狀态資訊可以看出，我們配置的主從同步是正常的。

可檢視master和slave上線程的狀态。在master上，可以看到slave的I/O線程建立的連接配接：

Master : mysql> show processlist\G;

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

2.row 為處理MySQL用戶端連接配接線程。

3.row 為處理本地指令行的線程。

Slave : mysql> show processlist\G;

1.row為I/O線程狀态。

2.row 為SQL線程狀态。

9、主從資料複制同步測試

(1) 在Master中的roncoo庫上變更資料的同步測試；

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

Master中添加完之後，登入Slave中檢視資料是否已同步。

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

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

在Slave中檢視資料庫

mysql> show databases;

最終的測試結果是，在Master中的操作，都成功同步到了Slave。

10、測試過程中，如果遇到同步出錯，可在Slave上重置主從複制設定（選操作）：

(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中手動插入資料，那樣資料就會不一緻，主從就會斷開，就需要重新配置了。

11、上面所搭建的是單向複制的主從，也是用的比較多的，而雙向主從其實就是Master和Slave都開啟日志功能，然後在Master執行授權使用者（這裡授權的是自己作為從伺服器，也就是這裡的IP位址是Master的IP位址），然後再在Master上進行chang master操作。

MySQL主從資料同步延遲問題的調優

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

(1) 網絡延遲；

(2) Master負載過高；

(3) Slave負載過高；

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

兩個可以減少主從複制延遲的參數（按需配置）：

MySQL可以指定3個參數，用于複制線程重連主庫：--master-retry-count，--master-connect-retry，--slave-net-timeout 。其中master-connect-retry 和 master-retry-count 需要在 Change Master 搭建主備複制時指定，而 slave-net-timeout 是一個全局變量，可以在 MySQL 運作時線上設定。具體的重試政策為：備庫過了 slave-net-timeout 秒還沒有收到主庫來的資料，它就會開始第一次重試。然後每過 master-connect-retry 秒，備庫會再次嘗試重連主庫。直到重試了 master-retry-count 次，它才會放棄重試。如果重試的過程中，連上了主庫，那麼它認為目前主庫是好的，又會開始 slave-net-timeout 秒的等待。slave-net-timeout 的預設值是 3600 秒，master-connect-retry 預設為 60 秒，master-retry-count 預設為 86400 次。也就是說，如果主庫一個小時都沒有任何資料變更發送過來，備庫才會嘗試重連主庫。這就是為什麼在我們模拟的場景下，一個小時後，備庫才會重連主庫，繼續同步資料變更的原因。

這樣的話，如果你的主庫上變更比較頻繁，可以考慮将 slave-net-timeout 設定的小一點，避免主庫 Binlog dump 線程終止了，無法将最新的更新推送過來。當然 slave-net-timeout 設定的過小也有問題，這樣會導緻如果主庫的變更确實比較少的時候，備庫頻繁的重新連接配接主庫，造成資源浪費。

slave-net-timeout=seconds

參數說明：當Slave從Master資料庫讀取log資料失敗後，等待多久重建立立連接配接并擷取資料，機關為秒，預設設定為3600秒。

在做MySQL Slave的時候經常會遇到很多錯誤，需要根據具體原因跨過錯誤繼續同步，但有時候是因為網絡不穩定、網絡閃斷造成同步不正常，如果Slave機器非常多的情況下，一個一個登入伺服器去stop slave、start slave變得無聊而且重複。從MySQL5.1開始支援的解決方案配置：

master-connect-retry=seconds

參數說明：在主伺服器當機或連接配接丢失的情況下，從伺服器線程重新嘗試連接配接主伺服器之前睡眠的秒數。如果主伺服器.info檔案中的值可以讀取則優先使用。如果未設定，預設值為60。

通常配置以上2個參數可以減少網絡問題導緻的主從資料同步延遲。

一般網絡問題的錯誤是：

[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