MHA高可用配置

操作步骤：
【1】安装MySQL数据库
【2】配置MySQL一主两从
【3】安装MHA软件
【4】配置无密码认证—免密登录
【5】配置MySQL、MHA高可用
【6】模拟master故障切换

——————————————MySQL5.6数据库的配置————————
//安装依赖环境
yum install -y ncurses-devel gcc-c++ perl-Module-Install

//安装gmake编译软件
tar zxvf cmake-2.8.6.tar.gz
cd cmake-2.8.6
./configure
gmake && gmake install

//安装MySQL数据库
tar zxvf mysql-5.6.36.tar.gz
cd mysql-5.6.36
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DSYSCONFIDIR=/etc 

make && make install

cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile

groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
chown -R mysql.mysql /usr/local/mysql
mkdir -p /data/mysql

/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--datadir=/usr/local/mysql/data \
--basedir=/usr/local/mysql

ln -s /usr/local/mysql/bin/mysql /usr/sbin
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin

/usr/local/mysql/bin/mysqld_safe --user=mysql &     //启动mysql服务

——————————————master服务器的配置——————————
vim /etc/my.cnf
[mysql1]
server-id = 70
log_bin = master-bin
log-slave-updates=true

/usr/local/mysql/bin/mysqld_safe --user=mysql &     //启动mysql服务
&
mysql -uroot -p
password:无密码

grant replication slave on *.* to 'myslave'@'192.168.169.%' identified by '123';  //数据库同步权限
grant all privileges on *.* to 'mha'@'192.168.169.%' identified by 'manager';
flush privileges;

此三条按照理论是不用添加的，但是实验会出现报错--两个slave通过主机名连接不上主库master
grant all privileges on *.* to 'mha'@'mysql1' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql2' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql3' identified by 'manager';
flush privileges;

show master status;
master_log_file='master-bin.000002',
master_log_pos=603;
——————————————slave01服务器的配置——————————————————————
vim /etc/my.cnf
[mysqld]
server-id = 80
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

/usr/local/mysql/bin/mysqld_safe --user=mysql  &    //启动mysql服务

mysql -uroot -p
password:无密码

grant replication slave on *.* to 'myslave'@'192.168.169.%' identified by '123';  //数据库同步权限
grant all privileges on *.* to 'mha'@'192.168.169.%' identified by 'manager';
flush privileges;

此三条按照理论是不用添加的，但是实验会出现报错--两个slave通过主机名连接不上主库master
grant all privileges on *.* to 'mha'@'mysql1' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql2' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql3' identified by 'manager';
flush privileges;

change master to master_host='192.168.169.88',
master_user='myslave',
master_password='123',
master_log_file='master-bin.000001',
master_log_pos=120;

start slave；
show slave status\G;

set global read only=1;     //必须把两个从库设置为全局只读模式
flush privileges;

——————————————slave02服务器的配置————————————————
vim /etc/my.cnf
[mysqld]
server-id = 90
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

/usr/local/mysql/bin/mysqld_safe --user=mysql  &    //启动mysql服务

mysql -uroot -p
password:无密码

grant replication slave on *.* to 'myslave'@'192.168.169.%' identified by '123';  //数据库同步权限
grant all privileges on *.* to 'mha'@'192.168.169.%' identified by 'manager';
flush privileges;

此三条按照理论是不用添加的，但是实验会出现报错--两个slave通过主机名连接不上主库master
grant all privileges on *.* to 'mha'@'mysql1' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql2' identified by 'manager';
grant all privileges on *.* to 'mha'@'mysql3' identified by 'manager';
flush privileges;

change master to master_host='192.168.169.88',
master_user='myslave',
master_password='123',
master_log_file='master-bin.000001',
master_log_pos=120;

start slave；
show slave status\G;

set global read only=1;   //必须把两个从库设置为全局只读模式
flush privileges;

——————————————安装MHA软件manager服务器配置—————————————
//四个节点所有服务器安装MHA依赖环境，首先安装epel源
yum install -y epel-release --nogpgcheck 

yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN
                   连接数据库的                配置文件             日志                           管理框架                            构建

//MHA软件包对每个操作系统的版本不一样，centos7.4必须选择0.57版本，
在所有服务器上面必须先安装node组件，最后在MHA-manager节点上安装manager组件，因为manager依赖的是node组件

