mysql mha高可用集群搭建

mha集群搭建

在 MySQL 高可用架构中，MHA（Master High Availability）通常采用一主多从的架构。

MHA 可以提供主从复制架构的自动 master failover 功能。当主服务器出现故障时，MHA 会将从服务器提升为新的主服务器，并在此期间通过与其他从服务器获取额外信息来避免一致性问题。

主从从部署

基本环境准备

yum源配置

cd /etc/yum.repos.d
mkdir bak
mv *.repo ./bak
cd /etc/yum.repos.d
wget -O CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum clean all
yum makecache

修改主机名和host文件解析

hostnamectl set-hostname master1 && bash
hostnamectl set-hostname slave1 && bash
hostnamectl set-hostname slave2 && bash

cat /etc/hosts
192.168.3.74 master1
192.168.3.75 slave1
192.168.3.76 slave2

配置3台数据库主机之间ssh免密登录

ssh-keygen
ssh-cpoy-id 192.168.3.74
ssh-cpoy-id 192.168.3.75
ssh-cpoy-id 192.168.3.76

安装mysql

删除原有的mariadb

yum remove mariadb* -y

添加源

cat <<EOF > /etc/yum.repos.d/mysql57.repo
[mysql57]
name=mysql57
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
gpgcheck=0
enabled=1
EOF

yum install mysql-community-server -y

启动mysql服务，查看初始密码

systemctl enable mysqld --now

cat /var/log/mysqld.log |grep password
2024-09-19T08:36:50.471114Z 1 [Note] A temporary password is generated for root@localhost: :Q,)akd#i8U+

使用初始密码登录，修改mysql的root账号初始密码

alter user 'root'@'localhost' identified with mysql_native_password by "aaA...111";

flush privileges;

也可以使用mysqladmin修改密码

mysqladmin -uroot -p"e#)er)sg?39A"  password aaA...111

主从配置

修改my.cnf配置文件（主）

vim /etc/my.cnf

server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = true

修改my.cnf配置文件（从1）

vim /etc/my.cnf

server-id = 2
log-bin = mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = true

修改my.cnf配置文件（从2）

vim /etc/my.cnf

server-id = 3
log-bin = mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = true

重启服务

systemctl restart mysqld

master1创建MHA用于监控的账号（mha）和主从同步账号（repl）

# 创建主从同步账号repl
create user 'repl'@'192.168.%.%' identified with mysql_native_password by 'repl1234A...';

# 授权
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%';

# 刷新权限，生效
flush privileges;

# 查看用户信息
select user,host from mysql.user;

# 查看授权
mysql> show grants for 'repl'@'192.168.%.%';
+--------------------------------------------------------+
| Grants for repl@192.168.%.%                            |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)

# 创建用于mha高可用账号mha
create user 'mha'@'192.168.%.%' identified with mysql_native_password by 'mhA1234...';

# 授权
grant all privileges on *.* to 'mha'@'192.168.%.%';

# 刷新权限，生效
flush privileges;

# 查看用户信息
mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| mha           | 192.168.%.% |
| repl          | 192.168.%.% |
|