MHA+binlogserver+VIP+sendreport 高可用架构
在MHA基础架构上进行升级改造
1.binlogsever：日志补偿
2.VIP功能：应用透明
3.sendreport：及时提醒
MHA高可用环境规划和实施
规划：
主库：db01
从库：db02，db03（manager）
主从环境：
一主两从GITD复制
确定关键程序位置（所有节点）：
'''
ln -s /app/database/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /app/database/mysql/bin/mysql /usr/bin/mysql
'''
配置个节点互信：
ssh-keygen
for i in 12 13 14 ;do ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.0.0.${i};done
#验证
for i in 12 13 14 ;do ssh root@10.0.0.${i} date;done
Fri Jul 17 15:03:21 CST 2020
Fri Jul 17 15:03:22 CST 2020
Fri Jul 17 15:03:22 CST 2020
部署基本MHA环境架构
下载mha
curl -O https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
curl -O https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
安装node
for i in 12 13 14 ;do ssh root@10.0.0.${i} yum -y install perl-DBD-MySQL;done
for i in 12 13 14 ;do ssh root@10.0.0.${i} rpm -ivh /tmp/mha4mysql-node-0.58-0.el7.centos.noarch.rpm;done
在db01主库中创建mha需要的用户（全部节点创建）
grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';
Manager软件安装（db03）
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
配置文件准备(db03)
#创建配置文件目录
mkdir -p /etc/mha
#创建日志目录
mkdir -p /var/log/mha/app1
#编辑mha配置文件
cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123456
repl_user=repl
ssh_user=root
[server1]
hostname=10.0.0.12
port=3306
[server2]
hostname=10.0.0.13
port=3306
[server3]
hostname=10.0.0.14
port=3306
EOF
状态检查(db03)
masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf
开启MHA(db03)：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
查看MHA状态
masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:2033) is running(0:PING_OK), master:10.0.0.12
MHA基础架构升级改造
MHA 应用透明(vip,db03)
vi /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 = '10.0.0.15';
my $brdc = '10.0.0.2';
my $ifdev = 'eth0';
my $key = '1';
my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
########RD##########################################################################
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 ($@) {
warn "Got Error: $@\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 ($@) {
warn $@;
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 \"`;
}
# 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";
}
yum install -y dos2unix
dos2unix /usr/local/bin/master_ip_failover
chmod +x /usr/local/bin/master_ip_failover
#添加配置文件
vim /etc/mha/app1.cnf
[server default]
master_ip_failover_script=/usr/local/bin/master_ip_failover
#db01:手工添加vip
ip addr add 10.0.0.15/24 brd 10.0.0.2 dev eth0 label eth0:1
ping 10.0.0.15
#db03 : 重启MHA
masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
masterha_check_status --conf=/etc/mha/app1.cnf
MHA 故障提醒
配置邮件服务
yum install -y sendmail mailx libreport-plugin-mailx sharutils mutt
vi /etc/mail.rc
set from=11@163.com #之前设置好的邮箱地址
set smtp=smtp.163.com #邮件服务器
set smtp-auth-user=11.com #之前设置好的邮箱地址
set smtp-auth-password=11 #授权码
set smtp-auth=login #默认login
配置HMA
vim /etc/mha/app1.cnf
report_script='echo "你的MHA已经自动转移"|mailx -v -s 'MHA 以转移' wxlinux@126.com'
额外的数据补偿(binlog_server)
vim /etc/mha/app1.cnf
[binlog1]
no_master=1
hostname=10.0.0.14
master_binlog_dir=/data/mysql/binlog
#创建必要目录
mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/*
#拉取主库binlog日志
cd /data/mysql/binlog
mysqlbinlog -R --host=10.0.0.12 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
#注意：
#拉取日志的起点,需要按照目前主库正在使用的binlog为起点.
#重启MHA-manager
masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
