基于MHA实现MySQL主节点高可用

最新推荐文章于 2023-06-09 16:13:28 发布

zhl52306

最新推荐文章于 2023-06-09 16:13:28 发布

阅读量486

点赞数

CC 4.0 BY-SA版权

文章标签： mysql linux 运维

本文链接：https://blog.csdn.net/zhl52306/article/details/129178854

环境:四台主机

10.0.0.61 CentOS7 MHA管理端

10.0.0.4 CentOS8 MySQL8.0 Master

10.0.0.5 CentOS8 MySQL8.0 Slave1

10.0.0.6 CentOS8 MySQL8.0 Slave2

在管理节点上安装两个包mha4mysql-manager和mha4mysql-node

说明:

mha4mysql-manager-0.58-0.el7.centos.noarch.rpm 只支持CentOS7上安装,不支持在CentOS8安装,支持MySQL5.7和MySQL8.0 ,但和CentOS8版本上的Mariadb-10.3.17不兼容

mha4mysql-manager-0.56-0.el6.noarch.rpm 不支持CentOS 8，只支持CentOS7及以下版本

两个安装包

mha4mysql-manager

mha4mysql-node

#下载

https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58

MHA的管理节点必须是CentOS7

#采用yum安装离线包的方式，解决依赖问题，安装manage、node两个节点

[root@mha-manager ~]#yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Dependencies Resolved

=====================================================================================================================

Package Arch Version Repository Size

=====================================================================================================================

Installing:

mha4mysql-manager noarch 0.58-0.el7.centos /mha4mysql-manager-0.58-0.el7.centos.noarch 328 k

mha4mysql-node noarch 0.58-0.el7.centos /mha4mysql-node-0.58-0.el7.centos.noarch 106 k

Installing for dependencies:

perl-Class-Load noarch 0.20-3.el7 base 27 k

perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k

perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k

perl-Config-Tiny noarch 2.14-7.el7 base 25 k

perl-DBD-MySQL x86_64 4.023-6.el7 base 140 k

perl-DBI x86_64 1.627-4.el7 base 802 k

perl-Data-Dumper x86_64 2.145-3.el7 base 47 k

perl-Data-OptList noarch 0.107-9.el7 base 23 k

perl-Email-Date-Format noarch 1.002-15.el7 epel 17 k

perl-IO-Compress noarch 2.061-2.el7 base 260 k

perl-IO-Socket-IP noarch 0.21-5.el7 base 36 k

perl-IO-Socket-SSL noarch 1.94-7.el7 base 115 k

perl-List-MoreUtils x86_64 0.33-9.el7 base 58 k

perl-Log-Dispatch noarch 2.41-1.el7.1 epel 82 k

perl-MIME-Lite noarch 3.030-1.el7 epel 96 k

perl-MIME-Types noarch 1.38-2.el7 epel 38 k

perl-Mail-Sender noarch 0.8.23-1.el7 epel 59 k

perl-Mail-Sendmail noarch 0.79-21.el7 epel 29 k

perl-MailTools noarch 2.12-2.el7 base 108 k

perl-Module-Implementation noarch 0.06-6.el7 base 17 k

perl-Module-Runtime noarch 0.013-4.el7 base 19 k

perl-Mozilla-CA noarch 20130114-5.el7 base 11 k

perl-Net-Daemon noarch 0.48-5.el7 base 51 k

perl-Net-LibIDN x86_64 0.12-15.el7 base 28 k

perl-Net-SMTP-SSL noarch 1.01-13.el7 base 9.1 k

perl-Net-SSLeay x86_64 1.55-6.el7 base 285 k

perl-Package-DeprecationManager noarch 0.13-7.el7 base 18 k

perl-Package-Stash noarch 0.34-2.el7 base 34 k

perl-Package-Stash-XS x86_64 0.26-3.el7 base 31 k

perl-Parallel-ForkManager noarch 1.18-2.el7 epel 28 k

perl-Params-Util x86_64 1.07-6.el7 base 38 k

perl-Params-Validate x86_64 1.08-4.el7 base 69 k

perl-PlRPC noarch 0.2020-14.el7 base 36 k

perl-Sub-Install noarch 0.926-6.el7 base 21 k

perl-Sys-Syslog x86_64 0.33-3.el7 base 42 k

perl-TimeDate noarch 1:2.30-2.el7 base 52 k

perl-Try-Tiny noarch 0.12-2.el7 base

配置邮件客户端

[root@mha-manager ~]#vim /etc/mail.rc

setfrom=785065903@qq.com

setsmtp=smtp.qq.com

setsmtp-auth-user=785065903@qq.com

setsmtp-auth-password=opkaroczdvusbbig

setsmtp-auth=login

setssl-verify=ignore

#重启邮件服务

[root@mha-manager ~]#systemctl restart postfix

在所有MySQL服务器上安装mha4mysql-node包

此包支持CentOS 8，7，6

mha4mysql-node

#主从节点安装node包

[root@master ~]#yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Repository extras is listed more than once in the configuration

Last metadata expiration check: 3:15:41 ago on Tue 15 Nov 202204:51:35 PM CST.

Dependencies resolved.

=====================================================================================================================

Package Architecture Version Repository Size

=====================================================================================================================

Installing:

mha4mysql-node noarch 0.58-0.el7.centos @commandline 35 k

Installing dependencies:

mariadb-connector-c x86_64 3.1.11-2.el8_3 appstream 199 k

mariadb-connector-c-config noarch 3.1.11-2.el8_3 appstream 14 k

perl-Carp noarch 1.42-396.el8 baseos 29 k

perl-DBD-MySQL x86_64 4.046-3.module+el8.6.0+904+ef468285 appstream 155 k

perl-DBI x86_64 1.641-4.module+el8.6.0+891+677074cb appstream 739 k

perl-Data-Dumper x86_64 2.167-399.el8 baseos 57 k

perl-Digest noarch 1.17-395.el8 appstream 26 k

perl-Digest-MD5 x86_64 2.55-396.el8 appstream 36 k

perl-Encode x86_64 4:2.97-3.el8 baseos 1.5 M

perl-Errno x86_64 1.28-421.el8 baseos 75 k

perl-Exporter noarch 5.72-396.el8 baseos 33 k

perl-File-Path noarch 2.15-2.el8 baseos 37 k

perl-File-Temp noarch 0.230.600-1.el8 baseos 62 k

perl-Getopt-Long noarch 1:2.50-4.el8 baseos 62 k

perl-HTTP-Tiny noarch 0.074-1.el8 baseos 57 k

perl-IO x86_64 1.38-421.el8 baseos 141 k

perl-MIME-Base64 x86_64 3.15-396.el8 baseos 30 k

perl-Math-BigInt noarch 1:1.9998.11-7.el8 baseos 194 k

perl-Math-Complex noarch 1.59-421.el8 baseos 108 k

perl-Net-SSLeay x86_64 1.88-2.module+el8.6.0+957+15d660ad appstream 378 k

perl-PathTools x86_64 3.74-1.el8 baseos 89 k

perl-Pod-Escapes noarch 1:1.07-395.el8 baseos 19 k

perl-Pod-Perldoc noarch 3.28-396.el8 baseos 85 k

perl-Pod-Simple noarch 1:3.35-395.el8 baseos 212 k

perl-Pod-Usage noarch 4:1.69-395.el8 baseos 33 k

perl-Scalar-List-Utils x86_64 3:1.49-2.el8 baseos 67 k

perl-Socket x86_64 4:2.027-3.el8 baseos 58 k

perl-Storable x86_64 1:3.11-3.el8 baseos 97 k

perl-Term-ANSIColor noarch 4.06-396.el8 baseos 45 k

perl-Term-Cap noarch 1.17-395.el8 baseos 22 k

perl-Text-ParseWords noarch 3.30-395.el8 baseos 17 k

perl-Text-Tabs+Wrap noarch 2013.0523-395.el8 baseos 23 k

perl-Time-Local noarch 1:1.280-1.el8 baseos 32 k

perl-URI noarch 1.73-3.el8 appstream 115 k

perl-Unicode-Normalize x86_64 1.25-396.el8 baseos 81 k

perl-constant noarch 1.33-396.el8 baseos 24 k

perl-interpreter x86_64 4:5.26.3-421.el8 baseos 6.3 M

perl-libnet noarch 3.11-3.el8 appstream 120 k

perl-libs x86_64 4:5.26.3-421.el8 baseos 1.6 M

perl-macros x86_64 4:5.26.3-421.el8 baseos 71 k

perl-parent noarch 1:0.237-1.el8 baseos 19 k

perl-podlators noarch 4.11-1.el8 baseos 117 k

perl-threads x86_64 1:2.21-2.el8 baseos 60 k

perl-threads-shared x86_64 1.58-2.el8 baseos 47 k

Installing weak dependencies:

perl-IO-Socket-IP noarch 0.39-5.el8 appstream 46 k

perl-IO-Socket-SSL noarch 2.066-4.module+el8.6.0+957+15d660ad appstream 297 k

perl-Mozilla-CA noarch 20160104-7.module+el8.6.0+965+850557f9 appstream 14 k

Enabling module streams:

perl 5.26

perl-DBD-MySQL 4.046

perl-DBI 1.641

perl-IO-Socket-SSL 2.066

perl-libwww-perl 6.34

Transaction Summary

=====================================================================================================================

Install 48 Packages

Total size: 14 M

Total download size: 13 M

Installed size: 39 M

Is this ok [y/N]:

[root@slave1 ~]#yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@slave2 ~]#yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@master ~]#rpm -ql mha4mysql-node

/usr/bin/apply_diff_relay_logs

/usr/bin/filter_mysqlbinlog

/usr/bin/purge_relay_logs

/usr/bin/save_binary_logs

/usr/share/man/man1/apply_diff_relay_logs.1.gz

/usr/share/man/man1/filter_mysqlbinlog.1.gz

/usr/share/man/man1/purge_relay_logs.1.gz

/usr/share/man/man1/save_binary_logs.1.gz

/usr/share/perl5/vendor_perl/MHA/BinlogHeaderParser.pm

/usr/share/perl5/vendor_perl/MHA/BinlogManager.pm

/usr/share/perl5/vendor_perl/MHA/BinlogPosFindManager.pm

/usr/share/perl5/vendor_perl/MHA/BinlogPosFinder.pm

/usr/share/perl5/vendor_perl/MHA/BinlogPosFinderElp.pm

/usr/share/perl5/vendor_perl/MHA/BinlogPosFinderXid.pm

/usr/share/perl5/vendor_perl/MHA/NodeConst.pm

/usr/share/perl5/vendor_perl/MHA/NodeUtil.pm

/usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm

在所有节点实现相互之间 ssh key 验证

#这里我用脚本直接实现，或者参考如下配置实现：

[root@mha-manager ~]#ssh-keygen

[root@mha-manager ~]#ssh-copy-id 127.0.0.1

[root@mha-manager ~]#rsync -av .ssh 10.0.0.8:/root/

[root@mha-manager ~]#rsync -av .ssh 10.0.0.18:/root/

[root@mha-manager ~]#rsync -av .ssh 10.0.0.28:/root/

#执行脚本实现节点间免密登录

[root@mha-manager ~]#bash ssh_key_push.sh

Dependencies Resolved

===========================================================================================

Package Arch Version Repository Size

===========================================================================================

Installing:

sshpass x86_64 1.06-2.el7 extras 21 k

Transaction Summary

===========================================================================================

Running transaction

Installing : sshpass-1.06-2.el7.x86_64 1/1

Verifying : sshpass-1.06-2.el7.x86_64 1/1

Installed:

sshpass.x86_64 0:1.06-2.el7

Complete!

Generating public/private rsa key pair.

Created directory '/root/.ssh'.

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:01Cjj4/x/gOs1MjH/v469edb7o2toLxCeDEUt30yYJs root@mha-manager

The key's randomart image is:

+---[RSA 2048]----+

| ..* |

| .= B |

| .o E + . |

| o= + |

| oSBo |

| . *** . |

| +.+o... ..|

| oo..o. +=|

| .====+=O|

+----[SHA256]-----+

root@10.0.0.4's password:

