在之前的博客中，介绍了mysql的主从模型以及深层次的mysql的读写分离插件——ProxySQL，让我们可以很大程度上提升数据库服务器的性能和优化用户的体验，但是，我们对于数据库的可靠性似乎缺了一点，因为一旦master数据库服务器宕机，我们的数据库基本上就是瘫痪了，所以，我们需要一个解决方案针对于数据库服务器的可靠性。
MHA（MySQL|Master High Availability）是一种基于主从模型的相当成熟的一种解决方案，我们对于master做一个高可用，使得哪怕在master数据库服务器宕机时，我们的slave可以及时顶上，直接变成master主机，保证服务可靠的运行；
由于mha4mysql不存在于镜像仓库和epel中，所以需要下载rpm包，我下载的是
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
建议mha服务器和master服务器分开；否则master物理宕机的话，mha也会宕机；
Master和slave上安装mha4mysql-node节点；
mha服务器两个rpm包都需要安装；
MHA： CentOS 7.5B 172.16.75.2
Master： CentOS 7.5D 172.16.75.4
Slave： CentOS 7.5C 172.16.75.3
三台主机都需要进行SSH免密通信；
因为如果master宕机，slave需要顶上，所以slave和master都需要开启二进制日志和中继日志；
Master配置文件：
[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemdinnodb_file_per_table=ONskip_name_resolve=ONserver_id=401log_bin=/var/lib/mysql/binlogsync_binlog=1innodb_flush_log_at_trx_commit=1relay_log_purge=0relay_log=relay_log[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d
Slave配置文件：
[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemdinnodb_file_per_table=ONskip_name_resolve=ONserver_id=301read_only=ONrelay_log=slavelogrelay_log_purge=0log_bin=binlog[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d
然后其他配置不变，搭建主从模型；
1.下载完mha的rpm包后，分别在对应的节点安装对应的安装包，通过查看mha4mysql-manager配置文件，全是二进制执行脚本。所以mha的配置文件需要自己写；
[root@slave1 ~]# rpm -ql mha4mysql-manager/usr/bin/masterha_check_repl/usr/bin/masterha_check_ssh/usr/bin/masterha_check_status/usr/bin/masterha_conf_host/usr/bin/masterha_manager/usr/bin/masterha_master_monitor/usr/bin/masterha_master_switch/usr/bin/masterha_secondary_check/usr/bin/masterha_stop/usr/share/man/man1/masterha_check_repl.1.gz/usr/share/man/man1/masterha_check_ssh.1.gz/usr/share/man/man1/masterha_check_status.1.gz/usr/share/man/man1/masterha_conf_host.1.gz/usr/share/man/man1/masterha_manager.1.gz/usr/share/man/man1/masterha_master_monitor.1.gz/usr/share/man/man1/masterha_master_switch.1.gz/usr/share/man/man1/masterha_secondary_check.1.gz/usr/share/man/man1/masterha_stop.1.gz/usr/share/perl5/vendor_perl/MHA/Config.pm/usr/share/perl5/vendor_perl/MHA/DBHelper.pm/usr/share/perl5/vendor_perl/MHA/FileStatus.pm/usr/share/perl5/vendor_perl/MHA/HealthCheck.pm/usr/share/perl5/vendor_perl/MHA/ManagerAdmin.pm/usr/share/perl5/vendor_perl/MHA/ManagerAdminWrapper.pm/usr/share/perl5/vendor_perl/MHA/ManagerConst.pm/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm/usr/share/perl5/vendor_perl/MHA/MasterRotate.pm/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm/usr/share/perl5/vendor_perl/MHA/Server.pm/usr/share/perl5/vendor_perl/MHA/ServerManager.pm
2.创建mha相对应的配置文件；
[root@slave1 ~]# mkdir /etc/mha[root@slave1 ~]# vim /etc/mha/app1.cnf[root@slave1 ~]#[root@slave1 ~]# cat /etc/mha/app1.cnf[server default]user=mha#登录用户password=mhapassmanager_workdir=/data/masterha/app1manager_log=/data/masterha/app1/manager.logremote_workdir=/data/masterha/app1ssh_user=rootrepl_user=repuser#master做replication slave授权的用户repl_password=123456ping_interval=1[server1]hostname=172.16.75.4candidate_master=1[server2]hostname=172.16.75.3candidate_master=1[root@slave1 ~]#[root@slave1 ~]# mkdir -pv /data/masterha/app1mkdir: 已创建目录"/data"mkdir: 已创建目录"/data/masterha"mkdir: 已创建目录"/data/masterha/app1"
3.根据配置文件内容，master还需要对mha做一个授权用户。
Master mysql：
MariaDB[(none)]>grant all on *.* to'mha'@'%'identified by'mhapass';Query OK, 0 rows affected(0.08 sec)
4.在mha服务器上进行ssh测试和repl测试（最后显示 OK即可）；
[root@slave1 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnfWed Nov 7 20:46:17 2018 -[warning]Global configurationfile/etc/masterha_default.cnf not found. Skipping.Wed Nov 7 20:46:17 2018 -[info]Reading application default configuration from /etc/mastermha/app1.cnf..Wed Nov 7 20:46:17 2018 -[info]Reading server configuration from /etc/mastermha/app1.cnf..Wed Nov 7 20:46:17 2018 -[info]Starting SSH connection tests..Wed Nov 7 20:46:18 2018 -[debug]Wed Nov 7 20:46:17 2018 -[debug]Connecting via SSH from root@172.16.75.4(172.16.75.4:22)to root@172.16.75.3(172.16.75.3:22)..Wed Nov 7 20:46:17 2018 -[debug]ok.Wed Nov 7 20:46:18 2018 -[debug]Wed Nov 7 20:46:17 2018 -[debug]Connecting via SSH from root@172.16.75.3(172.16.75.3:22)to root@172.16.75.4(172.16.75.4:22)..Wed Nov 7 20:46:18 2018 -[debug]ok.Wed Nov 7 20:46:18 2018 -[info]All SSH connection tests passed successfully.
[root@slave1 ~]# masterha_check_repl --conf=/etc/mha/app.cnf…172.16.75.4(172.16.75.4:3306)(current master)+--172.16.75.3(172.16.75.3:3306)Thu Nov 8 09:37:35 2018 -[info]Checking replication health on 172.16.75.3..Thu Nov 8 09:37:35 2018 -[info]ok.Thu Nov 8 09:37:35 2018 -[warning]master_ip_failover_script is not defined.Thu Nov 8 09:37:35 2018 -[warning]shutdown_script is not defined.Thu Nov 8 09:37:35 2018 -[info]Gotexitcode 0(Not master dead). MySQL Replication Health is OK.
5.启动mha4mysql进程；
[root@slave1 ~]# nohup masterha_manager --conf=/etc/mha/app.cnf > /data/masterha/app1/manager.log 2>&1 &[1]85154
6.检测mha的状态；
[root@slave1 ~]# masterha_check_status --conf=/etc/mha/app.cnfapp(pid:85154)is running(0:PING_OK), master:172.16.75.4
7.测试：直播抓娃娃APP找上海捌跃网络科技有限公司
把master的mysql进程关掉；然后查看manager上的mha日志：
Master：
[root@slave2 ~]# systemctl stop mariadb
Manager：
[root@slave1 ~]# masterha_check_status --conf=/etc/mha/app.cnfapp master is down and failover is running(50:FAILOVER_RUNNING). master:172.16.75.4[root@slave1 ~]# cat /data/masterha/app1/manager.log…… ----- Failover Report ----- app: MySQL Master failover 172.16.75.4(172.16.75.4:3306)to 172.16.75.3(172.16.75.3:3306)succeeded Master 172.16.75.4(172.16.75.4:3306)is down!Check MHA Manager logs at slave1.ljy.com:/data/masterha/app1/manager.logfordetails. Started automated(non-interactive)failover.The latest slave 172.16.75.3(172.16.75.3:3306)has all relay logsforrecovery.Selected 172.16.75.3(172.16.75.3:3306)as a new master.172.16.75.3(172.16.75.3:3306): OK: Applying all logs succeeded.Generating relaydifffiles from the latest slave succeeded.172.16.75.3(172.16.75.3:3306): Resetting slave info succeeded.Master failover to 172.16.75.3(172.16.75.3:3306)completed successfully.
最后，当出现Master failover to 172.16.75.3(172.16.75.3:3306) completed successfully.
就意味着我们的master已经转到slave上了，虽然之前的Master数据库服务器down掉，但是我们通过mha及时的切换到slave主机上，保证数据库服务器的可靠性。
遇到的大坑：Checking if super_read_only is defined and turned on..DBD::mysql::st execute failed: Unknown system variable'super_read_only' at /usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm line 245.
一开始用的0.58版本的mha，结果在进行repl检测的时候，出现了“super read only”的错误，这是因为我们的mysql版本和mha版本有不兼容的情况，换用0.56的版本就可以，因为0.58版本的mha兼容的是mariadb-10以后版本有”super_read_only”选项，我们正常CentOS-7系列主机上安装的MariaDB-5.56没有”super_read_only”选项，所以检测repl权限时，会说我们没有设置此选项；
总之，mha在生产环境中有很高的必要性，我们需要Proxysql做读写分离提升服务器型男的同时，又需要mha对master做高可用来保证服务器的可靠性，保障我们的数据库不间断的运行；
转自：http://blog.51cto.com/liujingyu/2314335
