mysql主從伺服器雙向配置（mysql 雙主方案）

本文目錄一覽：

• 1、如何配置兩個MySQL資料庫之間的主從同步功能？
• 2、mysql主從原理如何配置
• 3、MySQL的主從配置步驟你會那幾個？我和大家分享下我的幾個經驗
• 4、基於MySQL雙主的高可用解決方案理論及實踐
• 5、如何在一台windows主機上搭建mysql主從配置

如何配置兩個MySQL資料庫之間的主從同步功能？

IP的設置：A主機 IP：10.10.0.119；Mask：255.255.0.0；B主機 IP：10.10.8.112；Mask：255.255.0.0

在IP設置完成以後，需要確定兩主機的防火牆確實已經關閉。可以使用命令service iptables status查看防火牆狀態。如果防火牆狀態。

為仍在運行。使用service iptables stop來停用防火牆。如果想啟動關閉防火牆，可以使用setup命令來禁用或定製。最終以兩台主機可以相互ping通為佳。

3.2 配置A主(master) B從(slave)模式；3.2.1 配置A 為master。

增加一個用戶同步使用的帳號：

GRANT FILE ON *.* TO 『backup』@’10.10.8.112′ IDENTIFIED BY 『1234』;

GRANTREPLICATION SLAVE ON *.* TO 『backup』@’10.10.8.112′ IDENTIFIED BY 『1234』。

賦予10.10.8.112也就是Slave機器有File許可權，只賦予Slave機器有File許可權還不行，還要給它REPLICATION SLAVE的許可權才可以。

增加一個資料庫作為同步資料庫：create database test;

創建一個表結構：create table mytest (username varchar(20),password varchar(20));

修改配置文件：修改A的/etc/my.cnf文件。

在my.cnf配置項中加入下面配置：

server-id = 1 #Server標識

log-bin

binlog-do-db=test #指定需要日誌的資料庫

重起資料庫服務：

service mysqld restart

查看server-id：

show variable like 『server_id』。

mysql主從原理如何配置

1.在主資料庫伺服器為從伺服器添加一個擁有許可權訪問主庫的用戶:

GRANT REPLICATION SLAVE ON *.* TO ‘ test’@’%’ IDENTIFIED BY ‘test’;

(%表示允許所有IP，可設置指定從伺服器IP)

添加用戶後:

可在從伺服器上用mysql -h127.0.0.1 -utest -ptest; 來測試是否有許可權訪問主資料庫

2.在主據庫配置文件加上:

#master config

server-id = 1

log-bin = mysql-bin

3.在從伺服器資料庫配置文件：

server-id = 2

master-host = 10.0.0.199

master-user = test

master-password = test

replicate-do-db = test

master-port = 3306

log-bin = mysql-bin

如果你的一切配置順利

你在從伺服器上輸入命令：show slave status\G

成功情況：

Slave_IO_Running:yes

Slave_SQL_Running:yes

在主伺服器上輸入show master status

MySQL的主從配置步驟你會那幾個？我和大家分享下我的幾個經驗

一、登錄Master伺服器,修改my.ini

,添加如下內容：[*]#資料庫ID號，

為1時表示為Master,其中master_id必須為1到232–1之間的一個正整數值;[*]server-id

=

1[*]#啟用二進位日誌；[*]log-bin=mysql-bin[*]#需要同步的二進位資料庫名；[*]binlog-do-db=ultrax[*]#不同步的二進位資料庫名,如果不設置可以將其注釋掉;[*]binlog-ignore-db=mysql[*]#設定生成的log文件名；[*]log-bin=”E:/Database/materlog”[*]#把更新的記錄寫到二進位文件中；[*]log-slave-updates[*]#跳過錯誤，繼續執行複製；[*]slave-skip-errors配置完重啟

mysql

基於MySQL雙主的高可用解決方案理論及實踐

MySQL在互聯網應用中已經遍地開花，但是在銀行系統中，還在生根發芽的階段。本文記錄的是根據某生產系統實際需求，對資料庫高可用方案從需求、各高可用技術特點對比、實施、測試等過程進行整理，完善Mysql高可用方案，同時為後續開展分散式資料庫相關測試做相應準備。

存儲複製技術： 傳統IOE架構下，常用高可用方案，靠存儲底層複製技術實現數據的一致性，優點數據安全性有保障，限制在於是依賴存儲硬體，實施成本較高。