10.0.0.4 [ OK ]

root@10.0.0.5's password:

10.0.0.5 [ OK ]

root@10.0.0.6's password:

10.0.0.6 [ OK ]

root@10.0.0.7's password:

10.0.0.7 [ OK ]

root@10.0.0.11's password:

10.0.0.11 [ OK ]

root@10.0.0.61's password:

10.0.0.61 [ OK ]

在管理节点建立配置文件

注意: 此文件的行尾不要加空格等符号

[root@mha-manager ~]#mkdir /etc/mastermha

[root@mha-manager ~]#vim /etc/mastermha/app1.cnf

[server default]

user=mhauser #用于远程连接MySQL所有节点的用户,需要有管理员的权限

password=123456

manager_workdir=/data/mastermha/app1/ #目录会自动生成,无需手动创建

manager_log=/data/mastermha/app1/manager.log

remote_workdir=/data/mastermha/app1/

ssh_user=root #用于实现远程ssh基于KEY的连接,访问二进制日志

repl_user=repluser #主从复制的用户信息

repl_password=123456

ping_interval=1#健康性检查的时间间隔,检查命令:SELECT 1 As Value

master_ip_failover_script=/usr/local/bin/master_ip_failover #切换VIP的perl脚本,不支持跨网络,也可用Keepalived实现

report_script=/usr/local/bin/sendmail.sh #当执行报警脚本

check_repl_delay=0#默认值为1,表示如果slave中从库落后主库relay log超过100M，主库不会选择这个从库为新的master，因为这个从库进行恢复需要很长的时间.通过设置参数check_repl_delay=0，mha触发主从切换时会忽略复制的延时，对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master

master_binlog_dir=/data/mysql/ #指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定

[server1]

hostname=10.0.0.4

port=3306

candidate_master=1

[server2]

hostname=10.0.0.5

port=3306

[server3]

hostname=10.0.0.6

port=3306

candidate_master=1 #设置为优先候选master，即使不是集群中事件最新的slave,也会优先当master

#最终文件内容

[server default]

user=mhauser

password=123456

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=123456

ping_interval=1

master_ip_failover_script=/usr/local/bin/master_ip_failover

report_script=/usr/local/bin/sendmail.sh

check_repl_delay=0

master_binlog_dir=/data/mysql/

[server1]

hostname=10.0.0.4

port=3306

candidate_master=1

[server2]

hostname=10.0.0.5

port=3306

[server3]

hostname=10.0.0.6

port=3306

candidate_master=1

说明: 主库宕机谁来接管新的master

1. 所有从节点日志都是一致的，默认会以配置文件的顺序去选择一个新主

2. 从节点日志不一致，自动选择最接近于主库的从库充当新主

3. 如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。但是此节点日志量落后主库超过100M日志的话，也不会被选择。可以配合check_repl_delay=0，关闭日志量的检查，强制选择候选节点

实现Master

#部署数据库，可参考前面文章完成数据库部署

#配置数据库配置文件my.cnf

[root@master ~]#vim /etc/my.cnf

[mysqld]

server-id=4

log-bin

skip_name_resolve=1 #禁止反向域名解析

general_log #通用日志，观察结果使用,非必须项,生产无需启用

gtid_mode=on #开启gtid

enforce_gtid_consistency

#重启服务

[root@master ~]#systemctl restart mysqld.service

#创建主从同步复制权限账号，并赋权

mysql> create user repluser@'10.0.0.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repluser@'10.0.0.%';

Query OK, 0 rows affected (0.00 sec)

#创建mha管理权限账号，并赋权

mysql> create user mhauser@'10.0.0.%' identified by '123456';

Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to mhauser@'10.0.0.%';

Query OK, 0 rows affected (0.00 sec)

备注：

#如果是MySQL8.0以前版本执行下面操作，创建账户以及赋权

mysql>grant replication slave on *.* to repluser@'10.0.0.%' identified by

'123456';

mysql>grant all on *.* to mhauser@'10.0.0.%' identified by '123456';

#配置VIP

[root@master ~]#ifconfig eth0:1 10.0.0.100/24

[root@master ~]#ip a

................................

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000

link/ether 00:0c:29:9d:d5:be brd ff:ff:ff:ff:ff:ff

inet 10.0.0.4/24 brd 10.0.0.255 scope global noprefixroute eth0

valid_lft forever preferred_lft forever

inet 10.0.0.100/24 brd 10.0.0.255 scope global secondary eth0:1

valid_lft forever preferred_lft forever

inet6 fe80::20c:29ff:fe9d:d5be/64 scope link

valid_lft forever preferred_lft forever

实现slave

#部署数据库，可参考前面文章完成数据库部署

#配置从节点1

[root@slave1 ~]#vim /etc/my.cnf

[mysqld]

server-id=5 #不同节点此值各不相同

log-bin

read-only

relay_log_purge=0 #禁止自动删除不再使用的relay Log,可用于日志同步，默认值是1，会定期清理，如果清理了relay log，则会影响后期切换数据的还原，在mha环境，需禁用该项

skip_name_resolve=1#禁止反向解析,此项可选

general_log #通用日志，方便观察的设置,生产无需启用

gtid_mode=on #开启gtid

enforce_gtid_consistency

#重启mysql服务

[root@slave1 ~]#systemctl restart mysqld.service

#配置主从复制信息

CHANGEMASTERTO

MASTER_HOST='10.0.0.4',

MASTER_USER='repluser',

MASTER_PASSWORD='123456',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1; #启用gtid

#开启slave

mysql>STARTSLAVE;

#查看复制状态

mysql>showslavestatus\G

***************************1.row***************************

Slave_IO_State: Waiting forsourceto send event

Master_Host:10.0.0.4

Master_User: repluser

Master_Port:3306

Connect_Retry:60

Master_Log_File:master-bin.000006

Read_Master_Log_Pos:717

Relay_Log_File: slave1-relay-bin.000008

Relay_Log_Pos:463

Relay_Master_Log_File:master-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master_Server_Id:4

Master_UUID:210892f2-641e-11ed-a2cb-000c299dd5be

Master_Info_File: mysql.slave_master_info

SQL_Delay:0

SQL_Remaining_Delay:NULL

Slave_SQL_Running_State: Replica has readall relay log; waiting for more updates

Master_Retry_Count:86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Executed_Gtid_Set:210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Auto_Position:1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key:0

Network_Namespace:

1rowinset,1 warning (0.01 sec)

#配置从节点2

[root@slave2 ~]#vim /etc/my.cnf

[mysqld]

server-id=6 #不同节点此值各不相同

log-bin

read-only

relay_log_purge=0 #禁止自动删除不再使用的relay Log,可用于日志同步

skip_name_resolve=1#禁止反向解析,此项可选

general_log #通用日志，方便观察的设置,生产无需启用

gtid_mode=on #开启gtid,可不用手动查找二进制日志节点

enforce_gtid_consistency

#重启mysql服务

[root@slave2 ~]#systemctl restart mysqld.service

#配置主从复制信息

CHANGEMASTERTO

MASTER_HOST='10.0.0.4',

MASTER_USER='repluser',

MASTER_PASSWORD='123456',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1; #启用gtid

#开启slave

mysql>STARTSLAVE;

#查看状态

mysql>showslavestatus\G;

***************************1.row***************************

Slave_IO_State: Waiting forsourceto send event

Master_Host:10.0.0.4

Master_User: repluser

Master_Port:3306

Connect_Retry:60

Master_Log_File:master-bin.000006

Read_Master_Log_Pos:717

Relay_Log_File: slave2-relay-bin.000021

Relay_Log_Pos:367

Relay_Master_Log_File:master-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

........................................................................

Master_Server_Id:4

Master_UUID:210892f2-641e-11ed-a2cb-000c299dd5be

Master_Info_File: mysql.slave_master_info

SQL_Delay:0

SQL_Remaining_Delay:NULL

Slave_SQL_Running_State: Replica has readall relay log; waiting for more updates

Master_Retry_Count:86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Executed_Gtid_Set:210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Auto_Position:0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key:0

Network_Namespace:

1rowinset,1 warning (0.00 sec)

检查MHA的环境

#检查环境

[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf

...........................................

Tue Nov 1521:24:52 2022- [debug] Connecting via SSH from root@10.0.0.4(10.0.0.4:22) to root@10.0.0.5(10.0.0.5:22)..

Tue Nov 1521:24:53 2022- [debug] ok.

Tue Nov 1521:24:53 2022- [debug] Connecting via SSH from root@10.0.0.4(10.0.0.4:22) to root@10.0.0.6(10.0.0.6:22)..

Tue Nov 1521:24:53 2022- [debug] ok.

Tue Nov 1521:24:54 2022- [debug]

Tue Nov 1521:24:53 2022- [debug] Connecting via SSH from root@10.0.0.5(10.0.0.5:22) to root@10.0.0.4(10.0.0.4:22)..

Tue Nov 1521:24:53 2022- [debug] ok.

Tue Nov 1521:24:53 2022- [debug] Connecting via SSH from root@10.0.0.5(10.0.0.5:22) to root@10.0.0.6(10.0.0.6:22)..

Tue Nov 1521:24:54 2022- [debug] ok.

Tue Nov 1521:24:55 2022- [debug]

Tue Nov 1521:24:53 2022- [debug] Connecting via SSH from root@10.0.0.6(10.0.0.6:22) to root@10.0.0.4(10.0.0.4:22)..

Tue Nov 1521:24:54 2022- [debug] ok.

Tue Nov 1521:24:54 2022- [debug] Connecting via SSH from root@10.0.0.6(10.0.0.6:22) to root@10.0.0.5(10.0.0.5:22)..

Tue Nov 1521:24:54 2022- [debug] ok.

Tue Nov 1521:24:55 2022- [info] All SSH connection tests passed successfully.

[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf

app1 is stopped(2:NOT_RUNNING).

[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf

Tue Nov 1521:25:40 2022- [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Tue Nov 1521:25:40 2022- [info] Reading application default configuration from /etc/mastermha/app1.cnf..

Tue Nov 1521:25:40 2022- [info] Reading server configuration from /etc/mastermha/app1.cnf..

Tue Nov 1521:25:40 2022- [info] MHA::MasterMonitor version 0.58.

Tue Nov 1521:25:41 2022- [info] GTID failover mode =1

Tue Nov 1521:25:41 2022- [info] Dead Servers:

Tue Nov 1521:25:41 2022- [info] Alive Servers:

Tue Nov 1521:25:41 2022- [info] 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:25:41 2022- [info] 10.0.0.5(10.0.0.5:3306)

Tue Nov 1521:25:41 2022- [info] 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:25:41 2022- [info] Alive Slaves:

Tue Nov 1521:25:41 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:25:41 2022- [info] GTID ON

Tue Nov 1521:25:41 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:25:41 2022- [info] 10.0.0.6(10.0.0.6:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:25:41 2022- [info] GTID ON

Tue Nov 1521:25:41 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:25:41 2022- [info] Primary candidate for the new Master (candidate_master is set)

Tue Nov 1521:25:41 2022- [info] Current Alive Master: 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:25:41 2022- [info] Checking slave configurations..

Tue Nov 1521:25:41 2022- [info] Checking replication filtering settings..

Tue Nov 1521:25:41 2022- [info] binlog_do_db= , binlog_ignore_db=

Tue Nov 1521:25:41 2022- [info] Replication filtering check ok.

Tue Nov 1521:25:41 2022- [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Tue Nov 1521:25:41 2022- [info] Checking SSH publickey authentication settings on the current master..

Tue Nov 1521:25:42 2022- [info] HealthCheck: SSH to 10.0.0.4 is reachable.

Tue Nov 1521:25:42 2022- [info]

10.0.0.4(10.0.0.4:3306) (current master)

+--10.0.0.5(10.0.0.5:3306)

+--10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:25:42 2022- [info] Checking replication health on 10.0.0.5..

Tue Nov 1521:25:42 2022- [info] ok.

Tue Nov 1521:25:42 2022- [info] Checking replication health on 10.0.0.6..

Tue Nov 1521:25:42 2022- [info] ok.

Tue Nov 1521:25:42 2022- [info] Checking master_ip_failover_script status:

Tue Nov 1521:25:42 2022- [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.0.0.4 --orig_master_ip=10.0.0.4 --orig_master_port=3306

Checking the Status of the script.. OK

Tue Nov 1521:25:42 2022- [info] OK.

Tue Nov 1521:25:42 2022- [warning] shutdown_script is not defined.

Tue Nov 1521:25:42 2022- [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

启动MHA

#开启MHA,默认是前台运行,生产环境一般为后台执行

[root@mha-manager ~]#nohup masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master-conf --ignore_last_failover < /dev/null >/var/log/mha_manager.log 2>&1 &

[1] 26654

#选项说明

--remove_dead_master_conf：该参数代表当发生主从切换后，老的主库的 ip 将会从配置文件中移除。

--manger_log：日志存放位置。

--ignore_last_failover：在缺省情况下，如果 MHA 检测到连续发生宕机，且两次宕机间隔不足 8 小时的话，则不会进行 Failover，之所以这样限制是为了避免 ping-pong 效应。该参数代表忽略上次MHA 触发切换产生的文件，默认情况下，MHA 发生切换后会在日志记目录，也就是上面设置的日志app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件，为了方便，这里设置为--ignore_last_failover

#测试环境

[root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failover

#如果想停止后台执行的MHA,可以执行下面命令

[root@mha-master ~]#masterha_stop --conf=/etc/mastermha/app1.cnf

#查看状态

[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf

app1 (pid:26654) is running(0:PING_OK), master:10.0.0.4

#查看状态

[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf

app1 (pid:26654) is running(0:PING_OK), master:10.0.0.4

#查看健康性检查

[root@master ~]#tail -f /data/mysql/master.log

2022-11-15T13:46:20.783052Z 26 Query SELECT 1 As Value

2022-11-15T13:46:21.784816Z 26 Query SELECT 1 As Value

2022-11-15T13:46:22.788480Z 26 Query SELECT 1 As Value

2022-11-15T13:46:23.784697Z 26 Query SELECT 1 As Value

2022-11-15T13:46:24.786126Z 26 Query SELECT 1 As Value

2022-11-15T13:46:25.788656Z 26 Query SELECT 1 As Value

2022-11-15T13:46:26.789594Z 26 Query SELECT 1 As Value

2022-11-15T13:46:27.792608Z 26 Query SELECT 1 As Value

2022-11-15T13:46:28.790189Z 26 Query SELECT 1 As Value

2022-11-15T13:46:29.790340Z 26 Query SELECT 1 As Value

2022-11-15T13:46:30.792041Z 26 Query SELECT 1 As Value

2022-11-15T13:46:31.795440Z 26 Query SELECT 1 As Value

2022-11-15T13:46:32.795605Z 26 Query SELECT 1 As Value

2022-11-15T13:46:33.796529Z 26 Query SELECT 1 As Value

2022-11-15T13:46:34.796525Z 26 Query SELECT 1 As Value

2022-11-15T13:46:35.797295Z 26 Query SELECT 1 As Value

2022-11-15T13:46:36.799935Z 26 Query SELECT 1 As Value

排错日志

[root@mha-manager ~]#cat /data/mastermha/app1/manager.log

Tue Nov 1521:32:04 2022- [info] MHA::MasterMonitor version 0.58.

Tue Nov 1521:32:05 2022- [info] GTID failover mode =1

Tue Nov 1521:32:05 2022- [info] Dead Servers:

Tue Nov 1521:32:05 2022- [info] Alive Servers:

Tue Nov 1521:32:05 2022- [info] 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:32:05 2022- [info] 10.0.0.5(10.0.0.5:3306)

Tue Nov 1521:32:05 2022- [info] 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:32:05 2022- [info] Alive Slaves:

Tue Nov 1521:32:05 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

验证

#模拟故障，停止master的mysql服务

[root@master ~]#systemctl stop mysqld.service

#当 master down机后,mha管理程序自动退出，同时会收到告警邮件

[root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf

Tue Nov 1521:52:58 2022- [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Tue Nov 1521:52:58 2022- [info] Reading application default configuration from /etc/mastermha/app1.cnf..

Tue Nov 1521:52:58 2022- [info] Reading server configuration from /etc/mastermha/app1.cnf..

[root@mha-manager ~]#cat /data/mastermha/app1/manager.log

Tue Nov 1521:32:04 2022- [info] MHA::MasterMonitor version 0.58.

Tue Nov 1521:32:05 2022- [info] GTID failover mode =1

Tue Nov 1521:32:05 2022- [info] Dead Servers:

Tue Nov 1521:32:05 2022- [info] Alive Servers:

Tue Nov 1521:32:05 2022- [info] 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:32:05 2022- [info] 10.0.0.5(10.0.0.5:3306)

Tue Nov 1521:32:05 2022- [info] 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:32:05 2022- [info] Alive Slaves:

Tue Nov 1521:32:05 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:32:05 2022- [info] GTID ON

Tue Nov 1521:32:05 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:32:05 2022- [info] 10.0.0.6(10.0.0.6:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:32:05 2022- [info] GTID ON

Tue Nov 1521:32:05 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:32:05 2022- [info] Primary candidate for the new Master (candidate_master is set)

Tue Nov 1521:32:05 2022- [info] Current Alive Master: 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:32:05 2022- [info] Checking slave configurations..

Tue Nov 1521:32:05 2022- [info] Checking replication filtering settings..

Tue Nov 1521:32:05 2022- [info] binlog_do_db= , binlog_ignore_db=

Tue Nov 1521:32:05 2022- [info] Replication filtering check ok.

Tue Nov 1521:32:05 2022- [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Tue Nov 1521:32:05 2022- [info] Checking SSH publickey authentication settings on the current master..

Tue Nov 1521:32:05 2022- [info] HealthCheck: SSH to 10.0.0.4 is reachable.

Tue Nov 1521:32:05 2022- [info]

10.0.0.4(10.0.0.4:3306) (current master)

+--10.0.0.5(10.0.0.5:3306)

+--10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:32:05 2022- [info] Checking master_ip_failover_script status:

Tue Nov 1521:32:05 2022- [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.0.0.4 --orig_master_ip=10.0.0.4 --orig_master_port=3306

Checking the Status of the script.. OK

Tue Nov 1521:32:06 2022- [info] OK.

Tue Nov 1521:32:06 2022- [warning] shutdown_script is not defined.

Tue Nov 1521:32:06 2022- [info] Set master ping interval 1 seconds.

Tue Nov 1521:32:06 2022- [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.

Tue Nov 1521:32:06 2022- [info] Starting ping health check on 10.0.0.4(10.0.0.4:3306)..

Tue Nov 1521:32:06 2022- [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

Tue Nov 1521:49:37 2022- [warning] Got error on MySQL select ping: 1053 (Server shutdown in progress)

Tue Nov 1521:49:37 2022- [info] Executing SSH check script: exit0

Tue Nov 1521:49:38 2022- [info] HealthCheck: SSH to 10.0.0.4 is reachable.

Tue Nov 1521:49:38 2022- [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.4' (111))

Tue Nov 1521:49:38 2022- [warning] Connection failed 2 time(s)..

Tue Nov 1521:49:39 2022- [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.4' (111))

Tue Nov 1521:49:39 2022- [warning] Connection failed 3 time(s)..

Tue Nov 1521:49:40 2022- [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.4' (111))

Tue Nov 1521:49:40 2022- [warning] Connection failed 4 time(s)..

Tue Nov 1521:49:40 2022- [warning] Master is not reachable from health checker!

Tue Nov 1521:49:40 2022- [warning] Master 10.0.0.4(10.0.0.4:3306) is not reachable!

Tue Nov 1521:49:40 2022- [warning] SSH is reachable.

Tue Nov 1521:49:40 2022- [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mastermha/app1.cnf again, and trying to connect to all servers to check server status..

Tue Nov 1521:49:40 2022- [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Tue Nov 1521:49:40 2022- [info] Reading application default configuration from /etc/mastermha/app1.cnf..

Tue Nov 1521:49:40 2022- [info] Reading server configuration from /etc/mastermha/app1.cnf..

Tue Nov 1521:49:41 2022- [info] GTID failover mode =1

Tue Nov 1521:49:41 2022- [info] Dead Servers:

Tue Nov 1521:49:41 2022- [info] 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:41 2022- [info] Alive Servers:

Tue Nov 1521:49:41 2022- [info] 10.0.0.5(10.0.0.5:3306)

Tue Nov 1521:49:41 2022- [info] 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:49:41 2022- [info] Alive Slaves:

Tue Nov 1521:49:41 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:41 2022- [info] GTID ON

Tue Nov 1521:49:41 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:41 2022- [info] 10.0.0.6(10.0.0.6:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:41 2022- [info] GTID ON

Tue Nov 1521:49:41 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:41 2022- [info] Primary candidate for the new Master (candidate_master is set)

Tue Nov 1521:49:41 2022- [info] Checking slave configurations..

Tue Nov 1521:49:41 2022- [info] Checking replication filtering settings..

Tue Nov 1521:49:41 2022- [info] Replication filtering check ok.

Tue Nov 1521:49:41 2022- [info] Master is down!

Tue Nov 1521:49:41 2022- [info] Terminating monitoring script.

Tue Nov 1521:49:41 2022- [info] Got exit code 20 (Master dead).

Tue Nov 1521:49:41 2022- [info] MHA::MasterFailover version 0.58.

Tue Nov 1521:49:41 2022- [info] Starting master failover.

Tue Nov 1521:49:41 2022- [info]

Tue Nov 1521:49:41 2022- [info] * Phase 1: Configuration Check Phase..

Tue Nov 1521:49:41 2022- [info]

Tue Nov 1521:49:43 2022- [info] GTID failover mode =1

Tue Nov 1521:49:43 2022- [info] Dead Servers:

Tue Nov 1521:49:43 2022- [info] 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] Checking master reachability via MySQL(double check)...

Tue Nov 1521:49:43 2022- [info] ok.

Tue Nov 1521:49:43 2022- [info] Alive Servers:

Tue Nov 1521:49:43 2022- [info] 10.0.0.5(10.0.0.5:3306)

Tue Nov 1521:49:43 2022- [info] 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:49:43 2022- [info] Alive Slaves:

Tue Nov 1521:49:43 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:43 2022- [info] GTID ON

Tue Nov 1521:49:43 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] 10.0.0.6(10.0.0.6:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:43 2022- [info] GTID ON

Tue Nov 1521:49:43 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] Primary candidate for the new Master (candidate_master is set)

Tue Nov 1521:49:43 2022- [info] Starting GTID based failover.

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] ** Phase 1: Configuration Check Phase completed.

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] * Phase 2: Dead Master Shutdown Phase..

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] Forcing shutdown so that applications never connect to the current master..

Tue Nov 1521:49:43 2022- [info] Executing master IP deactivation script:

Tue Nov 1521:49:43 2022- [info] /usr/local/bin/master_ip_failover --orig_master_host=10.0.0.4 --orig_master_ip=10.0.0.4 --orig_master_port=3306--command=stopssh --ssh_user=root

Tue Nov 1521:49:43 2022- [info] done.

Tue Nov 1521:49:43 2022- [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.

Tue Nov 1521:49:43 2022- [info] * Phase 2: Dead Master Shutdown Phase completed.

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] * Phase 3: Master Recovery Phase..

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] * Phase 3.1: Getting Latest Slaves Phase..

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] The latest binary log file/position on all slaves is master-bin.000006:717

Tue Nov 1521:49:43 2022- [info] Retrieved Gtid Set: 210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Tue Nov 1521:49:43 2022- [info] Latest slaves (Slaves that received relay log files to the latest):

Tue Nov 1521:49:43 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:43 2022- [info] GTID ON

Tue Nov 1521:49:43 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] 10.0.0.6(10.0.0.6:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:43 2022- [info] GTID ON

Tue Nov 1521:49:43 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] Primary candidate for the new Master (candidate_master is set)

Tue Nov 1521:49:43 2022- [info] The oldest binary log file/position on all slaves is master-bin.000006:717

Tue Nov 1521:49:43 2022- [info] Retrieved Gtid Set: 210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Tue Nov 1521:49:43 2022- [info] Oldest slaves:

Tue Nov 1521:49:43 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:43 2022- [info] GTID ON

Tue Nov 1521:49:43 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] 10.0.0.6(10.0.0.6:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:43 2022- [info] GTID ON

Tue Nov 1521:49:43 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] Primary candidate for the new Master (candidate_master is set)

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] * Phase 3.3: Determining New Master Phase..

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] Searching new master from slaves..

Tue Nov 1521:49:43 2022- [info] Candidate masters from the configuration file:

Tue Nov 1521:49:43 2022- [info] 10.0.0.6(10.0.0.6:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:49:43 2022- [info] GTID ON

Tue Nov 1521:49:43 2022- [info] Replicating from 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:49:43 2022- [info] Primary candidate for the new Master (candidate_master is set)

Tue Nov 1521:49:43 2022- [info] Non-candidate masters:

Tue Nov 1521:49:43 2022- [info] Searching from candidate_master slaves which have received the latest relay log events..

Tue Nov 1521:49:43 2022- [info] New master is 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:49:43 2022- [info] Starting master failover..

Tue Nov 1521:49:43 2022- [info]

From:

10.0.0.4(10.0.0.4:3306) (current master)

+--10.0.0.5(10.0.0.5:3306)

+--10.0.0.6(10.0.0.6:3306)

To:

10.0.0.6(10.0.0.6:3306) (new master)

+--10.0.0.5(10.0.0.5:3306)

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] * Phase 3.3: New Master Recovery Phase..

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] Waiting all logs to be applied..

Tue Nov 1521:49:43 2022- [info] done.

Tue Nov 1521:49:43 2022- [info] Replicating from the latest slave 10.0.0.5(10.0.0.5:3306) and waiting to apply..

Tue Nov 1521:49:43 2022- [info] Waiting all logs to be applied on the latest slave..

Tue Nov 1521:49:43 2022- [info] Resetting slave 10.0.0.6(10.0.0.6:3306) and starting replication from the new master 10.0.0.5(10.0.0.5:3306)..

Tue Nov 1521:49:43 2022- [info] Executed CHANGE MASTER.

Tue Nov 1521:49:43 2022- [info] Slave started.

Tue Nov 1521:49:43 2022- [info] Waiting to execute all relay logs on 10.0.0.6(10.0.0.6:3306)..

Tue Nov 1521:49:43 2022- [info] master_pos_wait(slave1-bin.000008:197) completed on 10.0.0.6(10.0.0.6:3306). Executed 2 events.

Tue Nov 1521:49:43 2022- [info] done.

Tue Nov 1521:49:43 2022- [info] Getting new master's binlog name and position..

Tue Nov 1521:49:43 2022- [info] slave2-bin.000008:197

Tue Nov 1521:49:43 2022- [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.6', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repluser', MASTER_PASSWORD='xxx';

Tue Nov 1521:49:43 2022- [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: slave2-bin.000008, 197, 210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Tue Nov 1521:49:43 2022- [info] Executing master IP activate script:

Tue Nov 1521:49:43 2022- [info] /usr/local/bin/master_ip_failover --command=start--ssh_user=root --orig_master_host=10.0.0.4 --orig_master_ip=10.0.0.4 --orig_master_port=3306--new_master_host=10.0.0.6 --new_master_ip=10.0.0.6 --new_master_port=3306--new_master_user='mhauser' --new_master_password=xxx

Enabling the VIP -10.0.0.100/24 on the new master -10.0.0.6

Tue Nov 1521:49:43 2022- [info] OK.

Tue Nov 1521:49:43 2022- [info] Setting read_only=0 on 10.0.0.6(10.0.0.6:3306)..

Tue Nov 1521:49:43 2022- [info] ok.

Tue Nov 1521:49:43 2022- [info] ** Finished master recovery successfully.

Tue Nov 1521:49:43 2022- [info] * Phase 3: Master Recovery Phase completed.

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] * Phase 4: Slaves Recovery Phase..

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] * Phase 4.1: Starting Slaves in parallel..

Tue Nov 1521:49:43 2022- [info]

Tue Nov 1521:49:43 2022- [info] -- Slave recovery on host 10.0.0.5(10.0.0.5:3306) started, pid: 28018. Check tmp log /data/mastermha/app1//10.0.0.5_3306_20221115214941.log if it takes time..

Tue Nov 1521:49:44 2022- [info]

Tue Nov 1521:49:44 2022- [info] Log messages from 10.0.0.5 ...

Tue Nov 1521:49:44 2022- [info]

Tue Nov 1521:49:43 2022- [info] Resetting slave 10.0.0.5(10.0.0.5:3306) and starting replication from the new master 10.0.0.6(10.0.0.6:3306)..

Tue Nov 1521:49:43 2022- [info] Executed CHANGE MASTER.

Tue Nov 1521:49:43 2022- [info] Slave started.

Tue Nov 1521:49:43 2022- [info] gtid_wait(210892f2-641e-11ed-a2cb-000c299dd5be:1-9) completed on 10.0.0.5(10.0.0.5:3306). Executed 0 events.

Tue Nov 1521:49:44 2022- [info] End of log messages from 10.0.0.5.

Tue Nov 1521:49:44 2022- [info] -- Slave on host 10.0.0.5(10.0.0.5:3306) started.

Tue Nov 1521:49:44 2022- [info] All new slave servers recovered successfully.

Tue Nov 1521:49:44 2022- [info]

Tue Nov 1521:49:44 2022- [info] * Phase 5: New master cleanup phase..

Tue Nov 1521:49:44 2022- [info]

Tue Nov 1521:49:44 2022- [info] Resetting slave info on the new master..

Tue Nov 1521:49:44 2022- [info] 10.0.0.6: Resetting slave info succeeded.

Tue Nov 1521:49:44 2022- [info] Master failover to 10.0.0.6(10.0.0.6:3306) completed successfully.

Tue Nov 1521:49:44 2022- [info]

----- Failover Report -----

app1: MySQL Master failover 10.0.0.4(10.0.0.4:3306) to 10.0.0.6(10.0.0.6:3306) succeeded

Master 10.0.0.4(10.0.0.4:3306) is down!

Check MHA Manager logs at mha-manager:/data/mastermha/app1/manager.log for details.

Started automated(non-interactive) failover.

Invalidated master IP address on 10.0.0.4(10.0.0.4:3306)

Selected 10.0.0.6(10.0.0.6:3306) as a new master.

10.0.0.6(10.0.0.6:3306): OK: Applying all logs succeeded.

10.0.0.6(10.0.0.6:3306): OK: Activated master IP address.

10.0.0.5(10.0.0.5:3306): OK: Slave started, replicating from 10.0.0.6(10.0.0.6:3306)

10.0.0.6(10.0.0.6:3306): Resetting slave info succeeded.

Master failover to 10.0.0.6(10.0.0.6:3306) completed successfully.

Tue Nov 1521:49:44 2022- [info] Sending mail..

Tue Nov 1521:52:58 2022- [info] MHA::MasterMonitor version 0.58.

Tue Nov 1521:52:59 2022- [info] GTID failover mode =1

Tue Nov 1521:52:59 2022- [info] Dead Servers:

Tue Nov 1521:52:59 2022- [info] 10.0.0.4(10.0.0.4:3306)

Tue Nov 1521:52:59 2022- [info] Alive Servers:

Tue Nov 1521:52:59 2022- [info] 10.0.0.5(10.0.0.5:3306)

Tue Nov 1521:52:59 2022- [info] 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:52:59 2022- [info] Alive Slaves:

Tue Nov 1521:52:59 2022- [info] 10.0.0.5(10.0.0.5:3306) Version=8.0.30 (oldest major version between slaves) log-bin:enabled

Tue Nov 1521:52:59 2022- [info] GTID ON

Tue Nov 1521:52:59 2022- [info] Replicating from 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:52:59 2022- [info] Current Alive Master: 10.0.0.6(10.0.0.6:3306)

Tue Nov 1521:52:59 2022- [info] Checking slave configurations..

Tue Nov 1521:52:59 2022- [info] Checking replication filtering settings..

Tue Nov 1521:52:59 2022- [info] binlog_do_db= , binlog_ignore_db=

Tue Nov 1521:52:59 2022- [info] Replication filtering check ok.

Tue Nov 1521:52:59 2022- [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Tue Nov 1521:52:59 2022- [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln492] Server 10.0.0.4(10.0.0.4:3306) is dead, but must be alive! Check server settings.

Tue Nov 1521:52:59 2022- [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 402.

Tue Nov 1521:52:59 2022- [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Tue Nov 1521:52:59 2022- [info] Got exit code 1 (Not master dead).

[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf

app1 is stopped(2:NOT_RUNNING).

#验证VIP漂移至新的master上

[root@slave2 ~]#ip a show eth0

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000

link/ether 00:0c:29:fb:d7:c6 brd ff:ff:ff:ff:ff:ff

inet 10.0.0.6/24 brd 10.0.0.255 scope global noprefixroute eth0

valid_lft forever preferred_lft forever

inet 10.0.0.100/24 brd 10.0.0.255 scope global secondary eth0:1

valid_lft forever preferred_lft forever

inet6 fe80::20c:29ff:fefb:d7c6/64 scope link

valid_lft forever preferred_lft forever

#查看切换后复制状态，从节点2已提升为master

mysql> show master status;

+-------------------+----------+--------------+------------------+------------------------------------------+

+-------------------+----------+--------------+------------------+------------------------------------------+

+-------------------+----------+--------------+------------------+------------------------------------------+

1 row inset (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting forsource to send event

Master_Host: 10.0.0.6

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: slave2-bin.000008

Read_Master_Log_Pos: 197

Relay_Log_File: slave1-relay-bin.000002

Relay_Log_Pos: 375

Relay_Master_Log_File: slave2-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 197

Relay_Log_Space: 586

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_Server_Id: 6

Master_UUID: 2a739d20-641e-11ed-a03c-000c29fbd7c6

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 210892f2-641e-11ed-a2cb-000c299dd5be:1-9

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row inset, 1 warning (0.00 sec)

#程序將自动修改manager节点上的配置文件，自动将master ip剔除

[root@mha-manager mastermha]#cat app1.cnf

[server2]

hostname=10.0.0.5

port=3306

[server3]

hostname=10.0.0.6

port=3306

candidate_master=1

注意: 如果出错,需要删除下面文件再执行MHA

[root@mha-manager ~]#rm -f /data/mastermha/app1/app1.failover.error

修复主从

修复故障的主库,保证数据同步

修复主从,手工新故障库加入新的主,设为为从库

修复manager的配置文件

清理相关目录

检查ssh互信和replication的复制是否成功

检查VIP,如果有问题,重新配置VIP

重新运行MHA,查询MHA状态,确保运行正常

如果再次运行MHA，需要先删除下面文件

MHA只能漂移一次，如果多次使用必须删除以下文件，要不MHA不可重用，或者功能出现异常，第二次部分主机切换无法正常切换

[root@mha-manager ~]#rm -rf /data/mastermha/app1/ #mha_master自己的工作路径

[root@mha-manager ~]#rm -rf /data/mastermha/app1/manager.log #mha_master自己的日志文件

注意点

#从节点切换主节点之后，read_only值会临时更该，但my.cnf配置仍在，需人工删除

mysql> select @@read_only;

+-------------+

| @@read_only |

+-------------+

| 0 |

+-------------+

1 row inset (0.00 sec)

[mysqld]

server-id=6

log-bin

read-only

relay_log_purge=0

skip_name_resolve=1

general_log

gtid_mode=on

enforce_gtid_consistency

datadir=/data/mysql

基于MHA实现MySQL主节点高可用

在管理节点上安装两个包mha4mysql-manager和mha4mysql-node

在所有MySQL服务器上安装mha4mysql-node包

在所有节点实现相互之间 ssh key 验证

在管理节点建立配置文件

相关脚本

实现Master

实现slave

检查MHA的环境

启动MHA

排错日志

验证

修复主从

如果再次运行MHA，需要先删除下面文件

注意点