软件系统运行起来后最重要的部分是数据库中存储的数据，而数据库容易因各种原因损坏，构建一个高可用的数据库集群的作用就凸显出来

工作原理和流程

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服务器在同一组中会自动保持同步状态，当某台服务器故障时，整个复制组依然可以保持正常并对外提供服务。

集群构建

准备三台主机

设置主机名

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 {
dba@163.com
}
notification_email_from dba@dbserver.com
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实现读写分离及从库负载均衡