MySQL 8.0 InnoDB ReplicaSet with WordPress in OCI

Today’s article is again related to WordPress and MySQL 8.0. We will see how we can setup MySQL InnoDB ReplicaSet and configure WordPress to split the load using both MySQL Instances: we will split reads and writes between the Primary and the Secondary member of our ReplicaSet.

This will be the first part of our journey to achieve HA for our WordPress site on OCI and using all MySQL Servers we have deployed. We don’t want to have a server idle just waiting to take over in case of an incident.

MySQL InnoDB ReplicaSet

First some words about MySQL InnoDB ReplicaSet.

The ease of use of AdminAPI for MySQL InnoDB Cluster encouraged the MySQL Team to develop equivalent for classic asynchronous replication: MySQL InnoDB ReplicaSet. Like its big brother, InnoDB ReplicaSet includes MySQL Server, MySQL Router, Clone Plugin and MySQL Shell.

The big difference is that failover is not automatic for obvious reasons:

  • it uses asynchronous replication where there is no certification involved
  • 2 members is enough, there is no quorum involved or split-brain prevention

The Architecture – part I

For this part, the architecture will look like this illustration:

We will use two AD (Availability Domain) in which we will install a MySQL Server on a compute instance. We will also setup another compute instance to install our webserver with WordPress.

Only the Webserver will be accessible from the Internet. We will use a VPN with 2 subnets, one public and on private. The MySQL Servers will be located in the private one.

We will use 2 gateways, an Internet one for the public subnet and a NAT one for the private subnet in our VCN.

Networking (VCN)

Thanks to OCI’s web interface, this is very easy to setup when using the wizard:

Compute Instances

It’s time to create our compute instances. Don’t forget that we need to create:

  • one in the public subnet
  • two in the private subnet but one in each AD

Please check previous OCI related articles if you don’t know how to create a compute instance:

Don’t forget to use the same ssh key to be able to connect to the instances in the private subnet forwarding the ssh agent.

You should end-up with something similar:

Installing WordPress Server

We can now connect to our WordPress server (mywordpress01) and install all required packages, including PHP, Apache and some extensions:

$ ssh -i <path to your ssh key> opc@150.136.16.64

150.136.16.64 is the Public IP that was assigned to my box. You should of course use yours.

Let’s install and enable Remi’s repo to use the latest PHP 7.4 that supports all MySQL 8.0 authentication plugins.

[opc@mydrupal ~]$ sudo su -
# yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# yum -y install https://rpms.remirepo.net/enterprise/remi-release-7.rpm
# yum-config-manager --enable remi-php74

We can install PHP:

# yum -y install php php-cli php-mysqlnd php-zip php-gd \
                 php-mcrypt php-mbstring php-xml php-json

Now we will download and unpack WordPress:

# 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

As we are using Oracle Linux 7 on OCI, we also need to perform some security operations on our system to allow connections to our webserver but also allow the Apache’s process to modify files and connect to the database and to other services:

# firewall-cmd --zone=public --permanent --add-port=80/tcp
success
# firewall-cmd --reload
success
# setsebool -P httpd_can_network_connect_db 1
# setsebool -P httpd_can_network_connect 1
# cd /vaw/www
# chcon --type httpd_sys_rw_content_t html

We also need to add a rule on OCI for the Public Subnet to also allow connections to port 80:

We can start Apache (httpd) , and point our browser to the public ip, we should see the WordPress Installation Wizard:

Installing MySQL 8.0

We will install MySQL 8.0 in parallel on both database servers we created.

To connect in ssh on our database servers, we will use the webserver as jumphost:

$ ssh -i ~/.ssh/id_rsa_oci.pub -A opc@150.136.16.64
[opc@mywordpress01 ~]$ ssh 10.0.1.2
Last login: Fri May 29 06:07:39 2020 from ...
[opc@mymysql01 ~]$ sudo su -
[root@mymysql01 ~]#

For convenience, we will edit on all 3 servers the file /etc/hoststo have those entries:

10.0.0.2 mywordpress01.sub05281502570.lefredvcn.oraclevcn.com mywordpress01
10.0.1.2 mymysql01.sub05281502571.lefredvcn.oraclevcn.com mymysql01
10.0.1.3 mymysql02.sub05281502571.lefredvcn.oraclevcn.com mymysql02

We will first install the MySQL 8.0 repository on all 3 servers (even on the webserver in case we want to install MySQL Shell for testing or orchestrating without having to jump on a database server):

# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

Now on mymysql01 and mymysql02 we perform the exact same commands to install MySQL 8.0, MySQL Shell and start it:

# yum install -y mysql-server mysql-shell
# systemctl start mysqld
# systemctl enable mysqld

On both servers we will configure or Shell (this is optional):

# mysqlsh
mysql-js> shell.options.setPersist('history.autoSave', 1)
mysql-js> shell.options.setPersist('history.maxSize', 5000)
and we quit (I use ^D)
# cp /usr/share/mysqlsh/prompt/prompt_256pl+aw.json ~/.mysqlsh/prompt.json

Changing root password

Once again on both servers, we will connect using the default generated password and change it:

