WordPress in OCI with MySQL HeatWave Read Replicas and MySQL Router R/W Splitting

Some time ago, we saw how we could deploy WordPress on OCI using MySQL HeatWave Database Service with Read Replicas. We had to modify WordPress to use a specific plugin that configures the Read/Write Splitting on the application (WordPress): LudicrousDB.

Today, we will not modify WordPress to split the Read and Write operations, but we will use MySQL Router 8.2.0 (see [1], [2], [3]).

Architecture

The architecture is simple, we will use one application server (Oracle Linux 8 on an Ampere Compute Instance) with Apache, PHP 8.2 and MySQL Router. The latest WordPress will be installed on it:

You can see that we will use different version of MySQL in MySQL HeatWave Database Service. We can use the latest 8.0 and an Innovation Release as Read Replica.

WordPress

We deploy WordPress (6.4.2) on an Always Free Ampere Compute instance using 4 cores and 24GB of RAM:

As reminder, this is how we install Apache, PHP 8.2 and WordPress on the compute instance:

ssh -i <path to the ssh key> opc@<public IP address>
sudo su -
yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum -y install https://rpms.remirepo.net/enterprise/remi-release-8.rpm
rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

yum install -y mysql-shell --enablerepo=mysql-tools-innovation-community
yum install -y mysql-router --enablerepo=mysql-tools-innovation-community

dnf -y module enable php:remi-8.2
dnf -y install httpd php php-cli php-mysqlnd php-zip php-gd \
 php-mcrypt php-mbstring php-xml php-json

cd /var/www/
wget https://wordpress.org/latest.tar.gz
tar zxvf latest.tar.gz
rm -rf html/ latest.tar.gz
mv wordpress html
chown apache. -R html

systemctl start httpd
systemctl enable httpd

firewall-cmd --zone=public --permanent --add-port=80/tcp
firewall-cmd --zone=public --permanent --add-port=443/tcp
firewall-cmd --reload

chcon --type httpd_sys_rw_content_t /var/www/html
chcon --type httpd_sys_rw_content_t /var/www/html/*
setsebool -P httpd_can_network_connect_db 1
setsebool -P httpd_can_network_connect=1

MySQL HeatWave Database Service

Now we need to deploy a MySQL HeatWave instance in OCI. To use Read Replicas, the current requirement is to use at lease 4 OCPUs:

The IP of our new instance is available in the DB system’s connections tab:

Read Replicas

We can create one or more Read Replicas, and we can specify the required version, there is no need to use the same as the Primary DB System (source):

We need to keep the following information (IP of the Primary DB System and the Load Balancer one) for later:

WordPress Database and User

It’s time to create the WordPress database and its dedicated user in MySQL. We first connect to our new DB System using MySQL Shell from the Compute Instance (the WordPress instance):

mysqlsh --sql admin@10.0.1.191

And we can create the database and the dedicated user:

create database wordpress;
create user wp identified by 'W0rdPress4#';
grant all privileges on wordpress.* to wp;

MySQL Router

Transparent Read/Write splitting in MySQL Router is compatible with all integrated solutions for replication, high availability (HA), and disaster recovery (DR). These solutions are MySQL InnoDB ReplicaSet, MySQL InnoDB Cluster, and MySQL InnoDB ClusterSet.

To enable transparent Read/Write splitting, it is necessary to manually create certain tables and populate them with the IP address of our MySQL HeatWave Instance and the IP address of the Read Replica Load Balancer.

Download the following file, unzip it and load it in the DB System (via the compute instance):

[root@wordpress opc]# wget https://lefred.be/wp-content/uploads/2023/12/router_metadata.zip

[root@wordpress opc]# unzip router_metadata.zip 
Archive:  router_metadata.zip
  inflating: router_metadata.sql
     
[root@wordpress opc]# mysqlsh --sql admin@10.0.1.191 < router_metadata.sql 

Records: 3  Duplicates: 0  Warnings: 0
Records: 3  Duplicates: 0  Warnings: 0
Records: 2  Duplicates: 0  Warnings: 0

And we change the instances data to match what we have in OCI:

mysqlsh --sql admin@10.0.1.191

use mysql_innodb_cluster_metadata

update instances set address="10.0.1.191:3306", 
       mysql_server_uuid=@@server_uuid, instance_name=@@hostname
  where instance_id=1;
update instances set addresses = '{"mysqlX": "10.0.1.191:33060", 
       "mysqlClassic": "10.0.1.191:3306"}' 
  where instance_id=1;

update instances set address="10.0.1.16:3306", 
       mysql_server_uuid="86b0b07b-98d2-11ee-9c5a-020017241124",
       instance_name="read_replica_load_balancer" 
  where instance_id=2;
update instances set addresses = '{"mysqlX": "10.0.1.16:33060", 
       "mysqlClassic": "10.0.1.16:3306"}' 
  where instance_id=2;

I used the uuid of one of the replica for the load balancer entry.

Bootstrap

We can now bootstrap MySQL Router on the WordPress application server (the compute instance):

mysqlrouter --bootstrap admin@10.0.1.191 \
--conf-set-option=routing:bootstrap_rw_split.bind_port=3306 \
--user=mysqlrouter

You need to bootstrap using the IP of the Primary DB instance.

We can verify that in the new generated configuration (/etc/mysqlrouter/mysqlrouter.conf) the default R/W splitting port 6450 has been replaced by 3306:

We can start MySQL Router:

systemclt start mysqlrouter.service

Finishing WordPress Installation

Everything is now prepared to complete the WordPress installation process.

We enter the public IP of the compute instance in a browser and we follow the WordPress installation wizard:

Testing

To test, we will add a PHP code snippet to a new WordPress post that display some information related to the database it’s connected to:

And now we load the article several times:

Sometimes, it is observed that the PHP snippet is executed on one Read Replica at times and on the other one at other times.

We can see that we can create posts, meaning writes are indeed redirected as expected to the Primary DB System.

We can test that also by putting the query in a transaction, this will have the same effect than a write:

Perfect, it’s evident that we are reaching the MySQL HeatWave instance being the source of the Read Replicas !

Conclusion

We have observed that the MySQL HeatWave Database Service Read Replicas capability can be advantageous for WordPress without necessitating any modifications or configuration changes within WordPress itself. With the assistance of MySQL Router 8.2.0, which enables Transparent Read/Write Splitting, we can automatically direct read and write queries to the appropriate instance. This eliminates the need for plugins or altering the WordPress configuration on disk.

Enjoy deploying WordPress in OCI, MySQL HeatWave, Read Replicas and MySQL Router !