A complete guide to setup a Multi Site Highly Available Cloud Storage with Nextcloud

Thank you for stopping by, in this blog post I will show you how to set up a Multi-Site, Highly Available Cloud Storage using NextCloud over VPN and also accessible over the internet through HTTPS. I will also go over setting up a dynamic DNS, a DNS service at your home network, and a lot more. So let's get started. Well, by my career profile I might be a Pipeline TD, but over time, I have taught myself skills beyond visual effects pipeline into the verse of DevOps and Software infrastructure. I used weekends and holidays to set this up between my flat in London and my parent's home in Phillaur, India. Huge credits to my dentist brother who helped me put up the hardware together in India. Multi-site could have not been possible without a remote engineer.

What is Nextcloud?

Nextcloud is an open-source free to use cloud storage client-server software. It is a fork of owncloud (you can say the parent of owncloud from which nextcloud originated) which you can self-host at your premise on a micro server such as Raspberry Pi.

What Nextcloud is not!

Nextcloud is not backup software. If you plan to use nextcloud on a single node then you must plan backups of your precious data. In this post, I will also show you a single node and multi-node. The multi-site by default makes a backup of your entire data (without encryption of data).

Benefits and comparison

One of the real benefits of using nextcloud is that you can self-host it which your data stays at your premises. And in this post, I will be covering self-hosted multisite your own private cloud storage infrastructure and setup.

Click here to see the comparison of Nextcloud with other cloud storage services.

Who it is for?

Based on the fact that you can self-host at your premise, the greatest of all advantage is data protection and security still accessible from anywhere in the world using your VPN or over HTTPS. Unlike Dropbox, iCloud, or Google Drive, etc your data is stored on remote storage and is always under the risk of a hack.

• If you work in a profession that you generate a lot of data.
• if you live in a different part of the world than the actual country you came from that you have to move from place to place. (like me)
• If you have to constantly make the data you generate available with your partners in a remote location.
• If you want to share photographs, home videos, or music with your distributed family.
• Single board computer enthusiasts wanting to learn more and make the best use case of investment.

What you need to setup self hosted nextcloud?

Hardware

1. Raspberry Pi 3/4 (4GB or above) 
   or any Single board computer(sbc) (5-12 volt) you can keep up online that you do not waste electricity bill. Since I have much different make sbc, so I decided to go with a multi-node cluster, but you can do it on a Virtual Machine on a Windows laptop too. I am using a Raspberry Pi 3 and Raspberry Pi 4 on a remote site i.e. back home in India. The reason I suggest raspberry pi is because it is arm64 architecture, it has a large user base, and above all, it's Made in the UK.
2. Storage of 1TB SSD or HDD (more the merrier)
3. I am using a 1 TB NVMe SSD here in London. And a 1TB standard HDD in India.
4. Ethernet Cables CAT 7 should be sufficient. 
   I do not recommend connecting your pi to the router over wifi.
5. USB to MicroSD card Adapter

Software

1. Ubuntu Operating System
2. Apache2
3. Php7.*
4. MySQL/Mariadb
5. ProxySQL (for load balancing Galera Cluster)
6. Syncthing (for copying data to other site and high availability)
7. CoreDNS (for DNS Service at home network)
8. Haproxy (for load balancing Redis and Apache2)
9. Redis (to manage file locking and php session sharing)
10. OpenVPN
11. Latest Version of Nextcloud

Alright So let's get started. Put your favorite music on. This is going to take time. For me setting up and testing took months. But it's good as it is working seamlessly and now with this guide in hand it should fairly take less time for you. My original plan is shown below if you do not have more than 1 raspberry pi or any good single board computer you can just stick to a single node which you will also finish setting faster.

In London, I already had a Rock Pi 4b and Rockpro64 and later I bought rock pi x as it is an x86 single-board computer. In India, I have 2 raspberry pi, but I was not able to get the 2nd raspberry pi setup. So in my setup, I set up a cluster in London (Site A). As of now in India (Site B) I have a single node running everything on raspberry pi 3.

---

Prepare your first Node

The first node is where you host your single instance of Nextcloud, make sure you have sudo access. 

