mysql高可用架构MHA

mysql高可用MHA的原理

• 该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）,MHA Manager作为管理节点，可以单独部署在一台独立的机器上或其中一个slave节点上,MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master

• MHA工作原理：从故障master保存二进制日志事件，识别含有最新的slave，应用差异中继日志到其他slave，应用从master保存的二进制日志，将其中一个slave提升为master，其他的slave指向最新的master

mysql高可用MHA配置

• 高可用架构设计：一主三从，mha的Manager运行在slave（192.168.57.100）

Master                        192.168.67.200                     
Candicate master              192.168.57.121                  
Slave                  192.168.57.100             

• 配置互信(过程略)

• 搭建mysql主从复制(过程略),且在所有mysql数据库下创建用于监控的用户mha

• 所有节点安装mha4mysql-node

安装node依赖的包
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker cpan  perl-DBD-MySQL -y
安装node
/usr/local/bin
tar -xvf mha4mysql-node-0.56.tar
cd mha4mysql-manager-0.56
perl Makefile.PL
make && make install

安装完成后在/usr/local/bin下回生产如下文件
-r-xr-xr-x 1 root root 15498 Apr 20 10:05 apply_diff_relay_logs
-r-xr-xr-x 1 root root  4807 Apr 20 10:05 filter_mysqlbinlog
-r-xr-xr-x 1 root root  7401 Apr 20 10:05 purge_relay_logs
-r-xr-xr-x 1 root root  7263 Apr 20 10:05 save_binary_logs

• 192.168.57.100服务器上安装mha4mysql-manager

安装mha4mysql-manager依赖的包
-rw-r--r--. 1 root root  16308 Jul  3  2011 perl-Email-Date-Format-1.002-5.el6.noarch.rpm
-rw-r--r--. 1 root root 103432 Jul  3  2011 perl-MailTools-2.04-4.el6.noarch.rpm
-rw-r--r--. 1 root root  83864 Jul  3  2011 perl-MIME-Lite-3.027-2.el6.noarch.rpm
-rw-r--r--. 1 root root  33048 Jul  3  2011 perl-MIME-Types-1.28-2.el6.noarch.rpm
-rw-r--r--. 1 root root  76728 Jul  3  2011 perl-Params-Validate-0.92-3.el6.x86_64.rpm
-rw-r--r--. 1 root root  38264 Oct 18  2014 perl-TimeDate-1.16-13.el6.noarch.rpm
-rw-r--r--. 1 root root  21740 Jul 12  2016 perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
-rw-r--r--. 1 root root  25756 Jul 12  2016 perl-MIME-Lite-HTML-1.23-2.el6.noarch.rpm
-rw-r--r--. 1 root root  28448 Jul 12  2016 perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
-rw-r--r--. 1 root root  55224 Jul 12  2016 perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
-rw-r--r--. 1 root root  72328 Jul 12  2016 perl-Log-Dispatch-2.27-1.el6.noarch.rpm
-rw-r--r--. 1 root root  23416 Jul 12  2016 perl-Config-Tiny-2.12-7.1.el6.noarch.rpm

yum install *

安装mha4mysql-manager

cd mha4mysql-manager-0.56
perl Makefile.PL
make && make install

• 配置MHA
  创建MHA的工作目录，并且创建相关配置文件，如下为配置文件app1.cnf

[server default]
#mha manager工作目录
manager_workdir = /var/log/masterha/app1
manager_log = /var/log/masterha/app1/app1.log
remote_workdir = /var/log/masterha/app1

[server1]
hostname=192.168.57.200
port=3306
master_binlog_dir = /u01/mysql/mylog/MYORCL/binlog
candidate_master = 1
check_repl_delay = 0     #用防止master故障时，切换时slave有延迟，卡在那里切不过来。

[server2]
hostname=192.168.57.121
port=3306
master_binlog_dir = /u01/mysql/mylog/MYORCL/binlog
candidate_master = 1
check_repl_delay = 0

[server3]
hostname=192.168.57.100
port=3306
master_binlog_dir = /u01/mysql/mylog/MYORCL/binlog
candidate_master = 1
check_repl_delay = 0

#[server4]
#hostname=192.168.57.120
#port = 3307
#master_binlog_dir = /home/mysql/mysql3307/mylog/MYORCL/binlog
#no_master=1

配置文件masterha_default.conf

[server default]
#MySQL的用户和密码
user=mha
password=mha123456

