How to config Keepalived VIP Auto Failover for MySQL?
MySQL master slave replication doesn’t provide any solution in case of master failure, in that scenarios we have to manually do the configuration changes to make the next available server as master. Keepalived is a routing software written in C. It provide simple and robust facilities for high-availability to Linux system. On the other hand high-availability is achieved by VRRP protocol. VRRP is a fundamental brick for router failover. the note show how to install keepalived for MySQL.
Use keepalived to configure VIP, the application uses VIP to connect to the database, when the node is unavailable, the VIP automatically switches to other nodes.
download keepalived source from http://www.keepalived.org/download.html
# install keepalived keepalived-2.2.7
[root@master ~]# ls
all.sql keepalived-2.2.7.tar.gz
[root@master ~]# tar -zxvf keepalived-2.2.7.tar.gz
[root@master ~]# cd keepalived-2.2.7
[root@master keepalived-2.2.7]# ls
aclocal.m4 autogen.sh build-aux ChangeLog configure.ac COPYING Dockerfile.in keepalived lib Makefile.am README.md TODO
AUTHOR bin_install build_setup configure CONTRIBUTORS doc INSTALL keepalived.spec.in m4 Makefile.in snap tools
[root@master keepalived-2.2.7]# ./configure --prefix=/usr/local/keepalived-2.2.7
configure: error:
!!! OpenSSL is not properly installed on your system. !!!
!!! Can not include OpenSSL headers files. !!!
[root@master keepalived-2.2.7]# rpm -qa|grep -i ssl
perl-Net-SSLeay-1.55-4.el7.x86_64
openssl-libs-1.0.1e-60.el7.x86_64
perl-IO-Socket-SSL-1.94-5.el7.noarch
pyOpenSSL-0.13.1-3.el7.x86_64
openssl-1.0.1e-60.el7.x86_64
Note:
the error was miss openssl-devel .
[root@master mnt]# mount /dev/cdrom /mnt
mount: /dev/sr0 is write-protected, mounting read-only
[root@master Packages]# yum install openssl-devel
[root@master keepalived-2.2.7]# ./configure --prefix=/usr/local/keepalived-2.2.7
Keepalived configuration
------------------------
Keepalived version : 2.2.7
Compiler : gcc gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11)
Preprocessor flags : -D_GNU_SOURCE
Compiler flags : -g -g -O2 -Wextra -Wunused -Wstrict-prototypes -Wabi -Wbad-function-cast -Wcast-align -Wcast-qual -Wdisabled-optimization -Wdouble-promotion -Wfloat-equal -Wframe-larger-than=5120 -Winit-self -Winline -Winvalid-pch -Wjump-misses-init -Wlogical-op -Wmissing-declarations -Wmissing-field-initializers -Wmissing-include-dirs -Wmissing-prototypes -Wnested-externs -Wold-style-definition -Woverlength-strings -Wpointer-arith -Wredundant-decls -Wshadow -Wstack-protector -Wstrict-overflow=4 -Wsuggest-attribute=format -Wsuggest-attribute=noreturn -Wsuggest-attribute=pure -Wsync-nand -Wtrampolines -Wundef -Wuninitialized -Wunknown-pragmas -Wunsafe-loop-optimizations -Wunsuffixed-float-constants -Wvariadic-macros -Wwrite-strings -fPIE -Wformat -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -O2
Linker flags : -pie -Wl,-z,relro -Wl,-z,now
Extra Lib : -lm -lcrypto -lssl
Use IPVS Framework : Yes
IPVS use libnl : No
IPVS syncd attributes : No
IPVS 64 bit stats : No
HTTP_GET regex support : No
fwmark socket support : Yes
Use VRRP Framework : Yes
Use VRRP VMAC : Yes
Use VRRP authentication : Yes
With track_process : Yes
With linkbeat : Yes
Use BFD Framework : No
SNMP vrrp support : No
SNMP checker support : No
SNMP RFCv2 support : No
SNMP RFCv3 support : No
DBUS support : No
Use JSON output : No
libnl version : None
Use IPv4 devconf : No
Use iptables : No
Use nftables : No
init type : systemd
systemd notify : No
Strict config checks : No
Build documentation : No
Default runtime options : -D
*** WARNING - this build will not support IPVS with IPv6. Please install libnl/libnl-3 dev libraries to support IPv6 with IPVS.
[root@master keepalived-2.2.7]# make
[root@master keepalived-2.2.7]# make install
[root@master keepalived-2.2.7]# cd /usr/local
[root@master local]# ls
bin etc games include keepalived-2.2.7 lib lib64 libexec mysql mysql-5.7.26-el7-x86_64 sbin share src
[root@master keepalived-2.2.7]# tree
.
├── bin
│ └── genhash -> ../sbin/keepalived
├── etc
│ ├── keepalived
│ │ ├── keepalived.conf.sample
│ │ └── samples
│ │ ├── keepalived.conf.conditional_conf
│ │ ├── keepalived.conf.fwmark
│ │ ├── keepalived.conf.HTTP_GET.port
│ │ ├── keepalived.conf.inhibit
│ │ ├── keepalived.conf.IPv6
│ │ ├── keepalived.conf.misc_check
│ │ ├── keepalived.conf.misc_check_arg
│ │ ├── keepalived.conf.PING_CHECK
│ │ ├── keepalived.conf.quorum
│ │ ├── keepalived.conf.sample
│ │ ├── keepalived.conf.SMTP_CHECK
│ │ ├── keepalived.conf.SSL_GET
│ │ ├── keepalived.conf.status_code
│ │ ├── keepalived.conf.track_interface
│ │ ├── keepalived.conf.UDP_CHECK
│ │ ├── keepalived.conf.virtualhost
│ │ ├── keepalived.conf.virtual_server_group
│ │ ├── keepalived.conf.vrrp
│ │ ├── keepalived.conf.vrrp.localcheck
│ │ ├── keepalived.conf.vrrp.lvs_syncd
│ │ ├── keepalived.conf.vrrp.routes
│ │ ├── keepalived.conf.vrrp.rules
│ │ ├── keepalived.conf.vrrp.scripts
│ │ ├── keepalived.conf.vrrp.static_ipaddress
│ │ ├── keepalived.conf.vrrp.sync
│ │ ├── sample.misccheck.smbcheck.sh
│ │ └── sample_notify_fifo.sh
│ └── sysconfig
│ └── keepalived
├── sbin
│ └── keepalived
└── share
├── doc
│ └── keepalived
│ └── README
├── man
│ ├── man1
│ │ └── genhash.1
│ ├── man5
│ │ └── keepalived.conf.5
│ └── man8
│ └── keepalived.8
└── snmp
└── mibs
15 directories, 35 files
root@master local]# ln -s keepalived-2.2.7/ keepalived
# config keepalived
[root@master keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
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 qlen 1000
link/ether 08:00:27:9f:d1:0d brd ff:ff:ff:ff:ff:ff
inet 192.168.56.200/24 brd 192.168.56.255 scope global enp0s3
valid_lft forever preferred_lft forever
inet6 fe80::51e9:1b28:e5fa:9f8f/64 scope link
valid_lft forever preferred_lft forever
[root@master keepalived]# mkdir /etc/keepalived
[root@master keepalived]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
sysadmin@anbob.com
}
notification_email_from robot@anbob.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
}
vrrp_instance VI_1 {
state BACKUP
interface enp0s3
virtual_router_id 51
priority 120
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.222/24
}
}
[root@master keepalived]# systemctl start keepalived
[root@master keepalived]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
Active: active (running) since Fri 2022-03-18 10:29:48 CST; 3s ago
Docs: man:keepalived(8)
man:keepalived.conf(5)
man:genhash(1)
https://keepalived.org
Process: 12468 ExecStart=/usr/local/keepalived-2.2.7/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 12469 (keepalived)
CGroup: /system.slice/keepalived.service
├─12469 /usr/local/keepalived-2.2.7/sbin/keepalived -D
└─12470 /usr/local/keepalived-2.2.7/sbin/keepalived -D
Mar 18 10:29:48 master Keepalived[12469]: Startup complete
Mar 18 10:29:51 master Keepalived_vrrp[12470]: (VI_1) Receive advertisement timeout
Mar 18 10:29:51 master Keepalived_vrrp[12470]: (VI_1) Entering MASTER STATE
Mar 18 10:29:51 master Keepalived_vrrp[12470]: (VI_1) setting VIPs.
Mar 18 10:29:51 master Keepalived_vrrp[12470]: (VI_1) Sending/queueing gratuitous ARPs on enp0s3 for 192.168.56.222
Mar 18 10:29:51 master Keepalived_vrrp[12470]: Sending gratuitous ARP on enp0s3 for 192.168.56.222
Mar 18 10:29:51 master Keepalived_vrrp[12470]: Sending gratuitous ARP on enp0s3 for 192.168.56.222
Mar 18 10:29:51 master Keepalived_vrrp[12470]: Sending gratuitous ARP on enp0s3 for 192.168.56.222
Mar 18 10:29:51 master Keepalived_vrrp[12470]: Sending gratuitous ARP on enp0s3 for 192.168.56.222
Mar 18 10:29:51 master Keepalived_vrrp[12470]: Sending gratuitous ARP on enp0s3 for 192.168.56.222
[root@master keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
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 qlen 1000
link/ether 08:00:27:9f:d1:0d brd ff:ff:ff:ff:ff:ff
inet 192.168.56.200/24 brd 192.168.56.255 scope global enp0s3
valid_lft forever preferred_lft forever
inet 192.168.56.222/24 scope global secondary enp0s3
valid_lft forever preferred_lft forever
inet6 fe80::51e9:1b28:e5fa:9f8f/64 scope link
valid_lft forever preferred_lft forever
# add keepalived check mysql
mysql> select host,user from mysql.user;
+--------------+---------------+
| host | user |
+--------------+---------------+
| % | myuser |
| 192.168.56.% | rpl |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+--------------+---------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@master keepalived]# mysql -urpl -h 192.168.56.222 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>
[root@master keepalived]# cat keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
sysadmin@anbob.com
}
notification_email_from robot@anbob.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
}
vrrp_script chk_mysql_port {
script "/etc/keepalived/chk_mysql.sh"
interval 2
weight –50
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface enp0s3
virtual_router_id 51
priority 120
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.222/24
}
track_script {
chk_mysql_port
}
}
# mysql check shell
cat /etc/keepalived/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
systemctl stop keepalived
fi
[root@master keepalived]# chmod +x chk_mysql.sh
[root@master keepalived]# ll
total 8
-rwxr-xr-x 1 root root 134 Mar 18 11:00 chk_mysql.sh
-rw-r--r-- 1 root root 814 Mar 18 11:02 keepalived.conf
# test stop mysql
[root@master keepalived]# service mysql start Starting MySQL.. SUCCESS! [root@master keepalived]# systemctl restart keepalived [root@master keepalived]# systemctl status keepalived ● keepalived.service - LVS and VRRP High Availability Monitor Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled) Active: active (running) since Fri 2022-03-18 11:10:28 CST; 1s ago Docs: man:keepalived(8) man:keepalived.conf(5) man:genhash(1) https://keepalived.org Process: 17548 ExecStart=/usr/local/keepalived-2.2.7/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS) Main PID: 17549 (keepalived) CGroup: /system.slice/keepalived.service ├─17549 /usr/local/keepalived-2.2.7/sbin/keepalived -D └─17550 /usr/local/keepalived-2.2.7/sbin/keepalived -D Mar 18 11:10:28 master Keepalived_vrrp[17550]: Script user 'keepalived_script' does not exist Mar 18 11:10:28 master Keepalived_vrrp[17550]: SECURITY VIOLATION - scripts are being executed but script_security not enabled. Mar 18 11:10:28 master Keepalived_vrrp[17550]: Assigned address 192.168.56.200 for interface enp0s3 Mar 18 11:10:28 master Keepalived_vrrp[17550]: Assigned address fe80::51e9:1b28:e5fa:9f8f for interface enp0s3 Mar 18 11:10:28 master Keepalived_vrrp[17550]: Registering gratuitous ARP shared channel Mar 18 11:10:28 master Keepalived_vrrp[17550]: (VI_1) removing VIPs. Mar 18 11:10:28 master Keepalived_vrrp[17550]: VRRP sockpool: [ifindex( 2), family(IPv4), proto(112), fd(12,13) multicast, address(224.0.0.18)] Mar 18 11:10:28 master Keepalived[17549]: Startup complete Mar 18 11:10:28 master Keepalived_vrrp[17550]: VRRP_Script(chk_mysql_port) succeeded Mar 18 11:10:28 master Keepalived_vrrp[17550]: (VI_1) Entering BACKUP STATE [root@master keepalived]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 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 qlen 1000 link/ether 08:00:27:9f:d1:0d brd ff:ff:ff:ff:ff:ff inet 192.168.56.200/24 brd 192.168.56.255 scope global enp0s3 valid_lft forever preferred_lft forever inet 192.168.56.222/24 scope global secondary enp0s3 valid_lft forever preferred_lft forever inet6 fe80::51e9:1b28:e5fa:9f8f/64 scope link valid_lft forever preferred_lft forever [root@master keepalived]# service mysql stop Shutting down MySQL.. SUCCESS! [root@master keepalived]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 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 qlen 1000 link/ether 08:00:27:9f:d1:0d brd ff:ff:ff:ff:ff:ff inet 192.168.56.200/24 brd 192.168.56.255 scope global enp0s3 valid_lft forever preferred_lft forever inet6 fe80::51e9:1b28:e5fa:9f8f/64 scope link valid_lft forever preferred_lft forever
Note:
the keepalived was stoped and VIP release automatic after mysql stop.
similar to the same configuration on MySQL other nodes, only the priority in the keepalived configuration is changed. for slave1 e.g.
vrrp_instance VI_1 {
state BACKUP
interface enp0s3
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。