Mha_1_master
192.168.153.136
Mha_1_slave
192.168.153.137
Mha_1_slave
192.168.153.138
Mha_2_master
192.168.153.129
Mha_2_slave
192.168.153.130
Mha_2_slave
192.168.153.132
管理节点
192.168.153.135
除192.168.153.135以外的所有节点都要安装数据库，版本最好相同，无限制的情况下采用5.7的最新稳定版本，确保除192.168.153.135以外的所有节点3306端口的开放
查看端口是否开放的方法：firewall-cmd --list-ports
开放端口的方法：1.firewall-cmd --zone=public --add-port=22/tcp --permanent
2. firewall-cmd --reload
192.168.153.135节点上的操作：
生成秘钥：ssh-keygen -t rsa # 该命令敲击之后一直回车直至完成
发送公钥到对应的要免密服务器：
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.129
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.130
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.132
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.136
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.137
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.138
192.168.153.129节点上的操作：
生成秘钥：ssh-keygen -t rsa # 该命令敲击之后一直回车直至完成
发送公钥到对应的要免密服务器：
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.130
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.132
192.168.153.130节点上的操作：
生成秘钥：ssh-keygen -t rsa # 该命令敲击之后一直回车直至完成
发送公钥到对应的要免密服务器：
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.129
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.132
192.168.153.132节点上的操作：
生成秘钥：ssh-keygen -t rsa # 该命令敲击之后一直回车直至完成
发送公钥到对应的要免密服务器：
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.129
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.130
192.168.153.136节点上的操作：
生成秘钥：ssh-keygen -t rsa # 该命令敲击之后一直回车直至完成
发送公钥到对应的要免密服务器：
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.137
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.138
192.168.153.137节点上的操作：
生成秘钥：ssh-keygen -t rsa # 该命令敲击之后一直回车直至完成
发送公钥到对应的要免密服务器：
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.136
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.138
192.168.153.138节点上的操作：
生成秘钥：ssh-keygen -t rsa # 该命令敲击之后一直回车直至完成
发送公钥到对应的要免密服务器：
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.136
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.153.137
192.168.153.136节点上的操作：
创建复制用户：mysql> create user repl@'192.168.153.%' identified by 'repl_test123';
授权用户复制权限：mysql> grant replication slave on *.* to repl@'192.168.153.%';
刷新权限表：mysql> flush privileges;
修改数据库配置文件：vim /etc/my.cnf
[mysqld]
server-id = 1
gtid-mode=on
enforce-gtid-consistency=on
log-bin=binlog
binlog-format=row
重启数据库：service mysqld restart
192.168.153.137节点上的操作
创建复制用户：mysql> create user repl@'192.168.153.%' identified by 'repl_test123';
授权用户复制权限：mysql> grant replication slave on *.* to repl@'192.168.153.%';
刷新权限表：mysql> flush privileges;
修改数据库配置文件：vim /etc/my.cnf
[mysqld]
server-id = 2
gtid-mode=on
enforce-gtid-consistency=on
log-bin=binlog
binlog-format=row
log-slave-updates=1
重启数据库：service mysqld restart
192.168.153.138节点上的操作
修改数据库配置文件：vim /etc/my.cnf
[mysqld]
server-id = 3
gtid-mode=on
enforce-gtid-consistency=on
log-bin=binlog
binlog-format=row
log-slave-updates=1
重启数据库：service mysqld restart
192.168.153.137节点上的操作
开启复制：change master to
master_host='192.168.153.136',master_port=3306,master_user='repl',master_password='repl_test123',master_auto_position=1;
从表设置只读: set global read_only=1;
关闭中继日志自动清理：set global relay_log_purge=off;（因为MHA恢复过程依赖中继日志相关信息）
192.168.153.138节点上的操作
开启复制：change master to
master_host='192.168.153.136',master_port=3306,master_user='repl',master_password='repl_test123',master_auto_position=1;
从表设置只读: set global read_only=1;
关闭中继日志自动清理：set global relay_log_purge=off;（因为MHA恢复过程依赖中继日志相关信息）
加载插件：
192.168.153.136节点上的操作：
install plugin rpl_semi_sync_master soname 'semisync_master.so';
192.168.153.137和192.168.153.138节点上的操作：
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
启动半同步复制：
192.168.153.136节点上的操作：
set global rpl_semi_sync_master_enabled=1;
192.168.153.137和192.168.153.138节点上的操作：
set global rpl_semi_sync_slave_enabled=1;
重启从库IO线程：
192.168.153.137和192.168.153.138节点上的操作：
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD；
192.168.153.129，192.168.153.130，192.168.153.132的操作与192.168.153.136，192.168.153.137，192.168.153.138相同
1.检查依赖（所有节点）
rpm
-qa|grep 'perl-Config-Tiny\|perl-Log-Dispatch\|perl-DBD-MySQL\|perl-Parallel-ForkManager'
2.安装缺失的依赖（所有节点）
yum -y install
找不到yum源的解决办法
yum -y install epel-release
3.安装mha4mysql-node-0.58-0.el7.centos.noarch.rpm包(所有节点)
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
4.安装mha4mysql-manager-0.58-0.el7.centos.noarch.rpm包（192.168.153.135）
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
5.创建目录
mkdir -p /etc/masterha/
mkdir -p /var/log/masterha/app1/
mkdir -p /var/log/masterha/app2/
6.配置文件
vim /etc/masterha/app1.cnf
vim /etc/masterha/app2.cnf
vim /usr/local/bin/master_ip_failover
vim /usr/local/bin/master_ip_failover2
vim /usr/local/bin/master_ip_online_change
vim /usr/local/bin/master_ip_online_change2
vim /usr/local/bin/send_report
vim /usr/local/bin/send_report2
7.授权
chmod 777 /usr/local/bin/master_ip_failover
chmod 777 /usr/local/bin/master_ip_failover2
chmod 777 /usr/local/bin/master_ip_online_change
chmod 777 /usr/local/bin/master_ip_online_change2
chmod 777 /usr/local/bin/send_report
chmod 777 /usr/local/bin/send_report2
8.检查管理节点到所有Node节点的ssh连接状态
masterha_check_ssh --conf=/etc/masterha/app1.cnf
masterha_check_ssh --conf=/etc/masterha/app2.cnf
9. 检查复制环境
masterha_check_repl --conf=/etc/masterha/app1.cnf
masterha_check_repl --conf=/etc/masterha/app2.cnf
这里如果报mysqlbinlog的错误（注：如果此时Executed_Gtid_Set不为空，初步校验时不会用到mysqlbinlog，但是在自动切换时会出现问题）
则需要建立软连接
ln -s /data/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /data/mysql/bin/mysql /usr/local/bin/mysql
10. 检查管理节点的状态
masterha_check_status --conf=/etc/masterha/app1.cnf
masterha_check_status --conf=/etc/masterha/app2.cnf
11.开启管理节点监控
nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
nohup masterha_manager --conf=/etc/masterha/app2.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app2/manager.log 2>&1 &
至此,MHA基本搭建完成
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.153.204/24'; #这里需要改，漂移的VIP my $key = "2"; my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #这两行需要修改，一行是临时添加IP，一行是临时删除IP my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #注意能够使用的命令是ip还是ifconfig，以及网卡名称 my $exit_code = 0; 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" ) { # $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. my $exit_code = 1; eval { print "\n\n\n***************************************************************\n"; print "Disabling the VIP - $vip on old master: $orig_master_host\n"; print "***************************************************************\n\n\n\n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10; eval { print "\n\n\n***************************************************************\n"; print "Enabling the VIP - $vip on new master: $new_master_host \n"; print "***************************************************************\n\n\n\n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `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"; }邮件相关
1. yum install mailx
2. 首先在邮箱中开启smtp，开启后会得到一个授权码，这个授权码就代替了密码（自行去邮箱开启）
3. 请求数字证书（这里用的qq邮箱，所以向qq请求证书）
mkdir -p /root/.certs/
echo -n | openssl s_client -connect smtp.qq.com:465 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > ~/.certs/qq.crt
certutil -A -n "GeoTrust SSL CA" -t "C,," -d ~/.certs -i ~/.certs/qq.crt
certutil -A -n "GeoTrust Global CA" -t "C,," -d ~/.certs -i ~/.certs/qq.crt
certutil -A -n "GeoTrust SSL CA - G3" -t "Pu,Pu,Pu" -d ~/.certs/./ -i qq.crt
4.修改配置文件
set from= #显示的发件人，必须和认证用户邮箱一致
set smtp="smtps://smtp.qq.com:465" #指定第三方发邮件的smtp服务器地址
set smtp-auth-user= #SMTP认证用户邮箱
set smtp-auth-password= #SMTP授权码，不是邮箱密码
set smtp-auth=login # 认证方式，默认是login，也可以改成CRAM-MD5或PLAIN方式
set nss-config-dir=/root/.certs
5.邮件发送测试
echo "Hello" | mail -v -s "test" 29****@qq.com
vim /usr/local/bin/send_report
#/bin/bash source /root/.bash_profile orig_master_host=`echo "$1" | awk -F = '{print $2}'` new_master_host=`echo "$2" | awk -F = '{print $2}'` new_slave_hosts=`echo "$3" | awk -F = '{print $2}'` subject=`echo "$4" | awk -F = '{print $2}'` body=`echo "$5" | awk -F = '{print $2}'` #判断日志结尾是否有successfully，有则表示切换成功，成功与否都发邮件。 tac /var/log/masterha/app1/manager.log | sed -n 2p | grep 'successfully' > /dev/null if [ $? -eq 0 ] then echo -e "MHA $subject 主从切换成功\n master:$orig_master_host --> $new_master_host \n $body \n 当前从库:$new_slave_hosts" | mail -s "MySQL实例宕掉,MHA $subject 切换成功" -- 29****@qq.com else echo -e "MHA $subject 主从切换失败\n master:$orig_master_host --> $new_master_host \n $body" | mail -s "MySQL实例宕掉,MHA $subje ct 切换失败" -- 29****@qq.com fi
附加文件权限
