How to set up High Availability WordPress with HyperDB

WordPress is the most popular content management system available on the market. Out of the top 1 million websites globally, over 25% utilize WordPress to manage their content. This signifies that WordPress is a scalable content management system. When you start receiving more than 100,000 visitors per month, you will require a High Availability WordPress instance. There are numerous techniques to enhance the availability of your WordPress site.

In this guide, I will demonstrate how to set up WordPress to utilize MySQL replication using HyperDB. First of all, What is HyperDB? and what is MySQL replication? Let me provide a brief explanation of both these aspects.

What is MySQL Replication?

In simple terms, replication in this context means duplicating a database or databases from one machine to another. In this case, you can configure a master-slave replication in MySQL to copy all the data from the master to the slave in real-time. This means that your data will be available on two servers instead of just one.

Now, with our data on two servers, we can distribute the load between the two. In the case of master-slave replication, you can only perform insert, update, or delete operations on the master server, and the slave server will simply replicate the data from the master server. This means that we can use the slave server to process READ queries (when a user requests a page), and we can use the master server to process write queries (when you publish or update content).

This means that the load is now distributed across two servers instead of one. For example, if your master server crashes, you will not be able to make any updates from the admin panel, but your site will still be accessible because it will be reading data from the slave server.

What is HyperDB?

HyperDB is a drop-in WordPress plugin. This means that you do not have to install HyperDB by adding a new plugin. It is simply a PHP file that you need to upload to your WordPress installation directory. I will show you exactly how to perform this task.

Using HyperDB, we can enable WordPress to utilize different servers for different types of queries. For instance, we can configure WordPress to use the Master server to process insert, update, and delete queries, while the Slave server is used to process read queries whenever a user requests data.

This means that depending on the type of query, WordPress will utilize the servers specified in the HyperDB drop-in plugin. It is very easy to set up and can be done in 10 minutes once you are familiar with the process. Finally, let’s begin with the guide.

Prerequisites

In order to follow this guide, you must have a WordPress site configured on your server. If you have not set up your server yet, I recommend following our tutorial on setting up multiple WordPress sites on a single host. Even if you are only planning to host a single site currently, it may be beneficial for you to set up your server in this manner.

Also, you must have MySQL replication in place. If you have not completed this task, please follow our guide on setting up master-slave or master-master replication in MySQL.

You can set up WordPress first or set up replication first. The order does not matter in this case. Our guides have been created with this consideration in mind.

Once you have completed setting up your WordPress site and replication, you can proceed.

High Availability WordPress using HyperDB

I have divided the actual HyperDB configuration into three steps. In step one, we will download and extract the HyperDB plugin from WordPress. So, Let’s do it.

Download HyperDB

To download HyperDB, execute the following commands on your WordPress server. Make sure you are in the home directory of the current user. If you want to go to the home directory, simply execute the cd command on your terminal. Once you are in the home directory, execute the following commands.

$ sudo apt-get install unzip -y
$ wget https://downloads.wordpress.org/plugin/hyperdb.zip
$ unzip hyperdb.zip

Once the download is complete, we need to move the files available inside the hyperdb directory to our WordPress installation directory.

Move and Update db-config.php

Now, let’s move and update our db-config.php file to our WordPress installation directory. Similar to the wp-config.php file, the db-config.php file will contain all the information about the database servers associated with the WordPress site.

Execute the following command to move the db-config.php file to the WordPress installation directory.

$ sudo mv ~/hyperdb/db-config.php /var/www/example-site.com/db-config.php

After moving the file, execute the following command to open the file in edit mode.

$ sudo nano /var/www/example-site.com/db-config.php

Now, find DB_HOST and ignore the first occurrence. We just need to update the second occurrence. The default configuration will look like the following.

/**
 * This provides the same server again, only this time it is configured as a slave.
 * The last three parameters are set to the defaults but are shown for readability.
 */
$wpdb->add_database(array(
        'host'     => DB_HOST,     // If port is aside from 3306, use host:port.
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 0,
        'read'     => 1,
        'dataset'  => 'global',
        'timeout'  => 0.2,
));

Update the host directive from DB_HOST to SLAVE_HOST. Once done, press CTRL+X followed by Y followed by Enter to save the file. Now, we also need to update our wp-config.php file.

We need to add the slave host, which will be the private IP if your servers are in a private network or Public IP if your servers are not in a private network. Execute the following command to append the wp-config.php with the definition of SLAVE_HOST.

$ echo "define('SLAVE_HOST','IP_ADDRESS_OF_SLAVE');" | sudo tee -a /var/www/example-site.com/wp-config.php

Replace the colored part in the code. It is a one-line command to add one line at the end of the wp-config.php file. You can also open the file in edit mode and add the line manually, it does not make any difference.

Move db.php to wp-content

In the final step, we just need to move the db.php file from the hyperdb directory to the wp-content directory of our WordPress installation. Execute the following command to move the file.

$ sudo mv ~/hyperdb/db.php /var/www/example-site.com/wp-content/

And that’s all! Now, our WordPress installation is High Availability as it will read data from our slave server and write data to the master server.

Conclusion: As the traffic on your WordPress site grows, you need to ensure that your site is available 24/7 without any downtime. Even 60 minutes of downtime can cause significant damage to your site’s reputation. The first step I recommend taking to scale your WordPress site is to host the database on a different server. Then, you can configure HyperDB to further enhance availability, performance, and monitoring.

The most challenging and time-consuming aspect of this process is setting up MySQL replication. HyperDB configuration for High Availability WordPress is very easy and fast. So, once you are familiar with it, you will be able to configure HyperDB in minutes. If you have any questions or queries, please use the comment section below. If you are using WebHostingPeople VPS, you can also contact our Support department for assistance.