Deploy WordPress on OCI with MySQL Database Service using Read Replicas

Oracle recently released Read Replica for MySQL Database Service (also known as MySQL HeatWave, with or without the use of HeatWave accelerator for OLTP and OLAP).

Read Replicas are read-only copies of the MySQL DB system in the same region. When you add Read Replicas, they are automatically distributed accross Availability Domains and/or Fault Domains.

Each MySQL DB instance can have up to 18 read replicas.

Read Replicas use MySQL Asynchronous Replication with parallel workers. And when you create the first Read Replica, a Read Replica Load Balancer is created, which distributes read traffic among the read replicas.

Not all shapes are compatible with Read Replicas, the shape requires a minimum of 4 OCPUs:

Currently it is also not possible to change the shape of the Read Replica, you will have to manually configure the replication channel if, for example, you want to have an instance with HeatWave Cluster enabled (an instance using HeatWave secondary engine).

Architecture

For today’s article, we will deploy WordPress, the most popular open source content management, in Oracle Cloud Infrastructure and distribute the read traffic to Read Replicas.

To deploy WordPress on a compute instance, configure the network, and deploy the MySQL instance in OCI, we will use an OCI Resource Manager Stack.

With a single click, you will be able to deploy the following infrastructure:

Once deployed, we will see how to easily create new Read Replicas. We will then have the following final architecture:

Deployment

To start deploying all the required OCI resources, we start by clicking on the following button. This will deploy everything using OCI Resource Manager (Terraform):

Once clicked and if you are connected to the OCI console, we will arrive at this screen:

You must review and agree to Oracle Terms of Use, and if you do,the Terraform code will be loaded:

Then we just have to follow the wizard. This first section concerns the mandatory variables:

In the Shapes section it is important to choose a MySQL Shape with at least 4 OCPUs. You must enter the exact name of the shape because unfortunately the OCI API does not feed MySQL DB instance shapes in stacks:

We click on Next and we can apply the stack’s after its creation:

When the application task is complete, you can see information on how to connect to your newly deployed WordPress in the Logs section:

You can paste the public IP into your browser and finish the WordPress installation with the information returned in the logs. The database host is the IP returned by mds_instance_ip (10.0.1.249 in my example).

When WordPress is deployed, this is an example of what you get:

Read Replicas

To deploy Read Replicas, we need to choose the DB Instance and on the bottom left, in the Resources section, choose Read Replicas and click on Create read replica:

Once created, we can see that a new Endpoint is also created for the Read Replica and an additional one for the Read replica load balancer:

For the example, I will create another Read Replica to have 2 instances to serve the reads:

LudicrousDB

To use the Read Replicas and split MySQL queries for readings and writing, we’ll use LudicrousDB, a WordPress plugin that, once configured, will do exactly what we need.

In the past, I used HyperDB, but it doen’t seem to be compatible with the latest WordPress (6.1.1) and has some problems with MySQL 8.0’s sql_mode.

We need to connect to the compute instance (where WordPress is installed) in ssh to install and configure LudicrouDB.

To connect to the compute instance, we need to get the ssh private key that was generated during the Stack apply operation.

I haven’t found the easy way to get the sensitive information, some recent updates may have added new privileges to retrieve those outputs:

But, as workaround, you can retrieve the ssh key by going to Job resources in the Job details (for the apply stack) and then check the last entry:

We can then find and copy the private_key_openssh:

Just copy it to a file on your computer or to cloud shell. Remove the front and back double quotes character and replace all ‘\n’ by a carriage return.

Then change the permission to 600 (only your user should have read an write access).

When you are done, use OpenSSH to connect (from the command line or Putty) with the user opc using the public IP of the compute instance and the key we just copied:

We can now install LudicrousDB plugin to our WordPress.

[opc@wordpressserver1 ~]$ cd /var/www/html/wp-content/plugins
[opc@wordpressserver1 ~]$ sudo wget https://github.com/stuttter/ludicrousdb/archive/refs/heads/master.zip
[opc@wordpressserver1 plugins]$ sudo unzip master.zip
[opc@wordpressserver1 plugins]$ sudo mv ludicrousdb-master ludicrousdb
[opc@wordpressserver1 plugins]$ sudo rm master.zip
[opc@wordpressserver1 plugins]$ sudo chown -R apache. ludicrousdb
[opc@wordpressserver1 plugins]$ sudo cp ludicrousdb/ludicrousdb/drop-ins/db.php ../db.php
[opc@wordpressserver1 plugins]$ sudo cp ludicrousdb/ludicrousdb/drop-ins/db-config.php ../../

We just need to configure LudicrousDB to use the Read Replica Load Balancer:

[opc@wordpressserver1 ~]$ cd /var/www/html/
[opc@wordpressserver1 html]$ sudo vim db-config.php 

And we need to modify the db-config.php file to have something similar:

$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'    => 1,
	'read'     => 0,
) );

/**
 * 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'     => "10.0.1.152",     // 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,
) );

We just added the IP of the Read Replica Load Balancer (10.0.1.152) where read is 1.

And we are done !

I wrote a small PHP code snippet in a WordPress page that displays on which host the request is made:

<?php
$result = $wpdb->get_results("select @@hostname as host");
echo "<strong>host:</strong> " . $result[0]->host;
?>

And the result on some reloads of the page:

The name we see here are the actual host names of the instances as they appear in MySQL:

We can also see the connections on both Read Replicas in their Metrics section of the Oracle Cloud Infrastructure Console:

Conclusion

Deploying WordPress on Oracle Cloud Infrastructure (OCI) with MySQL Database Service using the new Read Replicas is a straightforward process that offers many benefits. By setting up Read Replicas, you can improve the performance and scalability of your WordPress site or any other application using MySQL.

With the detailed instructions provided in this article, you should now have the knowledge and tools necessary to successfully deploy WordPress on OCI with MySQL Database Service using Read Replicas.

This new feature provides elasticity very simply for read traffic, as you can add or remove MySQL instances on demand with a single click! !