mysql主從同步，半同步，組複制，MHA高可用配置及讀寫分離

一 資料庫安裝：

1 安裝資料庫

資料包下載下傳位址：

連結：https://pan.baidu.com/s/16CBO11HBf3Pl4eGEGg5vNw

密碼：3nzg

下載下傳所需的安裝包并進行解壓：

安裝相關資料包

将其發送至用戶端主機

用戶端檢視并安裝：

啟動master 資料庫并初始化密碼和修改密碼

其密碼預設在/var/log/mysqld.log中生成

初始化密碼

啟動伺服器并安裝是否修改成功

從庫檢視密碼位置并進行相關的初始化

檢視密碼是否修改成功

二 配置mysql資料庫的主從同步

1 主從同步原理：

Replication 線程：

Mysql的 Replication 是一個異步的複制過程（mysql5.1.7以上版本分為異步複制和半同步兩種模式），從一個 Mysql instace(我們稱之為 Master)複制到另一個 Mysql instance(我們稱之 Slave)。在 Master 與 Slave 之間的實作整個複制過程主要由三個線程來完成，其中兩個線程(Sql線程和IO線程)在 Slave 端，另外一個線程(IO線程)在 Master 端。

要實作 MySQL 的 Replication ，首先必須打開 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否則無法實作。因為整個複制過程實際上就是Slave從Master端擷取該日志然後再在自己身上完全 順序的執行日志中所記錄的各種操作。打開 MySQL 的 Binary Log 可以通過在啟動 MySQL Server 的過程中使用 “—log-bin” 參數選項，或者在 my.cnf 配置檔案中的 mysqld 參數組([mysqld]辨別後的參數部分)增加 “log-bin” 參數項。

2 主從同步的兩個線程及其功能

（1）sql_thread：負責從中繼日志讀取内容，然後replay到資料庫中；

（2）io_thread：負責連接配接主伺服器，把主伺服器的二進制日志複制到自己的中繼日志；

3 主從同步的方式

mysql支援單向、異步複制，複制過程中一個伺服器充當主伺服器，而一個或多個其他伺服器充當從伺服器。主伺服器将更新寫入二進制日志檔案，并維護檔案的一個索引以跟蹤日志循環，這些日志可以記錄發送到從伺服器的更新，當一個從伺服器連結主伺服器時，他通知主伺服器從伺服器在日志中讀取的最後一次成功更新的位置，從伺服器接受從那時起發生的任何更新，然後封鎖并等待主伺服器通知新的更新。

複制優點：

1 主伺服器/從伺服器設定增加了健壯性，主伺服器出現故障時，你可以切換到從伺服器作為備份

2 通過主伺服器和從伺服器之間切分處理客戶查詢的負荷，可以更好的提高用戶端的響應時間

3 從伺服器執行複制，不會影響主伺服器的更新和讀寫操作。

4 mysql的AB 複制

mysql資料庫的版本，要麼主庫比從庫版本低，要麼相同。

1 基本server-id及log-bin配置

1 主動端配置：

參數解析：

1 log-bin ：啟動二進制日志系統

2 server-id: 必須是1-2^32-1 之間的整數

重新開機伺服器

從庫端配置

2 權限相關配置：

1 檢視主庫相關情況，從庫連結主庫便從此開始，并配置相應的使用者和密碼及權限

從庫邊連結資料庫操作，并啟用從庫功能

參數詳解：

master_host=主伺服器IP

master_user=在主伺服器上建立的備份使用者名

master_password=備份使用者密碼

master_log_file=查詢master(主伺服器)的狀态得到的File列的值

master_log_pos=Position列的值。其值必須與主庫所對應的值相同

start slave：啟動從伺服器複制功能

檢視其是否連接配接成功

3 驗證主從同步：

檢視主庫資料庫情況并建立資料庫python

從庫邊檢視：

主庫配置資料表并插入資料：

從庫端檢視是否成功

主庫删除資料

從庫檢視是否操作成功

相關檔案作用：

