In this blog post, we are going to test load balancer solution for MySQL high availability by integrating it with Keepalived, HAProxy, xinetd software components.
High availability databases use an architecture that is designed to continue to function normally even when there are hardware or network failures within the system.
Why we need this?
Let’s take a scenario where we have MySQL Multi-Master / MASTER-SLAVE replication setup for high availability. In the case of Hardware/Network failure on MASTER, In order to failover to a next available server, we need to manually do the configuration changes for client connections.In this case, downtime is expected since manual failover will take some times. To solve this we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.
To avoid such downtimes and for the maximum high availability of the database, we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.
Advantages:
- Almost ZERO DOWNTIME for DB maintenance activities like database patching/upgrades, configuration changes that need DB restart etc.
- Easy Read-Write load distribution.
- Automatic Failover.
- Easy to setup and manage.
Load Balancer is a set of integrated software components that provide for balancing IP traffic across a set of real servers. It consists of two main technologies to monitor cluster members and cluster services: Keepalived and HAProxy.
Keepalived: It uses Linux virtual server (LVS) to perform load balancing and failover tasks on.
HAProxy: It performs load balancing and high-availability services to TCP and HTTP applications.
xinetd: “Extended Internet daemon” is an open-source super-server daemon which runs on many Unix-like systems and manages Internet-based connectivity.xinetd
runs constantly and listens on all ports for the services it manages. When a connection request arrives for one of its managed services, xinetd
starts up the appropriate server for that service.
VIP: Virtual IP addresses (or VIPs) allow you to use multiple IPs on a single physical network interface.
Keepalive Configuration:
Load Balancer #1 Configuration
global_defs { notification_email { mysql-user@mydomain.com } notification_email_from svr1@mydomain.com smtp_server localhost smtp_connect_timeout 30 } vrrp_instance VRRP1 { state MASTER # Specify the network interface to which the virtual address is assigned interface eth0 # The virtual router ID must be unique to each VRRP instance that you define virtual_router_id 71 # Set the value of priority higher on the master server than on a backup server priority 200 advert_int 1 authentication { auth_type PASS auth_pass 1066 } virtual_ipaddress { 192.168.2.111 192.168.2.112 } }
Load Balancer #2 Configuration
global_defs { notification_email { mysql-user@mydomain.com } notification_email_from svr2@mydomain.com smtp_server localhost smtp_connect_timeout 30 } vrrp_instance VRRP1 { state BACKUP # Specify the network interface to which the virtual address is assigned interface eth0 # The virtual router ID must be unique to each VRRP instance that you define virtual_router_id 71 # Set the value of priority higher on the master server than on a backup server priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1066 } virtual_ipaddress { 192.168.2.111 192.168.2.112 } }
HAProxy Configuration on Load balancer #1 and #2 :
global daemon maxconn 256 defaults mode http timeout connect 5000ms timeout client 50000ms timeout server 50000ms # MYSQL Configuration listen write_connections 192.168.2.111:3306 mode tcp balance roundrobin option httpchk server mysql_db1 192.168.2.105:3306 check port 9200 server mysql_db2 192.168.2.106:3306 check port 9200 backup listen read_connections 192.168.2.112:3306 mode tcp balance leastconn option httpchk server mysql_db3 192.168.2.107:3306 check port 9200
Note: Add all IP and hostname entries in /etc/hosts file.
xinetd configuration on all mysql server:
/etc/xinetd.d/mysqlchk
# default: on # description: mysqlchk service mysqlchk { flags = REUSE socket_type = stream port = 9200 wait = no user = nobody server = /opt/mysqlchk log_on_failure += USERID disable = no only_from = 0.0.0.0/0 per_source = UNLIMITED
NOTE: Create following script on all mysql server to monitor mysql status.
mysqlchk.stauts script [/opt/mysqlchk.stauts] : The purpose of this script is make haproxy capable of monitoring mysql properly
# It is recommended that a low-privileged-mysql user is created to be used by # this script. Something like this: #mysql> CREATE USER IF NOT EXISTS 'mysqlchkuser'@'localhost' IDENTIFIED BY 'P@ssword#567'; # mysql> GRANT show databases on *.* TO 'mysqlchkuser'@'localhost'; # mysql> flush privileges; MYSQL_HOST="localhost" MYSQL_PORT="3306" MYSQL_USERNAME="mysqlchkusr" MYSQL_PASSWORD="P@ssword#567" TMP_FILE="/opt/tmp/mysqlchk.out" ERR_FILE="/opt/tmp/mysqlchk.err" # # We perform a simple query that should return a few results. # mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE # # Check the output. If it is not empty then everything is fine and we return # something. Else, we just do not return anything. # if [ "$(/bin/cat $TMP_FILE)" != "" ] then # mysql is fine, return http 200 /bin/echo -e "HTTP/1.1 200 OKrn" /bin/echo -e "Content-Type: Content-Type: text/plainrn" /bin/echo -e "rn" /bin/echo -e "MySQL is running.rn" /bin/echo -e "rn" else # mysql is fine, return http 503 /bin/echo -e "HTTP/1.1 503 Service Unavailablern" /bin/echo -e "Content-Type: Content-Type: text/plainrn" /bin/echo -e "rn" /bin/echo -e "MySQL is *down*.rn" /bin/echo -e "rn" fi
Testing:
- Test MySQL connection with VIP address.
- Stop MySQL Active MASTER, and connect to MySQL.It should connect to backup MySQL server specified in HAProxy configuration.
- Test Keepalived and HAProxy failover.
All Set!!