sudo apt update

Then proceed upgrading your ubuntu.

sudo apt upgrade -y

Doing the above step is very important.

Install Web Server

I will be using Apache2 as I have some experience with it, I have known people prefer to use Nginx, I wasn't so lucky to get my hands on Nginx, I haven't tried it but feel free to use it. 

sudo apt install apache2 -y

Once the apache web server is installed you can navigate to the IP of your raspberry pi on your computer browser and you will see below. 

If you do not get a page like this above chance is the firewall on ubuntu is blocking port 80. To fix this run the command below

sudo ufw allow 80/tcp 

This will add an exception to the port 80 TCP layer in the ubuntu firewall, Keep in mind for whichever port I mention you must allow is shown above. Only in case of DNS and OpenVPN you will need to allow UDP as well.

Now let's pull nextcloud from nextcloud website 
https://nextcloud.com/install/#instructions-server
Once that download completes, unzip the file with the command:

unzip nextcloud-20.0.1.zip

Move the newly created directory into the Apache document root with the command shown below.

sudo mv nextcloud /var/www/html/

Give the newly-moved directory the proper permissions with the command:

sudo chown -R www-data:www-data /var/www/html/nextcloud

Now Lets configure Apache

Make Apache aware of the Nextcloud instance. Create a new configuration file with the command:​

sudo nano /etc/apache2/sites-available/nextcloud.conf

In that file, paste the following:

Alias /nextcloud "/var/www/html/nextcloud/"
<Directory /var/www/html/nextcloud/>
    Options +FollowSymlinks
    AllowOverride All
      <IfModule mod_dav.c>
        Dav off
      </IfModule>
     
     SetEnv HOME /var/www/html/nextcloud
    SetEnv HTTP_HOME /var/www/html/nextcloud
</Directory>

The lines above are pretty self-explanatory :-)
Note, I setup nextcloud on my.cloud.com/nextcloud and not directly on my.cloud.com. This is as I wanted to put something else on web root. 
Save and close the file. Enable the new site with the command:

sudo a2ensite nextcloud

Next, enable the necessary Apache modules with the command:

sudo a2enmod rewrite headers env dir mime expires

Now here is a trick to speed uploading of images and controlling cache expire using the apache expire module. Update the below code in your .httaccces file under /nextcloud/.htaccess

Code for .htaccess

<IfModule mod_expires.c>
  ExpiresActive On

  # Images
  ExpiresByType image/jpeg "access plus 1 year"
  ExpiresByType image/gif "access plus 1 year"
  ExpiresByType image/png "access plus 1 year"
  ExpiresByType image/webp "access plus 1 year"
  ExpiresByType image/svg+xml "access plus 1 year"
  ExpiresByType image/x-icon "access plus 1 year"

  # Video
  ExpiresByType video/mp4 "access plus 1 year"
  ExpiresByType video/mpeg "access plus 1 year"

  # CSS, JavaScript
  ExpiresByType text/css "access plus 1 month"
  ExpiresByType text/javascript "access plus 1 month"
  ExpiresByType application/javascript "access plus 1 month"

  # Others
  ExpiresByType application/pdf "access plus 1 month"
  ExpiresByType application/x-shockwave-flash "access plus 1 month"
</IfModule>

Installing and configuring php

I will be using php7.4 you can use whichever latest version available at the time of your install. I have tried a bit of extra effort to make the text color to make it a bit fancy.

sudo apt install php7.4-fpm php7.4-common php7.4-mysql php7.4-xml php7.4-xmlrpc php7.4-curl php7.4-gd php7.4-imagick php7.4-cli php7.4-dev php7.4-imap php7.4-mbstring php7.4-soap php7.4-zip php7.4-bcmath  php-apcu php-redis php7.4-dompdf -y

Configure your PHP and modules as recommended by Nextcloud following this link. Set the max file upload memory limit following the direction given here. The php.ini file can be edited for Nextcloud with a single command:

sudo sed -i '/^memory_limit =/s/=.*/= 512M/' /etc/php/7.4/apache2/php.ini

Installing and configure mariadb

