Prerequisites

In this article we assume you want to load balance a PHP application that needs MariaDB/MySQL access. You will need at least 4 servers to follow along, 1 load balancer, 2 (or more) application servers and a database/cache server as well.

Provision application servers

As stated in the prerequisites you will need at least two application servers. Spin them up if you haven't already. Mine are called appserverone and appservertwo.

Notice their IP addresses. You will use them shortly.

In order for us to see the balancing in action later, SSH into the two application servers and modify the default index.php file in the /var/www/html/ folder:

$ sudo nano /var/www/html/index.php

Change the name of the headings to differentiate the two so you know which server you are hitting when you visit your load balanced setup in a browser.

Create the load balancer and install required software

Next up is the load balancing server. This server is responsible for proxying requests to the application servers.

---

You have two options here:

1. Provision one of our perfect stacks that comes with everything you need ie. Nginx, SSL certificate and more but also comes with an overhead of software not needed for the load balancer. You can of course just remove this software with apt remove --purge list-of-packages.
    
2. Configuring the load balancer from scratch yourself. This guide will follow this approach.

Start off by provisioning a server (I called it loadbalancer) with the latest stable Ubuntu software (Focal 20.04 at the time of writing), SSH into the server and install nginx:

$ sudo apt-get update
$ sudo apt-get install -y nginx

Go ahead and open the load balancer in order to see that Nginx is installed by copying the public IP address and paste it into your browser. You should see the Welcome to nginx! default page.

Configure the load balancer and obtain an SSL certificate

First, remove the default sites-enabled file from nginx:

$ sudo rm /etc/nginx/sites-enabled/default

Second, add your own configuration file for nginx to spread out the traffic to the app servers:

$ sudo touch /etc/nginx/conf.d/serverdomain.com.conf

And modify it:

server {
    listen 80 default_server;
    listen [::]:80 default_server;
    root /var/www/html;
    server_name serverdomain.com www.serverdomain.com;
}

Test if syntax is correct and reload:

$ sudo nginx -t
$ sudo service nginx reload

Now download the Let’s Encrypt snap client and symlink it so the binary run from anywhere:

$ sudo snap install --classic certbot
$ sudo ln -s /snap/bin/certbot /usr/bin/certbot

If you encounter any issues with the above, try upgrading your system as a whole with the regular apt commands:

$ sudo apt update
$ sudo apt upgrade

Obtain the SSL/TLS Certificate - follow the instructions to set it up:

$ sudo certbot --nginx -d serverdomain.com

Finally, configure the .conf file to round-robin traffic between the app servers. Notice that Certbort modified the confguration quite a bit - add the following shown in bold:

upstream backend {
    server appserveripaddress:443;
    server appserveripaddress:443;
}

server {
    server_name serverdomain.com;
 	# managed by Certbot
    listen [::]:443 ssl ipv6only=on; # managed by Certbot
    listen 443 ssl; # managed by Certbot
    ssl_certificate /etc/letsencrypt/live/loadbalancer.vps.webdock.io/fullchain.pem; # managed by Certbot
    ssl_certificate_key /etc/letsencrypt/live/loadbalancer.vps.webdock.io/privkey.pem; # managed by Certbot
    include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot

	# We will include proxy_params which will help us proxy off to http requests the proxy_pass off to our upstream called “backend”
	 location / {
        include proxy_params;
        proxy_pass https://backend;
        proxy_redirect off;
    }

}

server {
    if ($host = loadbalancer.vps.webdock.io) {
        return 301 https://$host$request_uri;
    } # managed by Certbot

	# Notice the root is removed

    listen 80 ;
    listen [::]:80 ;
    server_name loadbalancer.vps.webdock.io;
    return 404; # managed by Certbot
}

Let's see if this works:

$ sudo nginx -t
$ sudo service nginx reload

Session persistence - simple option

With round robin-load balancing there is no guarantee that the same client will be always directed to the same server. In order to ensure this you need to be able to persist the user session when balancing the servers. This can be done in several ways and the most simple method is by ip hashing.

With ip hashing, the client’s IP is used as a hashing key to determine what application server should be selected for the client’s requests. This method ensures that the requests from the same client (provided they are not behind a proxy or a shared IP address) will always be directed to the same server except when this server is unavailable. Add the following in bold to your load balancers .conf file server block:

upstream backend {
	ip_hash;
    server appserveripaddress:443;
    server appserveripaddress:443;
}

Test it out by going to your loadbalancer and notice that you won't get bounced from appserverone to appservertwo from now on.

Database server

You can read a detailed explanation on how to enable remote access to MariaDB on Webdock here. The linked information assumes you start with our LEMP stack.

If you haven't spun up a database server go ahead and do so. I'll use a LEMP stack for this. Notice it comes with an overhead of software but gives you all the database tools you need

To allow remote connections to a MySQL server, you need to perform the following steps:

• Configure the MySQL server to listen for specific IP addresses
• Open the MySQL port in your firewall.
• Grant access to the remote user.

Configure the MySQL server

The first step is to set the MySQL server to listen on specific IP addresses. To do so, you need to edit the MySQL configuration file and add or change the value of the bind-address option. If the address is 0.0.0.0, the MySQL server accepts connections on all host IPv4 interfaces. Note that we are communicating on a public connection, thus no 127.0.0.1.

For MariaDB v10.6 and below:

$ sudo nano /etc/mysql/my.cnf

For MariaDB version above 10.6:

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Locate the bind address and change it from 127.0.0.1 (localhost) to 0.0.0.0

bind-address = 0.0.0.0

And restart mysql for changes to take affect

$ sudo systemctl restart mysql

Configuring UFW firewall

The last step is to configure your firewall to allow traffic on the default MySQL port (port 3306).

$ sudo ufw allow from appserverone_ip_address to any port 3306
$ sudo ufw allow from appservertwo_ip_address to any port 3306

Granting Access to the application servers

The next step is to allow access to the database to the application servers. Log in to the MySQL server:

$ sudo mysql

From inside the MySQL shell, run the GRANT statement as follows:

GRANT ALL ON database_server_name.* TO app_server_root_user_name@'app_server_ip_address' IDENTIFIED BY 'database_server_database_password';

Test it out!

If everything is setup up correctly, you will be able to login to the remote MySQL server from the terminal using your password:

$ mysql -u user_name_on_app_server -h database_server_ip -p

For testing access to data from the database server we need some data in the database to show, then try to dump the data. If you follow along - SQL query executed in PHPMyAdmin:

CREATE TABLE Persons (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(30) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	email VARCHAR(50),
	reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

And manually fill some data to the table

Then the following to the top of any of your application servers index.php files:

database_server_ip";
 $userName = "admin";
 $password = "database_server_password";
 $databaseName = "database_server_name";

try {
  $connection = new PDO("mysql:host=$server;dbname=$databaseName", $username, $password);
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $statement = $connection->prepare("SELECT id, firstname, lastname FROM Persons"); 
  $statement->execute();

  $result = $statement->fetchAll();
  die(var_dump($result));

} catch(PDOException $exception) {
  $exception->getMessage();
}
 $connection = null;

?>

Please note this is just simple code for example purposes and you shouldn't really access your database in this manner in PHP but use some sort of database wrapper or framework.

Conclusion

In this article we have shown how you can get a basic load balancing setup up and running on Webdock and allow database access from any of your application servers. MariaDB can be further spread out over multiple servers for load balancing of the database itself but that is beyond the scope of this article. We hope you found it useful!

Article Author:  Thomas Damsgaard