1. mysql-bin.index：伺服器一旦開啟二進制日志，會産生一個與二日志檔案同名，但是以.index結尾的檔案。它用于跟蹤磁盤上存在哪些二進制日志檔案。MySQL用它來定位二進制日志檔案。
2. mysqld-relay-bin.index：該檔案的功能與mysql-bin.index類似，但是它是針對中繼日志，而不是二進制日志。
3. master.info：儲存master的相關資訊。不要删除它，否則，slave重新開機後不能連接配接master。
4. relay-log.info：包含slave中目前二進制日志和中繼日志的資訊。

   5 配置GTID 的主從同步：

   一、GTID的概述：

   GTID=UUID+傳輸ID

   1、全局事物辨別：global transaction identifieds。

   2、GTID事物是全局唯一性的，且一個事務對應一個GTID。

   3、一個GTID在一個伺服器上隻執行一次，避免重複執行導緻資料混亂或者主從不一緻。

   4、GTID用來代替classic的複制方法，不在使用binlog+pos開啟複制。而是使用master_auto_postion=1的方式自動比對GTID斷點進行複制。

   5、MySQL-5.6.5開始支援的，MySQL-5.6.10後開始完善。

   6、在傳統的slave端，binlog是不用開啟的，但是在GTID中，slave端的binlog是必須開啟的，目的是記錄執行過的GTID（強制）

   二、GTID的組成部分：

   前面是server_uuid：後面是一個序列号

   例如：server_uuid：sequence number

   7800a22c-95ae-11e4-983d-080027de205a:10

   UUID：每個mysql執行個體的唯一ID，由于會傳遞到slave，是以也可以了解為源ID。

   Sequence number：在每台MySQL伺服器上都是從1開始自增長的序列，一個數值對應一個事務

   三、GTID比傳統複制的優勢：

   1、更簡單的實作failover，不用以前那樣在需要找log_file和log_Pos。

   2、更簡單的搭建主從複制。

   3、比傳統複制更加安全。

   4、GTID是連續沒有空洞的，是以主從庫出現資料沖突時，可以用添加空事物的方式進行跳過。

   四、GTID的工作原理：

   1、master更新資料時，會在事務前産生GTID，一同記錄到binlog日志中。

   2、slave端的i/o 線程将變更的binlog，寫入到本地的relay log中。

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

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

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

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

   要點：

   1、slave在接受master的binlog時，會校驗master的GTID是否已經執行過（一個伺服器隻能執行一次）。

   2、為了保證主從資料的一緻性，多線程隻能同時執行一個GTID。

   五 配置

   1 主庫端配置

   

   

   重新開機服務

   

   2 從庫端配置：

   

   

   

   關閉原先的主從同步連結并建立基于GTID的主從同步連結

   

   檢視是否連接配接成功

   

   3 檢視相關的二進制日志指令

   

   4 測試

   主庫插入資料

   

   從庫檢視

   

   檢視主庫日志情況，通過後端的數字來确定其同步是否完成，如果和主庫發送的資料相同，則表明從庫同步完成

   

   檢視從庫的有關同步字段内容

   

   6 配置線程增加（針對的是IO_thread）

   （均在slave端配置）：線程增加配置與将資料由檔案存儲轉換為表存儲的配置

   檢視原配置線程情況

   當配置了多線程之後，原本的單線程将用于處理與伺服器master之間的資訊操作，而不是用于同步複制。

   

   配置多線程處理和将同步日志的儲存由檔案轉義到表的配置，将原本對磁盤的操作優化為對記憶體的操作，性能有所提升

   

   slave_parallel_workers=16

   #并行的SQL線程數量，此參數隻有設定 1<N的情況下才會才起N個線程進行SQL重做。

   #經過測試對比發現， 如果主庫的連接配接線程為M， 隻有M < N的情況下， 備庫的延遲才可以完全避免。#否則，延遲一樣的會存在，但是畢竟SQL重做線程從原來的一個，更新到現在的N個， 這個延遲在一定的主庫TPS下會很短！

   master_info_repository=TABLE

   relay_log_info_repository=TABLE

   這兩個參數會将master.info和relay.info儲存在表中，預設是Myisam引擎，官方建議改為Innodb引擎，防止表損壞後自行修複。

   relay_log_recovery=ON

   #當slave從庫當機後，假如relay-log損壞了，導緻一部分中繼日志沒有處理，則自動放棄所有未執行的relay-log，并且重新從master上擷取日志，這#樣就保證了relay-log的完整性。預設情況下該功能是關閉的，将relay_log_recovery的值設定為 1時，可在slave從庫上開啟該功能，建議開啟。

