MySQL主從複製架構使用方法

来源:https://www.cnblogs.com/huchong/archive/2019/01/13/10253522.html
-Advertisement-
Play Games

一. 單個資料庫伺服器的缺點 資料庫伺服器存在單點問題 資料庫伺服器資源無法滿足增長的讀寫請求 高峰時資料庫連接數經常超過上限 二. 如何解決單點問題 增加額外的資料庫伺服器，組建資料庫集群 同一集群中的資料庫伺服器需要具有相同的數據 集群中的任一伺服器宕機後，其它伺服器可以取代宕機伺服器 三. M ...


一. 單個資料庫伺服器的缺點

資料庫伺服器存在單點問題
資料庫伺服器資源無法滿足增長的讀寫請求
高峰時資料庫連接數經常超過上限

二. 如何解決單點問題

增加額外的資料庫伺服器，組建資料庫集群

同一集群中的資料庫伺服器需要具有相同的數據

集群中的任一伺服器宕機後，其它伺服器可以取代宕機伺服器

三. MySQL主從複製架構

1. 主庫將變更寫入到主庫的binlog中

一些MySQL版本並不會開啟二進位日誌，所以一定要檢查是否開啟
如果剛開始沒有開啟，後面再進行開啟的話，需要重啟資料庫才能生效，而且資料庫的重啟往往會對業務造成很大的影響
儘管二進位日誌對性能有稍許的影響，所以還是建議大家無論是否使用複製功能，都要開啟Mysql二進位日誌，因為增量備份也需要二進位日誌

2. 從庫的IO線程在指定位置讀取主庫binlog內容存儲到本地的中繼日誌（Relay Log）中

要完成二進位日誌的傳輸過程，MySQL會在從伺服器上啟動一個工作線程，稱為IO線程，這個IO線程會跟主資料庫建立一個普通的客戶端連接，然後在主伺服器上啟動一個特殊的二進位轉儲線程稱為binlogdown線程
從庫上的IO線程通過這個二進位轉儲線程來讀取主庫上的二進位事件，如果該事件追趕上主庫，則會進入sleep狀態，直到主庫發起信號通知有新事件產生時，才會被喚醒，relay log的格式和binlog格式是完全相同的，
可以使用mysqlbinlog 來讀取relay log中的內容

3. 從庫的SQL線程讀取Relay Log日誌中的內容，併在從庫中重放

sql線程所執行的事件，我們可以通過配置選項來決定是否要寫入到從伺服器的二進位日誌中

目前mysql支持兩種複製類型

  1. 基於二進位日誌點的複製
  2. 基於GTID的複製（Mysql>=5.7推薦使用）

四. MySQL主從配置步驟

1. 配置主從資料庫伺服器參數

有些參數配置後需要資料庫重啟才能生效，為了不影響資料庫的正常使用，我們最好在伺服器上線的同時就把參數都配置好
特別是master伺服器的參數，更應該作為伺服器初始參數來進行配置

master伺服器

log_bin  = /data/mysql/sql_log/mysql-bin    # 指定mysql的binlog的存放路徑 /data/mysql/sql_log，以及日誌文件名首碼mysql-bin ,
                                                                    # 如果只是為了啟用binlog，可以不指定存放路徑，預設會存放到mysql的data目錄下，也就是會把日誌和數據文件存放在一起
                                                                    # 之所以指定路徑分開存放，是為了提高IO性能，所以還是建議日誌文件和數據文件分開存放
server_id = 100    # mysql的複製集群中通過server_id的值區分不同的伺服器，建議使用伺服器ip的後一段或後兩段的值進行配置，比如192.168.3.100,就設置為100或2100

slave 伺服器

log_bin  = /data/mysql/sql_log/mysql-bin 
server_id = 101

relay_log = /data/mysql/sql_log/relay-bin    # 指定relay_log日誌的存放路徑和文件首碼 ，不指定的話預設以主機名作為首碼

read_only = on    #    使所有沒有server許可權的用戶，在從伺服器上不能執行寫操作，不論這個用戶是否擁有寫許可權 （mysql5.7 可以使用 super_read_only = on ，限制super用戶也不能在從伺服器上執行寫操作）

skip_slave_start = on    # 在slave伺服器重啟時，不會自動啟動複製鏈路。預設情況下slave伺服器重啟後，mysql會自動啟動複製鏈路，如果這個時候存在問題，則主從鏈路會中斷，所以正常情況下，我們應該在伺服器重啟後檢查是否存在問題，然後再手動啟動複製鏈路

