The following series is meant to show you the fastest way to get the following:

• Part 1: MariaDB + Galera Cluster running with 3 Servers with read/write access that will automatically sync with each other.
• Part 2: A load balancer in DigitalOcean that will Round Robin between your 3 data bases.
• Part 3: A DNS entry in your domain name to create dbloadbalancer.yoursite.com in order to easily reference it on your .ENV file on your laravel project.

In order to take advantage of some of the shortcuts in the following guide you will need the following:

• A laravel Forge Account
• A DigitalOcean Account
• A SSH Client (I will use MobaXterm)

Part 1: MariaDB Galera Cluster

First: create three new servers using Forge as shown below:

Our servers will be called called DBNODE1,DBNODE2,DBNODE3. Forge will provide you with a sudo password and a database password.

Grab the information from each server on a secure place.

I will put them on an Airtable for now. As you can see Laravel Forge just saved us a lot of time by creating the servers with MariaDB, creating the sudo user and the root database password. Also Taylor (Creator of Laravel) mentioned that the servers deployed with Envoyer are kept up to date with the latest security patches.

Login to every server and create the following configuration file for Galera Cluster.

Don't know how to login to your server? Click here to learn.

In my case I'm using MobaXterm to login to all servers at the same time.

sudo nano /etc/mysql/conf.d/galera.cnf

The configuration should look like the following:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="DBNODE1"
wsrep_cluster_address="gcomm://10.136.49.168,10.136.5.203,10.136.47.33"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
#Enter the ip of the current node here and its name
wsrep_node_address="10.136.49.168"
wsrep_node_name="DBNODE1"

Where: 10.136.49.168,10.136.5.203, 10.136.47.33 are the private network IP addresses from Digital Ocean.

You will end up with the following variations for DBNODE2 AND DBNODE3

DBNODE2

...same code...
# Galera Node Configuration
#Enter the ip of the current node here and its name
wsrep_node_address="10.136.5.203"
wsrep_node_name="DBNODE2"

DBNODE3

...same code...
    # Galera Node Configuration
#Enter the ip of the current node here and its name
wsrep_node_address="10.136.47.33"
wsrep_node_name="DBNODE3"

Congratulations you have almost everything you need for a Mysql dabase cluster. You can add more servers if you need to.

Firewall

Run the following commands to open the ports needed by Mysql and Galera in all servers as explained in the Galera Guide:

sudo ufw allow 3306,4567,4568,4444/tcp
sudo ufw allow 4567/udp

Stop Mysql on all servers

sudo systemctl stop mysql

Start the Cluster on DBNODE1

Please note only the first server starting in the cluster should be started with this script. The other servers should be started normally.

sudo galera_new_cluster

I get a script error but the servers starts fine:

[email protected]:~$ sudo galera_new_cluster
/usr/bin/galera_new_cluster: 8: [: unexpected operator
[email protected]:~$

Check the first node is running

mysql -u forge -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

You will be asked to enter the Database root password that was given to you during the creation of the servers. The ones you saved in Airtable.

You should see this:

Good job! you have the first node in your cluster running. The reaining nodes will connect to this node.

Start the second server

sudo systemctl start mysql

Check the second node is running

mysql -u forge -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

Let's do the last DBNODE3

sudo systemctl start mysql
mysql -u forge -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

Ok, that was too easy! You got a Cluster with 3 servers running. You can write to any of them and they will stay synced.

In the next tutorial I will show you how I connect to all 3 servers using a load balancer (Round Robin).

Important:

Please take some time to read the manual from Galore. For example what if the power goes out and all servers get disconected? You will need to start the last active server. There are very clear instructions on the Galera website that explain this process. here.

Most Galera related questions you can find online. I hope this quick tutorial was helpful. I will post the load balancing tutorial in a few days.

Credits:

Digital Ocean - Detailed guide for seting up a DB cluster. They go a little deeper but do not use Laravel Forge, instead they create the server from scracth which is a little more time consuming. Galera Documentation

Greetings

Daniel Farina