WordPress MySQL Replication With HyperDB

Abhishek Ghosh

By Abhishek Ghosh October 7, 2019 9:58 am Updated on October 7, 2019

WordPress MySQL Replication With HyperDB

In our earlier guides, we have discussed about Standalone Database and Distributed Database, shown MySQL Configuration to Set Up Master-Slave Replication. Here is the required setup and settings to make WordPress use master/slave MySQL database with replication fully working. The total set of articles will make your WordPress setup far more advanced and fail proof. Obviously, like anything it will require some time as an advanced user to optimize the settings and make the thing working far better. You’ll get used how to manage and optimize the delay in replication. Tungsten Replicator is an open source high performance replication engine, you may try it later. You need to wget the WordPress HyperDB drop-in plugin. There is nothing to install it from WordPress like normal plugin, but drop-in plugin essentially PHP scripts.

The HyperDB plugin replaces the wpdb class. As a result, WordPress becomes able to write and read from additional slave database servers. The plugin supports replication, failover, load balancing, and partitioning. The HyperDB plugin is extremely powerful and it is about the skill of SysOps to make it using to create something like wordpress.com setup. Most of us need just for a single WordPress installation.

After completing wget and unzipping, move the db.php file into the wp-content directory. This file does not require activation or edit. Now, move the db-config.php file in the same directory where the site’s wp-config.php file resides. Open db-config.php file with any text editor. This is the most simple configuration :

<?php
/**
 * Register the master server to HyperDB
 */
$wpdb->add_database( array(
        'host'     => DB_HOST,
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 1, // master server takes write queries
        'read'     => 1, // ... and read queries
) );
/**
 * Register replica database server if it's available in this environment
 */
if ( ! empty( $_ENV['REPLICA_DB_HOST'] ) ) {
        $wpdb->add_database(array(
                'host'     => $_ENV['REPLICA_DB_HOST'] . ':' . $_ENV['REPLICA_DB_PORT'],
                'user'     => $_ENV['REPLICA_DB_USER'],
                'password' => $_ENV['REPLICA_DB_PASSWORD'],
                'name'     => $_ENV['REPLICA_DB_NAME'],
                'write'    => 0, // replica doesn't take write queries
                'read'     => 1, // ... but it does take read queries
        ));
}
// That's it!

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

<?php

/**

* Register the master server to HyperDB

*/

$wpdb->add_database( array(

'host' => DB_HOST,

'user' => DB_USER,

'password' => DB_PASSWORD,

'name' => DB_NAME,

'write' => 1, // master server takes write queries

'read' => 1, // ... and read queries

) );

/**

* Register replica database server if it's available in this environment

*/

if ( ! empty( $_ENV['REPLICA_DB_HOST'] ) ) {

$wpdb->add_database(array(

'host' => $_ENV['REPLICA_DB_HOST'] . ':' . $_ENV['REPLICA_DB_PORT'],

'user' => $_ENV['REPLICA_DB_USER'],

'password' => $_ENV['REPLICA_DB_PASSWORD'],

'name' => $_ENV['REPLICA_DB_NAME'],

'write' => 0, // replica doesn't take write queries

'read' => 1, // ... but it does take read queries

));

}

// That's it!

You will find more files on GitHub which can conditionally split load, as for example WordPress Admin can load from the master.

Tagged With mysql replication object cache , wordpress db slave , wordpress hyperdb , wordpress replication

WordPress MySQL Replication With HyperDB

This Article Has Been Shared 206 Times!

About Abhishek Ghosh

Here’s what we’ve got for you which might like :

Take The Conversation Further ...

This Article Has Been Shared 206 Times!

About Abhishek Ghosh

Here’s what we’ve got for you which might like :

Articles Related to WordPress MySQL Replication With HyperDB

Take The Conversation Further ...