mysql mha集群 主库切换_MySQL 高可用集群架构 MHA 详解

MHA Manager 端日常主要操作步骤

1)检查是否有下列文件，有则删除。

发生主从切换后，MHAmanager服务会自动停掉，且在manager_workdir(/data/masterha/app1/app1.failover.complete)目录下面生成文件app1.failover.complete，若要启动MHA，必须先确保无此文件)

find / -name 'app1.failover.complete'

rm -f /data/masterha/app1/app1.failover.complete

2)检查MHA当前置：

# masterha_check_repl --conf=/etc/masterha/app1.cnf

3)启动MHA：

#nohup masterha_manager  --conf=/etc/masterha/app1.cnf&>/etc/masterha/manager.log  &

当有slave 节点宕掉时，默认是启动不了的，加上 --ignore_fail_on_start即使有节点宕掉也能启动MHA，如下：

#nohup masterha_manager --conf=/etc/masterha/app1.cnf --ignore_fail_on_start&>/etc/masterha/manager.log  &

4)停止MHA：  masterha_stop  --conf=/etc/masterha/app1.cnf

5)检查状态：

# masterha_check_status --conf=/etc/masterha/app1.cnf

6)检查日志：

#tail -f /etc/masterha/manager.log

7)主从切换,原主库后续工作

vim /etc/my.cnf

read_only=ON

relay_log_purge = 0

MySQL> reset slave all;

mysql> reset master;

/etc/init.d/mysqld restart

mysql> CHANGE MASTER TO MASTER_HOST='192.168.137.130',MASTER_USER='repl',MASTER_PASSWORD='123456';

##与新主库做主从复制

masterha_check_status --conf=/etc/masterha/app1.cnf

app1 (pid:45950) is running(0:PING_OK), master:192.168.137.130

注意：如果正常，会显示"PING_OK"，否则会显示"NOT_RUNNING"，这代表MHA监控没有开启。

定期删除中继日志

在配置主从复制中，slave上设置了参数relay_log_purge=0，所以slave节点需要定期删除中继日志，建议每个slave节点删除中继日志的时间错开。

corntab -e

0 5 * * *  /usr/local/bin/purge_relay_logs - -user=root  --password=pwd123 --port=3306  --disable_relay_log_purge >>/var/log/purge_relay.log  2>&1

5、配置VIP

ip配置可以采用两种方式，一种通过keepalived的方式管理虚拟ip的浮动；另外一种通过脚本方式启动虚拟ip的方式(即不需要keepalived或者heartbeat类似的软件)。

1、keepalived方式管理虚拟ip，keepalived配置方法如下：

在master和Candidate主机上安装keepalived

安装依赖包：

[root@master ~]# yum install openssl-devel libnfnetlink-devel libnfnetlink popt-devel kernel-devel -y

ln -s /usr/src/kernels/2.6.32-642.1.1.el6.x86_64 /usr/src/linux

tar -xzf keepalived-1.2.20.tar.gz;cd keepalived-1.2.20

./configure --prefix=/usr/local/keepalived;make && make install

ln -s /usr/local/keepalived/sbin/keepalived /usr/bin/keepalived

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/keepalived

mkdir /etc/keepalived

ln -s /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf

chmod 755 /etc/init.d/keepalived

chkconfig --add keepalived

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

service keepalived restart

echo 1 > /proc/sys/net/ipv4/ip_forward

修改Keepalived的配置文件(在master上配置)

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

guopeng@163.com

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id mysql-ha1

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.137.100

}

}

在候选master(Candidate)上配置

[root@Candidate keepalived-1.2.20]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id mysql-ha2

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.137.100

}

}

启动keepalived服务，在master上启动并查看日志

/etc/init.d/keepalived start

tail -f/var/log/messages

Aug 14 01:05:25 minion Keepalived_vrrp[39720]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.137.100

[root@master ~]# ip addr show dev eth0

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:57:66:49 brd ff:ff:ff:ff:ff:ff

inet 192.168.137.134/24 brd 192.168.137.255 scope global eth0

inet 192.168.137.100/32 scope global eth0

inet6 fe80::20c:29ff:fe57:6649/64 scope link

valid_lft forever preferred_lft forever

[root@Candidate ~]# ip addr show dev eth0    ##此时备选master上是没有虚拟ip的

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:a5:b4:85 brd ff:ff:ff:ff:ff:ff

inet 192.168.137.130/24 brd 192.168.137.255 scope global eth0

inet6 fe80::20c:29ff:fea5:b485/64 scope link

valid_lft forever preferred_lft forever

注意：

上面两台服务器的keepalived都设置为了BACKUP模式，在keepalived中2种模式，分别是master->backup模式和backup->backup模式。这两种模式有很大区别。在master->backup模式下，一旦主库宕机，虚拟ip会自动漂移到从库，当主库修复后，keepalived启动后，还会把虚拟ip抢占过来，即使设置了非抢占模式(nopreempt)抢占ip的动作也会发生。在backup->backup模式下，当主库宕机后虚拟ip会自动漂移到从库上，当原主库恢复和keepalived服务启动后，并不会抢占新主的虚拟ip，即使是优先级高于从库的优先级别，也不会发生抢占。为了减少ip漂移次数，通常是把修复好的主库当做新的备库。