sudo apt install mariadb-server -y

ensure its up and running.

> systemctl status mariadb
mariadb.service - MariaDB 10.1.47 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: 
   Active: active (running) since Wed 2020-12-23 15:26:57 GMT; 4h 21min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 874 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START
  Process: 860 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCC
  Process: 552 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR=
  Process: 486 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_
  Process: 440 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run
 Main PID: 706 (mysqld)
   Status: "Taking your SQL requests now..."

You should see the line "Taking your SQL requests now...".
After this run the command below for you ready to use.

sudo mysql_secure_installation

Answer the questions and create a MySQL admin password.
Log in to the MySQL prompt with the command:​

sudo mysql -u root -p

Configure mysql/mariadb for Nextcloud

At the prompt, create the database with the command:

CREATE DATABASE nextcloud;

Now we're going to create a new user and give that user the proper permissions with the following commands:

CREATE USER 'nextcloud'@'localhost' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON nextcloud.* TO 'nextcloud'@'localhost';
ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

Where PASSWORD is a strong, unique password.
Finish up the database with the following commands:

FLUSH PRIVILEGES;
exit

Here is link to nextcloud docs regarding fine tuning your mysql configuration.
Finally, restart Apache with the command: 

sudo systemctl restart apache2

Completing the first node Nextcloud installation

---

Open a browser on a machine connected to your LAN and point it to http://Your_raspberrypi_IP/nextcloud. In the resulting window, fill out the necessary information for both the new admin user and the database.

Since as originally planned to use SSD for storing data, I mounted SSD first in the /etc/fstab file. Be careful and use UUID. I used blkid command to get UUID.​

$ sudo blkid
/dev/nvme0n1p1: LABEL="nvme" UUID="01cc0de2-dec6-4544-b953-c7f51ce01220" TYPE="ext4" PARTUUID="590747a2-01"

and use this UUID in /etc/fstab file.

$ sudo nano /etc/fstab

UUID="01cc0de2-dec6-4544-b953-c7f51ce01220"     /media/storage          ext4    data=journal

Remember to use your UUID you obtain from blkid command in the /etc/fstab file to mount your ssd. I have mounted the drive on the path 

cd /media
sudo mkdir -p storage/data
sudo chown www-data:www-data -R /media/storage/data

Then update this SSD path /media/storage/data in the web browser data folder. In the last line above I set the data folder ownership to www-data.

Finally hit the Finish setup button. The nextcloud installer will create around 150 tables in the nextcloud database. Be patient this step is going to take some time. It might seem like nothing happened but you can enter MySQL prompt and run the show tables in the nextcloud database. Don't worry if you forgot to enter the correct data folder path you can update that later in the /var/www/html/nextcloud/config/config.php file.

Now our first node setup is complete.

If you just want to have a single node setup this is now complete, we only need to make it available over the world wide web if you want to make it accessible over the internet from your home. There are two ways to achieve this, either you run an OpenVPN server at your home network, and when you want to access your nextcloud connect to your VPN server and access either using the mobile app or from the browser. The second way is to port forward port 80 and 443 on your router. Once your SSL(secure socket layer) set up is complete then you can remove port forward 80 as it is not necessary. Then you need a dynamic domain name setup and a client updating it regularly. Most routers have built features to update dynamic IP itself.

Take a note of your raspberry pi's IP using the ifconfig command.
Let's assume your pi is connected to the router using the ethernet and the eth0 interface IP assigned is 192.168.0.2. In your router setting configure your raspberry pi with a static up address so that it never changes to a different IP address.​
Let's first set up OpenVPN, it's super easy.

Method 1. Using Openvpn Server to access over wan.

First, register on a dynamic domain website like noip or dyndns.com, let us assume the domain registered is my.cloud.com then run the below script

curl -L https://install.pivpn.io | bash

Follow the steps, and at one place it will ask about setting the port choose a port slightly different than 1194, let's say 6594, otherwise all hackers no which is the default port or OpenVPN to attack. Then port forward the port 6594.
Secondly, it will say if you are using static IP or not, depending on what you have you can choose but in case you do not have static IP then enter the domain name you registered (in our case its my.cloud.com) ​

