MySql高可用叢集Keepalived熱備份MySQL Router負載均衡讀寫分離

軟體系統運作起來後最重要的部分是資料庫中存儲的資料，而資料庫容易因各種原因損壞，建構一個高可用的資料庫叢集的作用就凸顯出來

工作原理和流程

MySQL發展至今，在高可用性方面不斷前進，從最初的異步複制、半同步複制、群組複制，演進到現在的InnoDB Cluster和InnoDB ReplicaSet。

複制（Replication） 是本文中所有 MySQL 技術的基礎。

InnoDB 副本集（ReplicaSet） 無縫銜接其他 MySQL 官方提供的應用程式（MySQL Shell、MySQL Router），提供了另一種易于使用的程式設計方式來處理複制，屬于複制（Replication） 的簡易增強版。

組複制（Group Replication） 是一種彈性伸縮、高可用、容錯的複制拓撲，屬于複制（Replication） 的分布式高可用版本，但其本身不提供用戶端連接配接重定向、故障轉移、負載均衡等功能。

InnoDB 叢集（Cluster） 是InnoDB 副本集（ReplicaSet） 與 組複制（Group Replication） 的合成版，是InnoDB 副本集（ReplicaSet） 的高可用版，是 組複制（Group Replication） 的簡易、自動化、可程式設計的增強版。

NDB 叢集（Cluster） 是 使用由 Oracle 發行的 NDB 商業版存儲引擎的 MySQL 版本的伺服器組成的無共享架構的記憶體資料庫叢集，與标準 MySQL Server 8.0 有很多差異與限制。它适用于分布式計算環境，具有高可用、高備援的特點。

經上對比，我們采用流行、易操作的MySQL InnoDB叢集（Cluster）方案。

MySQL InnoDB叢集（Cluster）提供了一個內建的，本地的，HA解決方案。Mysq Innodb Cluster是利用組複制的 pxos 協定，保障資料一緻性，組複制支援單主模式和多主模式。

MySQL InnoDB叢集組成部分

1、MySQL Servers with Group Replication：向叢集的所有成員複制資料，同時提供容錯、自動故障轉移和彈性。MySQL Server 5.7.17或更高的版本。

2、MySQL Router：確定用戶端請求是負載平衡的，并在任何資料庫故障時路由到正确的伺服器。MySQL Router 2.1.3或更高的版本。

3、MySQL Shell：通過内置的管理API建立及管理Innodb叢集。MySQL Shell 1.0.9或更高的版本

叢集架構

名詞解釋

MGR: Mysql Group Replication 組複制，多台MySQL伺服器在同一組中會自動保持同步狀态，當某台伺服器故障時，整個複制組依然可以保持正常并對外提供服務。

叢集建構

準備三台主機

IP	主機名	角色	安裝軟體
192.168.56.21	mysql01	primary	mysql-shell,mysql-router actived,keepalived,mysql8.0.32
192.168.56.22	mysql02	secondary	mysql-shell,mysql-router backup,keepalived,mysql8.0.32
192.168.56.23	mysql03	secondary	mysql-shell,mysql8.0.32

設定主機名

cat << EOF >> /etc/hosts 
192.168.56.21 mysql01
192.168.56.22 mysql02
192.168.56.23 mysql03
EOF           

確定3台伺服器要互相通路正常，關閉防火牆

# 關閉 
systemctl stop firewalld 

# 開機禁用 
systemctl disable firewalld           

設定SSH免登入，用ssh-keygen生成公鑰，用ssh-copy-id将本地公鑰複制到遠端主機的authorized_keys檔案。每台機器都要執行，注意執行時修改遠端機器ip。

cd ~
ssh-keygen -t rsa
#一路回車
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.56.22
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.56.23           

測試免登陸在mysql01上ssh mysql03

[root@mysql01 ~]# ssh mysql03           

mysql server 8 安裝

• 檢視系統是否自帶mariadb并解除安裝（防止mysql與mariadb的檔案發生沖突）

rpm -qa | grep mariadb
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps           

• 從官網下載下傳MySQL Community Server包，并将壓縮包上傳到 /usr/local/ 路徑下，解壓

cd /usr/local
ls
tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz           

• 删除壓縮包，并将解壓的mysql檔案夾重命名為mysql

rm -rf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.32-linux-glibc2.12-x86_64/ ./mysql           

• 進入mysql檔案夾，建立data目錄

cd mysql
mkdir data           

• 建立mysql使用者群組并修改權限

groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql           

• 建立編輯my.cnf配置檔案

vim /etc/my.cnf

[client]
port = 3306

