Tutorial

How To Optimize WordPress Performance With MySQL Replication On Ubuntu 14.04

Published on May 21, 2014

How To Optimize WordPress Performance With MySQL Replication On Ubuntu 14.04

Introduction

In this tutorial, we will teach you how to scale up your WordPress MySQL database server setup using master-slave database replication and the HyperDB plugin for WordPress. Adding more database servers to your environment in this manner allows your WordPress application to read from multiple database servers, increasing read performance.

MySQL replication reaps the most performance benefits for a system that processes frequent reads and infrequent writes, like most WordPress installations. By using a single-master with multiple-slave setup, you can add more slaves to scale your system, until you run out of network bandwidth or your master cannot handle the update load. If you wish, you can add more than one slaves by repeating the “slave” portions of the replication sections of this tutorial.

We are assuming that your setup includes two load balanced WordPress application servers that connect to a separate MySQL database server (see the prerequisites for a tutorial on how to set that up). It is not strictly necessary to have load balanced application servers to follow this tutorial, but your MySQL database server should be separate from your application servers.

Prerequisites

Before continuing with this tutorial, you should have completed two tutorials or have a similar environment:

After following those tutorials, to set up WordPress with two load balanced web application servers and a separate database server, you should have four VPSs. Because we will be dealing with several VPSs, for reference purposes, we will call your four existing VPSs the following:

haproxy-www: Your HAProxy server for layer 4 load balancing your WordPress web application servers. This is the entry point into your website
wordpress-1: Your first WordPress web application server
wordpress-2: Your second WordPress web application server
mysql-1: Your MySQL server for WordPress

That is, your environment should look something like this:

WordPress and Separate MySQL Database Server

In addition to your current environment, we will require one additional VPS during this tutorial. We will call it:

mysql-2: Your slave MySQL database server

Our Goal

When we are finished with this tutorial, you will have two database servers will be replicating in a master-slave configuration. Your WordPress servers will selectively write to your master and read from both your master and slave databases, by use of the HyperDB WordPress plugin. Your final environment should look something like this:

Master-Slave Database Replication

Keep in mind that you do not need to have load balanced application servers (wordpress-1/wordpress-2) to follow this tutorial, and that you can add more slave databases if you want.

Set Up MySQL Master-Slave Replication

Before we can configure our WordPress application to read from multiple database servers, we need to set up our MySQL replication.

Create MySQL Slave VPS, mysql-2

You will want to create a new VPS that will act as the MySQL slave server–for reference purposes, we will call this server mysql-2. The slave will be configured to replicate all of the databases of your master MySQL server, including your WordPress database.

On mysql-2, install the MySQL software:

sudo apt-get update
sudo apt-get install mysql-server

Enter a root password for your MySQL installation. Next we will configure on our MySQL master server.

Configure Existing MySQL Server as a Master

The first step is to set up your existing MySQL database server, mysql-1, with a replication master configuration.

On mysql-1, edit the MySQL configuration file:

sudo vi /etc/mysql/my.cnf

Search for the following three lines:

<pre> bind-address = mysql_1_private_IP #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log </pre>

bind-address: the IP address that MySQL will listen on. This should already be set to mysql-1’s private IP address from your original setup
server-id: the unique server ID. Since this is the master server, we will want to leave the value as “1” and uncomment this line
log_bin: the location of the binary log file. The binary log is used to send data changes from the master to its slave for replication. Uncomment this line

The three lines should look like this (be sure to substitute the highlighted with database server’s private IP address):

<pre> bind-address = mysql_1_private_IP server-id = 1 log_bin = /var/log/mysql/mysql-bin.log </pre>

Optionally, if you want to restrict the replication to the wordpress database, specifically, add the following line to your configuration (substituting the highlighted with your desired database name):

<pre> binlog_do_db = wordpress </pre>

Save and quit. To put these changes into effect, restart mysql with the following command:

sudo service mysql restart

Connect to to the MySQL console with the following command, then enter the password when prompted:

mysql -u root -p

Create a user that will be used by our slave MySQL servers for replication purposes. We will call this user repl. Be sure to replace the repl_password with your own, strong password. The % specifies that the source IP for this user can be anything, but you may substitute the % with the private IP address of your slave MySQL server, mysql-2, to restrict connections with this user to that particular server:

<pre> CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘repl_password’; GRANT REPLICATION SLAVE ON . TO ‘repl’@‘%’; </pre>