keepalived+雙主複製： 兩台MySQL互為主從關係，即雙主模式，通過Keepalived配置虛擬IP，實現當其中的一台資料庫故障時，自動切換VIP到另外一台MySQL資料庫，備機快速接管業務來保證資料庫的高可用。

MHA： MHA部署在每台mysql伺服器上，定時探測集群中的master節點，當master出現故障時，它可以自動將最新的slave提升為新的master，然後將所有其他的slave重新指向新的master，優點在最大程度保證數據的一致性的前提下實現快速切換，最少需要3台伺服器，存在數據丟失的可能性。

PXC： Percona eXtra Cluster是Percona基於galera cluster封裝的集群方案。不同於普通多主複製，PXC保障強一致性和實時同步，故障切換更快。但是也需要3個節點，配置相對複雜，對性能也稍有影響。

除了上述方案外，還有MMM、Heartbeat+DRBD等高可用方案，此處不做詳細介紹。

綜合評估下，本次實施採用了 keepalived+mysql雙主實現資料庫同城雙機房的高可用。MySQL版本為： 5.7.21。操作系統：Red Hat Enterprise Linux Server 7.3。

配置過程如下：

Mysql-master1: IP地址1 –以下簡稱master1

Mysql-master2: IP地址2 –以下簡稱master2

Mysql-vip : VIP地址 –應用連接使用

Mysql複製相關概念描述：

1、 Mysql主從複製圖示：

2、 Mysql主從複製過程描述：

（1）master記錄二進位日誌：在每個事務更新數據完成之前，master在二進位日誌記錄這些改變。MySQL將事務寫入二進位日誌。在事務寫入二進位日誌完成後，master通知存儲引擎提交事務。

（2）slave將master的binarylog拷貝到自己的中繼日誌：首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然後開始binlog dump process。Binlog dump process從master的二進位日誌中讀取事務，如果已經同步了master，它會睡眠並等待master產生新的事件。I/O線程將這些事務寫入中繼日誌。

（3）SQL slave thread處理該過程的最後一步：SQL線程從中繼日誌讀取事務，並重放其中的事務而更新slave的數據，使其與master中的數據一致。只要該線程與I/O線程保持一致，中繼日誌通常會位於OS的緩存中，所以中繼日誌的開銷很小。

主主同步就是兩台機器互為主的關係，在任何一台機器上寫入都會同步至備端。

為了便於後續資料庫伺服器的擴展，且在整個複製環境中能夠自動地切換，降低運維成本，引入了當前主流的基於Mysql GTID的複製特性，工作原理及優缺點簡介如下。

3、 GTID工作原理簡介：

（1） master更新數據時，會在事務前產生GTID，一同記錄到Binlog日誌中。

（2） slave的I/O線程將變更的binlog寫入到本地的relay log中。

（3） slave的sql線程從relay log中獲取GTID，然後對比slave端的binlog是否有記錄。

（4） 如果有記錄說明該GTID的事務已經執行，slave會忽略。

（5） 如果沒有記錄，slave就會從relay log中執行該GTID的事務，並記錄到binlog。

（6） 在解析的過程中會判斷是否有主鍵，如果有就用索引，如果沒有就用全部掃描。

4、 GTID優點：

（1） 一個事務對應一個唯一的ID，一個GTID在一個伺服器上 只會執行一次。（2） GTID是用來替代傳統複製的方法，GTID複製與普通複製模式的最大不同就是不需要指定二進位文件名和位置。

（3） 減少手工干預和降低服務故障時間，當主機宕機之後會通過軟體從眾多的備機中提升一台備機為新的master。

5、 GTID也存在一些限制：

（1） 不支持非事務引擎。

（2） 不支持create table … select 語句複製(主庫直接報錯)。

（3） 不允許一個sql同時更新一個事務引擎表和非事務引擎表。

（4） 在一個複製組中，必須要求統一開啟GTID或者是統一關閉GTID。

（5） 開啟GTID需要重啟(5.7版本除外)。

（6） 開啟GTID後，就不再使用原理的傳統複製方式。

（7） 不支持create temporary table 和 drop temporary table語句。

（8） 不支持sql_slave_skip_counter。

前置條件：

主備兩個節點使用行內統一的安裝部署腳本安裝mysql5.7.21介質(略)

Master1端創建應用的資料庫(略)