#根據實際情況調整mysql.sock配置
socket = /tmp/mysql.sock

[mysqld]
#Mysql服務的唯一編号 每個mysql服務Id需唯一
server-id = 1

#服務端口号 預設3306
port = 3306

#mysql安裝根目錄
basedir = /usr/local/mysql

#mysql資料檔案所在位置
datadir = /usr/local/mysql/data

#pid
pid-file = /usr/local/mysql/mysql.pid

#設定socke檔案所在目錄
socket = /tmp/mysql.sock

#設定臨時目錄
tmpdir = /tmp

# 使用者
user = mysql
# 允許通路的IP網段
bind-address = 0.0.0.0

#錯誤日志
log_error=/usr/local/mysql/data/mysql-error.log

#設定認證插件
default_authentication_plugin=mysql_native_password

#設定sqlmode(根據需求自定義)
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


# 跳過密碼登入
#skip-grant-tables

#主要用于MyISAM存儲引擎,如果多台伺服器連接配接一個資料庫則建議注釋下面内容
skip-external-locking

#隻能用IP位址檢查用戶端的登入，不用主機名
skip_name_resolve = 1

#資料庫預設字元集,主流字元集支援一些特殊表情符号（特殊表情符占用4個位元組）
character-set-server = utf8mb4

#資料庫字元集對應一些排序等規則，注意要和character-set-server對應
collation-server = utf8mb4_general_ci

#設定client連接配接mysql時的字元集,防止亂碼
init_connect='SET NAMES utf8mb4'

#是否對sql語句大小寫敏感，1表示不敏感
lower_case_table_names = 1

#最大連接配接數
max_connections = 400

#最大錯誤連接配接數
max_connect_errors = 1000

#TIMESTAMP如果沒有顯示聲明NOT NULL，允許NULL值
explicit_defaults_for_timestamp = true

#SQL資料包發送的大小，如果有BLOB對象建議修改成1G
max_allowed_packet = 128M

#MySQL連接配接閑置超過一定時間後(機關：秒)将會被強行關閉
#MySQL預設的wait_timeout 值為8個小時, interactive_timeout參數需要同時配置才能生效
interactive_timeout = 1800
wait_timeout = 1800

#内部記憶體臨時表的最大值 ，設定成128M。
#比如大資料量的group by ,order by時可能用到臨時表，
#超過了這個值将寫入磁盤，系統IO壓力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728

#mysql binlog日志檔案儲存的過期時間，過期後自動删除
expire_logs_days = 5           

• 進入mysql檔案夾的bin目錄下，初始化mysql

cd /usr/local/mysql/bin
./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --initialize           

• 添加mysql服務，并設定開機自啟

#檢視是是否有MySQL服務 
chkconfig --list 
#将MySQL的服務腳本放到系統服務中，/etc/init.d是 /etc/rc.d/init.d的軟連結 
cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql 
#授予可執行權限 
chmod +x /etc/rc.d/init.d/mysql 
#添加mysql服務 
chkconfig --add mysql 
#使MySQL開機自啟 
chkconfig --level 345 mysql on           

• 建立mysql下bin目錄的同步連結，設定後無需進入mysql的bin目錄就可以執行mysql指令

ln -s /usr/local/mysql/bin/mysql /usr/bin           

• 檢視mysql服務的目前狀态，啟動mysql

#檢視狀态
service mysql status
#啟動
service mysql start
#停止
service mysql stop           

• mysql服務正常啟動，我們就可以登入mysql了，可以通過mysql下data目錄下的日志檔案擷取root使用者初始化密碼，将密碼複制粘貼，我們就可以成功登入mysql了

cat /usr/local/mysql/data/mysql-error.log           

• 重置root使用者密碼并設定root使用者遠端通路

#修改root使用者密碼
alter user 'root'@'localhost' identified by '你的密碼';
use mysql;
#授權root使用者任何IP通路
update user set host = '%' where user = 'root';
#重新整理權限
FLUSH PRIVILEGES;
#檢視使用者資訊是否被修改
select host, user, authentication_string, plugin from user;           

到此mysql server 8 資料庫的安裝配置完成了！

組複制配置

• 修改各個節點/etc/my.cnf 配置 MGR, 組複制 資訊，注意每台機器server_id，loose-group_replication_local_address需要不同

server_id = 4000000161 
loose-group_replication_group_name="a38e32fd-5fb6-11e8-ad7a-00259015d941" 
loose-group_replication_local_address= "192.168.56.21:3306" 
loose-group_replication_group_seeds= "192.168.56.21:3306,192.168.56.22:3306,192.168.56.23:3306" 
loose-group_replication_single_primary_mode=TRUE           

