MySQL 搭建雙主復制服務并通過 HAProxy 負載均衡的過程詳解

更新時間：2024年03月19日 15:39:19 作者：小畢超

在數(shù)據(jù)庫管理中,數(shù)據(jù)的備份和同步是至關重要的環(huán)節(jié),而雙主復制（Dual Master Replication）作為一種高可用性和數(shù)據(jù)同步的解決方案,本文將介紹MySQL雙主復制的配置過程并通過 HAProxy 負載均衡,感興趣的朋友一起看看吧

一、MySQL 搭建雙主復制高可用服務

在數(shù)據(jù)庫管理中，數(shù)據(jù)的備份和同步是至關重要的環(huán)節(jié)，而雙主復制（Dual Master Replication）作為一種高可用性和數(shù)據(jù)同步的解決方案，通過讓兩個數(shù)據(jù)庫實例同時充當主服務器和從服務器，MySQL雙主復制可以實現(xiàn)數(shù)據(jù)的雙向同步，為數(shù)據(jù)庫系統(tǒng)提供了更靈活和可靠的解決方案。即使其中一個主服務器發(fā)生故障，另一個主服務器仍然可以繼續(xù)提供服務，確保系統(tǒng)的穩(wěn)定性和可用性。這種數(shù)據(jù)同步方式不僅可以加強數(shù)據(jù)的備份與恢復能力，還可以提高系統(tǒng)的擴展性，適用于需要高度數(shù)據(jù)一致性和容錯性的場景。

本文將介紹MySQL雙主復制的配置過程，整體實現(xiàn)架構如下：

主機規(guī)劃：

ip	規(guī)劃
172.19.222.20	MySQL1
172.19.222.82	MySQL2
172.19.222.84	Haproxy

二、MySQL1 部署

mysql 的部署這里采用 docker + docker-compose 的方式快速實現(xiàn)，主要關注點在于配置文件：

首先編寫 my.cnf 文件，寫入如下內容：

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 主從同步
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
event_scheduler = 1
max_allowed_packet = 64M
# Custom config should go here
!includedir /etc/mysql/conf.d/

其中主從復制的參數(shù)解釋如下：

server-id = 1：指定MySQL實例的唯一標識ID，用于在主從復制中區(qū)分不同的MySQL實例。

log-bin = mysql-bin：啟用binlog日志，并指定binlog文件的前綴名為mysql-bin。binlog用于記錄所有的數(shù)據(jù)更改操作，以便主從服務器之間進行數(shù)據(jù)同步。

sync_binlog = 1：表示每次事務提交時都將強制把binlog緩沖區(qū)的內容寫入磁盤，確保binlog日志及時持久化，提高數(shù)據(jù)安全性。

binlog_checksum = none：設置binlog文件的校驗方式為none，表示不對binlog文件進行校驗和驗證。

binlog_format = mixed：指定binlog日志的格式為mixed，即混合模式，根據(jù)具體情況自動選擇使用statement或row格式記錄數(shù)據(jù)變更。

auto-increment-increment = 2：設置自增長字段的增量值為2，用于在主從復制中避免自增字段沖突。

auto-increment-offset = 1：設置自增長字段的偏移量為1，用于在主從復制中避免自增字段沖突。

slave-skip-errors = all：當從服務器在執(zhí)行SQL線程時發(fā)生錯誤時，跳過所有錯誤繼續(xù)執(zhí)行，這可能會導致數(shù)據(jù)不一致，謹慎使用。

event_scheduler = 1：啟用Event Scheduler，用于執(zhí)行預定的事件任務。

max_allowed_packet = 64M：設置最大允許的數(shù)據(jù)包大小為64MB，用于控制單個數(shù)據(jù)庫請求或查詢的數(shù)據(jù)包大小限制。

編寫 docker-compose.yml 文件：

version: '2.0'
services:         
  mysql:
    restart: always
    image: mysql:8.0.20
    container_name: mysql
    volumes:
      - ./data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/my.cnf
    command:
      --lower_case_table_names=1
      --character-set-server=utf8
      --sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    ports:
      - "3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=root123
      - TZ=Asia/Shanghai

啟動服務：

docker-compose up -d

下面使用客戶端工具連接該數(shù)據(jù)庫：

查看當前 master 狀態(tài)：

show master status;

注意這里查詢出來的 File 和 Position 下面主從復制時會用到。

創(chuàng)建用于主從復制的用戶：

CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica123';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

三、MySQL2 部署

同樣編寫 my.cnf 文件，寫入如下內容：

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 主從同步
server-id = 2
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors = all
event_scheduler = 1
max_allowed_packet = 64M
# Custom config should go here
!includedir /etc/mysql/conf.d/

配置和MySQL1差不多，主要注意 server-id 和 auto-increment-offset 的區(qū)別。

編寫 docker-compose.yml 文件：

version: '2.0'
services:         
  mysql:
    restart: always
    image: mysql:8.0.20
    container_name: mysql
    volumes:
      - ./data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/my.cnf
    command:
      --lower_case_table_names=1
      --character-set-server=utf8
      --sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    ports:
      - "3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=root123
      - TZ=Asia/Shanghai

啟動服務：

docker-compose up -d

下面使用客戶端工具連接該數(shù)據(jù)庫：

查看當前 master 狀態(tài)：

show master status;

同樣這里查詢出來的 File 和 Position 下面主從復制時會用到。

創(chuàng)建用于主從復制的用戶：

CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica123';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

四、配置主主復制

MySQL 1 同步 MySQL 2

在 MySQL1 中執(zhí)行：

change master to master_host='172.19.222.82',master_user='replica',master_password='replica123',master_log_file='mysql-bin.000003',master_log_pos=152;

注意：其中 master_log_file 和 master_log_pos 是上面部署 MySQL 2 時 show master status; 查詢的結果。

啟動同步進程：

start slave;

查看同步狀態(tài)：

show slave status\G

看到 Slave_IO_Running 和 Slave_SQL_Running 都為 Yes 則表示啟動成功。

MySQL 2 同步 MySQL 1

在 MySQL 2 中執(zhí)行：

change master to master_host='172.19.222.20',master_user='replica',master_password='replica123',master_log_file='mysql-bin.000003',master_log_pos=811;

其中 master_log_file 和 master_log_pos 是上面部署 MySQL 1 時 show master status; 查詢的結果。

啟動同步進程：

start slave;

查看同步狀態(tài)：

show slave status\G

同樣觀察 Slave_IO_Running 和 Slave_SQL_Running 都為 Yes 則表示啟動成功。

五、測試主主復制

首先在 MySQL1 中創(chuàng)建數(shù)據(jù)庫 testdb:

create database testdb;

然后在 MySQL 2 中查看數(shù)據(jù)庫：

show databases;

可以正常查到 MySQL 1 中創(chuàng)建的數(shù)據(jù)庫。

然后在 MySQL 2 中創(chuàng)建測試表：

