Mysq高可用架构MHA集群架构

Manger节点

配置邮件服务
[root@centos7-1 ~]$vim /etc/mail.rc
set from=1185673631@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=1185673631@qq.com
set smtp-auth-password=授权登录密码

[root@centos7-1 ~]$mail -s hello 1185673631@qq.com
test
.
EOT

安装包
[root@centos7-1 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@centos7-1 ~]$yum -y install mha4mysql-manager-0.56-0.el6.noarch.rpm
[root@centos7-1 ~]$ssh-keygen
[root@centos7-1 ~]$ssh-copy-id 10.0.0.7
[root@centos7-1 ~]$rsync -av .ssh 10.0.0.8:/root
[root@centos7-1 ~]$rsync -av .ssh 10.0.0.18:/root
[root@centos7-1 ~]$rsync -av .ssh 10.0.0.28:/root
[root@centos7-1 ~]$mkdir /etc/mastermha
[root@centos7-1 mastermha]$vim app1.cnf
[server default]
user=mhauser
password=tianze
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root 
repl_user=repluser
repl_password=tianze
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0 
[server1]
hostname=10.0.0.8 
[server2]
hostname=10.0.0.18
[server3]
hostname=10.0.0.28
candidate_master=1

[root@centos7-1 bin]$pwd
/usr/local/bin
[root@centos7-1 bin]$vim master_ip_failover

检查环境
[root@centos7-1 ~]$masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Tue Sep 1 15:52:53 2020 - [info] All SSH connection tests passed successfully.

root@centos7-1 ~]$masterha_check_repl --conf=/etc/mastermha/app1.cnf 
Checking the Status of the script.. OK 
Tue Sep 1 15:53:46 2020 - [info] OK.
Tue Sep 1 15:53:46 2020 - [warning] shutdown_script is not defined.
Tue Sep 1 15:53:46 2020 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

启动MHA 默认前台运行
[root@centos7-1 ~]$nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null

查看状态
[root@centos7-1 ~]$masterha_check_status --conf=/etc/mastermha/app1.cnf 
app1 (pid:11765) is running(0:PING_OK), master:10.0.0.8

排错日志
[root@centos7-1 ~]$cat /data/mastermha/app1/manager.log

 

master节点
[root@centos8-1 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@centos8-1 ~]$vim /etc/my.cnf.d/mariadb-server.cnf
##添加
[mysqld]
server-id=1
log-bin
skip_name_resolve=1
general_log

[root@centos8-1 ~]$systemctl start mariadb
[root@centos8-1 ~]$mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28204 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.000 sec)

MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'tianze';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant all on *.* to mhauser@'10.0.0.%' identified by 'tianze';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

[root@centos8-1 ~]$ifconfig eth0:1 10.0.0.100/24

查看到健康性检查
[root@centos8-1 ~]$tail -f /var/lib/mysql/centos8-1.log 
200901 15:59:07 16 Query SELECT 1 As Value
200901 15:59:08 16 Query SELECT 1 As Value
200901 15:59:09 16 Query SELECT 1 As Value
200901 15:59:10 16 Query SELECT 1 As Value
200901 15:59:11 16 Query SELECT 1 As Value
200901 15:59:12 16 Query SELECT 1 As Value
200901 15:59:13 16 Query SELECT 1 As Value
200901 15:59:14 16 Query SELECT 1 As Value
200901 15:59:15 16 Query SELECT 1 As Value
200901 15:59:16 16 Query SELECT 1 As Value
200901 15:59:17 16 Query SELECT 1 As Value
200901 15:59:18 16 Query SELECT 1 As Value

停止master 发现会自动把VIP迁移到其他从节点 提升为新主节点
[root@centos8-1 ~] systemctl stop mariadb

 

 收到告警邮件

slave节点1
[root@centos8-2 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
##添加
[mysqld]
server-id=2 
log-bin
read_only
relay_log_purge=0
skip_name_resolve=1

[root@centos8-2 ~]$systemctl start mariadb
[root@centos8-2 ~]$mysql
MariaDB [(none)]> change master to master_host='10.0.0.8',master_user='repluser',master_password='tianze',master_log_file='mariadb-bin.000002',master_log_pos=344;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave statusG

 

slave节点2
[root@centos8-3 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
##添加
[mysqld]
server-id=3
log-bin
read_only
relay_log_purge=0
skip_name_resolve=1

[root@centos8-3 ~]$systemctl start mariadb
[root@centos8-3 ~]$mysql
MariaDB [(none)]> change master to master_host='10.0.0.8',master_user='repluser',master_password='tianze',master_log_file='mariadb-bin.000002',master_log_pos=344;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave statusG

 

注：  MHA自动切换后会自动退出，然后修复节点重新加入集群中，需要删除如下文件，然后再开启MHA
删除文件
[root@centos7-1 ~]$rm -rf /data/mastermha/app1/app1.failover.complete 
[root@centos7-1 ~]$masterha_check_status --conf=/etc/mastermha/app1.cnf
tail -f /data/mastermha/app1/manager.log