# 下麵兩個參數是把主從複製信息存儲到innodb表中，預設情況下主從複製信息是存儲到文件系統中的，如果從伺服器宕機，很容易出現文件記錄和實際同步信息不同的情況，存儲到表中則可以通過innodb的崩潰恢復機制來保證數據記錄的一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE

2. 在master伺服器上創建用於複製的資料庫賬號

用於IO線程連接master伺服器獲取binlog日誌
需要* REPLICATION SLAVE** 許可權

    create user 'repl'@'ip段' identified by 'password';
    grant replication slave on *.* to 'repl'@'ip段';

3. 備份master伺服器上的數據並初始化 slave伺服器數據

建議主從資料庫伺服器採用相同的MySQL版本
建議使用全庫備份的方式初始化slave數據

採用相同版本的好處
我們可以使用全備的方式來初始化slave數據，還可以避免不同版本之間的差異造成資料庫同步失敗的問題

如果我們使用的主從複製的伺服器mysql版本不同，則一定要註意master上的版本一定要低於slave伺服器，不然同步的時候就可能出現錯誤

由於我們演示過程中的mysql伺服器都是使用的mysql5.7
所以我們可以使用全備的方式進行

mysqldump --master-data=2 -uroot -p -A --single-transaction -R --triggers

4. 啟動基於日誌點的複製鏈路

在slave伺服器上運行

mysql命令

CHANGE MASTER TO
MASTER_HOST= 'master_host_ip',
MASTER_USER= 'repl',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE='mysql_log_file_name',
MASTER_LOG_POS=xxxxxx;

5. 啟動基於GTID的複製鏈路

GTID：全局事務ID
GTID可以保證每一個在主上提交的事務，在複製集群中可以生成一個唯一的ID值，要使用基於GTID的複製，我們要在主從複製的配置文件中同時加入以下配置項

mysql配置

gtid_mode=on # 是否啟動gtid模式，啟動了此模式會在二進位日誌中會額外記錄每個事務的GTID標識符
enforce-gtid-consistency    # 強制gtid一致性，用於保證啟動gtid後事務的安全
log-slave-updates = on    # mysql5.6一定要啟用參數，5.7可以不啟用

mysql命令

CHANGE MASTER TO
MASTER_HOST= 'master_host_ip',
MASTER_USER= 'repl',
MASTER_PASSWORD = 'password',
MASTER_AUTO_POSITION=1;

GTID複製的限制

無法再使用create table ... select 語句建立表，只能先create表，再insert 數據
無法在事務中使用create temporary table 建立臨時表
無法使用關聯更新同時更新事務表和非事務表

4和5中選一個執行即可

五. mysql主從複製演示

1. 先對主伺服器進行配置

[client]
port  = 3306     # 客戶端埠號為3306
socket = /home/mysql/data/mysql.sock

[mysqld]

# skip #
skip_name_resolve = 1
skip-external-locking =1

# GENERAL #
user = mysql   # MySQL啟動用戶
default_storage_engine = InnoDB  # 新數據表的預設數據表類型
character-set-server = utf8      #     #服務端預設編碼（資料庫級別）
socket = /home/mysql/data/mysql.sock
pid_file =  /home/mysql/data/mysqld.pid
basedir = /home/mysql    #使用該目錄作為根目錄（Mysql安裝目錄）；

port = 3306
bind-address = 0.0.0.0
log_error_verbosity = 3
explicit_defaults_for_timestamp = off
#sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#sql_mode = NO_ENGINE_SUBSTITUTION

# undo log
# innodb_undo_directory = /home.mysql/undo
# innodb_undo_tablespaces = 32

# MyISAM #
key_buffer_size =32M

# SAFETY #
max_allowed_packet    = 100M
max_connect_errors    = 1000000
sysdate_is_now    =1
#innodb = FORCE
#innodb_strict_mode = 1  

# Replice #
server-id = 100
relay_log =  /home/mysql/sql_log/mysqld-relay-bin 

#plugin-load = semisync_master.so
log_slave_updates = on
master_info_repository = TABLE
relay_log_info_repository =TABLE
# gtid_mode = on
# enforce_gtid_consistency =on
# skip-slave-start =1
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout=200    # 0.2 second
master_info_respository = TABLE
# gtid_mode= on
# enforce_gtid_consistency = on
# skip-slave-start = 1

# DATA STORAGE #

