目录
一,MHA简介
1,MHA概述
一套优秀的MySQL高可用环境下故障切换和主从复制软件
MHA的出现就是解决MySQL单点故障的问题
MySQL故障过程中,MHA能做到0-30秒内自动完成故障切换
MHA能在故障切换过程中最大程度上能保证数据的一致性,以达到真正意义上的高可用
MHA的组成:
MHA Manager (管理节点)
MHA Node (数据节点)
MHA的特点:
自动切换过程中,MHA试图从宕机的主服务器上保存二进制日志,最大程度的保证数据不丢失
使用半同步复制,可以大大降低数据丢失的风险
目前MHA支持一主多从架构,最少三台服务器,即一主两从
MHA:Master High Availability,对主节点进行监控,可实现自动故障转移至其它节点;通过提升某一节点为新的主节点,基于主从复制实现,还需要客户端配合实现。
2,MHA集群架构
3,MHA工作过程
1,从宕机崩溃的master保存二进制日志事件(binlog events)
2,识别含有最新更新的slave
3,应用差异的中继日志(relay log)到其他的slave
4,应用从master保存二进制事件(binlog events)
5,提升一个slave为新的master
6,是其他的slave连接新的master进行复制
注:为了尽可能的减少主库硬件损坏宕机造成的数据丢失,因此在配置MHA的同时建议配置成MySQL的半同步复制
二,MHA高可用配置
在管理节点上安装两个包mha4mysql-manager和mha4mysql-node
说明:mha4mysql-manager-0.56-0.el6.noarch.rpm不支持centos8,只支持centos7以下版本
说明:mha4mysql-manager-0.58-0.el7.cetos.noarch.rpm,支持mysql5.7,但和centos8版本上的Mariadb-10.3.17不谦容
1,环境准备
master服务器:192.168.18.100 mysql5.7
slave1服务器:192.168.18.91 mysql5.7
slave2服务器:192.168.18.93 mysql5.7
MHA manager(管理节点服务器):192.168.18.90
关闭防火墙和selinux(所有机器都需要)
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
为了便于操作修改 Master、Slave1、Slave2 节点的主机名
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# su
[root@master ~]#
[root@localhost ~]# hostnamectl set-hostname slave1
[root@localhost ~]# su
[root@slave1 ~]#
[root@localhost ~]# hostnamectl set-hostname slave2
[root@localhost ~]# su
[root@slave2 ~]#
每一台节点服务器添加域名解析
[root@master ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.18.100 master
192.168.18.91 slave1
192.168.18.93 slave2
[root@slave1 ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.18.100 master
192.168.18.91 slave1
192.168.18.93 slave2
[root@slave2 ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.18.100 master
192.168.18.91 slave1
192.168.18.93 slave2
2,主从同步
修改 Master、Slave1、Slave2 节点的 Mysql主配置文件/etc/my.cnf
三台服务器的 server-id 不能一样
master节点配置
[root@master ~]# vim /etc/my.cnf
prompt=(\\u@\\h) [\\d]>\\_
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log_bin = master-bin
log-slave-updates = true
:wq
[root@master ~]# systemctl restart mysqld.service ##重启mysql服务
slave1节点配置
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 2
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
:wq
[root@slave1 ~]# systemctl restart mysqld.service ##重启mysql服务
slave2节点配置
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 3
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
:wq
[root@slave2 ~]# systemctl restart mysqld.service #重启mysql服务
做软链接设置每一台节点服务器都需要做
[root@master ~]# ln -s /usr/local/mysql/bin/{mysql,mysqlbinlog} /usr/sbin/
[root@master ~]# ls /usr/sbin/mysql*
/usr/sbin/mysql /usr/sbin/mysqlbinlog
[root@master ~]#
[root@slave1 ~]# ln -s /usr/local/mysql/bin/{mysql,mysqlbinlog} /usr/sbin/
[root@slave1 ~]# ls /usr/sbin/mysql*
/usr/sbin/mysql /usr/sbin/mysqlbinlog
[root@slave1 ~]#
[root@slave2 ~]# ln -s /usr/local/mysql/bin/{mysql,mysqlbinlog} /usr/sbin/
[root@slave2 ~]# ls /usr/sbin/mysql*
/usr/sbin/mysql /usr/sbin/mysqlbinlog
[root@slave2 ~]#
登录数据库授权主从用户(每一台节点服务器都需要操作)
[root@master ~]# mysql -uroot -p123123 ##登录数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
##授权主从
(root@localhost) [(none)]> grant replication slave on *.* to 'myslave'@'192.168.18.%' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'192.168.18.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.01 sec)
(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'master' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.01 sec)
(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [(none)]> flush privileges; ##刷新配置
Query OK, 0 rows affected (0.00 sec)
查看主节点
(root@localhost) [(none)]> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+----