How to setup a MySql replication database for WordPress

WordPress uses MySql database to store all the plugin options, theme options, your pages, your posts, users and comments, with more visitors your site need to be able to scale or in my case, I had user spikes (increased traffic) that took out the connection to MySql, as memory on the server was used up. Scaling your site by replicating your MySql database is a good start. MySql replication is a process that allows you to easily maintain multiple copies of MySQL data by having them copied automatically from a master to a slave database.

In this article I will cover a basic configuration example of MySQL replication—one MySQL database (master) will send information to a single MySQL database (slave). For this to work you would need two servers that are running MySql, the master is your main WordPress website and the slave is your staging server or backup server with MySql on it, in my case my staging server runs a LAMP stack.

Before you start it is important to write down in two columns representing the two servers, the following.

  • Master and Slave Server IP address
  • Master and Slave Server SSH Username and Password
  • Master and Slave Server Mysql root password

Setup

I assume that you have a user with sudo privileges and have MySQL installed. If you do not have MySQL, you can install it with this command:

sudo apt-get install mysql-server mysql-client

Step One – Configure the WordPress (Master) Database

Open up the MySQL configuration file on the master server.

sudo nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes.

The first step is to find the section that looks like this, binding the server to the localhost:

bind-address = 127.0.0.1

Replace the standard IP address with the IP address of the server.

bind-address = xx.xx.xx.xx IP Address of your WordPress Server (Master)

The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number, I find it easier to start with 1, but the number must be unique and cannot match any other server-id in your replication group.

Make sure this line is uncommented.

server-id = 1

Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin:

log_bin = /var/log/mysql/mysql-bin.log

Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

binlog_do_db = wordpressdb

After you make all of the changes, go ahead and save and exit out of the configuration file.

Refresh MySQL.

sudo service mysql restart

The next steps will take place in the MySQL shell, itself.

Open up the MySQL shell.

mysql -u root -p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO 'username_slave'@'%' IDENTIFIED BY 'password';

Follow up with:

FLUSH PRIVILEGES;

Switch to “wordpressdb” database.

USE wordpressdb;

Following that, lock the database to prevent any new changes:

FLUSH TABLES WITH READ LOCK;

Then type in:

SHOW MASTER STATUS;

You will see a table that should look something like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 |    66780 | wordpressdb  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

This is the position from which the slave database will start replicating. Write down these numbers as you would need them when setting up the slave server.

Proceeding with the database still locked, export your database using mysqldump (make sure you are typing this command in the bash shell, not in MySQL).

You can open another session to your server to do this or you can exit MySQL.

mysqldump -u root -p --opt wordpressdb > wordpressdb.sql

Get back into MySQL to unlock the databases (making them writeable again).

UNLOCK TABLES;
QUIT;

Now you are all done with the configuration of the wordpress master database.

Step Two—Configure the Staging Server (Slave) Database

Log into your staging (slave) server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

CREATE DATABASE wordpressdb;
EXIT;

Import the database that you previously exported from the master database.

mysql -u root -p wordpressdb < wordpressdb.sql

Now you need to configure the slave configuration in the same way as you did the master:

sudo nano /etc/mysql/my.cnf

You need to configure the server-id. This number, as mentioned before needs to be unique. Since it is set to the default value (1), be sure to change it’s something different. It can’t be the same as your WordPress Server (Master).

server-id               = 2

Following that, make sure that you have the following three criteria appropriately filled out:

relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = wordpressdb

You will need to add in the relay-log line: it is not there by default. Once you have made all of the necessary changes, save and exit out of the slave configuration file.

Restart MySQL once again:

sudo service mysql restart

The next step is to enable the replication from within the MySQL shell.

Open up the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx',MASTER_USER='username_slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=66780;

xx.xx.xx.xx = The IP address of your WordPress server (Master)

This command accomplishes several things at the same time:

  1. It designates the current server as the slave of our master server.
  2. It provides the server the correct login credentials
  3. Last, of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

With that—you have configured a master and slave server.

Activate the slave server:

START SLAVE;

You can see the details of the slave replication by typing in the \G command. The \G rearranges the text to make it more readable.

SHOW SLAVE STATUS\G

If there is an issue in connecting, you can try starting slave with a command to skip over it:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

WordPress Configuration

To have your database replicated works without any additional plugins, In the beginning, I was talking about scaling your database, to utilize your replicated slave database for scaling there is a plugin that you need named HyperDB from WordPress.  HyperDB is an advanced database class that supports replication, failover, load balancing, and partitioning.

HyperDB has two files and is not installed as a traditional WordPress Plugin. Nothing goes in the plugins directory.

  1. Enter a configuration in db-config.php.
  2. Deploy db-config.php in the directory that holds wp-config.php. This may be the WordPress root or one level above. It may also be anywhere else the webserver can see it; in this case, define DB_CONFIG_FILE in wp-config.php.
  3. Deploy db.php to the /wp-content/ directory. Simply placing this file activates it. To deactivate it, move it from that location or move the config file.

When your databases are located in separate physical locations there is typically an advantage to connecting to a nearby server instead of a more distant one. In the HyperDB configuration file, you can configure read and write parameters that can be used to place servers into logical groups of more or less preferred connections.

Here is an example of a HyperDB configuration that I use for this site

/**
 * save_queries (bool)
 * This is useful for debugging. Queries are saved in $wpdb->queries. It is not
 * a constant because you might want to use it momentarily.
 * Default: false
 */
$wpdb->save_queries = false;

/**
 * persistent (bool)
 * This determines whether to use mysql_connect or mysql_pconnect. The effects
 * of this setting may vary and should be carefully tested.
 * Default: false
 */
$wpdb->persistent = false;