datadir = /home/mysql/data     #mysql 數據文件存放的目錄
tmpdir = /tmp    # MySQL存放臨時文件的目錄

# BINARY LOGGING #

log_bin = /home/mysql/sql_log/mysql-bin
max_binlog_size  = 1000M
binlog_format = row
expire_log_days = 7
sync_binlog = 1

# CACHES AND LIMITS #

tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0

由於主伺服器一直在運行著，在生產環境中主伺服器是很少會重啟的，如果主伺服器重啟，會造成正常的業務訪問的中斷，所以在伺服器啟動之前就啟動了二進位日誌
這裡不需要重啟主伺服器了，由於主伺服器的預設server_id=1，我們雖然在配置文件中更改了它的值 ，但實際運行環境中並沒有改變

我們可以查看一下當前server_id

mysql> show variables like '%server_id%';

可以通過以下命令動態的進行修改

mysql> set global server_id = 100;

2. 再對從伺服器進行配置

[client]
port  = 3306     # 客戶端埠號為3306
socket = /home/mysql/data/mysql.sock

[mysqld]

# skip #
skip_name_resolve = 1
skip-external-locking =1

# GENERAL #
user = mysql   # MySQL啟動用戶
default_storage_engine = InnoDB  # 新數據表的預設數據表類型
character-set-server = utf8      #     #服務端預設編碼（資料庫級別）
socket = /home/mysql/data/mysql.sock
pid_file =  /home/mysql/data/mysqld.pid
basedir = /home/mysql    #使用該目錄作為根目錄（Mysql安裝目錄）；

port = 3306
bind-address = 0.0.0.0
log_error_verbosity = 3
explicit_defaults_for_timestamp = off
#sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#sql_mode = NO_ENGINE_SUBSTITUTION
read_only = on

# undo log
# innodb_undo_directory = /home.mysql/undo
# innodb_undo_tablespaces = 32

# MyISAM #
key_buffer_size =32M

# SAFETY #
max_allowed_packet    = 100M
max_connect_errors    = 1000000
sysdate_is_now    =1
#innodb = FORCE
#innodb_strict_mode = 1  

# Replice #
server-id = 101
relay_log =  /home/mysql/sql_log/mysqld-relay-bin 

#plugin-load = semisync_master.so
log_slave_updates = on
master_info_repository = TABLE
relay_log_info_repository =TABLE
# gtid_mode = on
# enforce_gtid_consistency =on
# skip-slave-start =1
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout=200    # 0.2 second
master_info_respository = TABLE
# gtid_mode= on
# enforce_gtid_consistency = on
# skip-slave-start = 1

# DATA STORAGE #

datadir = /home/mysql/data     #mysql 數據文件存放的目錄
tmpdir = /tmp    # MySQL存放臨時文件的目錄

# BINARY LOGGING #

log_bin = /home/mysql/sql_log/mysql-bin
max_binlog_size  = 1000M
binlog_format = row
expire_log_days = 7
sync_binlog = 1

# CACHES AND LIMITS #

tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0

修改完從伺服器配置後，重啟mysql伺服器

如果使用的是mysql5.7版本的需要註意
mysql5.7增加了server-uuid值，預設情況下載auto.cnf文件中，如果是使用的鏡像的方式安裝，可能大家的uuid一樣 ，所以需要把auto.cnf文件刪除掉。mysql重啟後會自動重新生成uuid的值，這樣就可以保證不同伺服器上的mysql實例的uuid的值是不一樣的

如果server-uuid的值相同，主從複製會出現問題

以上我們就完成了主從複製的配置，接下來我們要在主伺服器上建立複製賬號

3. 在mysql主伺服器上建立mysql複製賬號

mysql> create user 'dba_repl'@'192.168.3.%' identified by '123456';

mysql> grant replication slave on *.* to 'dba_repl'@'192.168.3.%';

建立好複製賬號以後

4. 通過mysql主伺服器上的全備初始化從伺服器上數據

進行全備

[root@localhost data]# cd /data/db_backup/
[root@localhost db_backup]#  mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events  --all-databases > all.sql
Enter password:

將其拷貝到從伺服器上

[root@localhost db_backup]# scp all.sql [email protected]:/root

在從伺服器上恢復備份進行初始化

[root@Node2 ~]# mysql -uroot -p < all.sql

初始化完成後，準備

5. 從伺服器進行基於日誌點的複製鏈路的配置

mysql> change master to master_host='192.168.3.100',
        -> master_user='dba_repl',
        -> master_password='123456',
        ->MASTER_LOG_FILE='mysql-bin.000017',MASTER_LOG_POS=663;

