MySQL high availability with HAProxy, Consul and Orchestrator
Tags:
MySQL,
Amazon Rds,
Dba Lounge,
Technical Track,
Cloud,
Open Source,
Xtrabackup,
Backups,
Encryption,
Amazon S3
Introduction
In this post we will explore one approach to MySQL high availability with HAProxy, Consul and Orchestrator. Let's briefly go over each piece of the puzzle first: - HAProxy is usually installed on the application servers or an intermediate connection layer, and is in charge of connecting the application to the appropriate backend (reader or writer). The most common deployment I've seen is to have separate ports for writes (which are routed to the master) and reads (which are load balanced over a pool of slaves). - Orchestrator's role is to monitor the topology and perform auto recovery as needed. The key piece here is how we can make HAProxy aware that a topology change has happened, and the answer lies within Consul (and Consul templates). - Consul is meant to be told the identity of the new master by Orchestrator. By leveraging Consul templates, we can then in turn propagate that information to HAProxy.Proof of concept
For this POC, I installed 3 test servers which run both MySQL and Consul: mysql1, mysql2 and mysql3. On server mysql3 I also installed HAProxy, Orchestrator and Consul-template.Installing Consul
1. Install Consul on mysql1, mysql2 and mysql3$ sudo yum -y install unzip
$ sudo useradd consul
$ sudo mkdir -p /opt/consul
$ sudo touch /var/log/consul.log
$ cd /opt/consul
$ sudo wget https://releases.hashicorp.com/consul/1.0.7/consul_1.0.7_linux_amd64.zip
$ sudo unzip consul_1.0.7_linux_amd64.zip
$ sudo ln -s /opt/consul/consul /usr/local/bin/consul
$ sudo chown consul:consul -R /opt/consul* /var/log/consul.log
2. Bootstrap the Consul cluster from one node. I've picked mysql3 here:
$ sudo vi /etc/consul.conf.json
{
"datacenter": "dc1",
"data_dir": "/opt/consul/",
"log_level": "INFO",
"node_name": "mysql3",
"server": true,
"ui": true,
"bootstrap": true,
"client_addr": "0.0.0.0",
"advertise_addr": "192.168.56.102"
}
$ sudo su - consul -c 'consul agent -config-file=/etc/consul.conf.json -config-dir=/etc/consul.d > /var/log/consul.log &'
3. Start Consul on mysql1 and have it join the cluster
$ sudo vi /etc/consul.conf.json
{
"datacenter": "dc1",
"data_dir": "/opt/consul/",
"log_level": "INFO",
"node_name": "mysql1",
"server": true,
"ui": true,
"bootstrap": false,
"client_addr": "0.0.0.0",
"advertise_addr": "192.168.56.100"
}
$ sudo su - consul -c 'consul agent -config-file=/etc/consul.conf.json -config-dir=/etc/consul.d > /var/log/consul.log &'
$ consul join 192.168.56.102
4. Start Consul on mysql2 and have it join the cluster
$ sudo vi /etc/consul.conf.json
{
"datacenter": "dc1",
"data_dir": "/opt/consul/",
"log_level": "INFO",
"node_name": "mysql2",
"server": true,
"ui": true,
"bootstrap": false,
"client_addr": "0.0.0.0",
"advertise_addr": "192.168.56.101"
}
$ sudo su - consul -c 'consul agent -config-file=/etc/consul.conf.json -config-dir=/etc/consul.d > /var/log/consul.log &'
$ consul join 192.168.56.102
At this point we have a working 3 node consul cluster. We can test writing k/v pairs to it and retrieving them back:
$ consul kv put foo bar
Success! Data written to: foo
$ consul kv get foo
bar
Configuring Orchestrator to write to Consul
Luckily, Orchestrator has built-in support for Consul so there is little assembly required there. The only caveat is we need to have Orchestrator populate the values in Consul manually by calling orchestrator-client the first time. This is because Orchestrator will only write the values each time there is a master change. 1. Configure Orchestrator to write to Consul on each master change. Add the following lines to Orchestrator config.$ vi /etc/orchestrator.conf.json
"KVClusterMasterPrefix": "mysql/master",
"ConsulAddress": "127.0.0.1:8500",
2. Restart Orchestrator
$ service orchestrator restart
3. Populate the current master value manually
$ orchestrator-client -c submit-masters-to-kv-stores
4. Check the stored values from command line
$ consul kv get mysql/master/testcluster
mysql1:3306
Using Consul template to manage haproxy
Since we have HAProxy running on mysql3, we need to install Consul template on that host to manage haproxy config. The idea is to configure Consul template to dynamically update HAProxy config file template, and reload HAProxy when there are changes to its configuration. For HAProxy, I am setting up two different pools here, the master is reachable through HAProxy via port 3307, while the slaves are accesible over 3308. 1. Install Consul template on mysql3$ mkdir /opt/consul-template
$ cd /opt/consul-template
$ sudo wget https://releases.hashicorp.com/consul-template/0.19.4/consul-template_0.19.4_linux_amd64.zip
$ sudo unzip consul-template_0.19.4_linux_amd64.zip
$ sudo ln -s /opt/consul-template/consul-template /usr/local/bin/consul-template
2. Create a template for HAProxy config file
$ vi /opt/consul-template/templates/haproxy.ctmpl
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
chroot /usr/share/haproxy
user haproxy
group haproxy
daemon
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
frontend writer-front
bind *:3307
mode tcp
default_backend writer-back
frontend stats-front
bind *:80
mode http
default_backend stats-back
frontend reader-front
bind *:3308
mode tcp
default_backend reader-back
backend writer-back
mode tcp
option httpchk
server master check port 9200 inter 12000 rise 3 fall 3
backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth user:pass
backend reader-back
mode tcp
balance leastconn
option httpchk
server slave1 192.168.56.101:3306 check port 9200 inter 12000 rise 3 fall 3
server slave2 192.168.56.102:3306 check port 9200 inter 12000 rise 3 fall 3
server master 192.168.56.100:3306 check port 9200 inter 12000 rise 3 fall 3
3. Create consul template config file
$ vi /opt/consul-template/config/consul-template.cfg
consul {
auth {
enabled = false
}
address = "127.0.0.1:8500"
retry {
enabled = true
attempts = 12
backoff = "250ms"
max_backoff = "1m"
}
ssl {
enabled = false
}
}
reload_signal = "SIGHUP"
kill_signal = "SIGINT"
max_stale = "10m"
log_level = "info"
wait {
min = "5s"
max = "10s"
}
template {
source = "/opt/consul-template/templates/haproxy.ctmpl"
destination = "/etc/haproxy/haproxy.cfg"
command = "sudo service haproxy reload || true"
command_timeout = "60s"
perms = 0600
backup = true
wait = "2s:6s"
}
4. Give sudo permissions to consul-template so it can reload haproxy
$ sudo vi /etc/sudoers
consul ALL=(root) NOPASSWD:/usr/bin/lsof, ...,/sbin/service haproxy reload
5. Start consul template
$ nohup /usr/local/bin/consul-template -config=/opt/consul-template/config/consul-template.cfg > /var/log/consul-template/consul-template.log 2>&1 &
And that is all the pieces we need. The next step is doing a master change (e.g. via Orchestrator GUI) and seeing the effects:
[root@mysql3 config]$ tail -f /var/log/consul-template/consul-template.log
2018/04/17 12:56:25.863912 [INFO] (runner) rendered "/opt/consul-template/templates/haproxy.ctmpl" => "/etc/haproxy/haproxy.cfg"
2018/04/17 12:56:25.864024 [INFO] (runner) executing command "sudo service haproxy reload || true" from "/opt/consul-template/templates/haproxy.ctmpl" => "/etc/haproxy/haproxy.cfg"
2018/04/17 12:56:25.864078 [INFO] (child) spawning: sudo service haproxy reload
Redirecting to /bin/systemctl reload haproxy.service
What happened? Orchestrator updated the K/V in Consul, and Consul template detected the change and updated the haproxy config file in turn, reloading haproxy after.
