How to set up High Availability WordPress with HyperDB

Posted on October 19th, 2019

WordPress is the most popular content management system available in the market. From top 1 million websites in this world, more than 25% use WordPress to manage the content. It means, WordPress is a scalable content management system. When you are getting more than 100k visitors in a month, you need High availability WordPress instance. There are many ways to improve the availability of your WordPress site.

In this guide, I will show you How you can set up WordPress to use mysql replication using HyperDB. First of all, What is HyperDB? and what is MySQL replication? Let me explain you about both these things in short.

What is MySQL Replication?

In simple words, replication here means replication of database or databases from one machine to another. In this case, you can set up a master-slave replication in MySQL to copy all the data from master to slave instantly. It means that all your data is available on two servers instead of just one.

Now, as we have our data on two servers, we can divide the load between two servers. In case of master-slave replication, you can only insert, update or delete data in master server, slave server will simply copy the data from master server. It means that we can use slave server to run READ queries (Whenever a user requests some page) and we can use master server to run write queries (Whenever you publish a content or update something).

It means that now the load is on two servers instead of one. For example, If your master server crashes, you won’t be able to update anything from the admin panel but your site will still be live because it is reading data from Slave server.

What is HyperDB?

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

Using HyperDB, we can enable WordPress to use different servers for different kind of queries. For example, we can enable WordPress to use Master server to run insert, update and delete queries while Slave server to use read queries whenever a user requests the data.

It means that depending on the type of query, WordPress will use the servers we mention in the HyperDB drop-in plugin. It is very easy to set up. You can set it up in 10 minutes once you get used to it. Finally, Let’s start with the guide.

Prerequisites

In order to follow this guide, You must have a WordPress site configured on your server. If you haven’t configured your server yet, I recommend you to follow our tutorial to set up multiple wordpress sites on single host. Even if you are just going to host a single site right now, It would be beneficial for you to set up your server this way.

And, you must have MySQL replication in place. If you haven’t performed this task, follow our guide to set up master-slave replication or master-master replication in MySQL.

You can either set up WordPress first or set up replication first. The order does not matter in this case. Our guides are created with this concern in mind.

Once you are done setting up your WordPress site and replication, you can move forward.

High Availability WordPress using HyperDB

I have divided actual HyperDB configuration in three steps. In the first step, 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 at the home directory of the current user. If you want to go to home directory, simply execute the cd command in 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 have 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. Just like wp-config.php file, db-config.php file will contain all the information about all the database servers connected 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 a file, execute the following command to open a 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 have to update the second occurrence. The default configuration will look like the following.

/**
 * This adds 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 clarity.
 */
$wpdb->add_database(array(
        'host'     => DB_HOST,     // If port is other than 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 have to update our wp-config.php file.

We have to add the slave host, which can be the private IP if your servers are in private network or Public IP if your servers are not in 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 last step, we just have to move the db.php file from hyperdb directory to our WordPress installation’s wp-content directory. 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 the WordPress site grows, we have to make sure that our WordPress site is available 24*7 without downtimes. Even 60 minutes of downtime can cause a huge damage to our site’s reputation. The first step I recommend you to take to scale your WordPress site is to host the database on different server. Then, you can set up HyperDB to further improve the availability, performance and monitoring.

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

6 Responses to “How to set up High Availability WordPress with HyperDB”

  1. Nicole-Ann Menezes says:

    thanks for this article, this is the only article i found that explains how to actually connects a HA MQ server to wordpress

    • Adarsh Sojitra says:

      Thanks for your positive reply Nicole! We are happy to help you!

      • Thebe says:

        This is the best tutorial ever. Thank you for this.

        Is master-master setup limited to 2 servers?

        If not then can I have as many masters as I can afford? If so do I just follow the same tutorial to add more masters?

        I also saw other setups for master-slave, so I would like to know I can therefore couple each master with a slave or slaves

        If all of the above is possible, will data be the same at all times on all the masters and slaves?

  2. Ankur says:

    hello
    Thanks for your code.
    I follow your step its working for not show any type of error.
    Is their any why check the both host name are connected properly in wordpress.
    thanks in advance

  3. SbAdmin says:

    Hi,
    I have tried it a lot of times but always the same after setting up i get these errors :

    Notice: Undefined variable: write in /wp-content/db.php on line 559

    Notice: Undefined variable: write in /wp-content/db.php on line 596

    Notice: compact(): Undefined variable: read in /wp-content/db.php on line 685

    Notice: compact(): Undefined variable: write in /wp-content/db.php on line 685

    Maybe you can help me out with a solution.

  4. HnD says:

    Thankyou for sharing, keep posting very good content shared

Leave a Reply