104_HAProxy Setup

Jump to bottom

Luke Yang edited this page May 20, 2023 · 1 revision

Preface

About

This page provides instructions for configuring HAProxy on the Percona servers to connect Percona to the HashiCorp vault servers when failover occurs. Each step includes an identity that indicates which application should be used for the instructions that proceed. Examples are also provided for steps that having varying parameters depending on the context (server names, usernames, passwords, etc).

Note: Even though each new step indicates shelling into the server, it is not necessary often as you can continue on a previously open terminal that is already shelled into the server. The goal is to provide ease of understanding if a specific portion needs to be configured or changed in contrast from going through start to finish.

Example Values

The following values will be used for the examples:

Parameter	Value
User on all Servers	luke
Primary Percona Server	b13.jmaconsulting.biz
Secondary Percona Server	ovh13.jmaconsulting.biz
Active Vault Server	ovh12.jmaconsulting.biz
Standby Vault Server	b10.jmaconsulting.biz
Standby Vault Server	b11.jmaconsulting.biz

Prerequisites

Percona Primary-Secondary replication servers configured
Vault High Avaiability Cluster Configured
LetsEncrypt SSL for Apache configured for all Vault servers and Percona servers

Main Documentation

1. Install HAProxy 2.4 [source]

Identity: Local Machine, Terminal

# shell into Active Percona Server
$ ssh [user]@[host]

# ===== example =====
$ ssh luke@b13.jmaconsulting.biz

Identity: Primary Percona Server, Terminal

# install HAProxy
$ sudo apt install --no-install-recommends software-properties-common
$ sudo add-apt-repository ppa:vbernat/haproxy-2.4 -y
$ sudo apt install haproxy=2.4.\*

# confirm installation was successful
$ haproxy -v

# update
$ sudo apt update && sudo apt upgrade -y

2. Configure SSL Reloading When Certificate and Key Renew [source]

Identity: Local Machine, Terminal

# shell into Primary Percona Server
$ ssh [user]@[host]

# ===== example =====
$ ssh luke@b13.jmaconsulting.biz

Identity: Primary Percona Server, Terminal

# install socat
$ sudo apt install socat

# add the following lines to the end of the existing renewal script
$ sudo nano /etc/letsencrypt/renewal-hooks/deploy/mysqld-deploy.sh

Identity: Primary Percona Server, Terminal

# make and change ownership of /var/lib/haproxy directory
$ sudo mkdir /var/lib/haproxy
$ sudo chown haproxy:haproxy /var/lib/haproxy

# edit the existing bash script to copy over and reload ssl cert and key for haproxy whenever certbot renews
$ sudo nano /etc/letsencrypt/renewal-hooks/deploy/mysqld-deploy.sh

Identity: mysqld-deploy.sh

#!/bin/sh
domain={{hostvars[inventory_hostname]['preseed_hostname']}}.{{hostvars[inventory_hostname]['preseed_domain']}}
cert_dir=/var/lib/mysql
user=mysql.mysql
cp /etc/letsencrypt/live/$domain/privkey.pem $cert_dir/privkey.pem

openssl x509 -in /etc/letsencrypt/live/$domain/fullchain.pem > $cert_dir/fullchain.pem

wget https://curl.se/ca/cacert.pem -P /etc/letsencrypt/live/$domain/
cat /etc/letsencrypt/live/$domain/fullchain.pem >> /etc/letsencrypt/live/$domain/cacert.pem
cp /etc/letsencrypt/live/$domain/cacert.pem $cert_dir/cacert.pem
rm /etc/letsencrypt/live/$domain/cacert.pem

chown $user $cert_dir/*.pem
chmod 700 $cert_dir/*.pem

mysql --login-path=renew_tls_user@localhost --execute="ALTER INSTANCE RELOAD TLS" 

hp_cert_dir=/var/lib/haproxy
user=haproxy.haproxy
cat $cert_dir/fullchain.pem $cert_dir/privkey.pem > $hp_cert_dir/certkey.pem
chown $user $hp_cert_dir/*.pem
chmod 700 $hp_cert_dir/*.pem

hp_loaded_cert_dir=/etc/haproxy/certs
user=haproxy.haproxy
cp $hp_cert_dir/certkey.pem $hp_loaded_cert_dir/certkey.pem
echo -e "set ssl cert /etc/haproxy/certs/certkey.pem <<\n$(cat /var/lib/haproxy/certkey.pem)\n" | socat tcp-connect:127.0.0.1:9999 -
echo commit ssl cert /etc/haproxy/certs/certkey.pem | socat tcp-connect:127.0.0.1:9999 -
chown $user $hp_loaded_cert_dir/*.pem
chmod 700 $hp_loaded_cert_dir/*.pem

Identity: Primary Percona Server, Terminal

# make cert directory for haproxy and load cert in
$ sudo mkdir /etc/haproxy/certs
$ sudo chown haproxy:haproxy /etc/haproxy/certs
$ sudo chmod 700 /etc/haproxy/certs
$ sudo cp /var/lib/haproxy/certkey.pem /etc/haproxy/certs

Identity: Primary Percona Server, Terminal

# run the script once to load the file
$ cd /etc/letsencrypt/renewal-hooks/deploy
$ bash mysqld-deploy.sh

3. Configure HAProxy 2.4 [source]

Identity: Local Machine, Terminal

# shell into Active Percona Server
$ ssh [user]@[host]

# ===== example =====
$ ssh luke@b13.jmaconsulting.biz

Identity: Primary Percona Server, Terminal

# edit the HAProxy configuration file and copy the following code into the file
$ sudo nano /etc/haproxy/haproxy.cfg

Identity: haproxy.cfg

global
  # Enable the HAProxy Runtime API
  stats socket 127.0.0.1:9999 level admin expose-fd listeners

defaults
  mode tcp
  timeout connect 5000ms
  timeout client 10000ms
  timeout server 5000ms

frontend percona
  mode tcp
  bind [percona_host].jmaconsulting.biz:8443 ssl crt /etc/haproxy/certs/certkey.pem
  use_backend vault
	
backend vault
  mode tcp
  timeout check 5000
  timeout server 10000
  timeout connect 5000
  balance roundrobin
  server node1 [active_vault_host_ipv4] ssl verify none
  server node2 [standby_vault_host_1_ipv4] ssl verify none
  server node3 [standby_vault_host_2_ipv4] ssl verify none

Identity: Primary Percona Server, Terminal

# restart HAProxy
$ sudo service haproxy restart

Identity: Primary Percona Server, Terminal

# Edit keyring_vault.conf file to use HAProxy address instead of Vault address directly
$ sudo nano /var/lib/mysql-keyring/keyring_vault.conf

Identity: keyring_vault.conf

vault_url=https://[percona_host].jmaconsulting.biz:8443
secret_mount_point=kv/dc1/master
secret_mount_point_version=AUTO
token=[vault_token]
vault_ca=/var/lib/mysql/cacert.pem

Identity: Primary Percona Server, Terminal

# Restart the Percona server
$ sudo service mysql restart