MySQL 8.0 InnoDB ReplicaSet with WordPress in OCI – part II

This article is the second part of our journey to WordPress and MySQL 8.0 High Availability on OCI. The first part can be read here.

We ended part I with one webserver hosting WordPress. This WordPress was connecting locally to MySQL Router using HyperDB add-on. This add-on allows to split the reads & writes on MySQL Servers using replication. And finally we had one MySQL InnoDB ReplicaSet of two members serving the database.

In this article, we will upgrade our architecture by adding an extra webserver (with MySQL Router) and a load balancer in front of our webservers.

The New Architecture

New Webserver Installation

We add a new compute instance, preferably into another AD than the first one and we call it myWordpres02.

Follow the different steps to install it from the first article.

Again for convenience (this is not mandatory), we will modify /etc/hosts on all 4 servers to have something like this referring all hosts:

10.0.1.2 mymysql01
10.0.1.3 mymysql02
10.0.0.3 mywordpress02.sub05281502570.lefredvcn.oraclevcn.com mywordpress02
10.0.1.4 mymysql03.sub05281502571.lefredvcn.oraclevcn.com mymysql03
10.0.0.2 mywordpress01.sub05281502570.lefredvcn.oraclevcn.com mywordpress01

When done, we should not forget to copy the configuration files and the 2 required files needed for HyperDB:

[opc@mywordpress01 ~]$ cd /var/www/html/
[opc@mywordpress01 html]$ scp wp-config.php mywordpress02:
[opc@mywordpress01 html]$ scp db-config.php mywordpress02:
[opc@mywordpress01 html]$ scp wp-settings.php mywordpress02:
[opc@mywordpress01 html]$ scp wp-content/db.php mywordpress02:

When this is copied, we need to move those files to the right location on mywordpress02:

[opc@mywordpress02 ~]$ sudo su -
[opc@mywordpress02 ~]#  cd /var/www/html/
[root@mywordpress02 html]# mv ~opc/wp-config.php .
[root@mywordpress02 html]# mv ~opc/db-config.php .
[root@mywordpress02 html]# mv ~opc/wp-settings.php .
[root@mywordpress02 html]# mv ~opc/db.php wp-content

Now, we need to perform some security changes:

[root@mywordpress02 html]# chown apache. -R *
[root@mywordpress02 html]# chcon --type httpd_sys_rw_content_t wp-content/db.php
[root@mywordpress02 html]# chcon --type httpd_sys_rw_content_t db-config.php

MySQL Router

It’s time to install and bootstrap MySQL Router:

[root@mywordpress02 html]# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[root@mywordpress02 html]# yum install -u mysql-router
[root@mywordpress02 html]# mysqlrouter --bootstrap clusteradmin@mymysql01 --user mysqlrouter
[root@mywordpress02 html]# systemctl start mysqlrouter
[root@mywordpress02 html]# systemctl enable mysqlrouter

We can already verify that everything is working as expected by pointing our browser to the mywordpress02 ‘s public IP.

OCI Load Balancer

We will setup the load balancer on top of our webserver, so if one has some issues, the service will stay up and use only the remaining webserver (for practical reasons, I use only 2 webservers but you could setup a farm of multiple instances).

The load balancer is deployed and we can use its assigned public ip to contact our WordPress site. Our request will end up on one of the Apache servers and the MySQL queries will be split on both members of our MySQL InnoDB ReplicaSet.

Don’t forget that is you are performing tests like I’m doing, you need to change the URL in WordPress Settings to point to the Load Balancer’s IP:

ReplicaSet Routers

We have now 2 routers registered in our ReplicaSet. Using AdminAPIit’s easy to get some info about them. The listRouters() method will list all Routers:

Conclusion

As you can see it’s very easy to add the amount of Webservers in OCI. It’s also very easy to connect them to an already running MySQL InnoDB ReplicaSet. And finally the use of the OCI Load Balancer makes all this very easy to use together.

We have now full automatic High Availability with load splitting between our WordPress servers. However in case of issue on the Primaty MySQL instance, a manual step is required to promote the current Secondary to the Primary role.

Migrating the a full automated HA architecture is the next and final step to our journey on OCI with WordPress and MySQL 8.0.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.