Now from your raspberry pi command line run command

pivpn add

And it will generate an ovpn file under /home/$USERNAME/ovpns/.
Copy this file to your phone or another computer where you would need to install the OpenVPN client. Browse this file and enter the username password to connect. Now you can open nextcloud using your raspberry pi as if you are at home. Note: To be able to access nextcloud using the registered domain you need to add it to /var/www/html/nextloud/config/config.php trusted domain array. In our case, we add my.cloud.com.

Method 2: Port forward 443, to access over https://yourdomain.com

Now, if you want to securely access your nextcloud using https://my.cloud.com you need to first get the SSL certificates. Fortunately, there are many free SSL certificate providers and  LetsEncrypt is the trusted one. For a single node follow this link to install Certbot Client using snap and get you the pem files (digital certificates for your website.)

Enable SSL

Alternatively, after step 6 mentioned in the link referred to above. You can generate the SSL certificate by running the command shown below.

Dry Run to check if everything is fine  setup

sudo certbot certonly  --dry-run  --webroot --webroot-path "/usr/share/apache2/default-site/"  --text -v --email your_email@example.com --agree-tos -d my.cloud.com

After you are assured that everything worked well in the previous step, It is time to obtain the certificates.

sudo certbot certonly  --server https://acme-v02.api.letsencrypt.org/directory/  --webroot --webroot-path "/usr/share/apache2/default-site/"  --text -v --email your_email@example.com --agree-tos -d my.cloud.com

The above command will install certificates at location /etc/letsencrypt/live/

# tree  /etc/letsencrypt/live/
/etc/letsencrypt/live/
├──README
└──my.cloud.com
    ├──README
    ├──cert.pem ->../../archive/my.cloud.com/cert1.pem
    ├──chain.pem ->../../archive/my.cloud.com/chain1.pem
    ├──fullchain.pem ->../../archive/my.cloud.com/fullchain1.pem
    └──privkey.pem ->../../archive/my.cloud.com/privkey1.pem

Finally the last step for single node nextcloud instance

I’ll begin by enabling the SSL module for Apache.

sudo a2enmod ssl

Apache sets up self-signed certificates as part of the installation, so for this guide, I'll use those. They can be replaced at any time with functioning 3rd party certificates by editing the vhost file we’ll create next. It’s highly recommended they’re switched sooner rather than later. Finally, edit the nextcloud.conf file which you created earlier in the steps above.​

sudo nano /etc/apache2/sites-available/nextcloud.conf

You can get the full updated nextcloud.conf file with SSL certificates from my GitHub https://github.com/sanfx/multisite-nextcloud/blob/ 
​Save and quit, then enable the new configuration:

sudo a2ensite nextcloud.conf

Now restart Apache:</>

sudo service apache2 restart

in your browser navigate to https://my.cloud.com/nextcloud and voila!

Now that our single-node setup is complete it's time to setup a multisite high available nextcloud clusters.

First step to multisite 

Now we will install required components of multisite stack.

Install DNS service on Site 1 Nodes 1 & 2 : CoreDNS

CoreDNS is a DNS server. It is written in Go. It can be used in a multitude of environments because of its flexibility. CoreDNS is licensed under the APLV2, and completely open source. I choose CoreDNS over Consul (DNS and service discovery tool from Hashicorp) only because CoreDNS took me less time to setup that serves the purpose.

wget https://github.com/coredns/coredns/releases/download/v1.8.0/coredns_1.8.0_linux_arm64.tgz

Extract the coredns file and move it /usr/bin/ location.

tar -xvf coredns_1.8.0_linux_arm64.tgz

sudo mv coredns /usr/bin/
sudo chmod +x /usr/bin/coredns

Copy the coredns.service file from my github to your /etc/systemd/system/
And copy Corefile from my github account to /etc/coredns/. You would have to create coredns folder manually. Take a note of this line in the Corefile 

192.168.0.2 my.cloud.com

