This assumes you have installed HAProxy.

You will probably also want to configure Master-Master replication between your mySQL or MariaDB databases.

HAProxy is a service that can be used to load balance requests between a TCP service. For example, let's say you have two mySQL or MariaDB databases. HAProxy can be used to load balance the requests across the two databases, using Round Robin or Least Connections.

If you have a firewall, such as iptables or firewalld, assuming your mySQL or MariaDB databases are listening on default port 3306, allow port 3306 in the firewall.

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

Let's say you want requests submitted into HAProxy on port 3306 (frontend) to be forwarded to the mySQL or MariaDB databases on port 3306 (backend), using round robin for load balancing. In this scenario, the frontend and backend blocks in haproxy.cfg would contain something like this. Let's break this down.

• The listener is named "mariadb". This can be any name you want, it just has to be something unique.
• The listener is listening on *:3306, meaning that any interface / IP address on the HAProxy server on port 3306. 
• round robin for load balancing.
• The first server is the MariaDB database at maria1.example.com.
• The second server is the MariaDB database at maria2.example.com.

listen mariadb
    bind *:3306
    mode tcp
    balance roundrobin
    server db1 maria1.example.com:3306 check
    server db2 maria2.example.com:3306 check

Or like this.

frontend mariadb
    bind *:3306
    default_backend maria_db

backend maria_db
    balance roundrobin
    server db1 maria1.example.com:3306 check
    server db2 maria2.example.com:3306 check

Restart HAProxy for this change to take effect.

systemctl restart haproxy

To test this, let's say you have the mysql client installed on your laptop. The mysql client can be used to issue a request to HAProxy, and then HAProxy should forward the request onto one of your mySQL or MariaDB databases, and if all goes well, you should be able to connect to one of your mySQL or MariaDB databases.

~]# mysql --host=<HAProxy hostname or IP address> --user=<mySQL or MariaDB user> --password
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52144
Server version: 10.6.4-MariaDB-1:10.6.4+maria~focal-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Then on one of your MariaDB databases, the show processlist should show the connection. Notice in this example that the Host is haproxy.example.com, which in this scenario is the hostname of the HAProxy system.

      Id: 40973
    User: root
    Host: haproxy.example.com:45520
      db: NULL
 Command: Sleep
    Time: 3
   State:
    Info: NULL
Progress: 0.000

Disconnect from MariaDB, reconnect, and the show processlist command on the other MariaDB database should now show the connection, as a proof of concept that requests are being load balanced between the MariaDB databases.

Instead of using roundrobin, it may make sense to use leaseconn, so that the MariaDB database with the least connections is used.

listen maria_db
    bind *:3306
    mode tcp
    balance leastconn
    server db1 maria1.example.com:3306 check
    server db2 maria2.example.com:3306 check