當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql 去重根据id_mycat数据库集群系列之mysql主从同步设置

發(fā)布時間：2025/4/16 数据库 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql 去重根据id_mycat数据库集群系列之mysql主从同步设置小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

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

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

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

一、mysql主從復制簡介

原理：

master服務器將數(shù)據(jù)的改變記錄二進制binlog日志，當master上的數(shù)據(jù)發(fā)生改變時，則將其改變寫入二進制日志中；
slave服務器會在一定時間間隔內(nèi)對master二進制日志進行探測其是否發(fā)生改變，如果發(fā)生改變，則開始一個I/OThread請求master二進制事件
同時主節(jié)點為每個I/O線程啟動一個dump線程，用于向其發(fā)送二進制事件，并保存至從節(jié)點本地的中繼日志中，從節(jié)點將啟動SQL線程從中繼日志中讀取二進制日志，在本地重放，使得其數(shù)據(jù)和主節(jié)點的保持一致，最后I/OThread和SQLThread將進入睡眠狀態(tài)，等待下一次被喚醒。
從庫會生成兩個線程,一個I/O線程,一個SQL線程;
I/O線程會去請求主庫的binlog,并將得到的binlog寫到本地的relay-log(中繼日志)文件中;
主庫會生成一個log dump線程,用來給從庫I/O線程傳binlog;
SQL線程,會讀取relay log文件中的日志,并解析成sql語句逐一執(zhí)行;

概述：

從庫會生成兩個線程,一個I/O線程,一個SQL線程;
I/O線程會去請求主庫的binlog,并將得到的binlog寫到本地的relay-log(中繼日志)文件中;
主庫會生成一個log dump線程,用來給從庫I/O線程傳binlog;
SQL線程,會讀取relay log文件中的日志,并解析成sql語句逐一執(zhí)行;

二、前期配置文件準備

服務器說明：

　　我在本機啟動了多個數(shù)據(jù)庫實例，具體的每一個實例如下：

2 主數(shù)據(jù)庫：192.168.1.88:3306
2 從數(shù)據(jù)庫1：192.168.1.88:3307
2 從數(shù)據(jù)庫2：192.168.1.88:3308

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

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

主數(shù)據(jù)庫的3306中的 my.ini配置文件如下：

[Client]port = 3306[mysqld]#設置3306端口port = 3306# 設置mysql的安裝目錄basedir=C:Program Files (x86)MySQLMySQL Server 5.7# 設置mysql數(shù)據(jù)庫的數(shù)據(jù)的存放目錄datadir=C:Program Files (x86)MySQLMySQL Server 5.7data# 允許最大連接數(shù)max_connections=200# 服務端使用的字符集默認為8比特編碼的latin1字符集character-set-server=utf8# 創(chuàng)建新表時將使用的默認存儲引擎default-storage-engine=INNODB# 主從復制設置# 設置服務id,注意改id值在同一個數(shù)據(jù)庫集群中要保持唯一server-id = 3306# 寫入磁盤策略該參數(shù)的有效值有 0、1、2# 0：事務提交時，不將重做日志緩沖寫入磁盤。因此如果 MySQL 發(fā)生宕機，那么就有可能丟失一部分事務。# 1：事務提交時，會將重做日志緩沖寫入磁盤，并且立即刷新(fsync())。保證一定持久化到了硬盤中。# 2：事務提交時，會將重做日志緩沖寫入磁盤，但是不會立即進行刷新操作。此時若操作系統(tǒng)發(fā)生宕機，也可能會丟失一部分數(shù)據(jù)。# 可以看到，只有1才能真正地保證事務的持久性，但是由于刷新操作 fsync() 是阻塞的，性能會明顯地下降。如果不在乎事務丟失，，0和2能獲得更高的性能。# 默認值為1，在實際使用過程中，為了效率，我們一般會在設置為2innodb_flush_log_at_trx_commit=2 # 開啟binlog日志同步功能# 這個參數(shù)直接影響mysql的性能和完整性。# sync_binlog=0 當事務提交后，Mysql僅僅是將binlog_cache中的數(shù)據(jù)寫入binlog文件，但不執(zhí)行fsync之類的磁盤，同步指令通知文件系統(tǒng)將緩存刷新到磁盤，而讓Filesystem自行決定什么時候來做同步，這個是性能最好的。# sync_binlog=n，在進行n次事務提交以后，Mysql將執(zhí)行一次fsync之類的磁盤同步指令，通知文件系統(tǒng)將Binlog文件緩存刷新到磁盤。# Mysql中默認的設置是sync_binlog=0，即不做任何強制性的磁盤刷新指令，這時性能是最好的，但風險也是最大的。一旦系統(tǒng)Crash，在文件系統(tǒng)緩存中的所有Binlog信息都會丟失。# 可根據(jù)自身項目的數(shù)據(jù)要求，進行靈活的設置sync_binlog=0# 二進制日志保留天數(shù)expire_logs_days=180# binlog日志文件名(可以任意命名)log-bin=mysql-bin# 同步的數(shù)據(jù)庫設置方式有兩種：# binlog_do_db：設置需要同步的數(shù)據(jù)庫# binlog-ignore-db：設置不需要同步的數(shù)據(jù)庫# 其中兩者只需要設置一個即可，根據(jù)我們平時的項目情況，都是設置binlog-ignore-db 排除不需要同步的一些系統(tǒng)級數(shù)據(jù)# 這樣設置有一個好處就是：如果業(yè)務數(shù)據(jù)庫有新增，那么也不需要再次來更新設置該配置# 兩種設置方式，都可以多條數(shù)據(jù)#同步的數(shù)據(jù)庫，除此之外別的不同步(和下面binlog-ignore-db二選一)# binlog_do_db=testdb #不同步數(shù)據(jù)庫，除此之外都同步binlog-ignore-db = information_schemabinlog-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = sys [mysql]# 設置mysql客戶端默認字符集default-character-set=utf8