Do not exit the MySQL console yet!

Export a Backup Of MySQL Master

Next, we will want to export a backup of the MySQL master database, to import into our slave database so it will be identical before we start replication. We need to lock the database so we can do a data dump. In your MySQL console on mysql-1, run this:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
EXIT

Now, from your command shell, run the following command to export a backup of the databases on your master MySQL server to a file called masterdump.sql:

mysqldump --lock-all-tables -u root -p --all-databases > masterdump.sql

Copy your masterdump.sql file to your slave server, mysql-2, using scp:

<pre> scp masterdump.sql user@mysql_2_private_IP:/tmp </pre>

Enter mysql-1’s MySQL console once again:

mysql -u root -p

At the MySQL prompt, unlock your database:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

Now run this statement to print out information that you will need to configure your MySQL slave:

Take a note of the values of File and Position, as you will need them when configuring your slave server. Let’s switch over to mysql-2 now.

Configure MySQL Slave

Now we will want to import the master database into our slave to synchronize them in preparation for replication.

On mysql-2, run this command to import the masterdump.sql file:

mysql -u root -p < /tmp/masterdump.sql

Next, we will set up mysql-2 as a replication slave. On mysql-2, edit the MySQL configuration file:

sudo vi /etc/mysql/my.cnf

Search for the following two lines:

<pre> bind-address = 127.0.0.1 #server-id = 1 </pre>

bind-address: the IP address that MySQL will listen on. Set to mysql-2’s private IP address
server-id: the unique server ID. Since this is the master server, change this value to 2 and uncomment this line

The two lines should look like this (be sure to substitute the highlighted with database server’s private IP address):

<pre> bind-address = mysql_2_private_IP server-id = 2 </pre>

Save and quit. Restart MySQL to put the changes into effect:

sudo service mysql restart

Enter the MySQL console:

mysql -u root -p

Next, we will connect the slave to the master. The five following values are required:

MASTER_HOST: set to mysql-1’s private IP
MASTER_USER: set to the replication user that we created on the master, repl
MASTER_PASSWORD: set to repl’s password, which should be substituted with your own password
MASTER_LOG_FILE: set to the “File” listed when you ran SHOW MASTER STATUS; on your master MySQL server
MASTER_LOG_POS: set to the “Position” listed when you ran SHOW MASTER STATUS; on your master MySQL server

The following statement connects your slave to your master server, and it requires that you substitute all of the highlighted fields with the appropriate values:

<pre> CHANGE MASTER TO MASTER_HOST=‘mysql_1_private_IP’, MASTER_USER=‘repl’, MASTER_PASSWORD=‘repl_password’, MASTER_LOG_FILE=‘mysql-bin.000001’, MASTER_LOG_POS=408; </pre>

If that statement ran properly, run this command to initiate the slave connection:

START SLAVE;

Your mysql-2 server should be connected as a slave now! Run the following command to check that the replication is working:

SHOW SLAVE STATUS\G

Revoke Write Privileges From Slave Users

This is optional because the HyperDB plugin can be configured to only read from your slave database server, but you may want revoke the write privileges from your wordpressuser database users on your slave database (because updates to your slave will not be replicated to your master, if you accidentally update your slave somehow).

On mysql-2, from your MySQL console run the following statement to list your database users:

You should see output similar to the above code block. You may view privileges for each user with the following command:

<pre> SHOW GRANTS FOR wordpressuser@wordpress_1_IP; </pre>

In this example, we have one wordpressuser for each WordPress server, so we will revoke the insert, update, and delete privileges from each of them (“wordpress” is the name of our database in this example):

<pre> REVOKE INSERT, UPDATE, DELETE ON wordpress.* FROM ‘wordpressuser’@‘wordpress_1_private_IP’; REVOKE INSERT, UPDATE, DELETE ON wordpress.* FROM ‘wordpressuser’@‘wordpress_2_private_IP’; FLUSH PRIVILEGES; </pre>

Now your MySQL replication setup is complete. Let’s move on to setting up WordPress to use both database servers properly.

Install and Configure HyperDB

We will use HyperDB to determine where to send updates (your master database) and read requests (your master and slave). Let’s download it to your home directory from the WordPress Plugin Directory (also install zip/unzip to unarchive it):