1、 修改MySQL配置文件

參考相關配置規範，分別設置master1、master2的my.cnf文件，

其中server-id參數設置為不同值;

由於後續keepalived會掛起VIP，應用通過VIP連接資料庫，為了避免應用程序無法通過VIP訪問，需將兩個節點的bind-address參數注釋掉；

2、 設置master1端自動半同步模式

Mysql的同步模式主要有如下3種：

a. 主從同步複製：數據完整性好，但是性能消耗略高；

b. 主從非同步複製：性能消耗低，但容易出現不一致；

c. 主從半自動複製：介於上述兩種之間，既保持了數據的完整性，又提高了性能；

基於上述特性，建議採用半自動同步模式，由於後續要配置為雙主模式，因此任一節點其角色既為master又為slave，因此相關的master/slave插件要同時配置，過程如下。

（1） 首先查看庫是否支持動態載入(默認都支持)

（2） 主從庫上分別安裝插件

作為主庫，安裝插件semisync_master.so

作為從庫，安裝插件semisync_slave.so

（3） 安裝完成後，從plugin表中能夠看到剛剛安裝的插件

（4） 分別打開主從庫半同步複製

同時添加到各自的my.cnf中，在後續資料庫實例重啟時自動載入該配置。

此時查看狀態還沒有啟動

（5） 兩個節點分別啟動IO進程

（6） 查看半同步狀態

3、 將master1設為master2的主伺服器

（1）在master1主機上創建授權賬戶，允許在master2主機上連接

（2）將主庫master1數據導出

（3）將master.sql傳輸到master2上並導入

（4）在master2端將master1設置為自己的主庫，並開啟slave功能

在master2上查看slave狀態

至此master1到master2的主從複製關係已經建立完成。

4、 將master2設為master1的主伺服器

在master1上執行

在master1上查看slave狀態

1、keepalived相關概念說明：

keepalived是集群管理中保證集群高可用的一個軟體解決方案，其功能類似於heartbeat，用來防止單點故障

keepalived是以VRRP協議為實現基礎的，VRRP全稱VirtualRouter Redundancy Protocol，即虛擬路由冗餘協議。

虛擬路由冗餘協議，可以認為是實現路由器高可用的協議，即將N台提供相同功能的路由器組成一個路由器組，這個組裡面有一個master和多個backup，master上面有一個對外提供服務的vip，master會發組播（組播地址為224.0.0.18），當backup收不到vrrp包時就認為master宕掉了，這時就需要根據VRRP的優先順序來選舉一個backup當master，這樣的話就可以保證路由器的高可用了。

keepalived主要有三個模塊，分別是core 、check和vrrp。core模塊為keepalived的核心，負責主進程的啟動、維護以及全局配置文件的載入和解析。check負責 健康 檢查，包括常見的各種檢查方式。vrrp模塊是來實現VRRP協議的。同時為了避免出現腦裂，應關閉防火牆或者開啟防火牆但允許接收VRRP協議。

2、keepalived的安裝配置

（1）配置本地yum源，在master1和master2兩台伺服器上安裝keepalived的相關依賴包Kernel-devel/openssl-devel/popt-devl等

配置指向rhel-7.5.iso的yum本地源，步驟略

注意：如不知道keepalived需要哪些依賴包，可到下載後的源碼解壓目錄下查看INSTALL 文件內容，安裝需要的依賴包，源碼安裝任何一個軟體都要養成查看源碼包文檔的習慣，比如INSTALL，README,doc等文檔，可以獲得很多有用的信息。

（2）在兩台mysql上解壓縮並編譯安裝keepalived

（3）master1、master2上分別配置keepalived.conf

注意上圖紅色字體中兩個節點配置相同處及差異。

說明：keepalived只有一個配置文件keepalived.conf，裡面主要包括以下幾個配置區域：

· global_defs：主要是配置故障發生時的通知對象以及機器標識。

· vrrp_instance：用來定義對外提供服務的VIP區域及其相關屬性。

· virtual_server：虛擬伺服器定義

（4）同時兩個節點上都需要添加檢測腳本

作用：是當mysql停止工作時自動關閉本機的keeplived服務，從而實現將故障主機踢出熱備組，因每台機器上keepalived只添加了本機為realserver，所以當mysqld正常啟動後，我們還需要手動啟動keepalived服務。