tar zxvf mha4mysql-node-0.57.tar.gz
cd mha4mysql-node-0.57
perl Makefile.PL
make && make install

//在MHA-manager上面安装manager组件
tar zxvf mha4MHA-manager-0.57.tar.gz
cd mhaMHA-manager-0.57
perl Makefile.PL
make && make install

manager安装后再/usr/local/bin下面生成几个工具:
masterha_check_ssh    //检查MHA的ssh配置状况
masterha_check_repl   //检查MySQL的复制状况
masterha_manager    //启动manager的脚本
masterha_check_status   //检测当前MHA运行状态
masterha_master_monitor   //检测master是否宕机
masterha_master_switch   //控制故障转移（自动或手动）
masterha_conf_host   //添加或删除配置的server信息
masterha_stop    //关闭manager

//node安装后也会在/usr/local/bin下面生成几个脚本（这些工具通常有MHA-Manager的脚本触发，无需人为的操作）
save_binary_logs   //保存和复制master的二进制日志
apply_diff_relay_logs  //识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog   //去除不必要的rollback事件（MHA已经不再使用这个工具）
purge_relay_logs   //清除中级日志（不会阻塞SQL线程）

//配置免密登录
1、在manager上面配置到所有数据库节点的无密码认证
ssh-keygen -t rsa  //一路回车
ssh-copy-id 192.168.169.88
ssh-copy-id 192.168.169.100
ssh-copy-id 192.168.169.200
2、在mysql1上面配置到所有数据库节点的无密码认证
ssh-keygen -t rsa  //一路回车
ssh-copy-id 192.168.169.100
ssh-copy-id 192.168.169.200
3、在mysql2上面配置到所有数据库节点的无密码认证
ssh-keygen -t rsa  //一路回车
ssh-copy-id 192.168.169.88
ssh-copy-id 192.168.169.200
3、在mysql3上面配置到所有数据库节点的无密码认证
ssh-keygen -t rsa  //一路回车
ssh-copy-id 192.168.169.88
ssh-copy-id 192.168.169.100

//配置MHA
1、在manager节点上复制相关脚本到/usr/local/bin目录
cp -ra /opt/mha4MHA-manager-0.57/samples/scripts /usr/local/bin
ls /usr/local/bin/scripts
master_ip_failover    //自动切换为vip（虚拟IP）管理的脚本
master_ip_online_change   //在线切换时vip管理脚本
power_manager    //故障发生后关闭主机的脚本
send_report    //因故障切换后发送报警的脚本

2、复制自动切换vip管理脚本到/usr/local/bin下面
cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
3、删除所有内容，复制下面所有
vim /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.169.220';
my $brdc = '192.168.169.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;
#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";
##################################################################################
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";
}

4、创建MHA软件目录并拷贝配置文件
mkdir /etc/masterha
cp /opt/mha4MHA-manager-0.57/samples/conf/app1.cnf /etc/masterha
vim /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/usr/local/mysql/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
ping_interval=1
remote_workdir=/tmp
repl_user=myslave
repl_password=123
report_script=/usr/local/bin/scripts/send_report
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.169.100 -s 192.168.169.200
shutdown_script=""
ssh_user=root
user=mha
password=manager

[server1]
hostname=192.168.169.88
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.169.100
port=3306

[server3]
hostname=192.168.169.200
port=3306



//测试ssh无密码认证，输出要是successfully
masterha_check_ssh -conf=/etc/masterha/app1.cnf

//测试为OK即可
masterha_check_repl -conf=/etc/masterha/app1.cnf

//注意：第一次配置需要去master上手动开启虚拟IP
/sbin/ifconfig ens33:1 192.168.169.220/24

//启动MHA
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 &

//查看MHA状态，可以看到当前的master是master服务器的节点ip
masterha_check_status --conf=/etc/masterha/app1.cnf

//查看MHA日志，也可以看到当前master是slave01节点IP
cat /var/log/masterha/app1/manager.log


——————————验证——————————————————
tailf /var/log/masterha/app1/manager.log   //在MHA上启动监控观察日志记录

pkill -9 mysql     //在主服务器mysql上模拟数据库宕机

ifconfig    //查看虚拟IP是否漂移到mysql2上面