2、MHA引入keepalived(MySQL服务进程挂掉时通过MHA 停止keepalived):

要想把keepalived服务引入MHA，我们只需要修改切换时触发的脚本文件master_ip_failover即可，在该脚本中添加在master发生宕机时对keepalived的处理。

编辑脚本/scripts/master_ip_failover，修改后如下。

manager编辑脚本文件：

mkdir /scripts

vim /scripts/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.137.100';

my $ssh_start_vip = "/etc/init.d/keepalived start";

my $ssh_stop_vip = "/etc/init.d/keepalived stop";

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_h

ost=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_

master_ip=ip --new_master_port=port\n";

}

现在已经修改这个脚本了，接下来我们在/etc/masterha/app1.cnf 中调用故障切换脚本

停止MHA：

masterha_stop  --conf=/etc/masterha/app1.cnf

在配置文件/etc/masterha/app1.cnf 中启用下面的参数(在[server  default下面添加])

master_ip_failover_script=/scripts/master_ip_failover

启动MHA：

#nohup masterha_manager  --conf=/etc/masterha/app1.cnf &>/etc/masterha/manager.log  &

检查状态：

]# masterha_check_status --conf=/etc/masterha/app1.cnf

app1 (pid:51284) is running(0:PING_OK), master:192.168.137.134

检查集群复制状态是否有报错:

]# masterha_check_repl  --conf=/etc/masterha/app1.cnf

192.168.137.134(192.168.137.134:3306) (current master)

+--192.168.137.130(192.168.137.130:3306)

+--192.168.137.146(192.168.137.146:3306)

Tue May  9 14:40:57 2017 - [info] Checking replication health on 192.168.137.130..

Tue May  9 14:40:57 2017 - [info]  ok.

Tue May  9 14:40:57 2017 - [info] Checking replication health on 192.168.137.146..

Tue May  9 14:40:57 2017 - [info]  ok.

Tue May  9 14:40:57 2017 - [info] Checking master_ip_failover_script status:

Tue May  9 14:40:57 2017 - [info]   /scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.137.134 --orig_master_ip=192.168.137.134 --orig_master_port=3306

IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===

Checking the Status of the script.. OK

Tue May  9 14:40:57 2017 - [info]  OK.

Tue May  9 14:40:57 2017 - [warning] shutdown_script is not defined.

Tue May  9 14:40:57 2017 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

注意： /scripts/master_ip_failover添加或者修改的内容意思是当主库数据库发生故障时，会触发MHA切换，MHA Manager会停掉主库上的keepalived服务，触发虚拟ip漂移到备选从库，从而完成切换。

当然可以在keepalived里面引入脚本，这个脚本监控mysql是否正常运行，如果不正常，则调用该脚本杀掉keepalived进程(参考MySQL 高可用性keepalived+mysql双主)。

测试：在master上停掉mysql

[root@master ~]# /etc/init.d/mysqld stop

Shutting down MySQL............                            [  OK  ]

到slave(192.168.137.146)查看slave的状态：

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.137.130

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

从上图可以看出slave指向了新的master服务器192.168.137.130(在故障切换前指向的是192.168.137.134)

查看vip绑定：

在192.168.137.134上查看vip绑定

[root@master ~]# ip addr show dev eth0

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:57:66:49 brd ff:ff:ff:ff:ff:ff

inet 192.168.137.134/24 brd 192.168.137.255 scope global eth0

inet6 fe80::20c:29ff:fe57:6649/64 scope link

valid_lft forever preferred_lft forever

在192.168.137.130上查看vip绑定

[root@Candidate ~]# ip addr show dev eth0

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:a5:b4:85 brd ff:ff:ff:ff:ff:ff

inet 192.168.137.130/24 brd 192.168.137.255 scope global eth0

inet 192.168.137.100/32 scope global eth0

从上面的显示结果可以看出vip地址漂移到了192.168.137.130

主从切换后续工作：现在Candidate变成主，需对原master重新做只从复制操作

修复成从库

启动keepalived

rm -fr app1.failover.complete

启动manager

3、通过脚本实现VIP切换

如果使用脚本管理vip的话，需要手动在master服务器上绑定一个vip

]#/sbin/ifconfig eth0:0 192.168.137.100/24

vim /scripts/master_ip_failover

my $vip = '192.168.137.100/24';

my $key = '0';

my $ssh_start_vip = "/sbin/ifconfigeth0:$key $vip";

my $ssh_stop_vip = "/sbin/ifconfigeth0:$key down";

之后的操作同上述keepalived操作

为了防止脑裂发生，推荐生产环境采用脚本的方式来管理虚拟ip，而不是使用keepalived来完成。到此为止，基本MHA集群已经配置完毕。