MariaDB + Galera Cluster + HAProxy

·
4 min read
database linux devops
#mariadb #galera #haproxy #debian #linux

Complete guide to setting up MariaDB Galera Cluster with HAProxy for high availability. Includes configuration, installation, and load balancing setup for production environments.

HA Proxy encapsulate a Galera cluster for High-Availability solution.

Configuration

There are 4 servers.

HAProxy, 2 nodes:

  • IP 1: 192.168.1.12
  • IP 2: 192.168.101.1

MariaDB, 3 nodes: (for quorum)

  • MariaDB1 (192.168.101.10)
  • MariaDB2 (192.168.101.11)
  • MariaDB3 (192.168.101.12)

Installing MariaDB

Takes three steps:

  • setup repository
  • add repo keyring
  • install MariaDB

Adding MariaDB Debian repository

Alternative I

Terminal window
$ cat > /etc/apt/sources.list.d/mariadb.list << EOF
# MariaDB 10.1 repository list - created 2016-07-22 08:52 UTC
# http://downloads.mariadb.org/mariadb/repositories/
deb [arch=amd64,i386] http://mariadb.biz.net.id/repo/10.1/debian jessie main
deb-src http://mariadb.biz.net.id/repo/10.1/debian jessie main
EOF

Alternative II

Terminal window
$ sudo apt-get install software-properties-common
$ sudo add-apt-repository 'deb [arch=amd64,i386] http://mariadb.biz.net.id/repo/10.1/debian jessie main'

Add GPG keyring

Add MariaDB Debian repository keyring

Terminal window
$ sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Install MariaDB

Installing MariaDB server will also automatically install RSYNC and Galera Cluster

Terminal window
$ sudo apt-get install mariadb-server

Configuring Galera

There are three steps to up the Galera cluster:

  • configure each nodes for galera.
  • run one node as the first in node.
  • run the two other nodes.

MariaDB1, MariaDB2, MariaDB3

Do this on all the MariaDB servers.

Terminal window
$ cat > /etc/mysql/conf.d/galera.cnf << EOF
[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0


#galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="apakek_cluster"
wsrep_cluster_address=gcomm://192.168.101.10,192.168.101.11,192.168.101.12
wsrep_sst_method=rsync
EOF

You can creatively change the name galera.cnf into galerbiji.cnf or any name that your brain could summon.

MariaDB1

For simplicity, lets assume we want to make MariaDB1 as the first master.

Debian with systemd (e.g. Jessie)

Terminal window
$ sudo /usr/bin/galera_new_cluster

Debian with other sysinit

Terminal window
sudo service mysql start --wsrep-new-cluster

Final check

To check how many node run on the cluster.

Terminal window
$ sudo mysql --defaults-file=/etc/mysql/debian.cnf -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
+--------------+
| cluster size |
+--------------+
| 1            |
+--------------+

MariaDB2, MariaDB3

The rest of the nodes.

Debian with systemd (e.g. Jessie)

Terminal window
$ sudo systemctl start mysql

Debian with other sysinit

Terminal window
$ sudo service mysql start

Final check

Final check how many node run on the cluster.

Terminal window
$ sudo mysql --defaults-file=/etc/mysql/debian.cnf -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
+--------------+
| cluster size |
+--------------+
| 3            |
+--------------+

Excercise

Create admin users that could be accessed via network is left as an excercise to the reader.

HAProxy

Create a user for HAProxy. This user is used to ping check health status.

Terminal window
$ sudo mysql --defaults-file=/etc/mysql/debian.cnf -e "CREATE USER 'haproxy'@'192.168.101.1'"

Append these lines in /etc/haproxy/haproxy.cfg file.

# Load Balancing for Galera Cluster
listen galera 192.168.1.12:3306
     balance source
     mode tcp
     option tcpka
     option mysql-check user haproxy
     server node1 192.168.101.10:3306 check weight 1
     server node2 192.168.101.11:3306 check weight 1
     server node2 192.168.101.12:3306 check weight 1

Restart HAProxy.

Terminal window
$ sudo service haproxy restart

Done.

Optimization

There are things that can be done to optimize:

  • Replacing HAProxy with hardware load-balancer .
  • Setup HAProxy as gateway/interface to application in each node. HAProxy per instance
  • Use cookbook like Chef, Puppet or Ansible to automate this process.

Further Read

Related Articles

Installing MCP Memory Service on GNU/Linux

Installing MCP Memory Service on GNU/Linux

MariaDB Runs While Under Quorum

MariaDB Runs While Under Quorum

Compiling Memory MCP Server Without CUDA