This means when you are at your home network connected via wifi or via pc, and try to open https://my.cloud.com it will be redirected by your DNS server to IP 192.168.0.2 instead of your router public WAN IP. So you don't reach your nextcloud via the internet but directly within your local network.

We need to update this DNS Server IP address at two places.

Set CoreDNS IP on your router's LAN > Primary DNS Server IP, the secondary DNS server IP can be 8.8.8.8 or 1.1.1.1 i.e. either google or Cloudflare or any resolver of your choice.

Secondly on all your server node's /etc/resolv.conf set two lines of both the coredns server such as 

nameserver 192.168.0.2
nameserver 192.168.0.3

Install Syncthing

We use syncthing to copy data to other instances of nextcloud and nextcloud app itself. 

wget https://github.com/syncthing/syncthing/releases/download/v1.12.0/syncthing-linux-arm64-v1.12.0.tar.gz

Extract syncthing binary and copy it var/www/htm/syncthing/

tar -xvf /syncthing-linux-arm64-v1.12.0.tar.gz

Change the permission on syncthing binary to executable

sudo chmod +x /var/www/html/syncthing/syncthing

Change the ownership of syncthing excutable to www-data

sudo chown www-data:www-data -R /var/www/html/syncthing/

We change the ownership to www-data because nextcloud web app itself and nextcloud data folder at /media/storage/data/. Now we install the systemd unit to /etc/systemd/system/

Download the syncthing systemd service from my github account.

sudo systemctl enable [email protected]

sudo systemctl start [email protected]

To make the syncthing web interface accesible from other devices on the local netowrk you must update the syncthing config file located at /var/www/html/syncthing/.config/config.xml.

then locate the line that looks like this

<address>127.0.0.1:8384</address>

Change the IP to 0.0.0.0 and restart the service

sudo systemctl restart [email protected]

Now you can access the syncthing web interface from any computeron the local network.
We start the service but we do not setup data to be copied as we do not have other nodes up and running.

Install Redis

Redis is Remote Dictionary Service, we will use the Redis cluster for file locking and storing PHP sessions. We will come to each other but let's first install the Redis. Installing Redis takes time. I believe you might be tired by now, not just by doing even if you were just reading through this page. 

mkdir ~/tmp
cd ~/tmp
wget http://download.redis.io/redis-stable.tar.gz
tar -xvf redis-stable.tar.gz

this enter compressed tar file is big and takes lot of time .

cd redis-stable
make

After the binaries have finished compiling, run the test suite to make sure everything was built correctly:

make test

This typically takes a few minutes to finish. Once the test completes, install the binaries onto the system by typing:

sudo make install

That’s it for installing Redis, and now you’re ready to start configuring it. To this end, you’ll need to create a configuration directory. The Redis configuration directory is conventionally located within the /etc/ directory, and you can create it there by typing:

sudo mkdir -p /media/storage/var/opt/redis-stable/

I installed redis-stable on my mounted SSD, as read write from ssd is faster compared to memory card.

Next, copy over the sample Redis configuration file that came included with the Redis source archive:

sudo cp ~/tmp/redis-stable/redis.conf /etc/redis

Redis configuration for Sentinel setup 

Redis sentinel is the high availability solution offered by Redis. In case of a failure in your Redis cluster, Sentinel will automatically detects the point of failure and bring the cluster back to stable mode without any human intervention.

What really happens inside Redis Sentinel? Sentinel always checks the MASTER and SLAVE instances in the Redis cluster, checking whether they working as expected. If sentinel detects a failure in the MASTER node in a given cluster, Sentinel will start a failover process. As a result, Sentinel will pick a SLAVE instance and promote it to MASTER. Ultimately, the other remaining SLAVE instances will be automatically reconfigured to use the new MASTER instance.

Sentinel acts as a configuration provider or a source of authority for clients service discovery.

What does that means ? Simply, application clients connect to the Sentinels and Sentinels provide the latest Redis MASTER address to them.
Furthermore, Sentinel is a robust distributed system, where multiple sentinels need to agree to about the fact a given master is no longer available. Then only the failover process starts a select a new MASTER node. This sentinel agreement is done according to the quorum value.

What is Quorum ?