重新開機用戶端并檢視MySQL庫中增加了記錄同步日志的GTID 的表

檢視此表情況

檢視線程數是否增加，增加15個：

7 半同步配置：

1 mysql 5.7 與 5.6 半同步比較

mysql 5.6 是當master送出事務之前，會向slave端發送binlog并送出事務，等到slave端傳回成功後再傳回給用戶端，

mysql 5.7 是在master送出事務之前，會向slave端發送binlog 資訊，并等待用戶端傳回成功後送出事務，并傳回給用戶端。

mysql5.7 的可靠性更高

2 主庫端加載master子產品：

3 從庫端配置：

主庫端檢視配置情況

其中rpl_semi_sync_master_enabled是控制Master是否開啟半同步，開啟或不開啟,将其設

置為ON或OFF(1or0).

rpl_semi_sync_master_timeout是控制Master等待多長時間被告知Slave已收到，也就是所

謂的逾時時間。

rpl_semi_sync_slave_enabled是控制Slave是否開啟半同步，開啟或不開啟，将其設定為ON

或OFF(1or0)。

其master處于開啟狀态。發現了slave

監控半同步複制的狀态變量（幾個常用的）：

Rpl_semi_sync_master_clients：檢視有多少個開啟半同步複制的插件的Slave

Rpl_semi_sync_master_status：檢視在Master上半同步複制是否正在運作，其值為ON時，

說明Master已啟用半同步且已被告知有Slave收到；其值為OFF時，說明Master沒啟用半同步

或是沒被告知，由于timeout等原因。

Rpl_semi_sync_master_no_tx：檢視有多少事務沒有用半同步複制的機制進行複制。

Rpl_semi_sync_master_yes_tx：檢視有多少事務是通過半同步複制機制成功複制。

Rpl_semi_sync_slave_status：檢視Slave上半同步複制是否正常運作，其值為ON時，說明

Slave正通過半同步複制且Slave I/O正在運作；為OFF時，反之。

重新開機io_thread使用相同步驟配置從節點，完成後需要重新開機io_thread，不重新開機當執行時會超

時，逾時後則自動降為異步：

8 資料庫的級聯配置：

同步方式，使用半同步的方式進行

server1---->server2(是server1 的從，也是server3的主)------>server3

架構圖：

server1 端配置：

用于使得加載子產品的永久性

2 server2

需要加載master子產品并激活，并且需要日志log-bin檔案，因為其是主庫

3 server3 配置

1 加載slave 子產品。

2 建立資料庫并将server2的相關配置導入

3 重新開機服務

4 将GTID等相關參數寫入配置檔案：

5 重新開機服務加載子產品

6 進行連接配接server2 資料庫：

4 檢視配置：

server2

server3

5 測試。

此時必須在server1 端進行測試：

8 mysql資料庫的讀寫分離：

環境server1 為server2的主伺服器.server3 為排程器，用于向用戶端提供一個API接口，用于調用mysql資料庫

1 基礎準備：

關閉server3的資料庫并設定為開機不啟動：

server1端配置權限，用于mysql資料庫的遠端登入：

2 安裝第三方軟體

解壓配置并将其名稱改為mysql-proxy

啟動服務：

3 建立配置檔案和日志檔案目錄：

建立并配置配置檔案：

--proxy-address=host:port ———— 代理服務監聽的位址和端口

--admin-address=host:port ———— 管理子產品監聽的位址和端口

