基础环境:
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
二、配置ssh免密登陆
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
三、主从复制环境的搭建(基于gtid的半同步)
1.基本配置操作
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
|
重启数据库: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
|
重启数据库:service mysqld restart
192.168.153.138节点上的操作
修改数据库配置文件:vim /etc/my.cnf
|
重启数据库:service mysqld restart
2.开启主从
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恢复过程依赖中继日志相关信息)
3.配置半同步复制(详见:https://blog.csdn.net/line_on_database/article/details/104517952)
加载插件:
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相同
四、部署MHA
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
附加文件权限