MySQL5.7+MHA+Keepalived高可用配置的示例分析

這篇文章主要介紹了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

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

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 邦定支持

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

四、安裝配置keepalived

mount /dev/cdrom /mnt
yum install kernel-devel openssl-devel popt-devel -y

tar zxvf keepalived-1.4.5.tar.gz
cd keepalived-1.4.5
./configure --prefix=/usr/local/keepalived/
make && make install

設置 keepalived 開機自動啟動

systemctl enable keepalived

配置keepalived 

192.168.8.57

mkdir /etc/keepalived
# vi /etc/keepalived/keepalived.conf
! Configuration file for keepalived
global_defs {
notification_email {
zdd5503@163.com
}
router_id 8.57
notification_email_from zdd5503@163.com
smtp_server stmp.163.com
smtp_connect_timeout 30
}
vrrp_instance v_mysql {
state BACKUP
interface enp0s3
virtual_router_id 200
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass mysql
}
virtual_ipaddress {
192.168.8.88/24
}
}
virtual_server 192.168.8.57 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.8.57 3306 {
weight 3
notify_down /etc/keepalived/keepalived_stop.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}

echo "#!/bin/bash" >/etc/keepalived/keepalived_stop.sh
echo "pkill keepalived" >>/etc/keepalived/keepalived_stop.sh
chmod u+x /etc/keepalived/keepalived_stop.sh

192.168.8.58

mkdir /etc/keepalived
# vi /etc/keepalived/keepalived.conf
! Configuration file for keepalived
global_defs {
notification_email {
zdd5503@163.com
}
router_id 8.58
notification_email_from zdd5503@163.com
smtp_server stmp.163.com
smtp_connect_timeout 30
}
vrrp_instance v_mysql {
state BACKUP
interface enp0s3
virtual_router_id 200
priority 90
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass mysql
}
virtual_ipaddress {
192.168.8.88/24
}
}
virtual_server 192.168.8.58 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.8.58 3306 {
weight 3
notify_down /etc/keepalived/keepalived_stop.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}

echo "#!/bin/bash" >/etc/keepalived/keepalived_stop.sh
echo "pkill keepalived" >>/etc/keepalived/keepalived_stop.sh
chmod u+x /etc/keepalived/keepalived_stop.sh

五、啟動并查看keepalived

192.168.8.57和192.168.8.58

systemctl daemon-reload
systemctl start keepalived
systemctl enable keepalived
ps -ef |grep keepalived

看到如下進程，即啟動正常，否則，啟動失敗

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

查看VIP

192.168.8.57

# 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這個機器

# mysql -uroot -pmysql -h292.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

ip a
1: 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 forever
2: 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 forever
3: 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 forever
4: 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位置

mysql -uroot -pmysql -h292.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進程

mysql -uroot -pmysql -h292.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"

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 末尾添加

vi /etc/rsyslog.conf
local0.*
/var/log/keepalived.log

RHEL6:

/etc/init.d/rsyslog restart

/etc/init.d/keepalived restart

RHEL7:

systemctl restart rsyslog

systemctl restart keepalived

查看keepalived日志

# 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.57
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Smtp server connection timeout = 30
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Email notification from = zdd5503@163.com
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Email notification = zdd5503@163.com
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Default interface = eth0
Oct 27 17:07:25 master Keepalived_vrrp[22697]: LVS flush = false
Oct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP IPv4 mcast group = 224.0.0.18
Oct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP IPv6 mcast group = ff02::12
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP delay = 5
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP repeat = 5
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP refresh timer = 0
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP refresh repeat = 1
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP lower priority delay = 5
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP lower priority repeat = 5
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Send advert after receive lower priority advert = true
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Send advert after receive higher priority advert = false
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous ARP interval = 0
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Gratuitous NA interval = 0
Oct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP default protocol version = 2
Oct 27 17:07:25 master Keepalived_vrrp[22697]: Iptables input chain = INPUT
Oct 27 17:07:25 master Keepalived_vrrp[22697]: VRRP check unicast_src = false

可以看到日志已經輸出，位置已變。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL5.7+MHA+Keepalived高可用配置的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!