MySQL数据库MHA, VIP 故障转移

MHA 加上MySQL 的半同步机制（Semi-Synchronous），可以保证master故障发生时，数据不丢失。
准备4台机器，IP分别为：
192.168.86.130

192.168.86.131

192.168.86.132

192.168.86.133

其中130-132三台机器部署MySQL集群，133部署MHA的manage节点。

一、搭建MySQL集群

分别在130-132上部署MySQL，采用1主2从的结构，主从复制采用MySQL的replication进行复制。MySQL配置文件my.cnf如下：

[mysqld]
server-id=130
datadir=/usr/local/mysql/data
relay_log_info_repository=file
#socket=/usr/local/mysql/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
default_authentication_plugin=mysql_native_password
default-time-zone='+8:00'

[mysqld_safe]
log-error=/usr/local/mysql/log/mariadb.log
pid-file=/usr/local/mysql/log/mariadb.pid
default_authentication_plugin=mysql_native_password
#[client]
#socket=/usr/local/mysql/data/mysql.sock
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

二、搭建MHA

1.在所有MySQL机器上部署MHA Node， 133机器上也需要部署MHA Node.

       yum install perl-DBD-MySQL

tar -zxf mha4mysql-node-X.Y.tar.gz
perl Makefile.PL
make
sudo make install

2.部署MHA Manager

2.1 部署MHA Manager步骤

先安装epel-release,不然安装依赖包的时候会卡住: yum install epel-release -y

# Config::Tiny
## perl -MCPAN -e "install Config::Tiny"
# Log::Dispatch
## perl -MCPAN -e "install Log::Dispatch"
# Parallel::ForkManager
## perl -MCPAN -e "install Parallel::ForkManager"
## Installing MHA Manager
$ tar -zxf mha4mysql-manager-X.Y.tar.gz
$ perl Makefile.PL
$ make
$ sudo make install

2.2 MHA Manager的配置文件/etc/app1.cnf内容如下：

[server default]
# mysql user and password
user=root
password=123456
ssh_user=root
# working directory on the manager
manager_workdir=/var/log/masterha/app1
# working directory on MySQL servers
remote_workdir=/var/log/masterha/app1

[server1]
hostname=hadoop-130

[server2]
hostname=hadoop-131

[server3]
hostname=hadoop-132

2.3 MHA Manager的配置文件/etc/masterha_default.cnf内容如下：

[server default]
master_binlog_dir=/usr/local/mysql/data
master_ip_failover_script=/usr/local/mha4mysql-manager-0.54/samples/scripts/master_ip_failover

2.4 /usr/local/mha4mysql-manager-0.54/samples/scripts/master_ip_failover内容中，主要修改sub main函数，红色字体为修改的地方：

sub main {
if ( $command eq "stop" || $command eq "stopssh" ) {
print "stop faild master";

# $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 {
# make down the faild master's VIP
`ssh root\@$orig_master_ip ifconfig ens33:0 down`;
print "The VIP of the failed master is down";

$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
print "start new master";
# 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 {
# fire up the VIP of the new master
`ssh root\@$new_master_ip ifconfig ens33:0 192.168.86.188 broadcast 192.168.86.255 netmask 255.255.255.0 up`;
print "The VIP of the new master is up";

$exit_code = 0;
};
if ($@) {
warn $@;

# If you want to continue failover, exit 10.
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {

# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}

2.5 开通各机器间通过SSH免密登录，生成id_rsa的方法需要百度搜一下具体的操作步骤

2.6 检查配置是否正确：

/usr/local/mha4mysql-manager-0.54/bin/masterha_check_ssh --conf=/etc/app1.cnf

/usr/local/mha4mysql-manager-0.54/bin/masterha_check_repl --conf=/etc/app1.cnf

三、配置VIP

       在MySQL的master上，增加虚拟IP:192.168.86.188

ifconfig ens33:0 192.168.86.188 broadcast 192.168.86.255 netmask 255.255.255.0 up

四、启动MHA Manager: /usr/local/mha4mysql-manager-0.54/bin/masterha_manager --conf=/etc/app1.cnf