mysql MHA + keepalived 实现高可用

MHA实现了mysql 的自动故障切换,但是切换后还需要手工修改应用的访问ip。

原文:http://bananalighter.com/mysql-master-ha-solution-one-master-one-candidate-master-and-multiple-slaves-configuration/

本文将配置keepalived与MHA一起工作,实现对mysql应用的透明高可用。

一、确定工作模式

MHA是检测mysql进程状态,自动连接备用机进行切换。

keepalived是检测keepalived的进程状态,自动将虚拟IP切换到备机。

因此在故障切换时,需要先切换mysql,等备机的mysql服务可用以后切换虚拟IP。

二、工作方式

MHA完成mysql master的切换后,调用“master_ip_failover”脚本杀死keepalived进程,触发keepalived切换虚拟IP。

1.修改/etc/masterha_default.cnf

[server default]
user=root
password=root
ssh_user=root
master_binlog_dir= /var/lib/mysql
remote_workdir=/data/log/masterha
#secondary_check_script= masterha_secondary_check -s remote_host1 -s remote_host2
ping_interval=3
master_ip_failover_script=/script/masterha/master_ip_failover
#shutdown_script= /script/masterha/power_manager
#report_script= /script/masterha/send_master_failover_mail

2.配置keepalived

直接使用官方源安装keepalived。

编辑/etc/keepalived/keepalived.conf

MASTER的配置文件:

! Configuration File for keepalived

global_defs {
#   notification_email {
#     acassen@firewall.loc
#     failover@firewall.loc
#     sysadmin@firewall.loc
#   }
#   notification_email_from Alexandre.Cassen@firewall.loc
#   smtp_server 192.168.200.1
#   smtp_connect_timeout 30
#   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER #candidate master则写BACKUP
    interface eth0
    virtual_router_id 51 #同一个集群这个东西是一样的
    priority 100 #从254到1,master > candidate master
    advert_int 1
    authentication { #认证账号,同一个集群一样
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress { #虚拟ip,主先用着,主跪了就飘给备
        172.19.17.228
    }
}

candidate MASTER的配置文件

! Configuration File for keepalived

global_defs {
#   notification_email {
#     acassen@firewall.loc
#     failover@firewall.loc
#     sysadmin@firewall.loc
#   }
#   notification_email_from Alexandre.Cassen@firewall.loc
#   smtp_server 192.168.200.1
#   smtp_connect_timeout 30
#   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.19.17.228
    }
}

三、配置masterha的ip failover脚本

新建文件:/script/masterha/master_ip_failover

service keepalived stop

四、使用测试

1.起keepalived

2.起mysql(注意,mysql的master ip应该配主机地址)

3.停master的mysql

[root@testdb1 ~]# service mysql stop
Shutting down MySQL (Percona Server).....[  OK  ]
[root@testdb1 ~]# service keepalived status
keepalived is stopped
[root@testdb1 ~]# masterha_check_status --conf=/etc/masterha/test_MMS.cnf 
test_MMS is stopped(2:NOT_RUNNING).

4.测试failover是否成功

使用其他机器通过虚地址228登陆mysql

[root@banana ~]# mysql -h 172.19.17.228 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

能登陆,成功了。

5.检查两台备机的情况

candidate master(211):

mysql> show slave status\G
Empty set (0.00 sec)
mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      120 | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

已经成功升主。

slave(227):

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.19.17.211
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 120

已经将主切换为211.

测试成功,大功告成。

 

 

 

 

 

 

 

 

 

 

Comments (2)

  1. 于海龙

    正常情况下没有问题, 如果master主机直接宕机的情况, keepalived会立即触发,vip也会立即应用到备用主机上。

    Reply
    1. 香蕉与打火机 (Post author)

      是的 这种情况下,应用侧还会出现短暂的不可写情况。

      Reply

Leave a Comment

电子邮件地址不会被公开。 必填项已用*标注