I want to talk about quorum as will also deal with this quorom while setting Maridb Galera Cluster. The quorum value is the number of Sentinels that need to agree about the fact the master is not reachable. However the quorum is only used to detect the failure. In order to actually perform a failover, one of the Sentinels need to be elected leader for the failover and be authorized to proceed. This only happens with the vote of the majority of the Sentinel processes.
Let’s get our hands dirty with Redis Sentinel.

We’ll stick to the basic setup with 3 server instances.

---

(Go to src folder.)
cd src/ && redis-server ../redis.conf &
cd src/ && redis-server ../sentinel.conf — sentinel &
(Or else you can simply use cd utils && ./install_server.sh.)

you will find redis.conf and sentinel.conf, update these configurations with the settings I have on github page
You can either choose to set up supervised by systemd in the config or create a systemd unit under /etc/systemd/system/

node-1$ sudo netstat -ltnp | grep 6380
tcp        0      0 0.0.0.0:6380            0.0.0.0:*               LISTEN      15240/redis-server  
tcp        0      0 0.0.0.0:26380           0.0.0.0:*               LISTEN      451/redis-server *: 

if you get error netstat command not found. then you can run the command below to install them.

sudo apt install net-tools

Setting up Mariadb Galera Cluster

Setting up the MariaDB Galera cluster may sound easy in the beginning, I did intensive testing over weeks and found we can easily have a split-brain situation and a hard time fixing it. 
Initially, I set it up using Rsync Snapshot State Transfer but I quickly learned that using the rsync method halts the frontend clients while the wsrep state transfer is in progress. I re-setup the Galera cluster using Xtrabackup-v2 as the SST method.
On an important Note: Set the data directory to the path on your SSD to gain performance. otherwise nextcloud will be very slow.
Follow the link to my GitHub account to copy the galera configuration on each of these nodes to /etc/mysql/conf.d/galera.conf later (Do not start Galera cluster as of yet, put galera.conf on desktop temporarily). After you confirm that the Nextcloud instance has been copied to both the nodes. Do not forget to update the trusted domain array in config.php.
I have saved the full nextcloud config on my Github account which you can use as a reference.

---

Assuming you have more than one pi's or other good single board computers let's put this entire ubuntu image to a file that you can then flash to sd card or an SSD connected via USB. (both raspberry pi and rock pi supports USB boot)​

Now that you have everything ready up and running for the first node of the cluster, stop apache2, CoreDNS, Redis, Syncthing, and MariaDB or if anything else is running and create an image of this running instance using the dd command.

sudo dd if=/dev/mmcblk0 of=~/sd-card-copy.img bs=4M

Copy this image and burn it onto the sd card of the second raspberry pi and repeat the same for the Node. 3. or You can take out Node 1 sd card and use Balena Ethcher to burn an image to sd card or an SSD.
Do not forget to set slaveof in redis.conf node 2 and node 3 and server id in mariadb configuration.

Once your Node 2 and Node 3 are up and running. create 3 more sd card or SSD with an image of ubuntu. Update redis.conf, sentinel.conf, galera.conf, and Corefile. with local IP of Site B Node 1.

Secondly use the pivpn command to create 5 client config for all the nodes on both Site A and Site B (excluding the OpenVPN server). And since OpenVPN is already installed from the first image you just have to copy this client config (rename *.ovpn to client.conf) files to /etc/openvpn/ directory and start the VPN.

sudo apt start openvpn@client

Setup the syncs between nodes and both Sites

Setting up sync using syncthing is the easiest part.
Take a look at this link to an intro and how to setup syncs

Install and Configure ProxySQL Load Balancer for Mariadb

In the beginning, I was thinking to go with MariaDB Maxscale, but at the time I was setting this up, I did not own an x86 board and Maxscale isn't available for arm64 architecture so I choose to go with ProxySQL.
PS: I highly recommend don't waste your time and efforts on using clustercontrol it only looks fancy but later you would have to bear the pain. I would say it is only meant for large infrastructures. I quickly got rid of it.

