MYSQL主從複製配置（整理）

来源:https://www.cnblogs.com/xhnxhnu/archive/2018/09/29/9726752.html
-Advertisement-
Play Games

MYSQL主從原理及過程 原理 Mysql的 Replication 是一個非同步的複製過程（mysql5.1.7以上版本分為非同步複製和半同步兩種模式），從一個 Mysql instace(我們稱之為 Master)複製到另一個 Mysql instance(我們稱之 Slave)。在 Master ...


MYSQL主從原理及過程

原理

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” 參數選項，或者在配置文件中的 mysqld 參數組([mysqld]標識後的參數部分)增加 “log-bin” 參數項。

基本過程

  1. Slave 上面的IO線程連接上 Master，並請求從指定日誌文件的指定位置(或者從最開始的日誌)之後的日誌內容；

  2. Master 接收到來自 Slave 的 IO 線程的請求後，通過負責複製的 IO 線程根據請求信息讀取指定日誌指定位置之後的日誌信息，返回給 Slave 端的 IO 線程。返回信息中除了日誌所包含的信息之外，還包括本次返回的信息在 Master 端的 Binary Log 文件的名稱以及在 Binary Log 中的位置；

  3. Slave 的 IO 線程接收到信息後，將接收到的日誌內容依次寫入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，並將讀取到的Master端的bin-log的文件名和位置記錄到master- info文件中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”

  4. Slave 的 SQL 線程檢測到 Relay Log 中新增加了內容後，會馬上解析該 Log 文件中的內容成為在 Master 端真實執行時候的那些可執行的 Query 語句，併在自身執行這些 Query。這樣，實際上就是在 Master 端和 Slave 端執行了同樣的 Query，所以兩端的數據是完全一樣的。

Mysql複製的幾種模式

.從 MySQL 5.1.12 開始，可以用以下三種模式來實現：

– 基於SQL語句的複製(statement-based replication, SBR)，

– 基於行的複製(row-based replication, RBR)，

– 混合模式複製(mixed-based replication, MBR)

相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是預設的。

設定主從複製模式：

log-bin=mysql-bin

#binlog_format="STATEMENT"

#binlog_format="ROW"

binlog_format="MIXED"

也可以在運行時動態修改binlog的格式。例如

mysql> SET SESSION binlog_format = 'STATEMENT';

mysql> SET SESSION binlog_format = 'ROW';

mysql> SET SESSION binlog_format = 'MIXED';

mysql> SET GLOBAL binlog_format = 'STATEMENT';

mysql主從複製配置

版本：mysql5.7 centos7.2

場景描述：
主資料庫伺服器：192.168.206.100，MySQL已經安裝，並且無應用數據。
從資料庫伺服器：192.168.206.200，MySQL已經安裝，並且無應用數據。

1 主伺服器上進行的操作

啟動mysql服務

service mysqld start

通過命令行登錄管理MySQL伺服器

mysql -uroot -p'new-password'

授權複製許可權給從資料庫伺服器192.168.206.200

mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.206.200' identified by ‘password’;

查詢主資料庫狀態

配置從伺服器時會用到
mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-master-bin.000001 | 154 | | | |
+-------------------------+----------+--------------+------------------+-------------------+

這裡需要註意一點，若查詢時返回的是

mysql> show slave status;
Empty set (0.01 sec)

這是因為沒有開啟bin-log造成的，需要去修改/etc/my.cnf文件

server-id =1
log-bin=mysql-master-bin

修改文件時還需要註意一點，mysql5.7之後，開啟binlog時還需要同時指定server-id，否則會報錯

2 配置從伺服器

修改從伺服器的配置文件/opt/mysql/etc/my.cnf

將 server-id = 1修改為 server-id = 2，並確保這個ID沒有被別的MySQL服務所使用。

啟動mysql服務

service mysqld start

登錄管理MySQL伺服器

mysql -uroot -p'new-password'

執行同步SQL語句

change master to
master_host='192.168.206.100',
master_user='root',
master_password='Xu261220..',
master_log_file='mysql-master-bin.000001',
master_log_pos=154;

正確執行後啟動Slave同步進程

mysql> start slave;

註意，這裡又有一個坑了
即使啟動start slave成功了，主從複製任然是失敗的

1、錯誤消息
mysql> show slave staus;
 
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; 
these UUIDs must be different for replication to work.
 
2、查看主從的server_id變數
master_mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 33|
+---------------+-------+
 
slave_mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 11|
+---------------+-------+
-- 從上面的情形可知，主從mysql已經使用了不同的server_id
 
3、解決故障
###查看auto.cnf文件
[root@dbsrv1 ~] cat /data/mysqldata/auto.cnf  ### 主上的uuid
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
 
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###從上的uuid，果然出現了重覆，原因是克隆了虛擬機，只改server_id不行
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
 
