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
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
[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
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:
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:
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.