mycat資料庫集群系列之mysql主從同步設置

　　

　　最近在梳理資料庫集群的相關操作，現在花點時間整理一下關於mysql資料庫集群的操作總結，恰好你又在看這一塊，供一份參考。本次系列終結大概包括以下內容：多資料庫安裝、mycat部署安裝、資料庫之讀寫分離主從複製、資料庫之雙主多重、資料庫分庫分表。每一個點，有可能會對應一篇或者多篇文章，由於還要繼續上班工作，所以本系列分享預計持續時間需要10天左右，有興趣的您可以持續關注。我是一個菜鳥，如果寫的不好的地方，望多多指點和包涵。

　　好了，不逼逼了，直接進入本次的主題：mycat的數據集群方式有很多種，比如：主從複製、多主多重等等，本次先從最簡單的主從複製開始說起。

　　主從複製，其實我前段時間也整理了一篇文章，那是在liux環境的設置，有興趣的可以了解了解//www.cnblogs.com/xiaoXuZhi/p/xyh_mysqlsynchro.html。其實liux和Windows的mysql主從設置在本質上一樣的，只是有點細微的不同，本次主要是針對windows環境的一個完整的流程搭建，所以也在專門梳理一下主從複製。

一、mysql主從複製簡介

原理：

  • master伺服器將數據的改變記錄二進位binlog日誌，當master上的數據發生改變時，則將其改變寫入二進位日誌中；

  • slave伺服器會在一定時間間隔內對master二進位日誌進行探測其是否發生改變，如果發生改變，則開始一個I/OThread請求master二進位事件

  • 同時主節點為每個I/O執行緒啟動一個dump執行緒，用於向其發送二進位事件，並保存至從節點本地的中繼日誌中，從節點將啟動SQL執行緒從中繼日誌中讀取二進位日誌，在本地重放，使得其數據和主節點的保持一致，最後I/OThread和SQLThread將進入睡眠狀態，等待下一次被喚醒。

  • 從庫會生成兩個執行緒,一個I/O執行緒,一個SQL執行緒;

  • I/O執行緒會去請求主庫的binlog,並將得到的binlog寫到本地的relay-log(中繼日誌)文件中;

  • 主庫會生成一個log dump執行緒,用來給從庫I/O執行緒傳binlog;

  • SQL執行緒,會讀取relay log文件中的日誌,並解析成sql語句逐一執行;

概述：

  • 從庫會生成兩個執行緒,一個I/O執行緒,一個SQL執行緒;

  • I/O執行緒會去請求主庫的binlog,並將得到的binlog寫到本地的relay-log(中繼日誌)文件中;

  • 主庫會生成一個log dump執行緒,用來給從庫I/O執行緒傳binlog;

  • SQL執行緒,會讀取relay log文件中的日誌,並解析成sql語句逐一執行;

二、前期配置文件準備

伺服器說明：

　　我在本機啟動了多個資料庫實例，具體的每一個實例如下：

  • ²  主資料庫：192.168.1.88:3306

  • ²  從資料庫1：192.168.1.88:3307

  • ²  從資料庫2：192.168.1.88:3308

　　通過前面的對主從複製的介紹，我們知道主從複製其實最終是通過日誌文件來進行交互，那麼具體的日誌文件怎麼開啟？具體的同步策略如何設置？下面將簡單的介紹，一步一步的走下去。

　　主從同步的配置文件都是在資料庫安裝目錄的下的更目錄的my.ini（liux是my.cnf）文件，在前一篇文章關於mysql多實例安裝中也提到了這個文件，這個次是直接在上面做配置新增即可，具體的配置文件如下，具體的配置參數值可以根據實際需要進行修正：

主資料庫的3306中的 my.ini配置文件如下：

 

