MHA配置方法

MHA搭建步骤
一、安装软件包
-rwxr-x---.  1 root root  21272 Jul  6  2016 perl-Config-Tiny-2.12-1.el6.rfx.noarch.rpm*
-rwxr-x---.  1 root root  72328 Jul  6  2016 perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm*
-rwxr-x---.  1 root root  14840 Jul  6  2016 perl-Parallel-ForkManager-0.7.5-2.2.el6.rf.noarch.rpm*
-rwxr-x---.  1 root root  76728 Jul  6  2016 perl-Params-Validate-0.92-3.el6.x86_64.rpm*
-rwxr-x---.  1 root root 118521 Nov  3  2016 mha4mysql-manager-0.57.tar.gz*
-rwxr-x---.  1 root root  54484 Nov  3  2016 mha4mysql-node-0.57.tar.gz*
-rwxr-x---.  1 root root  10353 Aug  2  2018 master_ip_online_change*
-rwxr-x---.  1 root root   2350 Aug  2  2018 master_ip_failover*
首先需要安装依赖包
rpm -ivh *.rpm
安装管理节点软件
cd mha4mysql-manager-0.57
perl Makefile.PL
make && make install
安装node节点软件
cd mha4mysql-node-0.57
perl Makefile.PL
make && make install
二、修改参数化参数文件
MASTER上修改配置,双一原则，保证数据库不丢失
innodb_flush_log_at_trx_commit = 1 --> redo log 1写磁盘、2写系统缓存(操作系统挂可能丢数据)、0写redo log buffer(mysql挂可能丢数据)
sync_binlog = 1 --> binlog 1写磁盘、0写系统缓存

SLAVE上修改配置
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

确保binlog格式为row
binlog_format='ROW'

三、创建MAH管理用户
CREATE USER 'admin'@'192.168.10.%' IDENTIFIED BY 'admin';
GRANT SUPER,RELOAD,REPLICATION CLIENT,SELECT ON *.* TO 'admin'@'192.168.10.%';

四、配置root的免密码登陆，利用oracle rac的脚本
sh a.sh -user root -hosts "m1 m2 m3" -advanced -noPromptPassphrase 

五、修改主节点和候选节点的配置文件
主
 vi /etc/masterha/app1.cnf 
[server default]
manager_workdir=/masterha/app1
manager_log=/mysqlapp/mysql/log/manager.log
user=admin
password=admin
ssh_user=root
repl_user=rep
repl_password=rep
ping_interval=1
shutdown_script=""
master_ip_online_change_script=""
report_script=""


[server1]
hostname=192.168.10.100
port=3306
candidate_master=1
master_binlog_dir="/data/3306"


[server2]
hostname=192.168.10.101
port=3306
candidate_master=1
master_binlog_dir="/data/3306"

[server3]
hostname=192.168.10.103
port=3306

候选主
[server default]
manager_workdir=/masterha/app1
manager_log=/mysqlapp/mysql/log/manager.log
user=admin
password=admin
ssh_user=root
repl_user=rep
repl_password=rep
ping_interval=1
shutdown_script=""
master_ip_online_change_script=""
report_script=""


[server1]
hostname=192.168.10.101
port=3306
candidate_master=1
master_binlog_dir="/data/3306"


[server2]
hostname=192.168.10.100
port=3306
candidate_master=1
master_binlog_dir="/data/3306"

[server3]
hostname=192.168.10.103
port=3306

