WordPress HyperDB Cluster with MySQL Replication

In this article, we’re going to look at WordPress and how to provide a basic WordPress high-availability cluster. WordPress HyperDB High Availability can be complicated. Unless you have a cluster of servers there is no way to provide High Availability. We’re assuming you are using Master-Slave MySQL Replication as there would be no need to use HyperDB in a Master-Master MySQL situation. Before you get going you should already have the following.

1. Minimum 3 NVMe VPS servers in any location running Ubuntu 20 / 22 LTS
2. Each is installed with NGINX, and PHP 7.4
3. You have already set up Mysql Master-Slave Replication. One of your instances is therefore a master.
4. Your Syncing data between your WordPress cluster using LSYNCD
5. Your WordPress site resolves from each instance if you update the A record. You may have a load balancer in place like HAProxy.

Need help with WordPress high availability? Contact us. You can install Ubuntu from your Discovery Control Panel.

WordPress High Availability Cluster

So, let’s look at the topology for this kind of setup.

This type of setup is a line configuration or bus. The Master sits at the end distributing changes in the database and/or files to the slaves. The replication is one-way. This means if the master is ever offline, you won’t be able to make any changes to your site but it will still resolve. A fully connected set-up would use Master-Master Replication or a MariaDB Galera Database Cluster. However, this does have its drawbacks. Your MySQL database must meet the requirements for Group Replication. Many plugins do not. In this type of setup, we need to make sure any changes to the database are always made on the master. We would see inconsistent data without this. That’s where HyperDB comes in.

However, in the MariaDB Replication article, we use NAS storage with VPS Servers to create a high-availability network for a WordPress site. That is the preferable way to achieve high availability for a website because it uses shared storage and a load balancer distributes the traffic over many servers.

What is HyperDB

To provide WordPress High Availability you can use HyperDB. It’s a very simple program that directs write connections to the master. It then directs read connections to the master/slaves. This ensures when your connection hits a slave, any changes you make are sent to the master so they can be replicated backend. Also, if a connection fails, WordPress HyperDB will route it to another host. So, let’s get started with installing HyperDB on our WordPress Virtual Cluster.

Install HyperDB

To install HyperDB. Just execute the following commands. We will then configure HyerDB for our Cluster. We’re downloading these to our Master and Slaves. We’re doing the Master first.

apt install unzip -y
cd /home
wget https://downloads.wordpress.org/plugin/hyperdb.zip
unzip hyperdb.zip

Now you have the files on your cluster. Let’s configure them. For the purpose of this, our cluster is made up of P1, P2 and P3. P2 is our Master which is replicating database changes to our slaves.

Configure WordPress HyperDB

Take a copy of the original db-config.php file. It contains useful information but it can become messy. Next, open up the file. You are looking for the following block.

cp /home/hyperdb/db-config.php /home
nano /home/hyperdb/db-config.php

$wpdb->add_database( array(
        'host'     => DB_HOST,
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 1, // primary server takes write queries
        'read'     => 1, // ... and read queries
) );

This represents the master details. It’s taken from the wp-config.php file. There is no need to modify this on your master. Underneath this, you will notice another block. This represents your slave. There is only one block but we will add another to ensure WordPress high availability virtual cluster. Modify like so;

/**
 * Slave FR

*/
$wpdb->add_database(array(
        'host'     => 'IP',     // If port is other than 3306, use host:port.
        'user'     => 'USER',
        'password' => 'PASSWORD',
        'name'     => 'DATABASE',
        'write'    => '0',
        'read'     => '1',
        'dataset'  => 'global',
        'timeout'  => '0.2',
));


/**
 * Slave DE
*/
$wpdb->add_database(array(
        'host'     => 'IP',     // If port is other than 3306, use host:port.
        'user'     => 'USER',
        'password' => 'PASSWORD',
        'name'     => 'DATABASE',
        'write'    => '0',
        'read'     => '1',
        'dataset'  => 'global',
        'timeout'  => '0.2',
));

WordPress HyperDB Configuration Continued

In the above example. We have added two slaves. For true WordPress High Availability, you need three servers. A master and two slaves. They are read-only. Our master is already set to read and write. Save the file but this time, save it in the main WordPress directory. The same directory that has wp-config.php. So next, to activate the configuration we just move the db.php file to the /wp-content directory.

 mv /home/hyperdb/db.php /home/directory/wp-content/

If you refresh your WordPress site, you shouldn’t see ANY errors. If you do. There is a misconfiguration in the db-config.php file.

Slave Configuration

Now you need to add the same files to your slaves. For true WordPress High Availability, you need a 3 node cluster. You should already have SSH Keys set up so you can access each slave without a password. Copy the db-config.php file to each slave’s WordPress directory. The one with wp-config.php in.

scp /home/site/db-config.php root@slaveip:/home/site/

In the slave. Open up the file again in Nano. But this time you need to change the top section to not allow writes because this is a slave.

$wpdb->add_database( array(
        'host'     => DB_HOST,
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 0, // primary server takes write queries
        'read'     => 1, // ... and read queries
) );

In the slave section, find the IP of this slave and change it to your master’s IP. You also need to allow writing.

$wpdb->add_database(array(
        'host'     => 'MASTER IP',     // If port is other than 3306, use host:port.
        'user'     => 'USER',
        'password' => 'PASSWORD',
        'name'     => 'DATABASE',
        'write'    => '1',
        'read'     => '1',
        'dataset'  => 'global',
        'timeout'  => '0.2',
));

Save and close the file. Do the same on any other slaves you have. Remove the slave IP and switch it for the Master IP. Remember to enable write access.

Activate High Availability For WordPress

Finally, to activate the configuration we copy the db.php file from our master server to all slaves. The file is being copied to the wp-content folder. The same as our master.

scp /home/site/wp-content/db.php root@slaveip:/home/site/wp-content/

Assuming there are no problems. You should not see any errors. To test the configuration, disable MySQL on the server that your website’s A record is pointing to. The connection will be rerouted to one of the other servers.

We’re using a load balancer to distribute the traffic over our cluster. The main reason to use HyperDB, in this case, is to ensure all write commands go to the master. You now have a High Availability WordPress Cluster. F2H.Cloud can easily create this type of setup spread over multiple locations. Get in touch.