[Client]
port = 3306
[mysqld]
#設置3306埠
port = 3306
# 設置mysql的安裝目錄
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7
# 設置mysql資料庫的數據的存放目錄
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7\data
# 允許最大連接數
max_connections=200
# 服務端使用的字符集默認為8比特編碼的latin1字符集
character-set-server=utf8
# 創建新表時將使用的默認存儲引擎
default-storage-engine=INNODB
# 主從複製設置
# 設置服務id,注意改id值在同一個資料庫集群中要保持唯一
server-id = 3306
# 寫入磁碟策略 該參數的有效值有 0、1、2
# 0：事務提交時，不將重做日誌緩衝寫入磁碟。因此如果 MySQL 發生宕機，那麼就有可能丟失一部分事務。
# 1：事務提交時，會將重做日誌緩衝寫入磁碟，並且立即刷新（fsync()）。保證一定持久化到了硬碟中。
# 2：事務提交時，會將重做日誌緩衝寫入磁碟，但是不會立即進行刷新操作。此時若作業系統發生宕機，也可能會丟失一部分數據。
# 可以看到，只有1才能真正地保證事務的持久性，但是由於刷新操作 fsync() 是阻塞的，性能會明顯地下降。如果不在乎事務丟失，，0和2能獲得更高的性能。
# 默認值為1，在實際使用過程中，為了效率，我們一般會在設置為2
innodb_flush_log_at_trx_commit=2  
# 開啟binlog日誌同步功能
# 這個參數直接影響mysql的性能和完整性。
# sync_binlog=0 當事務提交後，Mysql僅僅是將binlog_cache中的數據寫入binlog文件，但不執行fsync之類的磁碟，同步指令通知文件系統將快取刷新到磁碟，而讓Filesystem自行決定什麼時候來做同步，這個是性能最好的。
# sync_binlog=n，在進行n次事務提交以後，Mysql將執行一次fsync之類的磁碟同步指令，通知文件系統將Binlog文件快取刷新到磁碟。
# Mysql中默認的設置是sync_binlog=0，即不做任何強制性的磁碟刷新指令，這時性能是最好的，但風險也是最大的。一旦系統Crash，在文件系統快取中的所有Binlog資訊都會丟失。
# 可根據自身項目的數據要求，進行靈活的設置
sync_binlog=0
# 二進位日誌保留天數
expire_logs_days=180
# binlog日誌文件名（可以任意命名）
log-bin=mysql-bin

# 同步的資料庫設置方式有兩種：
# binlog_do_db：設置需要同步的資料庫
# binlog-ignore-db：設置不需要同步的資料庫
# 其中兩者只需要設置一個即可，根據我們平時的項目情況，都是設置binlog-ignore-db 排除不需要同步的一些系統級數據
# 這樣設置有一個好處就是：如果業務資料庫有新增，那麼也不需要再次來更新設置該配置
# 兩種設置方式，都可以多條數據
#同步的資料庫，除此之外別的不同步（和下面binlog-ignore-db二選一）
# binlog_do_db=testdb 
#不同步資料庫，除此之外都同步
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
 
[mysql]
# 設置mysql客戶端默認字符集
default-character-set=utf8

 

 

　　從資料庫的my.ini配置都很簡單了，只需要配置server-id，binlog-do-db，binlog-ignore-db即可。

其中的server-id必須配置

　　binlog-do-db，binlog-ignore-db可根據實際需要設置，如果是主設置的資料庫全部同步到從，那麼這兩個值就沒必要設置了，如果從只同步主的某一些庫，那麼需要設置，在數據項目中，絕大部分都不需要設置的。

 

從資料庫的3307中的 my.ini配置文件如下：

 

[Client]
port = 3307
 
[mysqld]
#設置3307埠
port = 3307
server-id = 3307
# 設置mysql的安裝目錄
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2
# 設置mysql資料庫的數據的存放目錄
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2\data
# 允許最大連接數
max_connections=200
# 服務端使用的字符集默認為8比特編碼的latin1字符集
character-set-server=utf8
# 創建新表時將使用的默認存儲引擎
default-storage-engine=INNODB
 
[mysql]
# 設置mysql客戶端默認字符集
default-character-set=utf8

 

從資料庫的3308中的 my.ini配置文件如下：

[Client]
port = 3308

[mysqld]
#設置3308埠
port = 3308
server-id = 3308
# 設置mysql的安裝目錄
basedir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2
# 設置mysql資料庫的數據的存放目錄
datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7.2\data
# 允許最大連接數
max_connections=200
# 服務端使用的字符集默認為8比特編碼的latin1字符集
character-set-server=utf8
# 創建新表時將使用的默認存儲引擎
default-storage-engine=INNODB
 
[mysql]
# 設置mysql客戶端默認字符集
default-character-set=utf8

 

三、主資料庫創建同步帳號