ProxySQL is an open-source MySQL proxy server, meaning it serves as an intermediary between a MySQL server and the applications that access its databases. ProxySQL can improve performance by distributing traffic among a pool of multiple database servers and also improve availability by automatically failing over to a standby if one or more of the database servers fail.

cd ~/tmp
curl -OL https://github.com/sysown/proxysql/releases/download/v1.4.4/proxysql_1.4.4-ubuntu16_amd64.deb

Install the package with dpkg, which is used to manage .deb software packages. The -i flag indicates that we’d like to install from the specified file.

sudo dpkg -i proxysql_*

Start ProxySQL Load Balancer

sudo systemctl start proxysql

Ensure ProxySQL service is running

● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (running) since Thu 2017-12-21 19:19:20 UTC; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 12350 ExecStart=/etc/init.d/proxysql start (code=exited, status=0/SUCCESS)
    Tasks: 23
   Memory: 30.9M
      CPU: 86ms
   CGroup: /system.slice/proxysql.service
           ├─12355 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
           └─12356 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

After this initialization, ProxySQL stores its configuration in a database that you can manage and modify via the command line.
To set the administrator password in ProxySQL, we’ll connect to that configuration database and update the appropriate variables.
First, access the administration interface. You’ll be prompted for a password which, on a default installation, is admin.

mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

ProxySQL uses port 6032 , make sure you add port 6032 to allow list of UFW on each of the node on both the sites.

Change the administrative account password by updating (UPDATE) the admin-admin_credentials configuration variable in the global_variables database. Remember to change the password in the command below to a strong password of your choice.​

UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';
Query OK, 1 row affected (0.00 sec)

This change won’t take immediate effect because of how ProxySQL’s configuration system works. It consists of three separate layers:

• memory, which is altered when making modifications from the command-line interface.
• runtime, which is used by ProxySQL as the effective configuration.
• disk, which is used to make a configuration persist across restarts.

Right now, the change you made is in memory. To put the change into effect, you have to copy the memory settings to the runtime realm, then save them to disk to make them persist.

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Follow this extensive guide on setting up ProxySQL on digitalocean . For what this guide on proxysql is done for 3 nodes you need to do the same for both Sites A and Site B.  So in the end we will have 2 ProxySQL instances running on each Site. I followed ProxySQL docs which is also very detailed.

​Connecting 2 sites database  server galera clusters

Connecting 2 sites database sync so a user on both sites gets to see the same content in nextcloud we are syncing with syncthing.
We achieve this by doing my setting up Master to Master Replication between two nodes of MariaDB servers of each site.
Instead of specifying another site MariaDB IP address, you specify the IP address and port of the server running ProxySQL. (Normally in master to master replication we connect two instances by specifying their node IPs but here we specify ProxySQL) ProxySQL further takes the request to the MariaDB Galera cluster which replicates the information to each Galera node.

---

Repeat the steps below on any one galera node on both sites.

CREATE USER 'replicator' IDENTIFIED BY 'replicate123';

Then on the node 1 of Site A

GRANT ALL PRIVILEGES ON *.* TO ‘replicator’@‘10.8.0.3’ IDENTIFIED BY ‘replicate123’ WITH GRANT OPTION;

10.8.0.3 is the IP address of the node running instance of ProxySQL. assigned by the OpenVPN tun0 virtual interface. The eth0 interface has IP 192.168.0.3
Then on node 1 of Site B in the MySQL client run:

GRANT ALL PRIVILEGES ON *.* TO ‘replicator’@‘10.8.0.5’ IDENTIFIED BY ‘replicate123’ WITH GRANT OPTION;

On the Site-B ProxySQL is running on the node with IP 10.8.0.5 assigned by OpenVPN virtual interface. In my case, the eth0 interface has IP 192.168.1.3

Then take a note of master status on both sites nodes.

show master status;

On the Site A, node 1 I got the following result.

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 747436

And on the Site B, node 1, I got the below result.

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mariadb-bin.000005
        Position: 3201952

Then on both these nodes of Site A and Site B stop the slave.

MariaDB [(none)]> stop slave;