• 給MySQL root使用者授權（才能配置InnoDB叢集執行個體）

grant all privileges on *.* to `root`@`%` with grant option;
flush privileges;           

安裝MySQLShell

安裝

從官網下載下傳MySQL Shell，将壓縮包上傳到每台機器/usr/local下，解壓、授權并配置環境變量

#解壓并配置
tar xvf mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz
#重命名
mv mysql-shell-8.0.32-linux-glibc2.12-x86-64bit mysql-shell
#授權給mysql使用者--必須
chown -R mysql.mysql mysql-shell
#設定環境變量
vi /etc/profile
export PATH=/usr/local/mysql-shell/bin:$PATH           

節點配置檢查

• 進入MySQL shell

mysqlsh           

• 在MySQL shell中執行，每台機器都要執行，注意修改主機名 mysql01 為目前節點

dba.configureInstance();
dba.checkInstanceConfiguration('root@mysql01:3306');           

建立叢集

添加叢集

在MySQL shell中

var cluster = dba.createCluster('testCluster');
cluster.addInstance('root@mysql02:3306');
cluster.addInstance('root@mysql03:3306');           

已有叢集添加機器

var cluster=dba.getCluster('testCluster'); 
cluster.addInstance('root@mysql04:3306');           

叢集搭建時克隆主服務的鏡像導緻所有節點的服務UUID都一緻，此時在叢集中添加節點時會提示UUID沖突報錯。

cluster.addInstance('root@mysql02:3306');
ERROR: RuntimeError: Cannot add an instance with the same server UUID (63e9282f-a9dd-11ed-9c7a-080027e7f5ef) of an active member of the cluster 'mysql01:3306'. Please change the server UUID of the instance to add, all members must have a unique server UUID.           

解決方案：

1、利用uuid函數生成新的uuid

mysql> select uuid();           

2、檢視配置檔案目錄

mysql> show variables like 'datadir';           

3、編輯配置檔案目錄

vim /usr/local/mysql/data/auto.cnf           

4、uuid修改新生成的uuid

[auto]
server-uuid=ec0276cb-aa1a-11ed-8076-080027e7f5ef           

5、重新開機服務

service mysql restart           

檢視叢集狀态

在MySQL shell中

var cluster=dba.getCluster('testCluster');
cluster.status();           

故障模拟測試

停掉主節點：

[root@mysql01 ~]# service mysql stop           

再到 mysql03 檢視叢集狀态，主已經切換到mysql02上

常見問題

metadata exists, instance belongs to that metadata, but GR is not active

叢集中所有伺服器重新開機後，執行指令var cluster = dba.getCluster("testCluster")時報：

Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (MYSQLSH 51314)

此時可嘗試重新開機叢集：

dba.rebootClusterFromCompleteOutage("testCluster");           

若依然不能解決，則登入MySQL，然後啟動該節點的group replication：

set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;           

擴充：

查詢組成員

SELECT * FROM performance_schema.replication_group_members;           

查詢叢集資訊

SELECT clusters.cluster_id,clusters.cluster_name from mysql_innodb_cluster_metadata.clusters;           

MySQLRouter路由

安裝

裝2台MySQL Router 做主備

從官網下載下傳MySQL Router，将壓縮包上傳到 mysql01 和 mysql02 的 /usr/local下，解壓、授權并配置環境變量

---解壓并配置
tar xvf mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz
mv mysql-router-8.0.32-linux-glibc2.12-x86_64 mysql-router
#授權 -- 必須
chown -R mysql.mysql mysql-router
---設定環境變量
vi /etc/profile
export PATH=/usr/local/mysql-shell/bin:/usr/local/mysql-router/bin:$PATH
#:wq儲存後使環境變量生效
source /etc/profile           

路由初始化,MySQL Router節點執行配置

MySQL Router主備節點

mysqlrouter --bootstrap root@localhost:3306 -d /usr/local/mysql-router/myrouter --user=root           

注：這裡會生成/usr/local/mysql-router/myrouter目錄, 并在裡面生成 mysqlrouter.conf 配置檔案，接着修改配置檔案

vi /usr/local/mysql-router/myrouter/mysqlrouter.conf           

添加以下内容