　　在處理前，由於上面剛剛設置了資料庫的配置文件，為了能夠生效，首先是重啟一下主資料庫。只需要執行如下兩個命令即可：

　　net stop mysql

　　net start mysql

 

創建主數據用戶同步使用的帳號：

  • 登錄資料庫：mysql -uroot -pxuyuanhong -P 3306

  • 創建數據同步用戶slaveData：

　　　　CREATE USER ‘slaveData’@’%’ IDENTIFIED BY ‘用戶登錄密碼’;

  • 設置許可權：

　　　　GRANT REPLICATION SLAVE ON *.* to ‘slaveData’@’%’ identified by ‘用戶登錄密碼’;

  • 刷新許可權：FLUSH PRIVILEGES;
  • 查看主機狀態：show master status;

 

四、從資料庫同步設置

　　前置條件：從資料庫同步設置，只以資料庫3307為例來說明：

　　從數據設置前，也還是按照主數據設置一樣，先重啟一下。只需要執行如下兩個命令即可：

　　 net stop mysql3307

　　net start mysql3307

登錄從資料庫：

　　mysql -uroot -pxuyuanhong -P 3307

執行手動同步命令：

　　CHANGE MASTER TO MASTER_HOST=’192.168.3.205′,MASTER_PORT=3306,MASTER_USER=’slaveData’,MASTER_PASSWORD=’xuyuanhong’,MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=844;

 

其中的參數值說明：

  • master_host：主資料庫IP地址

  • master_user：主資料庫連接用戶名

  • master_password：主數據量連接密碼

  • master_port：主數據埠

  • master_log_file：開始同步的主資料庫日誌名稱，主資料庫show master status

  • 隊列的Flie列數據

  • master_log_pos：開始同步的主資料庫日誌開始位置，主資料庫show master status

  • 隊列的Position列數據

啟動主從同步：

　　start slave;

查看主從同步狀態

　　show slave status\G;

　　主從同步狀態結果圖如下：如果Slave_IO_Running: Yes，Slave_SQL_Running: Yes時說明兩個執行緒已啟動，主從複製配置成功。

 

 

主從同步設置失敗如何處理

　　主從狀態的結果Slave_IO_Running: Yes，Slave_SQL_Running不都為true,那麼需要根據提示結果，重新設置。重新設置的流程如下：

  • 停止slave：stop slave;

  • 重置slave：reset slave;

  • 重複上面的：執行手動同步命令開始流程

五、sql同步執行失敗後不再同步處理

　　主從同步在實際使用過程中，如果處理的不好，可能會導致同步失敗，比如：從資料庫也在做新增操作，如果有自增主鍵約束，那麼就可能導致同步失敗。因為主從同步最終目的是實現數據的一致性，所有當某一項同步失敗，那麼就不會再做後同步處理。那麼如果出現同步失敗後又該如何處理呢？

　　在實際處理過程中，大概有兩種處理方式：

　　1、主從數據差異小，並且不管最終數據的一致性，那麼可以設置直接跳過同步出差的日誌，繼續同步後續數據，具體操作命令如下：

　　第一步：停止主從同步

　　stop slave;

　　第二步：跳過錯誤的日誌位置，繼續後續同步，後面的數字可變

　　set global
sql_slave_skip_counter =1;

　　第三步：繼續開啟主從同步

　　start slave;

　　2、如果主從數據差異大，並且要求主從數據完全一致，那麼需要：重新做主從，完全同步。

　　第一步：刪除從資料庫，將主資料庫備份到從資料庫（確保主從數據完全一致）

　　第二步：按照上面的流程，重新配置主從同步

六、自我一些總結

　　1、 主從同步資料庫，一定要注意讀寫分離。從資料庫做讀操作，不要做寫操作，最好直接只給從資料庫讀許可權，不給寫許可權

　　2、 資料庫設置主從同步帳號時，最好創建專門的帳號用來做主從同步

　　有關mysql的主從同步就分享到這兒了，下一篇將和大家一起分享mycat的多安裝配置、主從同步、讀寫分離等知識點，有興趣的小夥伴，可以持續關注，謝謝！

END
為了更高的交流，歡迎大家關注我的公眾號，掃描下面二維碼即可關注，謝謝：

Tags:

VirMach 便宜 VPS

QNews

QNews