Go Mysql-galera Cluster with HAproxy

Source: Internet
Author: User
Tags failover haproxy

When I started working in Open Stack, I had to investigate about the HA of the Nova component. Unfortunatly the Nova configuration needed a single entry point-to-connect to the MySQL database. The solution, came to me is to use HAProxy on top of my existing Galera cluster. I. Introduction

Reminder:i'll use the configuration of my previous Article:mysql multi-master réplication with Galera, the Architectur E works perfectly and the communications are established using SSL. But there were some issues:

    • There is no load-balancing, the application only use one node and Galera replicates the data accross the cluster
    • If the node where the application commits goes down, no more replication

The main goal was:

Setup a new feature which would provide load-balancing functionnality and access to my database through a single entry poin T by using a virtual IP address.

You can use the some load-balancers for this purpose:

    • Pen:a Tiny TCP Load-balancer
    • Glb:galera Load-balancer, mainly based on Pen and fix missing functionnality to pen
    • MySQL proxy:the one provides by MySQL
    • Haproxy:the Load-balancer

Here a use case:

II. Setup

I'll re-use my last galera configuration, here the architecture I set up:

NIC Configuration, /etc/network/interfaces :

# This file describes the network interfaces available on your system# and how to activate them. For more information, see interfaces(5).# The loopback network interfaceauto loiface lo inet loopback# The primary network interfaceauto eth0iface eth0 inet dhcp# The secondary network interfaceauto eth1iface eth1 inet staticaddress 10.0.0.10netmask 255.0.0.0

Local name resolution, /etc/hosts :

127.0.0.1   localhost127.0.1.1   haproxy-node0110.0.0.2    galera-node0210.0.0.1    galera-node0110.0.0.3    galera-node0310.0.0.10   haproxy-node0110.0.0.11   haproxy-node02

HAProxy Installation:

[email protected]:~$ sudo apt-get install haproxy -y

We allow the INIT script to launch HAProxy:

[email protected]:~$ sudo sed -i s/0/1/ /etc/default/haproxy

HAProxy Configuration from scratch:

# this config needs haproxy-1.1.28 or haproxy-1.2.1global        log 127.0.0.1   local0        log 127.0.0.1   local1 notice        #log loghost    local0 info        maxconn 1024        #chroot /usr/share/haproxy        user haproxy        group haproxy        daemon        #debug        #quietdefaults        log     global        mode    http        option  tcplog        option  dontlognull        retries 3        option redispatch        maxconn 1024        timeout connect 5000ms        timeout client 50000ms        timeout server 50000mslisten galera_cluster 0.0.0.0:3306        mode tcp        balance roundrobin        option tcpka        option mysql-check user haproxy        server galera-node01 10.0.0.1:3306 check weight 1        server galera-node02 10.0.0.2:3306 check weight 1        server galera-node03 10.0.0.3:3306 check weight 1

Main Options Details:

    • mode TCP : default mode, HAProxy on TCP level, a Full-duplex connection'll be Establishe D between the client and the server. Others options are available: http and health
    • balance roundrobin : Defines the load-balancing algorythm to use. Round-robin is a loop system, if you have 2 servers, the failover would be something like this:1,2,1,2
    • op tion Tcpka : Enable the keepalive also know as pipelining

If you specify a username with the option mysql-check , the check consists of sending, MySQL packet, one Client authentication Packet, and one QUIT packet, to correctly close MySQL session. We then parse the MySQL handshake initialisation packet and/or ERROR packet. It is a basic but useful test which does not produce error nor aborted connect on the server. However, it requires adding an authorization in the MySQL table, like this:

mysql> USE mysql;mysql> INSERT INTO user (Host,User) values (‘10.0.0.10‘,‘haproxy‘);mysql> FLUSH PRIVILEGES;

We Run HAProxy:

[email protected]:~$ sudo service haproxy start

Does HAProxy work?

[email protected]:~$ sudo netstat -plantu | grep 3306tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3729/haproxy

Round-robin Effects:

  [email  Protected] : ~$ mysql-uroot-proot-h127.0.0.1-e "show variables like ' wsrep_node_name ';" +-----------------+---------------+| variable_name | VALUE |+-----------------+---------------+| Wsrep_node_name | GALERA-NODE01 |+-----------------+---------------+[email protected]:~$ mysql-uroot-proot-h127.0.0.1-e "show Variables like ' wsrep_node_name '; +-----------------+---------------+| variable_name | VALUE |+-----------------+---------------+| Wsrep_node_name | GALERA-NODE02 |+-----------------+---------------+[email protected]:~$ mysql-uroot-proot-h127.0.0.1-e "show Variables like ' wsrep_node_name '; +-----------------+---------------+| variable_name | VALUE |+-----------------+---------------+| Wsrep_node_name | GALERA-NODE03 |+-----------------+---------------+  
Iii. Bonus SCRIPTSIII.1. HAProxy Hot Reconfiguration

Extract from the HAProxy Configuration Guide.