MASTER_LOG_FILE和MASTER_LOG_POS的值從全備文件中的CHANGE MASTER中獲取

以上複製鏈路的配置完成

啟動slave

mysql> start slave;

檢查是否啟動成功狀態

mysql> show slave status \G

顯示

Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running：Yes
Slave_SQL_Running: Yes

說明啟動成功了，可以在主伺服器上插入數據，在從服務上查看數據是否同步過來了

六. 主從複製的一些缺點

雖然主從複製，增加了一個資料庫副本，從資料庫和主資料庫的數據最終會是一致的
之所以說是最終一致，因為mysql複製是非同步的，正常情況下主從複製數據之間會有一個微小的延遲
通過這個資料庫副本看似解決了資料庫單點問題，但並不完美
因為這種架構下，如果主伺服器宕機，需要手動切換從伺服器，業務中斷不能忍受，不能滿足應用高可用的要求

如果才能解決當master伺服器宕機後，前端應用自動切換鏈接呢？

下節再進行學習


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

-Advertisement-
Play Games
更多相關文章
  • 003.Keepalived搭建LVS高可用集群
    一 基礎環境 1.1 IP規劃 OS：CentOS 6.8 64位 節點類型 IP規劃 主機名 類型 主 Director Server eth0：172.24.8.10 DR1 公共IP eth1：192.168.56.100 心跳 私有IP eth0:0：172.24.8.100 無 虛擬IP ...
  • pkg-config 用法
    在進行使用fuse 2.9寫程式的時候，遇到了pkg-config 命令和 .pc 文件。本篇博客就具體說明一下pkg-config 命令是什麼？ 我們首先看一下： pkg-config 簡介： 大家應該都知道用第三方庫，就少不了要使用到第三方的頭文件和庫文件。我們在編譯、鏈接的時候，必須要指定這些 ...
  • helpera64開機bootlogo-BUG
    環境： HelperA64開發板 Linux3.10內核 時間：2019.01.12 目標：修改開機bootlogo的BUG 問題： 1.24bit深度的bootlogo.bmp圖片會導致Qt5有色差，32位的圖片沒有色差。 2.將開機bootlogo圖片修改為32點陣圖片只有部分圖片能夠點亮屏就顯示 ...
  • Mycat - 實現資料庫的讀寫分離與高可用
    前言 開心一刻 上語文課，不小心睡著了，坐在邊上的同桌突然叫醒了我，並小聲說道：“讀課文第三段”。我立馬起身大聲讀了起來。正在黑板寫字的老師嚇了一跳，老師鬱悶的看著我，問道：“同學有什麼問題嗎？”，我貌似知道了什麼，蛋定的說了一句：“這段寫的真好！我給大伙念念！”，老師還較真了：“你說說看，好在哪裡 ...
  • Java架構師趣談Hbase之巨集觀架構
    相關文章 五分鐘輕鬆瞭解Hbase列式存儲 Hbase給初學者的“下馬威” Hbase王國游記之：Hbase客戶端API初體驗 👉§團結力量大 原始社會，由若幹血緣相近的宗族、氏族結合起來集體生活，這就是部落。最高首領就是酋長，此外還可能會有軍事首領，他們一起繁衍生息。 到了原始社會末期，頻繁戰爭 ...
  • 性能調優7：多表連接 - join
    在產品環境中，往往存在著大量的表連接情景，不管是inner join、outer join、cross join和full join（邏輯連接符號），在內部都會轉化為物理連接（Physical Join），SQL Server共有三種物理連接：Nested Loop（嵌套迴圈），Merge Join ...
  • oracle配置資料庫可恢復性（認證系列總結一）
    原創作品，轉載請註明出處：https://www.cnblogs.com/sunshine5683/p/10263246.html 接下來的n多天，將進入oracle認證系列的學習總結中，本該從asm開始總結，但由於在日常工作中使用最廣的還是oracle的安全性，所以就先從這點入手，這也是資料庫最重 ...
  • mysql學習【第4篇】：MySQL函數
    狂神聲明 : 文章均為自己的學習筆記 , 轉載一定註明出處 ; 編輯不易 , 防君子不防小人~共勉 ! mysql學習【第4篇】：MySQL函數 官方文檔 : 官方文檔 常用函數 分類: 數學函數 , 字元串函數 , 日期和時間函數 , 系統信息函數 聚合函數 mysql之自定義函數 什麼是函數： ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類