MySQL5.7+MHA+Keepalived高可用配置

数据库架构:一主两从

master:192.168.8.57

slave1:192.168.8.58

slave2:192.168.8.59

manager:192.168.8.60

MHA工具包:

mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz

keepalived-1.4.5.tar.gz

一、主从复制环境和MHA安装如下

http://blog.itpub.net/30135314/viewspace-2217566/

二、配置MHA相关文件

master_ip_failover

1234 my $vip '192.168.8.88/24';my $key '1';my $ssh_start_vip "systemctl start keepalived";my $ssh_stop_vip "systemctl stop keepalived";

master_ip_online_change

123456789 my $vip '192.168.8.88/24';my $key '1';my $ssh_start_vip "systemctl start keepalived";my $ssh_stop_vip "systemctl stop keepalived";my $orig_master_ssh_port = 22;my $new_master_ssh_port = 22;my $ssh_user "root";my $new_master_password='mysql';my $orig_master_password='mysql';

三、添加非本机 IP 邦定支持

12 echo "net.ipv4.ip_nonlocal_bind=1" >> /etc/sysctl.confsysctl -p

四、安装配置keepalived

12 mount /dev/cdrom /mntyum install kernel-devel openssl-devel popt-devel -y
1234 tar zxvf keepalived-1.4.5.tar.gzcd keepalived-1.4.5./configure --prefix=/usr/local/keepalived/make && make install

设置 keepalived 开机自动启动

1 systemctl enable keepalived

配置keepalived 

192.168.8.57

1234567891011121314151617181920212223242526272829303132333435363738394041424344 mkdir /etc/keepalived# vi /etc/keepalived/keepalived.conf! Configuration file for keepalivedglobal_defs {notification_email {zdd5503@163.com}router_id 8.57notification_email_from zdd5503@163.comsmtp_server stmp.163.comsmtp_connect_timeout 30}vrrp_instance v_mysql {state BACKUPinterface enp0s3virtual_router_id 200priority 100advert_int 1nopreemptauthentication {auth_type PASSauth_pass mysql}virtual_ipaddress {192.168.8.88/24}}virtual_server 192.168.8.57 3306 {delay_loop 2lb_algo wrrlb_kind DRpersistence_timeout 60protocol TCPreal_server 192.168.8.57 3306 {weight 3notify_down /etc/keepalived/keepalived_stop.shTCP_CHECK {connect_timeout 10nb_get_retry 3delay_before_retry 3connect_port 3306}}}
123 echo "#!/bin/bash" >/etc/keepalived/keepalived_stop.shecho "pkill keepalived" >>/etc/keepalived/keepalived_stop.shchmod u+x /etc/keepalived/keepalived_stop.sh

192.168.8.58

1234567891011121314151617181920212223242526272829303132333435363738394041424344 mkdir /etc/keepalived# vi /etc/keepalived/keepalived.conf! Configuration file for keepalivedglobal_defs {notification_email {zdd5503@163.com}router_id 8.58notification_email_from zdd5503@163.comsmtp_server stmp.163.comsmtp_connect_timeout 30}vrrp_instance v_mysql {state BACKUPinterface enp0s3virtual_router_id 200priority 90advert_int 1nopreemptauthentication {auth_type PASSauth_pass mysql}virtual_ipaddress {192.168.8.88/24}}virtual_server 192.168.8.58 3306 {delay_loop 2lb_algo wrrlb_kind DRpersistence_timeout 60protocol TCPreal_server 192.168.8.58 3306 {weight 3notify_down /etc/keepalived/keepalived_stop.shTCP_CHECK {connect_timeout 10nb_get_retry 3delay_before_retry 3connect_port 3306}}}
123 echo "#!/bin/bash" >/etc/keepalived/keepalived_stop.shecho "pkill keepalived" >>/etc/keepalived/keepalived_stop.shchmod u+x /etc/keepalived/keepalived_stop.sh

五、启动并查看keepalived

192.168.8.57和192.168.8.58

1234 systemctl daemon-reloadsystemctl start keepalivedsystemctl enable keepalivedps -ef |grep keepalived

看到如下进程,即启动正常,否则,启动失败

12345 #ps -ef|grep keepalivedroot     16916     1  0 14:53 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -Droot     16917 16916  0 14:53 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -Droot     16918 16916  0 14:53 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -Droot     17772  9992  0 16:17 pts/2    00:00:00 grep --color=auto keepalived

查看VIP

192.168.8.57

12 # ip a|grep 88    inet 192.168.8.88/24 scope global secondary enp0s3

192.168.8.58看不到192.168.8.88/24

六、测试keepalived可用性(测试之前关闭MHA进程,避免故障切换造成干扰)

MySQL连接测试

查到目前VIP192.168.8.88在192.168.8.57这个机器

