[轉]MySQL-Galera Cluster With HAproxy

標籤：des   blog   http   os   io   for   ar   art   div   

When I started working on 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 that came to me was to use HAProxy on top of my existing Galera cluster. I. Introduction

Reminder: I will use the configuration of my previous article: MySQL multi-master réplication with Galera, the architecture works perfectly and the communications are established using SSL. But there were some issues:

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

The main goal was:

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

You can use 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 will 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 will work at TCP level, a full-duplex connection will be established between the client and the server. Two others options are available: httpand health
• balance roundrobin: defines the load-balancing algorythm to use. Round-robin is a loop system, if you have 2 servers, the failover will be something like this: 1,2,1,2
• option tcpka: enable the keepalive also know as pipelining

If you specify a username with the option mysql-check, the check consists of sending two 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 look like this :#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.pid    mv /etc/haproxy/config /etc/haproxy/config.new    mv /etc/haproxy/config.old /etc/haproxy/config    exit 0fi

This setup is interesting but not ready for production. Indeed, putting an HAProxy node on top created a huge SPOF. In the next article, I will 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 90 60# description: GLB is a TCP load balancer similar to Pen. #              It lacks most of advanced Pen features, as #              the aim was to make a user-space TCP proxy which is #              as fast as possible. It can utilize multiple CPU cores. #              A list of destinations can be configured at runtime. #              Destination "draining" is supported. It features #              weight-based connection balancing (which becomes #              round-robin if weights are equal).### 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 INFOprog="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.75 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  start}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`] $prog: failed to query ‘getstats‘ from 127.0.0.1:$CONTROL_PORT"  exit -1}add() {  if [ "$1" == "" ]; then      echo $"Usage: $0 add <ip>:<port>[:<weight>]"      exit -1  fi  if [ "`echo "$1" | nc 127.0.0.1 $CONTROL_PORT`" == "Ok" ]; then      echo "[`date`] $prog: added ‘$1‘ successfully"      #getinfo      exit 0  fi  echo "[`date`] $prog: failed to add target ‘$1‘."  exit -1}remove() {  if [ "$1" == "" ]; then      echo $"Usage: $0 remove <ip>:<port>"      exit -1  fi  if [ "`echo "$1:-1" | nc 127.0.0.1 $CONTROL_PORT`" == "Ok" ]; then      echo "[`date`] $prog: removed ‘$1‘ successfully"      #getinfo      exit 0  fi  echo "[`date`] $prog: failed to remove target ‘$1‘."  exit -1}case $1 in  start)      start  ;;  stop)      stop  ;;  restart)      restart  ;;  getinfo)      getinfo  ;;  getstats)      getstats  ;;  status)      getinfo  ;;  add)      add $2  ;;  remove)      remove $2  ;;  *)      echo $"Usage: $0 {start|stop|restart|status|getstats|getinfo|add|remove}"  exit 2esac

[轉]MySQL-Galera Cluster With HAproxy