After you stop the slave, run the below commands in MySQL prompt of Node 1 on Site A copying Site B's File name and position as shown below.

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = ‘10.8.0.5’, ‘MASTER_USER’=‘replicator’, ‘MASTER_PASSWORD’=‘replicate123’, MASTER_LOG_FILE=‘mariadb-bin.000005’, MASTER_LOG_POS= 3201952;

Take a note the IP I have specified above is that of ProxySQL node I gave on Site B. Now similarly run the same command for Node on Site B.

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = ‘10.8.0.3’, ‘MASTER_USER’=‘replicator’, ‘MASTER_PASSWORD’=‘replicate123’, MASTER_LOG_FILE=‘mysql-bin.000003’, MASTER_LOG_POS=747436;

And in the later case, I pointed node 1 of Site B to a node of Site A running ProxySQL. It's time to start the slave on both sites.

MariaDB [(none)]> start slave;

To confirm everything went fine.  check slave status.

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.8.0.5
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1175781
               Relay_Log_File: mysqld-relay-bin.000010
                Relay_Log_Pos: 922870
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0

If you see this, It means connecting both the sites galera cluster via ProxySQL was successful. 

Setup HAProxy to load balance web servers

Lets first install HAProxy in node 2 that has IP 192.168.0.3

sudo apt install haproxy -y

# And ensure the haproxy daemon service is running by running the systemd command.

sudo systemctl status  haproxy

We install 2 instances of HAProxy, One on Site-A and the other on Site-B. HAProxy of Site A will be load balancing web server and Redis on SIte A and interfacing with the dynamic name IP provider, whichever you choose, I choose Noip. An instance of HAProxy on Site B will just be providing load balancing of web servers and Redis to the clients on Site B.

Although load balancing Galera cluster too can be done through HAProxy, ProxySQL gave me fine control. With HAProxy we will load balance Apache2 and Redis Sentinel. Configuring HAProxy is quite easy.

Lets configure HAProxy for Apache2

Recall above we used Letsencrypt SSL for our website to open https://my.cloud.com. we now need to move those certificates from nextcloud.conf to haproxy.cfg except we do not do exactly the way it is done. HAProxy takes the certificate in a single file so we combine fullchain.pem and privkey.pem into one file and save it to /etc/haproxy/certs/ location

DOMAIN='my.cloud.com' sudo -E bash -c 'cat /etc/letsencrypt/live/$DOMAIN/fullchain.pem /etc/letsencrypt/live/$DOMAIN/privkey.pem > /etc/haproxy/certs/$DOMAIN.pem'

Now it's time updateHAProxy configuration file. Take a look at the HAProxy config file on my GitHub account. Repeat the same steps for the HAProxy config file on Site B even if it doesn't interface Noip as of yet.

Add redis server IP to HAProxy

Take a look at the updated config HAProxy on my GitHub account. once done reload haproxy.

sudo systemctl reload haproxy

If everything went fine, you should be able to access nextcloud via haproxy port 80. You can check the status of haproxy_node_IP:9090

Setup PHP Session sharing

Even though accessing nextcloud via Haproxy is working fine but login sessions are not being maintained to do that we need to update the php.ini file on each of the nodes on both the Sites. And edit this file shown below.

sudo vi /etc/php/7.4/apache2/php.ini
# or
sudo vi /etc/php/7.4/fpm/php.ini

And update these 2 lines .

session.save_handler = redis
session.save_path = "tcp://192.168.1.3:3679?auth=yourverycomplexpasswordhere"

Since we are running Redis Sentinel Setup behind HAProxy we set the save_path to redis master exposed by load balancer. 

Finally, everything should be up and working, Only the Galera part and connecting site is complicated everything else is simple enough. if you are attempting this I recommend you split your tasks into days. I have shared an install script that prepares the nodes for installation excluding Redis and ProxySQL. I have also included nextcloud/config/config.php file in my GitHub account.

With this our multisite nextcloud cloud storage setup is complete. I hope you take advantage you learned while attempting to implement it. Thank you for making it this far with me. I wish you a Merry Christmas & Happy Holidays.

Click image to join the signal group to discuss or ask questions

Scan QR Code to join the Signal group.