So keepalived has been around for quite a while now .... however it is still a mystery to many.
So this is a very simple example of how keepalived can work with MySQL. Hopefully, this can help those with questions.
We will have a Simple master to slave set up. Meaning.. we write to one unless we have failover to the second for some event.
1st - install keepalived
# yum search keepalived
keepalived.x86_64 : Load balancer and high availability service
Name and summary matches only, use "search all" for everything.
# yum -y install keepalived
You should now have an config file
# ls -ltr /etc/keepalived/keepalived.conf
Keep the original as you always backup .. right....
# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.orig
So you need to figure out an ipaddress you can use for your virtual ip. I picked 192.168.0.123 for this example.
Next, we will set up a script to be used for our new config file.
A few things I did here..
I left a .cnf file for keepalived and a log all in the /etc/keepalived.
This makes it simple for the example so you can do this or use your own cnf files.
A script:
cat /etc/keepalived/keepalived_check.sh
#!/bin/bash
# monitor mysql status
# if this node mysql is dead
# and its slave is alive , then stop its keepalived. The other node will bind the IP.
export MYSQL_HOME=/etc/keepalived/
export PATH=$MYSQL_HOME/bin:$PATH
mysql="/usr/bin/mysql"
mysqladmin="/usr/bin/mysqladmin"
delay_file="$MYSQL_HOME/slave_delay_second.log"
slave_host=$1
ALIVE=$($mysqladmin --defaults-file=$MYSQL_HOME/.my.localhost.cnf ping | grep alive | wc -l );
REMOTEALIVE=$($mysqladmin --defaults-file=$MYSQL_HOME/.my.remotehost.cnf ping | grep alive | wc -l );
if [[ $ALIVE -ne 1 ]]
then
#echo "MySQL is down"
if [[ $REMOTEALIVE -eq 1 ]]
then
# echo "Shutdown keep alive "
systemctl stop keepalived
# echo " keepalived stop "
fi
#else
#echo "MySQL is up"
#date
fi
exit 0 #all done
New config file
cat /etc/keepalived/keepalived.conf
global_defs {
notification_email {
anothermysqldba@gmail.com
}
notification_email_from anothermysqldba@gmail.com
smtp_server localhost
smtp_connect_timeout 30
}
vrrp_script check_mysql {
script "/etc/keepalived/keepalived_check.sh "
interval 2
weight 2
}
vrrp_instance VI_1 {
state MASTER
interface enp0s8 # <--- WHAT INTERFACE NAME HOLDS YOUR REAL IP /sbin/ifconfig
# found with ip link show
virtual_router_id 51
priority 101
advert_int 1
nopreempt # only needed on higher priority node
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_mysql
}
virtual_ipaddress {
192.168.0.123
}
}
This is all great but does it work....
So we have 2 hosts
[root@centosa keepalived]# hostname
centosa
[root@centosb keepalived]# hostname
centosb
Start keepalived
[root@centosa 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: inactive (dead)
[root@centosa keepalived]# systemctl restart keepalived
[root@centosa 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)
[root@centosa keepalived]# ssh 192.168.0.123 'hostname'
root@192.168.0.123's password:
centosa
Prove the connections work already
[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.101 -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosb |
+------------+
[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.102 -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosa |
+------------+
Double check that it is running...
[root@centosa keepalived]# systemctl status keepalived | grep active
Active: active
[root@centosb keepalived]# systemctl status keepalived | grep active
Active: active
Test current VIP .. stop mysql and watch same VIP change hosts ...
[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.123 -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosa |
+------------+
[root@centosa keepalived]# systemctl stop mysqld
[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.123 -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosb |
+------------+