--proxy-backend-addresses=host:port ———— 後端mysql伺服器的位址和端口（主伺服器）

--proxy-read-only-backend-addresses=host:port ———— 後端隻讀mysql伺服器的位址和端口（從伺服器）

--proxy-lua-script=file ———— 完成mysql代理功能的Lua腳本

--daemon ———— 以守護程序模式啟動mysql-proxy

--defaults-file=/path/to/conf_file_name ———— 預設使用的配置檔案路徑

--log-file=/path/to/log_file_name ———— 日志檔案名稱

--log-level=level ———— 日志級别

--log-use-syslog ———— 基于syslog記錄日志

--user=user_name ———— 運作mysql-proxy程序的使用者

修改其讀寫分離規則：

其表示為至少連結1次和至多連結2 次就切換到從庫。

授其執行權限權：

安裝檢視連結的軟體：lsof

4 啟動并檢視服務是否成功

5 遠端連結測試：

9 mysql的組複制：

1 組複制限制：

存儲引擎必須為Innodb

每個表必須提供主鍵

隻支援ipv4，網絡需求較高

一個組最多隻能有9台伺服器

不支援 Replication event checksums，

不支援 Savepoints

多主模式不支援SERIALIZABLE事務隔離級别

多主模式不能完全支援級聯外鍵限制

多主模式不支援在不同節點上對同一個資料庫對象并發執行DDL(在不同節點上對同一行并發進行RW事務，後發起的事務會失敗)

2 複制結構圖

使用場景：

1 彈性複制

2 高可用分片

3 代替主從複制

4 自動系統

3 檔案配置：my.cnf

server 1 端配置

1 transaction_write_set_extraction=XXHASH64

server 必須為每個事物收集寫集合，并使用XXHASH64 雜湊演算法将其編碼為散列

2 loose-group_replication_group_name

告知插件，正在加入或建立的組要命名為後端的内容

3 loose-group_relication_start_on_boot=off

訓示插件在server啟動時不自動啟動組複制

4 loose-group——replication_local_address=“”

告知插件本節點使用的IP位址

5 loose-group_replication_bootstrap_group=off

告知插件，當下面這些server需要加入組時，應該連結到這些主機和端口上通路他們。此選項不是列出組中的所有成員，而是當次server需要加入組時需要通路的server清單

6 loose-group_replication_single_primary_mode=FALSE

本次搭建的是mutil_mode

server 2 端配置

server 3 端配置

4 重新開機服務使其加載配置

5 mysql内部配置

1 關閉二進制日志。使其不被記錄到日志中

set sql_log_bin=0;

2 建立複制使用者與授權

grant replication slave on . to admin@'%' identified by 'ADMIN@root1';

3 重新整理授權

flush privileges;

4 開啟二進制日志記錄功能

set sql_log_bin=1;

5 設定組相關資訊

change master to master_user='admin',master_password='ADMIN@root1' for channel 'group_replication_recovery';

6 安裝GR 插件

install plugin group_replication soname 'group_replication.so';

7 開啟master啟動後組複制自動進行

set global group_replication_bootstrap_group=ON;

8 設定發現方式

set global group_replication_ip_whitelist="172.25.254.0/24";

Query OK, 0 rows affected (0.00 sec)

9 啟動組：

start group_replication;

10 關閉master啟動後組複制自動進行

set global group_replication_bootstrap_group=OFF;

11 檢視是否成功

select * from performance_schema.replication_group_members;

server 2 端配置：

相容加入組

set global group_replication_allow_local_disjoint_gtids_join=ON;

server 3 端配置：

6 測試

其建立的資料表應該有主鍵，不能重複。

10 mysql MHA 配置：

server1 為server2 和server3 的master 但server2 可以作為server1 的slave 也可以在server1down之後作為server3的主裝置，server3 不具備做主master的權利。

server3 上有排程器

1 介紹：

MHA，即MasterHigh Availability Manager and Toolsfor MySQL，是日本的一位MySQL專家

