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.