cd ~; wget http://downloads.wordpress.org/plugin/hyperdb.zip
sudo apt-get install zip
unzip hyperdb.zip

It should be unarchived to a directory called “hyperdb”, in your home directory. Copy the sample configuration file to your WordPress installation (substitute the highlighted with your WordPress installation path), and open it for editing:

<pre> cp ~/hyperdb/db-config.php /var/www/example.com/ vi /var/www/example.com/db-config.php </pre>

Look for the second occurrence of DB_HOST, which should be directly after some comments that describe setting up a slave and it should look exactly like the following:

<pre> $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, )); </pre>

The first occurrence of DB_HOST defines the master database server, and the second occurrence defines the slave database server (denoted by the 'write' => 0,). Replace the second occurrence of DB_HOST with DB_SLAVE_1:

<pre> ‘host’ => DB_SLAVE_1, // If port is other than 3306, use host:port. </pre>

Save and exit. Next you will want to define DB_SLAVE_1 in your wp-config.php, which HyperDB will use as a slave database host. Open wp-config.php for editing:

<pre> vi /var/www/example.com/wp-config.php </pre>

Find the line that defines DB_HOST and add the following line under it, substituting your slave’s private IP address (mysql-2):

<pre> define(‘DB_SLAVE_1’, ‘mysql_2_private_IP’); </pre>

Then save and exit.

Finish the HyperDB installation by copying the db.php file to the wp-content directory in your WordPress installation, then disabling write access to it:

<pre> cp ~/hyperdb/db.php /var/www/example.com/wp-content/ sudo chmod a-w /var/www/example.com/wp-content/db.php </pre>

Then update the ownership of your wordpress files to their appropriate values (in this tutorial, we have been using www-data for the user/group ownership):

<pre> sudo chown -R www-data:www-data /var/www/example.com/ </pre>

Now your WordPress read requests will be served by both your master and slave databases, while updates will be sent to your master (which will then be replicated to your slave).

Conclusion

Now that you have completed your MySQL replication and HyperDB setup, your database environment will be able to handle increased read traffic i.e. more concurrent users! Remember that you can add more MySQL slaves if you want to scale your database serving capacity even more.

<div class=“author”>By Mitchell Anicas</div>

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors

Mitchell Anicas

author

Still looking for an answer?

Ask a question Search for more help

Was this helpful?

7 Comments

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

M • August 10, 2021

Hello, Thank you for the awesome article, very complete and detailed.

Are you planning on updating it (it’s years old now), and maybe optionally adding the DO’s Managed Database configuration to the recipe?

Thank you!

rupert • May 24, 2018

@Mitchell I REALLY need some help here, please…!! I have it all working beautifully, to a point.

I have a master and Slave set up, all replicating the databases between them, however, as soon as I add the db.php to the wp-content folder, i.e. to “activate” it, when I try and add a new post or a page, I get a page without the content entry boxes, and a warning “You are currently editing the page that shows your latest posts.”

There obviously something I am missing? A seeting in HyperDB ??? My config looks like this…

in wp-config.php: /** MySQL hostname */ define(‘DB_HOST’, ‘10.2.243.11’); define(‘DB_SLAVE_1’, ‘10.2.243.12’);

In db-config.php:

I have commented out the first instance of the DB_HOST as told to by the comments, as the primary server obviously exists in wp-config.php.

$wpdb->add_database(array( /**‘host’ => DB_HOST, // If port is other than 3306, use host:port.*/ ‘host’ => DB_SLAVE_1, // 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, ));

Have you got any ideas? Much appreciated in advance ;¬)

shatrughanjha • February 24, 2018

Its not working with mysql 5.7.

newbie • March 6, 2017

@manicas i have an app in php framework. what to do for other php application not wordpress? i mean HyperDB alternative.

lamhdiadam • September 14, 2015

العاب برق

what can I do if I have any problem in my database?

mike708593 • April 4, 2015

Hi, I have successfully set up the MySQL replication using two MySQL servers. I have tested creating sample posts and both database servers wrote them down successfully.

Now this is still an empty Wordpress and an empty database server. I plan to move a live site into this replication set up and this is my question: If I do a mysqldump and restore the dump to the master MySQL server, will the slave picks up the dump right away?

Thanks.

azlyjnor • December 23, 2014

Thanks for the tutorial!

Should I be installing HyperDB on Wordpress-2 as well? Or would the installation on Wordpress-1 suffice?