Let us look at how to Scale WordPress Sites with MySQL Replicas and HyperDB. With the support of our MySQL support services at Bobcares, we will now go through a complete overview of the process.

Scale WordPress Sites with MySQL Replicas and HyperDB

Most WordPress sites can only service read and write requests from a single database. As a result, high-traffic websites may encounter delay while requests are processed.

MySQL replication replicates material from the primary database to a replica database in real time. This enables us to distribute queries over many databases, improving site speed and load times.

What is HyperDB?

HyperDB is a plug-in that substitutes the default wpdb class. This enables WordPress to write to and read from other database servers. HyperDB is compatible with:

• Database replication
• Failover
• Load balancing
• Partitioning

Remember that HyperDB is a sophisticated tool with a variety of tweaking options based on database design and network topology. Before implementing a complicated setup, we should first determine whether a smaller configuration would suffice.

Install and Configure HyperDB: Scale WordPress Sites with MySQL Replicas and HyperDB

Before we can Scale WordPress Sites, a Customer Success Manager (CSM) must upgrade the site service level to Elite. The Pantheon platform will configure and manage the primary and replica databases automatically.

When we upgrade the site service level to Elite, follow the instructions below.

1. Firstly, download the HyperDB plugin archive from the WordPress plugin repository and place the db.php file in the /wp-content directory. This is a plug-in that does not need to be activate at any time.
2. AFter that, inside db-config.php, configure the primary and replica databases. This file should be placed in the same directory as the wp-config.php file for the site.
3. Finally, install the database drop-in db.php to production. WordPress will begin assigning MySQL database reads and writes based on the settings you specify in db-config.php.

The sample configurations below can be used instead of the dp-config.php file included with the plugin download. These examples require no further changes for Pantheon-powered sites.

Split Reads Between Primary and Replica

To distribute the load between two servers, we can split reads between the primary and replica databases. Make sure to follow the exact steps to get a Scale WordPress Sites.

?php
/**
* Register the primary server to HyperDB
*/
$wpdb->add_database( array(
'host'     => DB_HOST,
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 1, // primary 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
));
}

Pass Frontend Read Queries to Replica, WordPress Dashboard Reads and Writes to Primary

We can send all frontend database read requests to the replica. This frees up the primary database for WordPress dashboard reads and writes. This configuration can assist to ensure WordPress dashboard availability during periods of heavy frontend usage.

If reads to the replica start failing, we should utilize some sort of cache fallback. This is the final step to Scale WordPress Sites with MySQL Replicas and HyperDB.


'host'     => DB_HOST,
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
write'    => 1, // primary server takes write queries
'read'     => is_admin() || empty( $_ENV['REPLICA_DB_HOST'] ) ? 1 : 0,
// ... but only takes read queries in the admin if the replica is available
) );
/**
* Register replica database server if it's available in this environment
*/
if ( ! empty( $_ENV['REPLICA_DB_HOST'] ) && ! is_admin() ) {
$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
));
}

[Need assistance with similar queries? We are here to help]

Conclusion

To sum up we have now seen how to Scale WordPress Sites with MySQL Replicas and HyperDB. With the support of our MySQL support services, we have now gone through all of the configurations necessary to set it up.