[root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf  /data/mysqldata/auto.cnf.bk  ###重命名該文件
[root@dbsrv2 ~]# service mysql restart  ###重啟mysql
Shutting down MySQL.[  OK  ]
Starting MySQL.[  OK  ]
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf  ###重啟後自動生成新的auto.cnf文件，即新的UUID
[auto]
server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9
 
 
###再次查看slave的狀態已經正常
[root@dbsrv1 ~]# mysql -uroot -pxxx -e "show slave status\G"|grep Running
Warning: Using a password on the command line interface can be insecure.
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
###主庫端查看自身的uuid
master_mysql> show variables like 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value|
+---------------+--------------------------------------+
| server_uuid   | 62ee10aa-b1f7-11e4-90ae-080027615026 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
###主庫端查看從庫的uuid
master_mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID   |
+-----------+------+------+-----------+--------------------------------------+
|33 |  | 3306 |11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |
|22 |  | 3306 |11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |
+-----------+------+------+-----------+--------------------------------------+

參考：https://blog.csdn.net/leshami/article/details/43854505

其中Slave_IO_Running 與 Slave_SQL_Running 的值都必須為YES，才表明狀態正常。

如果主伺服器已經存在應用數據，則在進行主從複製時，需要做以下處理：
(1)主資料庫進行鎖表操作，不讓數據再進行寫入動作
mysql> FLUSH TABLES WITH READ LOCK;

(2)查看主資料庫狀態
mysql> show master status;

(3)記錄下 FILE 及 Position 的值。
將主伺服器的數據文件（整個/opt/mysql/data目錄）複製到從伺服器，建議通過tar歸檔壓縮後再傳到從伺服器解壓。

(4)取消主資料庫鎖定
mysql> UNLOCK TABLES;

3 驗證主從複製效果

在主伺服器上創建資料庫first_db

mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)

在主伺服器上創建表first_tb

mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

在主伺服器上的表first_tb中插入記錄

mysql> insert into first_tb values (001,“myself”);
Query Ok, 1 row affected (0.00 sec)

在從伺服器上查看

mysql> show databases;


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • nginx代理 upstream輪詢
    問題描述 我有2個Tomcat 一個埠開啟（8021），一個埠未開啟（8022），在nginx里用upstream模塊進行代理 ，代理的負載演算法採用的是預設的輪詢演算法，配置成功後，訪問頁面時並沒有出現輪詢的效果 ，頁面一直顯示是正常訪問的頁面，原則上會有一次出現502界面或者報錯的頁面的，但是通 ...
  • L2TP連接嘗試失敗，因為安全層在初始化與遠程電腦的協商時遇到了一個處理錯誤（轉）
    L2TP連接嘗試失敗，因為安全層在初始化與遠程電腦的協商時遇到了一個處理錯誤 錯誤描述：“ L2TP連接嘗試失敗，因為安全層在初始化與遠程電腦的協商時遇到了一個處理錯誤” 只有這個沒有錯誤碼。 從網上參考 Bob Hou: http://blog.csdn.net/minxihou 博客 首先連 ...
  • shell基礎 -- 基本語法
    本文介紹一下 shell 的語法。 一、變數 在 shell 里，使用變數之前通常並不需要事先為他們做出聲明，需要使用的時候直接創建就行了。預設情況下，所有變數都被看做字元串並以字元串來存儲，即使它們被賦值為數值時也是如此。shell 和一些工具會在需要時把數值型字元串轉換成對應的數值以對它們進行操 ...
  • jenkins + ansible + docker 代碼集成發佈
    一、環境搭建 1. 安裝Java 配java_home, /etc/profile 2.安裝Jenkins 下載war包，用 Java -jar Jenkins.war或者 把war包放tomcat的webapps下 http://ip:8080或者 http://ip:8080/jenkins 3 ...
  • IP負載均衡
    推薦一篇關於LVS的好文： https://www.cnblogs.com/gaoxu387/p/7941381.html 一、原博主要內容： 1、概述 IP負載均衡：四層負載，是基於IP+埠的負載均衡，主要代表是LVS（Linux Virtual Server） LVS工作原理：LVS的IP負載 ...
  • Linux安裝配置JDK
    首先去官網根據系統版本下載對應的jdk(註意是Linux版)，我們在Linux創建一個文件夾專門存放上傳的安裝包。 然後利用上傳工具把我們下載好的壓縮包上傳到Linux 上，選擇 /opt/software 目錄（這裡選擇自己創建的目錄即可）。 然後再創建一個目錄用於我們的jdk安裝目錄 這裡我把目 ...
  • 第一章 Linux命令行簡介
    1 Linux系統命令操作語法的格式 命令_[參數選項]_[文件或路徑] 其中 _ 至少一個空格 如：rm -f /etc/hosts 其中/etc/hosts完整路徑不帶空格 中括弧表示可選的，即命令的參數和路徑文件都是可選的，參數選項表示使用一個命令的不同功能，比如，可以刪除一個文件和創建一個文 ...
  • mysql讀寫分離配置（整理）
    mysql讀寫分離配置 環境：centos7.2 mysql5.7 場景描述： 資料庫Master主伺服器：192.168.206.100 資料庫Slave從伺服器：192.168.206.200 MySQL Proxy調度伺服器：192.168.206.210 以下操作，均是在192.168.20 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類