Hot reconfiguration View on Github

  #!/bin/bash#a Hot reconfiguration script would: #Extract from http://haproxy.1wt.eu/download/ 1.3/doc/architecture.txt# Save previous Statemv/etc/haproxy/config/etc/haproxy/config.oldmv/var/run/haproxy.pid/ Var/run/haproxy.pid.oldmv/etc/haproxy/config.new/etc/haproxy/configkill-ttou $ (cat/var/run/haproxy.pid.old) if Haproxy-p/var/run/haproxy.pid-f/etc/haproxy/config;    Then echo "New instance successfully loaded, stopping previous one."  KILL-USR1 $ (cat/var/run/haproxy.pid.old) rm-f/var/run/haproxy.pid.old exit 1else echo "New instance failed to    Start, resuming previous one. " Kill-ttin $ (cat/var/run/haproxy.pid.old) rm-f/var/run/haproxy.pid MV/VAR/RUN/HAPROXY.PID.OLD/VAR/RUN/HAPROXY.P ID mv/etc/haproxy/config/etc/haproxy/config.new mv/etc/haproxy/config.old/etc/haproxy/config Exit 0fi  /pre>

This setup is interesting and not a ready for production. Indeed, putting a HAProxy node on top created a huge SPOF. In the next article, I'll setup a failover active/passive cluster with PACEMAKER.III.2 Init script GLB

First download it from here and install it:)

Init script for the Galera Load Balancer daemon.

Start/stop the Galera Load Balancer daemon. Download Me

#!/bin/sh## GLBD start/stop The Galera Load Balancer daemon.## processname:glbd# chkconfig:2345-60# descript ION:GLB is a TCP load balancer similar to Pen. # It lacks most of the advanced Pen features, as # The aim is to make a user-space TCP proxy which is # as fast as possible. It can utilize multiple CPU cores. # A List of destinations can configured at runtime. # Destination "draining" is supported. It Features # weight-based connection balancing (which becomes # Round-robin if weights are Equa L). # # # BEGIN INIT info# provides:glbd# required-start: $local _fs# required-stop: $local _fs# default-start:2345# Default -stop:90# Short-description:run GLBD daemon# description:glb is a TCP load balancer similar to pen.### END INIT Infopro G= "GLBD" proc=glbdexec=/usr/sbin/glbdlisten_port= "8010" control_port= "8011" threads= "2" DEFAULT_TARGETS= "djdb2 : 8000:0.75 djdb3:8000:0.75 divjobs3:8000:0. Divjobs4:8000:1 "Stop () {echo-n" [' Date '] $prog: Stopping ... "Killall $exec &>/dev/null if [$?] -ne 0];      Then echo "failed." return fi echo "done."}      Start () {if pidof $prog &>/dev/null; then echo "[' Date '] $prog: Already running ..."; Exit-1 fi echo "[' Date '] $prog: Starting ..." Wait_for_connections_to_drop $exec--daemon--control 127.0.0.1: $CONTROL  _port--threads $THREADS $LISTEN _port $DEFAULT _targets pid=$! If [$?-ne 0];      Then echo "[' Date '] $prog: Failed to start." Exit-1 fi echo "[' Date '] $prog: Started, pid= $PID" Exit 0}restart () {echo "[' Date '] $prog: Restarting ..." Stop STA Rt}wait_for_connections_to_drop () {while (Netstat-na | grep-m 1 ": $LISTEN _port" &>/dev/null), do echo "[' Date '] $prog: Waiting for lingering sockets to clear up ... "sleep 1s done;} GetInfo () {echo GetInfo | nc 127.0.0.1 $CONTROL _port && exit 0 echo "[' Date '] $prog: Failed to query ' GetInfo ' From 127.0.0.1: $CONTROL _port "Exit-1}getstats () {echo getstats | nc 127.0.0.1 $CONTROL _port && exit 0 echo" [' Date '] $pro g:failed to query ' GetStats ' from 127.0.0.1: $CONTROL _port "Exit-1}add () {if ["] = = ""]; Then echo $ "Usage: $ add <ip>:<port>[:<weight>]" exit-1 fi if ["' Echo" $ | NC 127.0.0.1 $CONTROL _port ' "= =" OK "]; Then echo "[' Date '] $prog: Added ' $ ' successfully" #getinfo exit 0 fi echo "[' Date '] $prog: Failed to ad  D target ' $ '. Exit-1}remove () {if ["] = =" "]; Then echo $ "Usage: $ remove <ip>:<port>" exit-1 fi If ["' Echo" $1:-1 "| NC 127.0.0.1 $CONTROL _port ' "= =" OK "]; Then echo "[' Date '] $prog: Removed ' $ ' successfully" #getinfo exit 0 fi echo "[' Date '] $prog: Failed to  Remove target ' $ '.  Exit-1}case $ in start) start;  stop) stop;;  restart) restart;;  GetInfo) GetInfo;;  GetStats) getstats;; status) GetInfo;;  Add) add $;;  Remove) remove $;; *) echo $ "Usage: $ start|stop|restart|status|getstats|getinfo|add|remove}" Exit 2esac

[Go]mysql-galera Cluster with HAproxy

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.