MHA基本使用
第一章：简介
1.1简介
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。
目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，因为至少需要三台服务器，出于机器成本的考虑，淘宝也在该基础上进行了改造，目前淘宝TMHA已经支持一主一从。
1.2原理
当master出现故障时，通过对比slave之间I/O线程读取masterbinlog的位置，选取最接近的slave做为latestslave。其它slave通过与latest slave对比生成差异中继日志。在latest slave上应用从master保存的binlog，同时将latest slave提升为master。最后在其它slave上应用相应的差异中继日志并开始从新的master开始复制。
在MHA实现Master故障切换过程中，MHA Node会试图访问故障的master（通过SSH），如果可以访问（不是硬件故障，比如InnoDB数据文件损坏等），会保存二进制文件，以最大程度保证数据不丢失。MHA和半同步复制一起使用会大大降低数据丢失的危险。
故障恢复部分日志
Tue Apr 23 19:58:53 2019 - [warning] master_ip_failover_script is not defined.
Tue Apr 23 19:58:53 2019 - [warning] shutdown_script is not defined.
Tue Apr 23 19:58:53 2019 - [info] Set master ping interval 2 seconds.
Tue Apr 23 19:58:53 2019 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Tue Apr 23 19:58:53 2019 - [info] Starting ping health check on 192.168.240.208(192.168.240.208:3306)..
Tue Apr 23 19:58:53 2019 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Tue Apr 23 19:59:01 2019 - [warning] Got error on MySQL select ping: 2013 (Lost connection to MySQL server during query)
Tue Apr 23 19:59:01 2019 - [info] Executing SSH check script: exit 0
Tue Apr 23 19:59:02 2019 - [info] HealthCheck: SSH to 192.168.240.208 is reachable.
Tue Apr 23 19:59:03 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.240.208' (111))
Tue Apr 23 19:59:03 2019 - [warning] Connection failed 2 time(s)..
Tue Apr 23 19:59:05 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.240.208' (111))
Tue Apr 23 19:59:05 2019 - [warning] Connection failed 3 time(s)..
Tue Apr 23 19:59:07 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.240.208' (111))
Tue Apr 23 19:59:07 2019 - [warning] Connection failed 4 time(s)..
Tue Apr 23 19:59:07 2019 - [warning] Master is not reachable from health checker!
Tue Apr 23 19:59:07 2019 - [warning] Master 192.168.240.208(192.168.240.208:3306) is not reachable!
Tue Apr 23 19:59:07 2019 - [warning] SSH is reachable.
Tue Apr 23 19:59:07 2019 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.conf again, and trying to connect to all servers to check server status..
Tue Apr 23 19:59:07 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 23 19:59:07 2019 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Apr 23 19:59:07 2019 - [info] Reading server configuration from /etc/mha/app1.conf..
Tue Apr 23 19:59:08 2019 - [info] GTID failover mode = 1
Tue Apr 23 19:59:08 2019 - [info] Dead Servers:
Tue Apr 23 19:59:08 2019 - [info] 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:08 2019 - [info] Alive Servers:
Tue Apr 23 19:59:08 2019 - [info] 192.168.240.209(192.168.240.209:3306)
Tue Apr 23 19:59:08 2019 - [info] 192.168.240.210(192.168.240.210:3306)
Tue Apr 23 19:59:08 2019 - [info] Alive Slaves:
Tue Apr 23 19:59:08 2019 - [info] 192.168.240.209(192.168.240.209:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:08 2019 - [info] GTID ON
Tue Apr 23 19:59:08 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:08 2019 - [info] 192.168.240.210(192.168.240.210:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:08 2019 - [info] GTID ON
Tue Apr 23 19:59:08 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:08 2019 - [info] Checking slave configurations..
Tue Apr 23 19:59:08 2019 - [info] read_only=1 is not set on slave 192.168.240.209(192.168.240.209:3306).
Tue Apr 23 19:59:08 2019 - [info] read_only=1 is not set on slave 192.168.240.210(192.168.240.210:3306).
Tue Apr 23 19:59:08 2019 - [info] Checking replication filtering settings..
Tue Apr 23 19:59:08 2019 - [info] Replication filtering check ok.
Tue Apr 23 19:59:08 2019 - [info] Master is down!
Tue Apr 23 19:59:08 2019 - [info] Terminating monitoring script.
Tue Apr 23 19:59:08 2019 - [info] Got exit code 20 (Master dead).
Tue Apr 23 19:59:08 2019 - [info] MHA::MasterFailover version 0.56.
Tue Apr 23 19:59:08 2019 - [info] Starting master failover.
Tue Apr 23 19:59:08 2019 - [info]
Tue Apr 23 19:59:08 2019 - [info] * Phase 1: Configuration Check Phase..
Tue Apr 23 19:59:08 2019 - [info]
Tue Apr 23 19:59:09 2019 - [info] GTID failover mode = 1
Tue Apr 23 19:59:09 2019 - [info] Dead Servers:
Tue Apr 23 19:59:09 2019 - [info] 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:09 2019 - [info] Checking master reachability via MySQL(double check)...
Tue Apr 23 19:59:09 2019 - [info] ok.
Tue Apr 23 19:59:09 2019 - [info] Alive Servers:
Tue Apr 23 19:59:09 2019 - [info] 192.168.240.209(192.168.240.209:3306)
Tue Apr 23 19:59:09 2019 - [info] 192.168.240.210(192.168.240.210:3306)
Tue Apr 23 19:59:09 2019 - [info] Alive Slaves:
Tue Apr 23 19:59:09 2019 - [info] 192.168.240.209(192.168.240.209:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:09 2019 - [info] GTID ON
Tue Apr 23 19:59:09 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:09 2019 - [info] 192.168.240.210(192.168.240.210:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:09 2019 - [info] GTID ON
Tue Apr 23 19:59:09 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:09 2019 - [info] Starting GTID based failover.
Tue Apr 23 19:59:09 2019 - [info]
Tue Apr 23 19:59:09 2019 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Apr 23 19:59:09 2019 - [info]
Tue Apr 23 19:59:09 2019 - [info] * Phase 2: Dead Master Shutdown Phase..
Tue Apr 23 19:59:09 2019 - [info]
Tue Apr 23 19:59:09 2019 - [info] Forcing shutdown so that applications never connect to the current master..
Tue Apr 23 19:59:09 2019 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Tue Apr 23 19:59:09 2019 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Tue Apr 23 19:59:10 2019 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] * Phase 3: Master Recovery Phase..
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] The latest binary log file/position on all slaves is binlog.000003:194
Tue Apr 23 19:59:10 2019 - [info] Retrieved Gtid Set: 135987dd-68b9-11e9-8a47-000c29a35f67:1-3
Tue Apr 23 19:59:10 2019 - [info] Latest slaves (Slaves that received relay log files to the latest):
Tue Apr 23 19:59:10 2019 - [info] 192.168.240.209(192.168.240.209:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:10 2019 - [info] GTID ON
Tue Apr 23 19:59:10 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:10 2019 - [info] 192.168.240.210(192.168.240.210:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:10 2019 - [info] GTID ON
Tue Apr 23 19:59:10 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:10 2019 - [info] The oldest binary log file/position on all slaves is binlog.000003:194
Tue Apr 23 19:59:10 2019 - [info] Retrieved Gtid Set: 135987dd-68b9-11e9-8a47-000c29a35f67:1-3
Tue Apr 23 19:59:10 2019 - [info] Oldest slaves:
Tue Apr 23 19:59:10 2019 - [info] 192.168.240.209(192.168.240.209:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:10 2019 - [info] GTID ON
Tue Apr 23 19:59:10 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:10 2019 - [info] 192.168.240.210(192.168.240.210:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Tue Apr 23 19:59:10 2019 - [info] GTID ON
Tue Apr 23 19:59:10 2019 - [info] Replicating from 192.168.240.208(192.168.240.208:3306)
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] * Phase 3.3: Determining New Master Phase..
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] Searching new master from slaves..
Tue Apr 23 19:59:10 2019 - [info] Candidate masters from the configuration file:
Tue Apr 23 19:59:10 2019 - [info] Non-candidate masters:
Tue Apr 23 19:59:10 2019 - [info] New master is 192.168.240.209(192.168.240.209:3306)
Tue Apr 23 19:59:10 2019 - [info] Starting master failover..
Tue Apr 23 19:59:10 2019 - [info]
From:
192.168.240.208(192.168.240.208:3306) (current master)
+--192.168.240.209(192.168.240.209:3306)
+--192.168.240.210(192.168.240.210:3306)
To:
192.168.240.209(192.168.240.209:3306) (new master)
+--192.168.240.210(192.168.240.210:3306)
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] * Phase 3.3: New Master Recovery Phase..
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] Waiting all logs to be applied..
Tue Apr 23 19:59:10 2019 - [info] done.
Tue Apr 23 19:59:10 2019 - [info] Getting new master's binlog name and position..
Tue Apr 23 19:59:10 2019 - [info] binlog.000002:874
Tue Apr 23 19:59:10 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.240.209', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Tue Apr 23 19:59:10 2019 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: binlog.000002, 874, 135987dd-68b9-11e9-8a47-000c29a35f67:1-3
Tue Apr 23 19:59:10 2019 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Tue Apr 23 19:59:10 2019 - [info] ** Finished master recovery successfully.
Tue Apr 23 19:59:10 2019 - [info] * Phase 3: Master Recovery Phase completed.
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] * Phase 4: Slaves Recovery Phase..
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] * Phase 4.1: Starting Slaves in parallel..
Tue Apr 23 19:59:10 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] -- Slave recovery on host 192.168.240.210(192.168.240.210:3306) started, pid: 17406. Check tmp log /var/log/mha/app1/192.168.240.210_3306_20190423195908.log if it takes time..
Tue Apr 23 19:59:11 2019 - [info]
Tue Apr 23 19:59:11 2019 - [info] Log messages from 192.168.240.210 ...
Tue Apr 23 19:59:11 2019 - [info]
Tue Apr 23 19:59:10 2019 - [info] Resetting slave 192.168.240.210(192.168.240.210:3306) and starting replication from the new master 192.168.240.209(192.168.240.209:3306)..
Tue Apr 23 19:59:10 2019 - [info] Executed CHANGE MASTER.
Tue Apr 23 19:59:10 2019 - [info] Slave started.
Tue Apr 23 19:59:10 2019 - [info] gtid_wait(135987dd-68b9-11e9-8a47-000c29a35f67:1-3) completed on 192.168.240.210(192.168.240.210:3306). Executed 0 events.
Tue Apr 23 19:59:11 2019 - [info] End of log messages from 192.168.240.210.
Tue Apr 23 19:59:11 2019 - [info] -- Slave on host 192.168.240.210(192.168.240.210:3306) started.
Tue Apr 23 19:59:11 2019 - [info] All new slave servers recovered successfully.
Tue Apr 23 19:59:11 2019 - [info]
Tue Apr 23 19:59:11 2019 - [info] * Phase 5: New master cleanup phase..
Tue Apr 23 19:59:11 2019 - [info]
Tue Apr 23 19:59:11 2019 - [info] Resetting slave info on the new master..
Tue Apr 23 19:59:11 2019 - [info] 192.168.240.209: Resetting slave info succeeded.
Tue Apr 23 19:59:11 2019 - [info] Master failover to 192.168.240.209(192.168.240.209:3306) completed successfully.
Tue Apr 23 19:59:11 2019 - [info] Deleted server1 entry from /etc/mha/app1.conf .
Tue Apr 23 19:59:11 2019 - [info]
----- Failover Report -----
app1: MySQL Master failover 192.168.240.208(192.168.240.208:3306) to 192.168.240.209(192.168.240.209:3306) succeeded
Master 192.168.240.208(192.168.240.208:3306) is down!
Check MHA Manager logs at localhost.localdomain:/var/log/mha/app1/app1.log for details.
Started automated(non-interactive) failover.
Selected 192.168.240.209(192.168.240.209:3306) as a new master.
192.168.240.209(192.168.240.209:3306): OK: Applying all logs succeeded.
192.168.240.210(192.168.240.210:3306): OK: Slave started, replicating from 192.168.240.209(192.168.240.209:3306)
192.168.240.209(192.168.240.209:3306): Resetting slave info succeeded.
Master failover to 192.168.240.209(192.168.240.209:3306) completed successfully.
1.3工具包
1.3.1Manager工具包
主要有以下几个工具：
-
masterha_check_ssh检查MHA的SSH配置状况 -
masterha_check_repl检查MySQL复制状况 -
masterha_manger启动MHA -
masterha_check_status检测当前MHA运行状态 -
masterha_master_monitor检测master是否宕机 -
masterha_master_switch控制故障转移（自动或者手动） -
masterha_conf_host添加或删除配置的server信息
1.3.2 Node工具包
这些工具通常由MHA Manager的脚本触发，无需人为操作,主要有以下几个工具：
-
save_binary_logs保存和复制master的二进制日志 -
apply_diff_relay_logs识别差异的中继日志事件并将其差异的事件应用于其他的slave filter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用这个工具） -
purge_relay_logs清除中继日志（不会阻塞SQL线程）
第二章：安装
2.1 规划
| IP | 系统版本 | 数据库版本 | 主从关系 | MHA角色 |
|---|---|---|---|---|
| 192.168.240.208 | CentOS7.6 | MySQL5.7.25 | master | MHA-NODE |
| 192.168.240.209 | CentOS7.6 | MySQL5.7.25 | slave | MHA-NODE |
| 192.168.240.210 | CentOS7.6 | MySQL5.7.25 | slave | MHA-NODE,MHA-MASTER |
准备(所有节点关闭防火墙，selinux)
systemctl stop firewalld
setenforce 0
2.2 安装MySQL5.7.25
安装MySQL5.7.25，安装方式为rpm方式(所有节点)
yum install net-tools -y
yum remove mariadb-libs -y
rpm -ivh mysql-community-client-5.7.25-1.el7.x86_64.rpm \
mysql-community-common-5.7.25-1.el7.x86_64.rpm \
mysql-community-libs-5.7.25-1.el7.x86_64.rpm \
mysql-community-servelr-5.7.25-1.el7.x86_64.rpm
创建目录结构(所有节点)
mkdir -pv /datadir/{temp,log,data}
chown -R mysql:mysql /datadir
初始化数据库(所有节点)
mysqld --initialize-insecure --datadir=/datadir/data --user=mysql
chown -R mysql:mysql /datadir
修改配置文件(所有节点)
[mysqld]
user=mysql
port=3306
datadir=/datadir/data
log-error=/datadir/log/err.log
pid-file=/datadir/temp/mysqld.pid
socket=/datadir/temp/mysql.sock
symbolic-links=0
server_id=208
gtid-mode=on
enforce-gtid-consistency=true
relay_log_purge=0
log_slave_updates=ON
log_bin=/datadir/log/binlog
binlog_format=ROW
[client]
socket=/datadir/temp/mysql.sock
只需要修改server_id这一项即可(保证所有MySQL服务器都不同)
启动数据库(所有节点)
mysqld --defaults-file=/etc/my.cnf --daemonize
2.3 设置主从复制
创建主从复制用户
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
设置从库(所有从库节点)
mysql> CHANGE MASTER TO
MASTER_HOST = '192.168.240.208',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = 'password',
MASTER_AUTO_POSITION = 1;
启动主从复制(所有从库节点)
START SLAVE;
检查是否成功
SHOW SLAVE STATUS\G
当看到IO线程和SQL线程为YES表示搭建成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.4 安装与基本使用
安装依赖的perl模块
DBI与DBD-mysqltar包下载路径 https://dev.mysql.com/downloads/dbi.html
yum install perl-ExtUtils-MakeMaker -y
yum install cpan -y
yum install perl-Test-Simple -y
yum install perl-Devel-CheckLib -y
yum install gcc -y
# 安装mysql开发包，可以在官网下载
rpm -ivh ~/mysql-community-devel-5.7.25-1.el7.x86_64.rpm
cd ~
tar xf DBI-1.642.tar.gz
cd DBI-1.642
perl Makefile.PL
make && make install
cd ~
tar xf DBD-mysql-4.050.tar.gz
cd DBD-mysql-4.050
perl Makefile.PL
make && make install
MHA软件包下载地址
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
安装MHA-Node节点软件包(所有服务器都要安装，因为MHA管理器模块内部依赖于MHA节点模块)
cd ~
tar xf mha4mysql-node-0.56.tar.gz
cd mha4mysql-node-0.56/
perl Makefile.PL
make && make install
安装MHA-Manager软件包(在前期规划中的Manager节点执行)
yum install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y
cd ~
tar xf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56/
perl Makefile.PL
make && make install
2.5 所有主机互相建立互信
所有节点都要执行
# 输入之后一直回车即可
ssh-******
# 根据提示输入信息
for i in {208..210}; do ssh-copy-id [email protected]${i} ; done
2.6 数据库创建mha管理用户
主库创建即可
grant all on *.* to 'mha'@'%' identified by 'Mha@123';
2.7 创建mha目录结构与配置文件
在MHA的管理节点执行
mkdir -p /etc/mha
mkdir -p /var/log/mha/app1
创建mha基本配置文件
[[email protected] bin]# cat /etc/mha/app1.conf
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/app1.log
master_binlog_dir=/datadir/data/
user=mha
password=[email protected]
ping_interval=2
repl_user=repl
repl_password=password
ssh_user=root
[server1]
hostname=192.168.240.208
port=3306
[server2]
hostname=192.168.240.209
port=3306
[server3]
hostname=192.168.240.210
port=3306
[server default]是全局配置，[server#]为私有的配置
| 全局配置参数 | 描述 |
|---|---|
| manager_workdir | 设置manager的工作目录 |
| manager_log | 设置manager的日志路径 |
| master_binlog_dir | 设置master保存binlog的位置 |
| user | 设置监控用户(mysql中的用户非系统用户) |
| password | 设置监控用户的密码 |
| ping_interval | 发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover |
| repl_user | 设置复制的用户 |
| repl_password | 设置复制用户对应的密码 |
| ssh_user | ssh登陆的用户 |
2.8 检查互信与主从状态
mha管理节点执行
masterha_check_ssh --conf=/etc/mha/app1.conf
masterha_check_repl --conf=/etc/mha/app1.conf
2.9 启动MHA
mha管理节点执行
nohup masterha_manager --conf=/etc/mha/app1.conf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/app1.log 2>&1 &
nohub masterha_manager
# --remove_dead_master_conf 故障的主库从配置文件中删除
# --ignore_last_failover 在缺省情况下，如果MHA检测到连续发生宕机，且两次宕机间隔不足8小时的话，则不会进行Failover，之所以这样限制是为了避免ping-pong效应。该参数代表忽略上次MHA触发切换产生的文件，默认情况下，MHA发生切换后会在日志目录，也就是上面我设置的/data产生app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件，为了方便，这里设置为--ignore_last_failover。
第三章：高级配置
3.1定期清除中继日志
默认情况下，如果SQL线程已完成执行，则会自动删除从属服务器上的中继日志。但是仍然需要这样的中继日志来恢复其他从属。因此，您需要禁用自动中继日志清除，并定期清除旧的中继日志。但是，在手动清除中继日志时，您需要关心复制延迟问题。在ext3文件系统上，删除大文件需要很长时间，这将导致严重的复制延迟。为了避免复制延迟，暂时创建中继日志的硬链接会有所帮助。
MHA Node有一个命令行工具purge_relay_logs来做到这一点。它创建硬链接，执行SET GLOBAL relay_log_purge = 1，等待几秒钟，以便SQL线程可以切换到新的中继日志（只要它显着落后），并执行SET GLOBAL relay_log_purge = 0
purge_relay_logs删除中继日志而不阻塞SQL线程。需要定期清除中继日志（每天一次，每6小时一次等），因此应使用定时任务在每个从属服务器上定期调用purge_relay_logs。例如，您可以从cron调用purge_relay_logs，如下所示。
[[email protected] data]# crontab -l
# purge relay logs at 0am
0 0 * * * /usr/local/bin/purge_relay_logs --user=mha --password=[email protected] --disable_relay_log_purge >> /var/log/mha/app1/purge_relay_logs.log 2>&1
官方文档 https://github.com/yoshinorim/mha4mysql-manager/wiki/Requirements#purge_relay_logs_script
3.2设置候选master
通过下面2个参数可以决定在主从切换的时候哪台从库一定会升为主库
# 设置为候选master，发生主从切换之后会将此数据库生为主库
candidate_master=1
# 默认情况下如果有一个slave落后master 100M的relay logs的话
# MHA不会选择该slave作为新master，因为对于这个slave的恢复需要很长时间，通过设置check_repl_delay=0，MHA触发切换在选择新的master的时候会忽略复制延时检测，与candidate_master配合使用，可以保证某个机器主从切换后一定为主
check_repl_delay=0
3.3配置vip
完整脚本如下（需要手工创建）
[[email protected] scripts]# cat /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.240.250/24';
my $key = '1';
my $ssh_start_vip = "/usr/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/usr/sbin/ifconfig ens33:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ([email protected]) {
warn "Got Error: [email protected]\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ([email protected]) {
warn [email protected];
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
主要修改的部分是
my $vip = '192.168.240.250/24'; # vip地址
my $key = '1';
my $ssh_start_vip = "/usr/sbin/ifconfig ens33:$key $vip"; # 启动vip的命令
my $ssh_stop_vip = "/usr/sbin/ifconfig ens33:$key down"; # 关闭vip的命令
使用步骤：
- 修改配置文件
/etc/mha/app1.conf,全局配置部分添加切换vip的脚本路径
配置文件添加下面一行
# ip切换脚本路径
master_ip_failover_script=/usr/local/bin/master_ip_failover
- 在主库手动启动vip
/usr/sbin/ifconfig ens33:1 192.168.240.250/24
- 重启mha
# 关闭
masterha_stop --conf=/etc/mha/app1.conf
# 启动
nohup masterha_manager --conf=/etc/mha/app1.conf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/app1.log 2>&1 &
binlogserver
设置binlogserver为了将主库binlog实时备份都服务器保存
服务器要求：MySQL5.6以上版本，支持GTID并开启
[binlog1]
no_master=1
hostname=192.168.240.210
master_binlog_dir=/data/mysql/binlog
创建此目录
mkdir -p /data/mysql/binlog
拉取binlog
cd /data/mysql/binlog
# 从主库的binlog.000001开始拉取
mysqlbinlog -R --host=192.168.240.250 --user=mha --password=[email protected] --raw --stop-never binlog.000001 &
启动MHA之前，必须要先执行拉取binlog,然后MHA配置文件中填写binlog server相关信息
当故障回复的时候，需要手动更改主库ip，重新拉取
第四章：常见错误
4.1 从库不小心写入，导致MHA切换失败
解决方式
# 从：
stop slave
reset slave
reset master
# 新主：
stop slave
reset slave
reset master
# 从：
change