use testdb;
create table `test` (
  `id` int not null auto_increment,
  `name` varchar(255) default null,
  primary key (`id`)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

接著在 MySQL 1 中查看表：

use testdb;
show tables;

可以正?？吹?MySQL 2 創(chuàng)建的表。

然后在 MySQL 1 中寫入一條測試數(shù)據(jù)：

insert into test(name) values('小明');

然后到 MySQL 2 中查看表數(shù)據(jù)：

select * from test;

可以正常查到 MySQL 1 寫入的數(shù)據(jù)。

六、配置 HAProxy 負載 MySQL 服務

經(jīng)過上面測試已經(jīng)證明主主復制功能正常，數(shù)據(jù)無論寫在哪個數(shù)據(jù)庫上都能同步給另外一臺，下面通過 HAProxy 負載均衡代理 MySQL 1 和 MySQL 2 ，對外提供統(tǒng)一的入口。

下載 HAProxy：

yum install -y haproxy

覆蓋修改 /etc/haproxy/haproxy.cfg 配制文件，注意修改 backend mysql-apiserver 下的兩個 MySQL 的 ip ：

cat > /etc/haproxy/haproxy.cfg << EOF
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon 
    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------  
defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000
#---------------------------------------------------------------------
# mysql apiserver frontend which proxys to the backends
#--------------------------------------------------------------------- 
frontend mysql-apiserver
    mode                 tcp
    bind                 *:3306
    option               tcplog
    default_backend      mysql-apiserver    
#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend mysql-apiserver
    mode        tcp
    balance     roundrobin
    server      master1   172.19.222.20:3306 check
    server      master2   172.19.222.82:3306 check
#---------------------------------------------------------------------
# collection haproxy statistics message
#---------------------------------------------------------------------
listen stats
    bind                 *:1080
    stats auth           admin:awesomePassword
    stats refresh        5s
    stats realm          HAProxy\ Statistics
    stats uri            /admin?stats
EOF

啟動haproxy

systemctl start haproxy

設置開機自啟：

systemctl enable haproxy

查看啟動狀態(tài)：

systemctl status haproxy

測試

通過 84 服務器連接 MySQL：

查看前面創(chuàng)建的數(shù)據(jù)庫、表、數(shù)據(jù)：

寫入一條數(shù)據(jù)：

insert into test(name) values("小紅");

分別到 MySQL 1和 MySQL 2查詢數(shù)據(jù)：

都可以查詢到數(shù)據(jù)，到此 MySQL 搭建雙主復制服務并通過 HAProxy 負載均衡就結束了。

到此這篇關于MySQL 搭建雙主復制服務并通過 HAProxy 負載均衡的文章就介紹到這了,更多相關MySQL 雙主復制服務內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL中explain語句的基本使用教程
這篇文章主要給大家介紹了關于MySQL中explain語句的基本使用教程，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-03-03
Mac上安裝Mysql的詳細步驟及配置
這篇文章主要給大家介紹了關于Mac上安裝Mysql的詳細步驟及配置,文中通過圖文介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
2023-02-02
MySQL UNION操作符基礎知識點
在本文里小編給大家整理了關于MySQL UNION操作符的相關知識點內容，需要的朋友們跟著學習下。
2019-02-02
MySQL權限USAGE和ALL PRIVILEGES的用法
本文主要介紹了MySQL權限USAGE和ALL PRIVILEGES的用法,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-09-09
Mysql去重的幾種方式分步講解
SQL去重是數(shù)據(jù)分析工作中比較常見的一個場景,下面這篇文章主要給大家介紹了關于SQL去重的3種實用方法的相關資料,文中通過圖文以及實例代碼介紹的非常詳細,需要的朋友可以參考下
2022-12-12
MySQL報1366錯誤的原因及解決
SQL Error1366是一個常見的 MySQL 錯誤,主要成因是數(shù)據(jù)類型不匹配或數(shù)據(jù)超出了范圍,本文主要介紹了MySQL報1366錯誤的原因及解決,感興趣的可以了解一下
2024-02-02
深入淺析Mysql聯(lián)合索引最左匹配原則
這篇文章主要介紹了Mysql聯(lián)合索引最左匹配原則,使用聯(lián)合索引的好處多多，具體內容詳情大家跟隨腳本之家小編一起學習吧
2018-06-06
深入理解Mysql事務隔離級別與鎖機制問題
MySQL默認的事務隔離級別是可重復讀，用Spring開發(fā)程序時，如果不設置隔離級別默認用MySQL設置的隔離級別，如果Spring設置了就用已設置的隔離級別，本文重點給大家介紹Mysql事務隔離級別與鎖機制的相關知識，一起看看吧
2021-09-09
MySQL數(shù)據(jù)庫服務器端核心參數(shù)詳解和推薦配置
MySQL手冊上也有服務器端參數(shù)的解釋，以及參數(shù)值的相關說明信息，現(xiàn)針對我們大家重點需要注意、需要修改或影響性能的服務器端參數(shù)，作其用處的解釋和如何配置參數(shù)值的推薦，此事情拖了不少時間，為方便大家?guī)兔m錯
2011-12-12
mysql中json_extract的具體使用
mysql5.7版本開始支持JSON類型字段,本文主要介紹了mysql中json_extract的具體使用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-05-05