# grep password /var/log/mysqld.log
2020-05-28T17:54:12.872680Z 6 [Note] [MY-010454] [Server] 
A temporary password is generated for root@localhost: t=Vi8ftN&i2Q
# mysqlsh --sql root@localhost
mysql-sql> set password='MySQL8isGreat!';

MySQL InnoDB ReplicaSet

Now that we have changed the password, we will reconnect on each instance locally and configure them to be part of a MySQL InnoDB ReplicaSet:

[root@mymysql01 ~]# mysqlsh root@localhost
mysql-js> dba.configureReplicaSetInstance()

We choose option 2 to create a dedicated users:

username: clusteradmin
pasword: MySQL8isGreat!

We do the same on mymysql02:

[root@mymysql02 ~]# mysqlsh root@localhost
mysql-js> dba.configureReplicaSetInstance()

We let them restart MySQL of course.

Now before creating our InnoDB ReplicaSet, we need to open communication between our webserver located in the Public Subnet and our database servers in the Private Subnet. We do that on OCI’s web interface:

And once again, we also need to open the local firewall on the MySQL servers:

# firewall-cmd --zone=public --permanent --add-port=3306/tcp
success
# firewall-cmd --zone=public --permanent --add-port=33060/tcp
success
# firewall-cmd --reload
success

We will test this connection and create the ReplicaSet using the webserver. Let’s connect again on mywordpress01:

# yum install -y mysql-shell

I invite you to configure MySQL Shell like we did on the database servers

Let’s connect to mymysql01using the shell:

[opc@mywordpress01 ~]$ mysqlsh clusteradmin@mymysql01
mysql-js> rs=dba.createReplicaSet('myWordpressRS')
...
mysql-js> rs.addInstance('mymysql02')
....

Clone will happen and now we have a functional MySQL InnoDB ReplicaSet:

Now on the Primary (currently mymysql01), we will create the worpress schema and an user for it:

[root@mymysql01 ~]# mysqlsh --sql root@localhost
mysql-sql> CREATE DATABASE wordpress;
mysql-sql> CREATE USER wordpress IDENTIFIED BY 'W0rdPr3$$';
mysql-sql> GRANT ALL PRIVILEGES ON wordpress.* to wordpress;

We must use root locally on the Primary, because currently the root user cannot connect remotely and the uset clusteradmin doesn’t have enough privileges to GRANT ALL PRIVILEGES ON wordpress.

Finishing WordPress Installation

It’s time to finish the WordPress installation using mymysql01 as database server. When all is working we will deploy and use MySQL Router.

Then, just follow the next steps in the wizard and finally you should reach:

Deploying MySQL Router

Now that our Website is up and running, we can install, bootstrap and start MySQL Router on our application server (mywordpress01):

# yum install -y mysql-router
...
# mysqlrouter --bootstrap clusteradmin@mymysql01 --user mysqlrouter
# systemctl start mysqlrouter
# systemctl enable mysqlrouter

HyperDB

Currently, we have all WordPress queries being processed by mymysql01, mymysql02replicates the data and waits in case we have an issue (or need to do maintenance) on the Primary to be promoted as Primary.

By default, WordPress is not able to split reads and writes queries and send to replicas the reads to off-load the Primary.

This is the goal of HyperDB add-on for WordPress.

Let’s install it:

The installation of this plugin doesn’t do much, now we need to make modification manually in some files on the webserver to use it:

# cd /var/www/html
# mv wp-content/plugins/hyperdb/db-config.php .
# mv wp-content/plugins/hyperdb/db.php wp-content/
# chown apache. db-config.php wp-content/db.php
# chcon --type httpd_sys_rw_content_t /var/www/html

We have to edit db-configto have something like this:

$wpdb->add_database(array(
  'host' => '127.0.0.1:6446', // If port is other than 3306, use host:port.
  'user' => DB_USER,
  'password' => DB_PASSWORD,
  'name' => DB_NAME,
  'write' => 1,
  'read' => 0,
));
$wpdb->add_database(array(
  'host' => '127.0.0.1:6447', // 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.5,
));

Note the ports used and of course we connect on 127.0.0.1 to reach MySQL Router.

We also need to make a change on both servers because HyperDB makes queries not compatible with the new SQL_MODE:

[root@mymysql01 ~]# mysqlsh --sql root@localhost
mysql-sql> SET PERSIST SQL_MODE='';

[root@mymysql02 ~]# mysqlsh --sql root@localhost
mysql-sql> SET PERSIST SQL_MODE='';

We can refresh our WordPress site and now it uses both MySQL instances !

To manual change the Primary role to another server, we just need to use MySQL Shell like this:

[root@mywordpress01 ~]# mysqlsh clusteradmin@mymysql01
...
MySQL   mymysql01:33060+   JS  rs=dba.getReplicaSet()

MySQL   mymysql01:33060+   JS  rs.setPrimaryInstance('mymysql02')

This is an overview:

Conclusion

We saw how we can benefit of MySQL InnoDB ReplicaSet deployment in OCI and how to use a replica (Secondary) to actually transfer read queries to it and not leave it idle. And we also have the possibility to manually promote it as Primary (the database server getting the writes).

In the next article we will see how we can deploy an extra webserver with a loadbalancer in front, an extra step to our HA setup on OCI.

Enjoy MySQL 8.0 and Oracle Cloud Infrastructure !

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 *

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.

Continue