There are scenarios where we have to provide the high ability to MySQL database instances and we use the Master and Slave replication method of MySQL database.
In the same case to segregate the Read and Write database traffic. We widly use HA- Proxy. It is a feature rich open source Load blancing tool with many unique features like reverse proxy but in out case we are going to use it only for Hight aviliblity purpose.
root@haproxy01:~# haproxy -v
HA-Proxy version 2.0.13-2ubuntu0.3 2021/08/27 - https://haproxy.org/
How to Install it?
You simply use yum or apt commands to install it
sudo apt install -y haproxy
Check the version
Install Mysql Client for HA Proxy Node to communicate with mysql master and slave databases.
apt-get install -y mysql-client
cd /etc/haproxy/
cp haproxy.cfg haproxy.cfg_org
vim haproxy.cfg
root@haproxy01:~# cat /etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local0 notice
log /dev/log local0
user haproxy
group haproxy
# Default SSL material locations
defaults
log global
mode tcp
option tcplog
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s
listen stats
mode http
bind *:9201
stats enable
stats uri /stats
stats realm Strictly\ Private
stats auth admin:admin
listen mysql-cluster
bind *:3306
mode tcp
option mysql-check user haproxy_user
balance roundrobin
server master 192.168.56.205:3306 check
server slave1 192.168.56.206:3306 check
listen mysql-cluster1
bind 192.168.1.208:3306
mode tcp
option mysql-check user haproxy_user
balance roundrobin
server mysql-1 192.168.1.205:3306 check
server mysql-2 192.168.1.206:3306 check
Create HA proxy user on mysql01/205 on primary node
GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'%' IDENTIFIED BY 'Oracle@123' WITH GRANT OPTION;
flush privileges;
Test the configuration and it should start without error & Target should come up on GUI
haproxy -f /etc/haproxy/haproxy.cfg -db
systemctl restart haproxy.service
Check HA proxy GUI and see all the MySQL target is up and running fine using HA Proxy Admin link:
HA Proxy Link Structure:
http://<localhost or IP/HostName/stats
http://192.168.1.208:9201/stats
Default Credentials :
UserName : admin
Password: admin
Read More...