[routing:read_writer]
# 寫節點位址
bind_address=0.0.0.0
# 寫節點端口
bind_port=33061
#MySQL router提供兩種mode：read-only和read-write，設定為read-write，常用于設定destinations為master時，實作master的高可用
# 模式:讀還是寫
mode=read-write
# 主節點位址：預設情況下第一台主資料庫為寫主庫，當第一台主資料庫DOWN機後，第二台資料庫被提升為主庫
destinations=mysql01:3306,mysql02:3306,mysql03:3306
max_connections=1024
#讀節點負載均衡配置
[routing:balancing]
#綁定的IP位址
bind_address=0.0.0.0
#監聽的端口
bind_port=33062
mode=read-only
# 主節點位址：預設情況下第一台主資料庫為寫主庫，當第一台主資料庫DOWN機後，第二台資料庫被提升為主庫
destinations=mysql01:3306,mysql02:3306,mysql03:3306
max_connections=1024           

啟動服務：

# -c為指定配置檔案路徑
nohup mysqlrouter -c /usr/local/mysql-router/myrouter/mysqlrouter.conf &           

将上述指令添加到/usr/local/mysql-router/mysqlrouter-start.sh 檔案，添加軟連結，友善直接執行

ln -s /usr/local/mysql-router/mysqlrouter-start.sh /usr/bin           

之後在任意目錄都可執行，啟動mysql router

mysqlrouter-start.sh           

注：至此，叢集和router安裝結束，可以使用遠端工具連接配接

測試

管理節點本身連接配接mysqlsh：

[root@mysql01 ~]# mysqlsh --uri root@localhost:6446           

其他節點遠端登入

[root@mysql03 ~]# mysql -uroot -hmysql01 -P33061 -p           

進入資料庫後做測試

mysql> show databases;           

建立資料庫：

mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;           

在其他主機上（mysql01 mysql02 mysql03）登入資料庫發現剛剛建立的資料庫已經同步

MySQL Router主備

keepalived原理

• 在多台機器上安裝keepalived并啟動服務，初始指定一台為master，其他為backup，綁定一個虛拟IP（VIP）到網卡并向外提供，外部可以用虛拟IP通路到master。如目前示例，我們用VIP通路MySQL Router而不是實際IP。
• keepalived輪詢檢查本機的某個application運作情況，如果該application運作中突然停止，keepalived感覺到後停止目前節點（master）的keepalived服務，同時master切換到某個backup并把虛拟IP綁定到該backup的網卡，該backup更新為master。
• 這時運維人員可以檢查原master上目标服務，修複後可以重新啟動，并作為一個backup待命。

keepalived安裝

安裝前準備

yum -y install gcc gcc-c++ autoconf automake make
yum -y install zlib zlib-devel openssl openssl-devel pcre pcre-devel           

安裝

# yum 安裝
yum -y install keepalived           

啟動

# 啟動 keepalived
systemctl start keepalived 
# 加入開機啟動 keepalived
systemctl enable keepalived 
# 重新啟動 keepalived
systemctl restart keepalived 
# 檢視 keepalived 狀态
systemctl status keepalived           

配置

在MySQL Router主備節點上都建立 /usr/local/keepalived/check_mysqlrouter.sh 腳本，檢測MySQL Router是否正常執行，若沒執行就停止keepalived服務，這樣實作主備切換，内容如下：

#!/bin/bash 
CHECK_TIME=3
#mysql router is working STATUS_OK is 1 , down STATUS_OK is 0 
STATUS_OK=1
function check_mysqlrouter_health (){
MYROUTER_PROCESS=`ps -ef|grep -i mysqlrouter.conf |grep -v grep | awk '{print $2}'|wc -l `
if [[ ${MYROUTER_PROCESS} -eq 1 ]] ;then
STATUS_OK=1
else
STATUS_OK=0
fi
return $STATUS_OK
}
while [[ $CHECK_TIME -ne 0 ]]
do
let "CHECK_TIME-=1"
check_mysqlrouter_health
if [[ $STATUS_OK = 1 ]] ; then
CHECK_TIME=0
exit 0
fi
if [[ $STATUS_OK -eq 0 ]] && [[ $CHECK_TIME -eq 0 ]]
then
#不生效：耗時太長，無法執行完腳本就被迫退出
#systemctl stop keepalived.service
#以下兩種方式都可以，建議使用第2種，因為腳本未執行完被迫退出時，signal也是15，無法區分是被動停止，還是主動停止
#/usr/bin/kill -15 `cat /var/run/keepalived.pid`
kill -9 $(ps -ef | grep [k]eepalived)
exit 1
fi
sleep 1
done           

注意：我們使用 kill -9 $(ps -ef | grep [k]eepalived) 停止keepalived服務，原因見上述代碼說明。

備份 /etc/keepalived/keepalived.conf，重新建立一個 keepalived.conf 輸入内容如下：