/**
 * max_connections (int)
 * This is the number of mysql connections to keep open. Increase if you expect
 * to reuse a lot of connections to different servers. This is ignored if you
 * enable persistent connections.
 * Default: 10
 */
$wpdb->max_connections = 10;

/**
 * check_tcp_responsiveness
 * Enables checking TCP responsiveness by fsockopen prior to mysql_connect or
 * mysql_pconnect. This was added because PHP's mysql functions do not provide
 * a variable timeout setting. Disabling it may improve average performance by
 * a very tiny margin but lose protection against connections failing slowly.
 * Default: true
 */
$wpdb->check_tcp_responsiveness = true;

/**
 * This is the most basic way to add a server to HyperDB using only the
 * required parameters: host, user, password, name.
 * This adds the DB defined in wp-config.php as a read/write server for
 * the 'global' dataset. (Every table is in 'global' by default.)
 */

$wpdb->add_database(array(
    'host'     => DB_HOST,
    'user'     => DB_USER,
    'password' => DB_PASSWORD,
    'name'     => DB_NAME,
));

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

If this article was of use to you let me know by commenting below.


Posted

in

,

by

Comments

15 responses to “How to setup a MySql replication database for WordPress”

  1. Kevin Avatar
    Kevin

    Thank you so much

  2. PaulIsLoud Avatar
    PaulIsLoud

    Worked like a charm, thanks a bunch. You could consider being explicit about the character set and collation:

    `CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;`

    Source: https://www.digitalocean.com/community/tutorials/how-to-install-wordpress-with-lemp-on-ubuntu-16-04

    And this is the ‘standard’ way of exporting a WordPress database:

    mysqldump –add-drop-table -h db01.example.net -u dbocodex -p wp > blog.bak.sql

    (note the addition of –add-drop-table)
    Source: https://codex.wordpress.org/Backing_Up_Your_Database

    Not sure if it is a functional improvement on what you’ve got, but I ran through this entire process twice and kept having to switch back and forth between this guide and those sources to get things just right.

    1. torbjornzetterlund Avatar

      Thanks for pointing out the weakness of my article, I was not thinking about the character set when I put it together. Thank you for improving on what I got started.

  3. Rajendra Avatar
    Rajendra

    Hi torbjornzetterlund,
    Thanks for the details. How can we store siteurl and home fields information if we use common MySQL DB for a WordPress cluster environment. Since each WordPress environment has it’s own URL’s how can we manage with common db? For example if we have 6 servers running on it’s separate machine and they grouped as a cluster, how can store siteurl and host values for all the environment in common DB or same schema. Your prompt response would be much appreciated. Thanks.
    Raj

    1. torbjornzetterlund Avatar

      With a replication server, it will replicate the db over to each server, that should contain the site URL’s.

      1. Rajendra Avatar
        Rajendra

        Do we need to create multiple tables with different table prefix for each WordPress environment if we want to use common DB? Basically how can we store SiteURL and home fields for more than one WordPress environment in a single DB? Please advise. Thanks.

        1. torbjornzetterlund Avatar

          It all depends on how you have setup your WordPress environment, if you have setup your multiple WordPress as multisite. You can use replication from one db to another db, if you setup each of your WordPress sites on separate db’s, then you would need to replicated each WordPress db.

  4. Rajendra Avatar
    Rajendra

    Thanks for your reply. If we want to use a common MySQL DB for multiple WordPress instances do we need to use different table prefixes in order to store siteurl and home fields information for all the environments? Basically how can we store multiple WordPress environments details in a single DB? Does this approach is preferred approach in production environments? I want to have a MySql master slave replication setup for 4 wordpress servers in production for the same blog. Please advise. Thanks.

    1. torbjornzetterlund Avatar

      If you have WordPress sites, we there own MySql database, then you would need to replicated them each separately. If you have setup WordPress as a multisite, you would have one MySql database, each WordPress site would have a prefix table name in that MySql database. IF you setup replication, for a multisite, then you would only need to replicate one MySQL database, and you could do it Master Slave, and have many slaves.

  5. Rob Davis Avatar
    Rob Davis

    We’re using Google Cloud to host our wordpress. You get to the SQL server(s) by using a proxy program which translates all database requests to tcp/localhost. In WordPress you can force TCP by using 127.0.0.1, however the db.php seems to always be looking for an SQL socket and won’t connect to my SQL instances. WordPress then thinks I need to reinstall.

    Any ideas how to force this to use tcp?

    1. torbjornzetterlund Avatar

      I’m not that familiar with GCP hosting SQL server, and the connections. You can install a WordPress instance on GCP, and it uses the local SQL server, in GCP you could setup triggers when you need to scale, do you then really need to setup replication server?

  6. Tanner Avatar
    Tanner

    Is it possible to have Redis with this setup? I know that the Redis WordPress plugins require a db.php as well so I’m thinking it might conflict with HyperDB

    1. torbjornzetterlund Avatar

      I have not tried with Redis, not sure if that will work. As you pointing out could be conflict with HyperDB, just ry to see how it goes.

  7. Thebe Avatar
    Thebe

    Hi Sir,

    Thank you for this.

    Will I be correct to say this setup will also work if mysql and wordpress are installed separately right?
    If so then will the slave be able to take over should the master go down?
    can I also follow the same tutorial to add more slaves?

    1. Torbjorn Zetterlund Avatar
      Torbjorn Zetterlund

      To answer your questions, yes you can follow the setup if you have your MySQL and WordPress on different servers. Yes, the slave can take over if the master MySQL fails. You can use the same tutorial to add more slaves. You may also be interested in another article I wrote about moving MySQL to the Cloud, with a Cloud SQL solution – Migrate your WordPress local MySQL to cloud sql

Leave a Reply

Your email address will not be published. Required fields are marked *