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.
Thanks to OCI’s web interface, this is very easy to setup when using the wizard:
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> email@example.com
18.104.22.168is 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 firstname.lastname@example.org [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
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>
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:
We do the same on
[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
# 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
rootuser cannot connect remotely and the uset
clusteradmindoesn’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 (
# yum install -y mysql-router ... # mysqlrouter --bootstrap clusteradmin@mymysql01 --user mysqlrouter # systemctl start mysqlrouter # systemctl enable mysqlrouter
Currently, we have all WordPress queries being processed by
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.1to reach MySQL Router.
We also need to make a change on both servers because HyperDB makes queries not compatible with the new
[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:
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 !