采用Perl語言編寫的一個腳本管理工具，

該工具僅适用于MySQLReplication 環境，目的在于維持Master主庫的高可用性。

MHA(Master High Availability)是自動的master故障轉移和Slave提升的軟體包.

它是基于标準的MySQL複制(異步/半同步).

2 MHA組成部分：

MHA由兩部分組成：

MHA Manager(管理節點)

MHA Node(資料節點)

3 MHA部署解讀：

MHA Manager可以單獨部署在一台獨立機器上管理多個master-slave叢集,也可以部署在一

台slave上.

MHA Manager探測叢集的node節點,當發現master出現故障的時候,它可以自動将具有最新數

據的slave提升為新的master,然後将所有其它的slave導向新的master上.整個故障轉移過程

對應用程式是透明的。

MHA node運作在每台MySQL伺服器上(master/slave/manager),它通過監控具備解析和

清理logs功能的腳本來加快故障轉移的。

4 MHA優缺點介紹：

優點：

1. 故障切換時，可以自行判斷哪個從庫與主庫的資料最接近，就切換到上面，可以減少數

   據的丢失，保證資料的一緻性

2. 支援 binlog server，可提高 binlog 傳送效率，進一步減少資料丢失風險。

3. 可以配置 mysql 5.7 的增強半同步，來保證資料的時時同步

   缺點：

4. 自動切換的腳本太簡單了，而且比較老化，建議後期逐漸完善。

5. 搭建 MHA 架構，需要開啟 linux 系統互信協定，是以對于系統安全性來說，是個不小

   的考驗。

   5 原理介紹：

   MHA的目的在于維持MySQL Replication中Master庫的高可用性，其最大特點是可以修複多個

   Slave之間的差異日志，最終使所有Slave保持資料一緻，然後從中選擇一個充當新的Master，并将其它Slave指向它。當master出現故障時，可以通過對比slave之間I/O thread 讀取主庫binlog的position号，選取最接近的slave做為備選主庫（備胎）。其它的從庫可以通過與備選主庫對比生成差異的中繼日志。

   在備選主庫上應用從原來master儲存的binlog，同時将備選主庫提升master。最後在其它slave上應用相應的差異中繼日志并開始從新的master開始複制。

   mysql高可用.txt

   6 MHA工具包功能介紹：

   Manager工具:

   #masterha_check_ssh : 檢查MHA的SSH配置。

   #masterha_check_repl : 檢查MySQL複制。

   #masterha_manager : 啟動MHA。

   #masterha_check_status : 檢測目前MHA運作狀态。

   #masterha_master_monitor : 監測master是否當機。

   #masterha_master_switch : 控制故障轉移(自動或手動)。

   #masterha_conf_host : 添加或删除配置的server資訊。

   7 . Node工具：

   #save_binary_logs : 儲存和複制master的二進制日志。

   #apply_diff_relay_logs : 識别差異的中繼日志事件并應用于其它slave。

   #filter_mysqlbinlog : 去除不必要的ROLLBACK事件(MHA已不再使用這個工具)。

   #purge_relay_logs : 清除中繼日志(不會阻塞SQL線程)。

   1 主從同步的搭建

   server1上配置檔案配置：

   

   server2 上配置：

   

   server3上配置：

   

   server1和server2上開啟半同步主從子產品

   

   

   server3上開啟從同步子產品

   

重新開機server1server2server3 加載配置檔案

配置主從同步：

server2 端

server3 端

2 MHA軟體安裝

必須保證ssh均能實作無密碼登入

1 安裝節點軟體：

2 安裝控制節點軟體：

server1 端進行授權，使得MHA能夠操作mysql。

1 hostname：目标執行個體的主機名或者IP位址，這個參數是強制的，隻能配置在app配置檔案的[server_xxx]段落标記下

2 port：目标執行個體的端口，預設是3306，MHA使用mysql server的IP和port連接配接

3 ssh_user ：ssh方式連結其他主機使用的使用者名