（5）分別啟動兩個節點的keepalived服務

檢查兩個節點keepalived啟動進程

檢查兩個節點的vip掛載情況

（6）主備機故障切換測試

停止master2的mysql服務，看keepalived 健康 檢查程序是否會觸髮腳本，自動進行故障切換，步驟略

查看master1節點的VIP掛載情況，驗證是否實現了自動切換，步驟略

說明在master2伺服器的mysql服務發生故障時，觸發了腳本，自動完成了切換。

（7）現在我們把master2的mysql服務開起來，並且keepalived的服務也需要啟動。

即便master2的mysql服務和keepalived服務都重新開啟了，master1仍然是主master了，master2未對主master的權利進行搶奪，說明設置的nopreempt參數生效了，為了保證群集的穩定性，生產環境不允許搶佔配置，只有當master1的mysql服務壞掉的時候，master2才會再次成為主master，否則它永遠只能當master1的備份。（註：nopreempt一般是在優先順序高的mysql上設置）

Sysbench是一個模塊化的、跨平台、多線程基準測試工具，可用於評估資料庫負載情況，通過sysbench命令配置IP地址、埠號、用戶名、密碼連接到指定的資料庫db1中，創建多個表，並快速插入指定條數的記錄，觀察主備庫同步效率

(1) 下載開源工具sysbench-0.4.12.14.tar.gz，放置在相應目錄下並解壓

(2) 使用iso配置本地yum源並安裝Sysbench如下的依賴包(步驟略)：autoconf/automake/cdbs/debhelper(=9)/docbook-xml/docbook-xsl/libmysqlclient15-dev/libtool/xsltproc

(3) 編譯sysbench

編輯配置文件/etc/ld.so.conf中添加mysql lib目錄/mysql/app/5.7.21/lib，並執行命令ldconfig生效

(4) 執行sysbench壓測

使用sysbench工具向主節點的db1資料庫中創建5張表，並且每張表分別插入10萬條記錄

同時觀察備機同步效率

幾個重要的參數說明：

B、半自動同步模式、非同步模式切換測試

(1) 檢查主備同步狀態，及同步參數設置

rpl_semi_sync_master_enabled參數表示啟用半同步模式；

rpl_semi_sync_master_timeout參數單位為毫秒，表示主庫事務等待從庫返回commit成功信息超過10秒就降為非同步模式，不再等待從庫，等探測到從庫io線程恢復後，再返回為半自動同步；

rpl_semi_sync_master_wait_no_slave參數表示事務提交後需要等待從庫返回確認信息；

(2) 將slave的io線程停止

(3) 使用sysbench向master寫入少量的數據，本例創建一張表，並插入10條記錄，命令包裝在1.sh測試腳本中

通過記錄的時間戳發現，master在等待了slave10秒無響應，自動切換為非同步模式，將數據寫入本地。

(4) Slave啟動io線程，數據自動追平

至此MySQL主主複製配置完成，運行在半自動同步模式，通過keepalived實現Mysql的HA高可用。

上線後應符合統一的標準監控策略，添加備份協議對數據進行周期備份並保存到帶庫中，以及定期的數據恢複測試。

由於是靠keepalived實現的高可用，還應將如下資源添加到監控管理平台：

1、 對每台資料庫主機的3個keepalived進程進行監控；

2、 對主備節點的io線程、sql線程工作狀態進行監控；

如何在一台windows主機上搭建mysql主從配置

1、首先要在本地建立兩個mysql服務（參考這裡），指定不同的埠。我這裡一個主（3306），一個從（3307）。2、然後修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步的資料庫

#binlog-ignore-db=mysql #不同步的資料庫,如果指定了binlog-do-db這裡應該可以不用指定的

log-bin=mysql-bin #要生成的二進位日記文件名稱

修改從配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、在主庫添加一個用戶 repl 並指定replication許可權

create user ‘repl’@’127.0.0.1’ identified by ‘asdf’;

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’127.0.0.1’; — –這裡我指定資料庫（test.*）時報錯，而指定全庫（*.*）時會成功。

4、保持主從mysql的test資料庫初始狀態一致。

一般是先將所有的表加讀鎖，然後copy磁碟上的資料庫文件夾。我這裡直接停止服務，然後將數據文件拷貝過去。

5、在主資料庫裡面運行show master status;記下file和position欄位對應的參數。

mysql show master status;