MySQL high availability with ProxySQL, Consul and Orchestrator

Proof of concept

• ProxySQL + consul-template + Consul (client mode)
• MySQL
• Orchestrator + Consul (client mode)
• Consul (server mode)

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_expect": 3,
  "retry_join": [
  "192.168.56.100",
  "192.168.56.101",
  "192.168.56.102"
  ],
  "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_expect": 3,
  "retry_join": [
  "192.168.56.100",
  "192.168.56.101",
  "192.168.56.102"
  ],
  "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_expect": 3,
  "retry_join": [
  "192.168.56.100",
  "192.168.56.101",
  "192.168.56.102"
  ],
  "retry_join": ,
  "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

$ consul kv put foo bar
 Success! Data written to: foo
 
 $ consul kv get foo
 bar

Configuring Orchestrator to write to Consul

1. Configure Orchestrator to write to Consul on each master change. Add the following lines to Orchestrator configuration:

 $ 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. We need to tell Orchestrator to populate the values in Consul while bootstrapping the first time. This is accomplished by calling orchestrator-client. Orchestrator will update the values automatically if there is a master change.

$ 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 ProxySQL

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 ProxySQL config file. Note this template also deals with slave servers.

$ vi /opt/consul-template/templates/proxysql.ctmpl
 
 
 DELETE FROM mysql_servers where hostgroup_id=0;
 REPLACE into mysql_servers (hostgroup_id, hostname) values ( 0, "" );
 
 
 REPLACE into mysql_servers (hostgroup_id, hostname) values ( 1, "" );
 
 LOAD MYSQL SERVERS TO RUNTIME;
 SAVE MYSQL SERVERS TO DISK;

3. Create consul-template config file. Note that we need Consul agent, which will provide us with a Consul API endpoint at port 8500, installed locally in order for consul template to subscribe to 127.0.0.1:8500.

$ 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/proxysql.ctmpl"
  destination = "/opt/consul-template/templates/proxysql.sql"
 
 # log in to proxysql and execute the template file containing sql statements to set the new topology
  command = "/bin/bash -c 'mysql --defaults-file=/etc/proxysql-admin.my.cnf < /opt/consul-template/templates/proxysql.sql'"
  command_timeout = "60s"
  perms = 0644
  backup = true 
  wait = "2s:6s"
 }

4. 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 &

[root@mysql3 config]$ tail -f /var/log/consul-template/consul-template.log
 
 [INFO] (runner) rendered "/opt/consul-template/templates/proxysql.ctmpl" => "/opt/consul-template/templates/proxysql.sql"
 [INFO] (runner) executing command "/bin/bash -c 'mysql --defaults-file=/etc/proxysql-admin.my.cnf < /opt/consul-template/templates/proxysql.sql'" from "/opt/consul-template/templates/proxysql.ctmpl" => "/opt/consul-template/templates/proxysql.sql"

Conclusion