搭建MySQL高可用架构MHA

  环境准备

1、修改主机名

hostnamectl set-hostname manager

更新host文件

vim /etc/hosts

替换127.0.0.1后面的主机名为对应的主机名称

127.0.0.1   master

2、关闭防火墙，关闭Selinux

systemctl stop firewalld  #临时关闭
systemctl disable firewalld  #永久关闭

setenforce 0  #临时关闭
sed -i '/SELINUX/s/enforcing/disabled/' /etc/selinux/config  #永久关闭

3、配置hosts文件

cat >> /etc/hosts <<EOF
192.168.5.54 manager
192.168.5.116 master
192.168.5.103 slave1
192.168.5.194 slave2
EOF

4、配置免密登录

ssh-keygen -t rsa 
ssh-copy-id -i /root/.ssh/id_rsa.pub root@manager
ssh-copy-id -i /root/.ssh/id_rsa.pub root@master
ssh-copy-id -i /root/.ssh/id_rsa.pub root@slave1
ssh-copy-id -i /root/.ssh/id_rsa.pub root@slave2

验证

ssh manager 'hostname'
ssh master  'hostname'
ssh slave1  'hostname'
ssh slave2  'hostname'

5、配置各MySQL节点

vim /etc/my.cnf

修改以下相关参数，server-id值各库保持不一致即可

[mysqld]
#复制集群中的各节点的id均必须唯一
server-id = 100
#开启中继日志            
log-bin = mysql-bin  
#开启二进制日志   
relay-log=relay-bin           
#关闭名称解析（非必须）
skip_name_resolve            
#使得更新的数据写进二进制日志中
log-slave-updates = 1

重启mysql服务

systemctl restart mysqld

6、新建共同账号

在3台mysql节点，新建用于复制的账号

use mysql;
create user 'repl'@'%' identified by 'Test@1234';
grant replication slave on *.*  to 'repl'@'%';
flush privileges;

6、配置一主多从复制架构

master节点

show master status;

slave1和slave2节点

change master to master_host='192.168.5.116',
master_port=3306,
master_user='repl',
master_password='Test@1234',
master_log_file='mysql-bin.000001',
master_log_pos=862;
start slave;

启动slave问题处理：Slave failed to initialize relay log info structure from the repository

错误原因：从库之前已有relay log

解决方法：
使用RESET SLAVE语句，清除master信息和relay日志的信息，删除所有的relay日志文件，并开始创建一个全新的中继日志

stop slave;
reset slave;

 在读库节点上，查询运行状态

show slave status G

7、新建用户mha管理的账号

只用在master节点执行

use mysql;
create user  'mhaadmin'@'192.168.%.%'  identified by 'Test@1234';
grant all on *.*  to 'mhaadmin'@'192.168.%.%' ;
flush privileges;

8、安装MHA包

四台机器上都要安装node包

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
yum install -y epel-release 
yum install -y perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

manager节点

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
yum install -y epel-release 
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

9、创建Manager相关目录与配置文件

mkdir -p /etc/mha
mkdir -p /var/log/mha/app1
vim /etc/mha/app1.cnf

[server default]                        
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mhaadmin
password=Test@1234
ping_interval=2
repl_user=repl
repl_password=Test@1234
ssh_user=root

[server1]
hostname=192.168.5.116
port=3306

[server2]
hostname=192.168.5.103
port=3306

[server3]
hostname=192.168.5.194
port=3306

10、检查

检查ssh免密

masterha_check_ssh --conf=/etc/mha/app1.cnf

检查MySQL复制状态

masterha_check_repl --conf=/etc/mha/app1.cnf

11、启动MHA服务

nohup masterha_manager --conf=/etc/mha/app1.cnf  &> /var/log/mha/app1/manager.log &
masterha_check_status --conf=/etc/mha/app1.cnf