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
|
1 2 3 4 5 6 7 8 9 10 11 |
[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的配置文件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
! 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的配置文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
! 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
|
1 |
service keepalived stop |
四、使用测试
1.起keepalived
2.起mysql（注意，mysql的master ip应该配主机地址）
3.停master的mysql
|
1 2 3 4 5 6 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[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）：
|
1 2 3 4 5 6 7 8 9 10 |
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）：
|
1 2 3 4 5 6 7 8 9 |
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.
测试成功，大功告成。
正常情况下没有问题， 如果master主机直接宕机的情况， keepalived会立即触发，vip也会立即应用到备用主机上。
是的 这种情况下，应用侧还会出现短暂的不可写情况。