　　從數(shù)據(jù)庫的my.ini配置都很簡單了，只需要配置server-id，binlog-do-db，binlog-ignore-db即可。

其中的server-id必須配置

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

從數(shù)據(jù)庫的3307中的 my.ini配置文件如下：

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

從數(shù)據(jù)庫的3308中的 my.ini配置文件如下：

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

三、主數(shù)據(jù)庫創(chuàng)建同步賬號

　　在處理前，由于上面剛剛設置了數(shù)據(jù)庫的配置文件，為了能夠生效，首先是重啟一下主數(shù)據(jù)庫。只需要執(zhí)行如下兩個命令即可：

　　net stop mysql

　　net start mysql

創(chuàng)建主數(shù)據(jù)用戶同步使用的賬號：

登錄數(shù)據(jù)庫：mysql -uroot -pxuyuanhong -P 3306
創(chuàng)建數(shù)據(jù)同步用戶slaveData：

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

設置權限：

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

刷新權限：FLUSH PRIVILEGES;
查看主機狀態(tài)：show master status;

四、從數(shù)據(jù)庫同步設置

　　前置條件：從數(shù)據(jù)庫同步設置，只以數(shù)據(jù)庫3307為例來說明：

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

　　 net stop mysql3307

　　net start mysql3307

登錄從數(shù)據(jù)庫：

　　mysql -uroot -pxuyuanhong -P 3307

執(zhí)行手動同步命令：

　　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;

其中的參數(shù)值說明：

master_host：主數(shù)據(jù)庫IP地址
master_user：主數(shù)據(jù)庫連接用戶名
master_password：主數(shù)據(jù)量連接密碼
master_port：主數(shù)據(jù)端口
master_log_file：開始同步的主數(shù)據(jù)庫日志名稱，主數(shù)據(jù)庫show master status
隊列的Flie列數(shù)據(jù)
master_log_pos：開始同步的主數(shù)據(jù)庫日志開始位置，主數(shù)據(jù)庫show master status
隊列的Position列數(shù)據(jù)

啟動主從同步：

　　start slave;

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

　　show slave statusG;

　　主從同步狀態(tài)結(jié)果圖如下：如果Slave_IO_Running: Yes，Slave_SQL_Running: Yes時說明兩個線程已啟動，主從復制配置成功。

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

　　主從狀態(tài)的結(jié)果Slave_IO_Running: Yes，Slave_SQL_Running不都為true,那么需要根據(jù)提示結(jié)果，重新設置。重新設置的流程如下：

停止slave：stop slave;
重置slave：reset slave;
重復上面的：執(zhí)行手動同步命令開始流程

五、sql同步執(zhí)行失敗后不再同步處理

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

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

　　1、主從數(shù)據(jù)差異小，并且不管最終數(shù)據(jù)的一致性，那么可以設置直接跳過同步出差的日志，繼續(xù)同步后續(xù)數(shù)據(jù)，具體操作命令如下：

　　第一步：停止主從同步

　　stop slave;
　　第二步：跳過錯誤的日志位置，繼續(xù)后續(xù)同步，后面的數(shù)字可變
　　set global sql_slave_skip_counter =1;

　　第三步：繼續(xù)開啟主從同步
　　start slave;

　　2、如果主從數(shù)據(jù)差異大，并且要求主從數(shù)據(jù)完全一致，那么需要：重新做主從，完全同步。

　　第一步：刪除從數(shù)據(jù)庫，將主數(shù)據(jù)庫備份到從數(shù)據(jù)庫(確保主從數(shù)據(jù)完全一致)

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

六、總結(jié)

　　1、主從同步數(shù)據(jù)庫，一定要注意讀寫分離。從數(shù)據(jù)庫做讀操作，不要做寫操作，最好直接只給從數(shù)據(jù)庫讀權限，不給寫權限

　　2、數(shù)據(jù)庫設置主從同步賬號時，最好創(chuàng)建專門的賬號用來做主從同步

原文鏈接：https://www.cnblogs.com/xiaoXuZhi/p/xyh_mysqlMasterSlaveSet.html

總結(jié)

以上是生活随笔為你收集整理的mysql 去重根据id_mycat数据库集群系列之mysql主从同步设置的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇：周长最短面积最大_圆内接三角形中，正三角
下一篇： idea swagger生成接口文档_s