global_defs {
notification_email {
[email protected]
}
notification_email_from [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYROUTER-HA
enable_script_security
script_user root
}
vrrp_script check_running
{
script "/usr/local/keepalived/check_mysqlrouter.sh"
#輪詢間隔要大于check_mysqlrouter.sh腳本執行時間
interval 3
weight 20
fall 2
}
vrrp_instance VI_1 {
#主節點配置，備份節點修改為 BACKUP
state MASTER
#虛拟IP綁定的網卡名稱
interface enp0s3
virtual_router_id 51
priority 98
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1024
}
#虛拟IP位址
virtual_ipaddress {
192.168.56.20
}
#執行的腳本，對應 vrrp_script
track_script {
check_running
}
}           

注意：

• 修改綁定的網卡名稱 enp0s3 為實際網卡名稱
• 修改虛拟IP位址 192.168.56.20 為實際網段内未被占用的IP
• 修改備份節點的 state MASTER 為 state BACKUP
• 輪詢間隔 interval 3 要大于 check_mysqlrouter.sh 腳本執行時間，以免腳本無法正确執行完

keepalived測試

• 執行 mysqlrouter-start.sh 啟動主備節點的MySQL Router服務
• 執行 systemctl start keepalived 啟動主備節點的keepalived服務
• 主備節點輸入 ip a 指令，看到虛拟IP綁定到了mysql01主節點

• 在 mysql03 節點輸入 mysql -h 192.168.56.20 -uroot -P33061 -p 通過虛拟IP通路MySQL Router
• 執行 show databases; 可以看到MySQL叢集中的資料庫

• 關閉主節點 mysql01 的MySQL Router服務，主節點keepalived服務自動停止

• 主備節點輸入 ip a 指令，看到虛拟IP綁定到了mysql02備節點，實作切換備份

• 在 mysql03 節點輸入 mysql -h 192.168.56.20 -uroot -P33061 -p 通過虛拟IP仍然可通路MySQL Router

讀節點負載均衡測試

在 mysql03 節點輸入 mysql -h 192.168.56.20 -uroot -P33062 -p密碼 -e "show variables like 'hostname';" 可以看到切換到了不同的主機

常見問題

check_mysqlrouter.sh exited due to signal 15

問題描述：關閉主節點 mysql01 的MySQL Router服務，mysql01 的keepalived服務不會停止，使用 systemctl status keepalived 指令檢視可以看到keepalived服務仍在運作，并提示 check_mysqlrouter.sh exited due to signal 15

● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2023-02-15 18:08:22 CST; 2min 43s ago
Process: 10945 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 10946 (keepalived)
Tasks: 6
CGroup: /system.slice/keepalived.service
├─10946 /usr/sbin/keepalived -D
├─10947 /usr/sbin/keepalived -D
├─10948 /usr/sbin/keepalived -D
├─12652 /usr/sbin/keepalived -D
├─12653 /bin/bash /usr/local/keepalived/check_mysqlrouter.sh
└─12662 sleep 3
2月 15 18:10:17 mysql01 Keepalived_vrrp[10948]: /usr/local/keepalived/check_mysqlrouter.sh exited due to signal 15           

問題原因：keepalived.conf 中配置 check_mysqlrouter.sh 腳本執行間隔 interval 2 如果腳本不能在間隔時間内執行完，會被動退出而不再執行，例如我們在腳本中用 systemctl stop keepalived.service 指令停止keepalived服務，而此指令并不能在2秒内執行完keepalived會退出該腳本的執行，進入下一次輪詢，造成 check_mysqlrouter.sh 腳本一直沒正确執行完成，進而keepalived服務無法停止。

問題解決：

• 使用 kill -9 $(ps -ef | grep [k]eepalived) 快速停止keepalived服務。
• interval 2 改成 interval 3 增加腳本執行間隔時間，讓腳本有足夠時間執行完本次再進行下一次。

參考文獻

• Centos7 安裝 Mysql8（解壓版）
• MySQL innodb cluster安裝部署-MySQL Server、MySQL Shell、MySQL Router全過程
• MySQL-InnodbCluster安裝部署-KeepAlived實作MySQLRouter的高可用
• MySQL 8.0.18 InnoDB Cluster 主從（MGR）完整安裝配置
• Keepalived--02--安裝和解除安裝
• MySQL Router單點隐患通過Keepalived實作
• 一分鐘了解nohup和&的功效
• keepalived的script腳本不執行解決辦法
• keepalived+nginx遇到的幾個問題
• MySQL Route負載均衡與讀寫分離Docker環境使用
• mysql router負載均衡_Centos7部署MySQL-router實作讀寫分離及從庫負載均衡