• 131
• 255
• 14

How to setup a MySql replication database for WordPress

by bernt & torsten

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.