1234567 # mysql -uroot -pmysql -h192.168.8.88 -e "show variables like 'server_id'"mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 57    |+---------------+-------+

关闭keepalived测试

关闭192.168.8.57上keepalived进程,此时在此节点已查不到VIP

1234567891011121314151617181920212223 ip a1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00    inet 127.0.0.1/8 scope host lo       valid_lft forever preferred_lft forever    inet6 ::1/128 scope host        valid_lft forever preferred_lft forever2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000    link/ether 08:00:27:4d:70:17 brd ff:ff:ff:ff:ff:ff    inet 192.168.8.58/24 brd 192.168.8.255 scope global noprefixroute enp0s3       valid_lft forever preferred_lft forever    inet 192.168.8.88/24 scope global secondary enp0s3       valid_lft forever preferred_lft forever    inet6 fe80::6a31:3e92:8b6f:83c0/64 scope link noprefixroute        valid_lft forever preferred_lft forever    inet6 fe80::5198:593b:cdc5:1f90/64 scope link tentative noprefixroute dadfailed        valid_lft forever preferred_lft forever3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000    link/ether 52:54:00:f4:55:bb brd ff:ff:ff:ff:ff:ff    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0       valid_lft forever preferred_lft forever4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000    link/ether 52:54:00:f4:55:bb brd ff:ff:ff:ff:ff:ff

可以看到VIP已经飘至192.168.8.58

此时启动192.168.8.57上的keepalived进程,VIP依然在192.168.8.58节点。

模拟宕机测试

查看VIP位置

1234567 mysql -uroot -pmysql -h192.168.8.88 -e "show variables like 'server_id'"mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 57    |+---------------+-------+

关闭192.168.8.57节点mysql进程

1234567 mysql -uroot -pmysql -h192.168.8.88 -e "show variables like 'server_id'"mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 58    |+---------------+-------+

可以看到VIP已飘至192.168.8.58节点

七、修改keepalived日志位置

默认日志存放在系统日志:/var/log/messages 下

把日志单独存放

修改 vi /usr/local/keepalived/etc/sysconfig/keepalived

把 KEEPALIVED_OPTIONS="-D" 修改为:KEEPALIVED_OPTIONS="-D -d -S 0"

1234567891011121314 vi /usr/local/keepalived/etc/sysconfig/keepalived# Options for keepalived. See `keepalived --help' output and keepalived(8) and# keepalived.conf(5) man pages for a list of all options. Here are the most# common ones :## --vrrp               -P    Only run with VRRP subsystem.# --check              -C    Only run with Health-checker subsystem.# --dont-release-vrrp  -V    Dont remove VRRP VIPs & VROUTEs on daemon stop.# --dont-release-ipvs  -I    Dont remove IPVS topology on daemon stop.# --dump-conf          -d    Dump the configuration data.# --log-detail         -D    Detailed log messages.# --log-facility       -S    0-7 Set local syslog facility (default=LOG_DAEMON)#KEEPALIVED_OPTIONS="-D -d -S 0"

在/etc/rsyslog.conf 末尾添加

123 vi /etc/rsyslog.conflocal0.*/var/log/keepalived.log

RHEL6:

/etc/init.d/rsyslog restart

/etc/init.d/keepalived restart

RHEL7:

systemctl restart rsyslog

systemctl restart keepalived

查看keepalived日志

1234567891011121314151617181920212223 # tail -100f /var/log/keepalived.log Oct 27 17:07:25 master Keepalived_vrrp[22697]: ------< Global definitions >------Oct 27 17:07:25 master Keepalived_vrrp[22697]: Router ID = 8.57Oct 27 17:07:25 master Keepalived_vrrp[22697]: Smtp server connection timeout = 30Oct 27 17:07:25 master Keepalived_vrrp[22697]: Email notification from = zdd5503@163.comOct 27 17:07:25 master Keepalived_vrrp[22697]: Email notification = zdd5503@163.comOct 27 17:07:25 master Keepalived_vrrp[22697]: Default interface = eth0Oct 27 17:07:25 master Keepalived_vrrp[22697]: LVS flush = falseOct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP IPv4 mcast group = 224.0.0.18Oct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP IPv6 mcast group = ff02::12Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP delay = 5Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP repeat = 5Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP refresh timer = 0Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP refresh repeat = 1Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP lower priority delay = 5Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP lower priority repeat = 5Oct 27 17:07:25 master Keepalived_vrrp[22697]: Send advert after receive lower priority advert = trueOct 27 17:07:25 master Keepalived_vrrp[22697]: Send advert after receive higher priority advert = falseOct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP interval = 0Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous NA interval = 0Oct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP default protocol version = 2Oct 27 17:07:25 master Keepalived_vrrp[22697]: Iptables input chain = INPUTOct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP check unicast_src = false

可以看到日志已经输出,位置已变。


上一篇:MySQL--16 MHA修复


下一篇:基于MHA的MySQL高可用