4 candidate_master：從不同的從庫伺服器中，提升一個可靠的機器為新主庫，可以通過在配置檔案中對應的從庫伺服器的配置段下添加candidate_master=1來提升這個從庫被提升為新主庫的優先級

5 no_master：通過在目标伺服器的配置段落設定no_master=1，它永遠也不會變為新主庫，

6 repl_user：在所有slave上執行change master的複制使用者名，這個使用者最好是在主庫上擁有replication slave權限

7 repl_password：repl_user參數指定的使用者的密碼，預設情況下，是目前複制帳号的密碼，如果你運作了online master switch腳本且使用了–orig_master_is_new_slave做線上切換，目前主庫作為了從庫加入新主庫，此時，如果你沒有設定repl_password來指定複制帳号的密碼，目前主庫作為從庫加入新主庫的時候會在change master的時候失敗，因為預設情況下這個密碼是空的，MHA做切換的時候，其他所有作為從庫加入新主庫時，都需要使用這個密碼

8 remote_workdir：每一個MHA node（指的是mysql server節點）生成日志檔案的工作路徑，這個路徑是絕對路徑，如果該路徑目錄不存在，則會自動建立，如果沒有權限通路這個路徑，那麼MHA将終止後續過程，另外，你需要關心一下這個路徑下的檔案系統是否有足夠的磁盤空間，預設值是/var/tmp

9 master_binlog_dir：在master上生成binlog的絕對路徑，這個參數用在master挂了，但是ssh還可達的時候，從主庫的這個路徑下讀取和複制必須的binlog events，這個參數是必須的，因為master的mysqld挂掉之後，沒有辦法自動識别master的binlog存放目錄。預設情況下，master_binlog_dir的值是/var/lib/mysql,/var/log/mysql，/var/lib/mysql目錄是大多數mysql分支預設的binlog輸出目錄，而 /var/log/mysql是ubuntu包的預設binlog輸出目錄，這個參數可以設定多個值，用逗号隔開

10 manager_workdir：mha manager生成的相關狀态檔案的絕對路徑，如果沒有設定，則預設使用/var/tmp

11 manager_log：mha manager生成的日志據對路徑，如果沒有設定，mha manager将列印在标準輸出，标準錯誤輸出上，如：當mha manager執行故障轉移的時候，這些資訊就會列印

12 ping_interval：這個參數表示mha manager多久ping（執行select ping sql語句）一次master，連續三個丢失ping連接配接，mha master就判定mater死了，是以，通過4次ping間隔的最大時間的機制來發現故障，預設是3，表示間隔是3秒

13 master_ip_failover_script：通常在MHA環境下，通常需要配置設定一個VIP供master用于對外提供讀寫服務，如果master挂了，HA軟體指引備用伺服器接管VIP，另外一個通用的方法，是在資料庫中建立一個全局的應用程式名稱與讀寫IP位址之間的全局類目映射（ {app1_master1, 192.168.0.1}, {app_master2, 192.168.0.2}, …）來代替VIP位址的使用，在這種情況下，如果你的master挂了，你需要更新這個資料庫中的全局類目映射。第二種方法這裡個人覺得跟智能DNS解析類似。

14 master_ip_online_change_script：這是一個與master_ip_failover_script 參數很接近的參數，但是這個參數不使用故障轉移指令，而是master的線上change指令

15 shutdown_script：你可能想要強制關閉master節點來避免master節點重新啟動服務，這對于避免腦裂來說是很重要的，

16 report_script：在故障轉移完成或者說因為錯誤而終止的時候，你可能希望發送一個報告出來，這就是使用report_script 參數的目的

17 init_conf_load_script：這個腳本在你不想在配置檔案中設定純文字的資訊的時候使用

3 進行切換操作：

在server3上進行檢視：

server1端的主指向server2上：

server3上進行檢視：

server2上進行檢視：

4 添加插件設定VIP：

1 開啟腳本：

2 在現任master上配置VIP，以做啟動之用

3 配置腳本

5 設定遠端郵箱報警：必須是網易郵箱