MySQL Router HA with Keepalived | MySQL InnoDB Cluster

keepalive provides frameworks for both load balancing and high availability. The load balancing framework relies on the well-known and widely used Linux Virtual Server (IPVS) kernel module, which provides Layer 4 load balancing. Keepalived implements a set of health checkers to dynamically and adaptively maintain and manage load-balanced server pools according to their health. High availability is achieved by the Virtual Redundancy Routing Protocol (VRRP).

  • In short, Keepalived provides two main functions:
    • Health checking for LVS systems
    • Implementation of the VRRPv2 stack to handle load balancer failover

mysqlrouter –bootstrap [email protected] –user mysqlrouter –force

Strong_Password1!

systemctl enable mysqlrouter

##ON both servers

sudo yum -y install keepalived

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.back

vi /etc/keepalived/keepalived.conf

############mysqlrouter 1 ##############

vrrp_script chk_mysqlrouter {
script “/bin/killall -0 /usr/bin/mysqlrouter” # check the haproxy process
interval 2 # every 2 seconds
weight 2 # add 2 points if OK
fall 2
}

vrrp_instance VI_1 {
state MASTER
interface enp0s3
virtual_router_id 50
priority 102
advert_int 1
virtual_ipaddress {
192.168.56.111
}
track_script {
chk_mysqlrouter
}
}

############ mysqlrouter 2 ##############

cript chk_mysqlrouter {
script “/bin/killall -0 /usr/bin/mysqlrouter” # check the haproxy process
interval 2 # every 2 seconds
weight 2 # add 2 points if OK
fall 2
}

vrrp_instance VI_2 {
state BACKUP
interface enp0s3
virtual_router_id 51
priority 101
advert_int 1
virtual_ipaddress {
192.168.56.111
}
track_script {
chk_mysqlrouter
}
}

systemctl start keepalived (on both servers)
systemctl enable keepalived
systemctl status keepalived
ip addr show

while [ 1 ] do
sleep 1
mysql -h 192.168.56.111 -P 6447 -uinnodbcluster -pStrong_Password1! \
-e “select @@hostname;”
done

 415 views last month,  1 views today

About Mughees Ahmed

Over 2-year experience of Administration in Oracle, SQL Server, and MySQL databases using various tools and technologies. Keen on learning new database technologies having very good analytical skills. Working knowledge of Red Hat Linux, UNIX, Solaris and Windows Server 2012 is a valuable addition to my knowledge desk. KNOWLEDGE & SKILLS • Oracle Database Server 10g/11g/12c. • Oracle RAC, Data guard. • Oracle Golden Gate (Oracle to Oracle, Oracle to MySQL and MySQL to Oracle) • Oracle Enterprise Manager Grid Control, Toad, SQL developer. • SQL Server 2005/2008/2012/2016. • SQL Server Failover clustering, mirroring & log shipping, Always On availability groups. • MySQL 5 Administration, MySQL Workbench, MySQL Enterprise Monitor, SQLyog • MySQL NDB Cluster Installation,Administration. • MySQL Asynchronous/Semi-synchronous replication. • Oracle OEM Contact me on [email protected]

Leave a Reply

Do NOT follow this link or you will be banned from the site!