mysql 兩主一從環境搭建（5.7.24）

搭建說明

  • 兩主一從，從本質上說，只不過是機器 master-a 和 master-b 互為主從機（熱備），然後通過 keepalived 進行高可用配置，使得在同一時間內只會有一台對外提供服務，實現單寫機制，另一個機器 slave 則作為 master-a 的從機存在（冷備）。
  • 從機 slave 無法直接將虛擬IP（VIP）作為主機：正常運行時，slave 綁定了一個主機 master-a, 一旦綁定之後，在 master-a 不可用的時候，無法自動切換到 master-b，因為需要考慮 二進位日誌文件名(master_log_file)及位置(master_log_pos）在 master-a 和 master-b 上是不一致的，此時只有在 master-a 恢復可用時，master-a 從 master-b 同步數據之後，slave 數據最終才會和 master-a 保持一致。

環境準備

  1. 三台 centos7 的伺服器，伺服器ip為 192.168.86.124(master-a)、192.168.86.125(master-b)、192.168.86.126(slave)
  2. 一個虛擬IP（VIP）：192.168.86.250
  3. mysql-5.7.24-linux-glibc2.12-x86_64 安裝包

資料庫安裝

  1. 將 mysql 安裝包上傳到三台伺服器上
  2. 解壓安裝包 
    tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
  3. 創建 mysql 安裝路徑，然後移動剛才解壓的文件夾並重命名 
    mkdir /data     #創建安裝路徑
mv mysql-5.7.24-linux-glibc2.12-x86_64 /data/mysql  #移動文件並重命名
  4. 創建 mysql 用戶和組、創建mysql運行需要的文件夾及賦權 
    groupadd mysql     #創建用戶組 
useradd -r -g mysql mysql       #創建用戶
cd /data/mysql                   #切換到安裝目錄
mkdir data                      #創建數據存放目錄
mkdir mysql-log                 #創建日誌存放目錄
mkdir mysql-log/err-log
mkdir mysql-log/slow-log
mkdir mysql-log/relay-log
mkdir mysql-log/bin-log         
chown -R root:mysql .           #將當前目錄以及子目錄，所有者改變為 mysql，所屬組修改為 mysql
chown -R mysql:mysql data
chown -R mysql:mysql mysql-log
  5. 設置用戶作業系統資源限制 
    vi /etc/security/limits.conf
# 在文件中加入以下內容
mysql   soft   nproc   2047
mysql   hard   nproc   16384
mysql   soft   nofile  1024
mysql   hard   nofile  65536
  6. 設置鏈接和服務 
    cp /data/mysql/support-files/mysql.server /etc/init.d/mysql  # 可以使用service mysql start
ln -s  /data/mysql/bin/mysql /usr/bin         #任何路徑可以使用 mysql 命令
  7. 配置 /etc/my.cnf 
    vi /etc/my.cnf
    • master-a 
      [mysqld]
port = 3306                             #埠
basedir = /data/mysql                    #mysql安裝路徑
datadir = /data/mysql/data/              #mysql數據存放路徑

#日誌設置
log-error = /data/mysql/mysql-log/err-log/db-err.log   #錯誤日誌路徑
slow-query-log-file = /data/mysql/mysql-log/slow-log/db-slow.log  #慢SQL日誌路徑
relay-log=/data/mysql/mysql-log/relay-log/relay-log
long-query-time = 20 #怎樣才算是慢sql，單位是秒

#開啟 binlog 同步
server_id = 0001                  #一個集群內的 MySQL 伺服器 ID，全局唯一
log-bin = /data/mysql/mysql-log/bin-log/db-binlog      #開啟 Binlog 並寫明存放日誌的位置
max-binlog-cache_size = 64M       #binlog 最大能夠使用cache的記憶體大小
max-binlog-size = 1G              #binlog 日誌每達到設定大小後，會使用新的 binlog 日誌
expire_logs_days = 15             #只保留最近15天的bin日誌
binlog-format = mixed             #混合模式複製
innodb_flush_log_at_trx_commit = 1 #和 sync_binlog 控制MySQL磁碟寫入策略以及數據安全性
sync-binlog = 1                  #控制資料庫的binlog刷到磁碟上去

## 主主同步配置
replicate-do-db=db_test     #需要從主庫-2同步的資料庫1
replicate-do-db=db_dev      #需要從主庫-2同步的資料庫2
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates=1

#性能調優配置
innodb_buffer_pool_size = 24576M
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet = 64M
join_buffer_size = 64M
sort_buffer_size = 2M
read_rnd_buffer_size = 16M

#SQL模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    • master-b 
      [mysqld]
port = 3306
basedir = /data/mysql
datadir = /data/mysql/data

#日誌設置
slow-query-log-file=/data/mysql/mysql-log/slow-log/db-slow.log
log-error=/data/mysql/mysql-log/err-log/db-err.log
relay-log=/data/mysql/mysql-log/relay-log/relay-log
long-query-time = 20 #怎樣才算是慢sql，單位是秒

#開啟 binlog 同步
server_id = 0002                  #一個集群內的 MySQL 伺服器 ID，全局唯一
log-bin = /data/mysql/mysql-log/bin-log/db-binlog      #開啟 Binlog 並寫明存放日誌的位置
max-binlog-cache_size = 64M       #binlog 最大能夠使用cache的記憶體大小
max-binlog-size = 1G              #binlog 日誌每達到設定大小後，會使用新的 binlog 日誌
expire_logs_days = 15             #只保留最近15天的日誌
binlog-format = mixed             #混合模式複製
innodb_flush_log_at_trx_commit = 1 #和 sync_binlog 控制MySQL磁碟寫入策略以及數據安全性
sync-binlog = 1                  #控制資料庫的binlog刷到磁碟上去

#主主設置
replicate-do-db=db_test     #需要從主庫-1同步的資料庫1
replicate-do-db=db_dev      #需要從主庫-1同步的資料庫2
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates=1

#性能調優設置
innodb_buffer_pool_size = 24576M
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet = 64M
join_buffer_size = 64M
sort_buffer_size = 2M
read_rnd_buffer_size = 16M

#SQL模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    • slave 
      [mysqld]
port = 3306
basedir = /data/mysql
datadir = /data/mysql/data

#日誌設置
expire_logs_days = 15
long-query-time = 3
slow-query-log-file=/data/mysql/mysql-log/slow-log/db-slow.log
log-error=/data/mysql/mysql-log/err-log/db-err.log

#主從設置
server_id = 0003 #這裡需要在集群中保持唯一
replicate-do-db=db_test     #需要從主庫同步的資料庫1
replicate-do-db=db_dev      #需要從主庫同步的資料庫2
read_only=1                 #只讀設置

#性能調優設置
innodb_buffer_pool_size = 24576M
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet = 64M
join_buffer_size = 64M
sort_buffer_size = 2M
read_rnd_buffer_size = 16M

#SQL模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

資料庫初始化

  1. 初始化資料庫 
     cd /data/mysql/bin/
 ./mysqld --defaults-file=/etc/my.cnf  --user=mysql --initialize  #初始化
  2. 查看密碼(初始化資料庫之後會生成一個 root 賬戶的默認密碼) 
    cat /data/mysql/mysql-log/err-log/db-err.log
  3. 啟動 mysql 
    service mysql start
  4. 登錄並修改 root 密碼 
    mysql -u root -p
set password = password('root');
flush privileges;

#如果需要使用 root 進行遠程連接，執行以下語句
use mysql;
update user set host = '%' where user = 'root';
flush privileges;
  5. 創建用戶並賦權
    • 主機 master-a、master-b 
      #創建一個用於和從機「通訊」的用戶，具有同步許可權 
grant replication slave on *.* to 'copy'@'%' identified by 'copy'
flush privileges;

#創建一個用於訪問和操作某些特定的庫的用戶，不應該讓應用直接使用 root 用戶.
grant all privileges  on db_test.* to "app_user"@'%' identified by "app_user";
flush privileges;
    • 從機 slave 
      #創建一個用於訪問和操作某些特定的庫的用戶，不應該讓應用直接使用 root 用戶.
grant all privileges  on db_test.* to "app_user"@'%' identified by "app_user";
flush privileges;

主從同步啟動

  1. 主機 master-a
    1. 在 master-b 中獲得二進位日誌文件名(master_log_file)及位置(master_log_pos) 
      show master status

      image

    2. 在 master-a 中執行以下命令 
      stop slave;     #暫停
reset slave;    #重置
change master to master_host="master-b 的IP", master_user="copy",master_password="copy",master_log_file="db-binlog.000002",master_log_pos=2532; #設置和主機同步的用戶資訊，日誌文件資訊
start slave;    #啟動
show slave status \G #查看從機狀態，主要是看 Slave_IO_Running Slave_SQL_Running 上是否都是 yes

      image

  2. 主機 master-b、從機 slave
    1. 在 master-a 中獲得二進位日誌文件名(master_log_file)及位置(master_log_pos) 
      show master status
    2. 在 master-b和 slave 中執行以下命令 
      stop slave;     #暫停
reset slave;    #重置
change master to master_host="master-a 的IP", master_user="copy",master_password="copy",master_log_file="db-binlog.000002",master_log_pos=2532; #設置和主機同步的用戶資訊，日誌文件資訊
start slave;    #啟動
show slave status \G #查看從機狀態，主要是看 Slave_IO_Running Slave_SQL_Running 上是否都是 yes

keepalived 安裝啟動（master-a、master-b）

  1. keepalived 安裝 
    yum install -y keepalived
  2. keepalived 配置 
    vi /etc/keepalived/keepalived.conf

    文件內容為：

    global_defs {
  router_id LVS_DEVEL
}

vrrp_sync_group VG_1 {
  group {
    VI_1
  }
}

vrrp_instance VI_1 {
  state BACKUP  #兩個機器都是這個值，不要設置為 MASTER,使用下面的 priority 來控制 
  interface ens33  #通過 ifconfig 查看
  virtual_router_id 51
  priority 100 #優先順序，另一台機器設置為 90 
  advert_int 1
  nopreempt  # 另一台優先順序低的機器不設置此參數！
  authentication {
    auth_type root
    auth_pass root
  }
  virtual_ipaddress {
   192.168.86.250 # 這個是VIP
  }
}

virtual_server 192.168.86.250 3306 {  # 這個是VIP
  delay_loop 2
  lb_algo rr
  lb_kind DR
  persistence_timeout 50
  protocol TCP

  real_server 192.168.86.124 3306 {   # 這是機器的IP地址
    weight 3
    notify_down /data/mysql/bin/mysql.sh
    TCP_CHECK {
      connect_timeout 5
      nb_get_retry 3
      delay_before_retry 3
      connect_port 3306
    }
  }
}
  3. 創建一個關閉 keepalived 的腳本，用於在 mysql 無法訪問時，關閉 keepalived 以釋放 VIP 
    vi /data/mysql/bin/mysql.sh

    文件內容為：

    #!/bin/sh
pkill keepalived

    將文件設置為可執行文件：

    chmod +x /data/mysql/bin/mysql.sh
  4. keepalived 啟動 
    systemctl start keepalived
  5. keepalived 其它相關命令 
    systemctl enable keepalived # 開機自啟動
systemctl start keepalived     # 啟動
systemctl stop keepalived     # 暫停
systemctl restart keepalived  # 重啟
systemctl status keepalived   # 查看狀態
tail -f /var/log/messages

測試

  1. 首先，所有機器、軟體都正常運行
    • 通過各個 IP和VIP 連接資料庫查看均能正常連接到資料庫
      image
    • master-a 獲得了 vip 
      ip addr

      image

  2. 通過 VIP 連接資料庫，之後創建一個 db_test 庫以及 tb_user表
    • master-a、master-b、slave 均存在創建的庫和表
  3. 連接 master-a 的資料庫，並新增一條數據
    • master-b、slave 均有新增的數據
  4. 連接 master-b 的資料庫，並新增一條數據
    • master-a、slave 均有新增的數據
  5. 關閉 master-a 的mysql 
    service  mysql stop  #關閉 mysql

systemctl status keepalived #查看 keepalived 狀態
    • master-a 的 keepalived 自動關閉了，原因是 mysql 關閉會觸發 keepalived 配置的 notify_down 腳本
    • master-b 獲得了 vip
  6. 通過 VIP 連接資料庫，並插入一條數據
    • master-a（無法連接）
    • master-b （存在新增數據）
    • slave（不存在新增數據，因為 master-a 掛掉了）
  7. 重啟 master-a 的 mysql 和 keepalived
    • vip 仍然在 master-b ，因為 master-a keepalived 配置了非搶佔模式 nopreempt，因此若是主機故障排除後需要將主機的keepalived重啟，然後重啟從機的keepalived，需要讓主機獲取到 VIP
    • master-a、master-b 同步了剛剛新增的數據
  8. 關閉 master-b、slave 的mysql，通過 master-a 新增一條數據，之後重啟 master-b、slave 的 mysql，以及 master-b 的 keepalived
    • master-b存在新增數據
    • slave 存在新增數據
    • vip 仍然在 master-a

常見問題

Tags:

VirMach 便宜 VPS

QNews

QNews