六、检查ssh连通性
[root@m1 mha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Sat Jun 22 22:04:02 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Jun 22 22:04:02 2019 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Jun 22 22:04:02 2019 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Jun 22 22:04:02 2019 - [info] Starting SSH connection tests..
Sat Jun 22 22:04:03 2019 - [debug] 
Sat Jun 22 22:04:02 2019 - [debug]  Connecting via SSH from root@192.168.10.100(192.168.10.100:22) to root@192.168.10.101(192.168.10.101:22)..
Sat Jun 22 22:04:02 2019 - [debug]   ok.
Sat Jun 22 22:04:02 2019 - [debug]  Connecting via SSH from root@192.168.10.100(192.168.10.100:22) to root@192.168.10.103(192.168.10.103:22)..
Sat Jun 22 22:04:03 2019 - [debug]   ok.
Sat Jun 22 22:04:03 2019 - [debug] 
Sat Jun 22 22:04:03 2019 - [debug]  Connecting via SSH from root@192.168.10.101(192.168.10.101:22) to root@192.168.10.100(192.168.10.100:22)..
Sat Jun 22 22:04:03 2019 - [debug]   ok.
Sat Jun 22 22:04:03 2019 - [debug]  Connecting via SSH from root@192.168.10.101(192.168.10.101:22) to root@192.168.10.103(192.168.10.103:22)..
Sat Jun 22 22:04:03 2019 - [debug]   ok.
Sat Jun 22 22:04:04 2019 - [debug] 
Sat Jun 22 22:04:03 2019 - [debug]  Connecting via SSH from root@192.168.10.103(192.168.10.103:22) to root@192.168.10.100(192.168.10.100:22)..
Sat Jun 22 22:04:03 2019 - [debug]   ok.
Sat Jun 22 22:04:03 2019 - [debug]  Connecting via SSH from root@192.168.10.103(192.168.10.103:22) to root@192.168.10.101(192.168.10.101:22)..
Sat Jun 22 22:04:04 2019 - [debug]   ok.
Sat Jun 22 22:04:04 2019 - [info] All SSH connection tests passed successfully.
七、检查同步用户的连通情况
[root@m1 ~]#  masterha_check_repl --conf=/etc/masterha/app1.cnf
Sat Jun 22 22:09:57 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Jun 22 22:09:57 2019 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Jun 22 22:09:57 2019 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Jun 22 22:09:57 2019 - [info] MHA::MasterMonitor version 0.57.
Sat Jun 22 22:09:57 2019 - [info] GTID failover mode = 1
Sat Jun 22 22:09:57 2019 - [info] Dead Servers:
Sat Jun 22 22:09:57 2019 - [info] Alive Servers:
Sat Jun 22 22:09:57 2019 - [info]   192.168.10.100(192.168.10.100:3306)
Sat Jun 22 22:09:57 2019 - [info]   192.168.10.101(192.168.10.101:3306)
Sat Jun 22 22:09:57 2019 - [info]   192.168.10.103(192.168.10.103:3306)
Sat Jun 22 22:09:57 2019 - [info] Alive Slaves:
Sat Jun 22 22:09:57 2019 - [info]   192.168.10.100(192.168.10.100:3306)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Sat Jun 22 22:09:57 2019 - [info]     GTID ON
Sat Jun 22 22:09:57 2019 - [info]     Replicating from 192.168.10.101(192.168.10.101:3306)
Sat Jun 22 22:09:57 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Jun 22 22:09:57 2019 - [info]   192.168.10.103(192.168.10.103:3306)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Sat Jun 22 22:09:57 2019 - [info]     GTID ON
Sat Jun 22 22:09:57 2019 - [info]     Replicating from 192.168.10.101(192.168.10.101:3306)
Sat Jun 22 22:09:57 2019 - [info] Current Alive Master: 192.168.10.101(192.168.10.101:3306)
Sat Jun 22 22:09:57 2019 - [info] Checking slave configurations..
Sat Jun 22 22:09:57 2019 - [info]  read_only=1 is not set on slave 192.168.10.100(192.168.10.100:3306).
Sat Jun 22 22:09:57 2019 - [info]  read_only=1 is not set on slave 192.168.10.103(192.168.10.103:3306).
Sat Jun 22 22:09:57 2019 - [info] Checking replication filtering settings..
Sat Jun 22 22:09:57 2019 - [info]  binlog_do_db= , binlog_ignore_db= 
Sat Jun 22 22:09:57 2019 - [info]  Replication filtering check ok.
Sat Jun 22 22:09:57 2019 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Jun 22 22:09:57 2019 - [info] Checking SSH publickey authentication settings on the current master..
Sat Jun 22 22:09:57 2019 - [info] HealthCheck: SSH to 192.168.10.101 is reachable.
Sat Jun 22 22:09:57 2019 - [info] 
192.168.10.101(192.168.10.101:3306) (current master)
 +--192.168.10.100(192.168.10.100:3306)
 +--192.168.10.103(192.168.10.103:3306)

Sat Jun 22 22:09:57 2019 - [info] Checking replication health on 192.168.10.100..
Sat Jun 22 22:09:57 2019 - [info]  ok.
Sat Jun 22 22:09:57 2019 - [info] Checking replication health on 192.168.10.103..
Sat Jun 22 22:09:57 2019 - [info]  ok.
Sat Jun 22 22:09:57 2019 - [warning] master_ip_failover_script is not defined.
Sat Jun 22 22:09:57 2019 - [warning] shutdown_script is not defined.
Sat Jun 22 22:09:57 2019 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
八、启动mha
主节点启动mha
[root@m1 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log  2>&1 &
[1] 13272
[root@m1 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf 
app1 (pid:13272) is running(0:PING_OK), master:192.168.10.101

删除老的切换记录文件
rm -rf  rm /masterha/app1/app1.failover.complete

关闭主节点，验证是否生效

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.101
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 234
               Relay_Log_File: m3-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

从节点会自动切换
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.100
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 234
               Relay_Log_File: m3-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
候选节点会升级为主，原主节点会脱离。

将脱离的主机点重新加入到主从。
CHANGE MASTER TO
 MASTER_HOST='192.168.10.100',
  MASTER_USER='rep',
  MASTER_PASSWORD='rep',
  MASTER_AUTO_POSITION = 1;
start slave;
show slave status \G;


再在新的主节点启动mah管理进程

rm -rf  rm /masterha/app1/app1.failover.complete
nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log  2>&1 &
 masterha_check_status --conf=/etc/masterha/app1.cnf 
 
app1 (pid:23378) is running(0:PING_OK), master:192.168.10.100

CHANGE MASTER TO
 MASTER_HOST='192.168.10.101',
  MASTER_USER='rep',
  MASTER_PASSWORD='rep',
  MASTER_AUTO_POSITION = 1;
start slave;
show slave status \G;