#系统ssh用户
ssh_user=root

#复制用户
repl_user=repbak
repl_password=zhxg1234

#监控
ping_interval=1
#shutdown_script=""

#切换调用的脚本
master_ip_failover_script= /etc/masterha/master_ip_failover
master_ip_online_change_script= /etc/masterha/master_ip_online_change

检查主从复制是否正常

masterha_check_repl --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf


报错：
Can't locate Log/Dispatch.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr
/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/share/perl5/MHA/MasterMonitor.pm line 28.BEGIN failed--compilation aborted at /usr/local/share/perl5/MHA/MasterMonitor.pm line 28.
Compilation failed in require at /usr/local/bin/masterha_check_repl line 25.
BEGIN failed--compilation aborted at /usr/local/bin/masterha_check_repl line 25.

解决：
yum install perl-Mail-Sender
yum install perl-Log-Dispatch

正常

terha/app1.confWed Apr 29 03:32:16 2020 - [info] Reading default configuration from /etc/masterha/masterha_default.con
f..Wed Apr 29 03:32:16 2020 - [info] Reading application default configuration from /etc/masterha/app1.con
f..Wed Apr 29 03:32:16 2020 - [info] Reading server configuration from /etc/masterha/app1.conf..
Wed Apr 29 03:32:16 2020 - [info] Setting max_ping_errors to 4, ping_interval to 1.
Wed Apr 29 03:32:16 2020 - [info] MHA::MasterMonitor version 0.56.
Wed Apr 29 03:32:16 2020 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error 
when connecting 192.168.57.121(192.168.57.121:3306) :1045:Access denied for user 'mha'@'192.168.57.100' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Wed Apr 29 03:32:17 2020 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error 
when connecting 192.168.57.200(192.168.57.200:3306) :1045:Access denied for user 'mha'@'192.168.57.100' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Wed Apr 29 03:32:17 2020 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln309] Got fatal error,
 stopping operationsWed Apr 29 03:32:17 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln425] Error happened o
n checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 327Wed Apr 29 03:32:17 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln524] Error happened o
n monitoring servers.Wed Apr 29 03:32:17 2020 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

masterha_check_ssh检查ssh互信是否正常

[root@mycat masterha]# masterha_check_ssh  --global_conf=/etc/masterha/masterha_default.conf --conf=/et
c/masterha/app1.confWed Apr 29 04:42:49 2020 - [info] Reading default configuration from /etc/masterha/masterha_default.con
f..Wed Apr 29 04:42:49 2020 - [info] Reading application default configuration from /etc/masterha/app1.con
f..Wed Apr 29 04:42:49 2020 - [info] Reading server configuration from /etc/masterha/app1.conf..
Wed Apr 29 04:42:49 2020 - [info] Setting max_ping_errors to 4, ping_interval to 1.
Wed Apr 29 04:42:49 2020 - [info] Starting SSH connection tests..
Wed Apr 29 04:42:50 2020 - [debug] 
Wed Apr 29 04:42:49 2020 - [debug]  Connecting via SSH from root@192.168.57.200(192.168.57.200:22) to r
oot@192.168.57.121(192.168.57.121:22)..Wed Apr 29 04:42:50 2020 - [debug]   ok.
Wed Apr 29 04:42:50 2020 - [debug] 
Wed Apr 29 04:42:49 2020 - [debug]  Connecting via SSH from root@192.168.57.121(192.168.57.121:22) to r
oot@192.168.57.200(192.168.57.200:22)..Wed Apr 29 04:42:50 2020 - [debug]   ok.
Wed Apr 29 04:42:50 2020 - [info] All SSH connection tests passed successfully.

mha启动masterha_manager

masterha_manager --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf > /var/log/masterha/app1/mha_manager.log 2>&1 & 

启动报错

Wed Apr 29 03:52:08 2020 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln111] SSH connection from r
oot@192.168.57.100(192.168.57.100:22) to root@192.168.57.200(192.168.57.200:22) failed!

解决
#[server3]
#hostname=192.168.57.100
#port=3306
#master_binlog_dir = /data/mysql/data
#candidate_master = 1
#check_repl_delay = 0

检查进程是否存在

ps aux|grep masterha_manager

检查masterha的状态

masterha_check_status --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf

app1 (pid:4507) is running(0:PING_OK), master:192.168.57.200

停止masterha运行

masterha_stop --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf