MySQL 8.0 InnoDB Cluster with WordPress in OCI – part III

With this post we are reaching the end of our journey to HA for WordPress & MySQL 8.0 on OCI.

If you have not read the two previous articles, this is just the right time.

• MySQL 8.0 InnoDB ReplicaSet with WordPress in OCI
• MySQL 8.0 InnoDB ReplicaSet with WordPress in OCI – part II

We started this trip using the MySQL InnoDB ReplicaSet where only 2 servers are sufficient but doesn’t provide automatic fail-over.

In this article we will upgrade our InnoDB ReplicaSet to InnoDB Cluster. Therefor we will also need another compute instance for the 3rd recommended node.

Architecture

Our architecture will be like this:

The creation of mymysql03is something you should master now. If you don’t please check back the first article.

Migration to MySQL InnoDB Cluter

The first steps when we have mymysql03 up and running, is to dismantle out nice InnoDB ReplicaSet.

It’s always recommended to put the WordPress site in maintenance.

We start with the server playing the role of Secondary (currently mymysql01 on my setup):

MySQL  mymysql01:33060+   JS  rs.removeInstance('mymysql01')
The instance 'mymysql01:3306' was removed from the replicaset.

As we will make some modification on it, we also need to allow writes to it:

MySQL  mymysql01:33060+   JS  \sql SET GLOBAL SUPER_READ_ONLY=0;

We can now configure this server to be part of a MySQL InnoDB Cluster:

MySQL  mymysql01:33060+   JS  dba.configureInstance()
Configuring MySQL instance at mymysql02:3306 for use in an InnoDB cluster…
This instance reports its own address as mymysql01:3306
Clients and other cluster members will communicate with it through this address by default. 
If this is not correct, the report_host MySQL system variable should be changed.
NOTE: Some configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable        | Current Value | Required Value | Note                       |
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32         | NONE           | Update the server variable |
+-----------------+---------------+----------------+----------------------------+
Do you want to perform the required configuration changes? [y/n]: y
Configuring instance…
The instance 'mymysql01:3306' was configured to be used in an InnoDB cluster.

For mymysql02 (the Primary node), we cannot remove it from the ReplicaSet, so we just need to remove the metadata:

MySQL  mymysql01:33060+   JS  \c clusteradmin@mymysql02
Creating a session to 'clusteradmin@mymysql02'
Please provide the password for 'clusteradmin@mymysql02':
Save password for 'clusteradmin@mymysql02'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion… Press ^C to stop.
Closing old connection…
Your MySQL connection id is 293574 (X protocol)
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL  mymysql02:33060+   JS  dba.dropMetadataSchema()
Are you sure you want to remove the Metadata? [y/N]: y
Metadata Schema successfully removed.

Now we have to configure it and configure mysmyql03 too (for mymysql03, don’t forget to create a user with the same credentials as the other two nodes. In the previous post we used clusteradmin).

When all 3 servers are configured, we also need to allow the communication for Group Replication. This means that on all 3 database servers, we will run the following commands:

firewall-cmd --zone=public --permanent --add-port=33061/tcp
firewall-cmd --reload
semanage port -a -t mysqld_port_t -p tcp 33060-33061

For convenience I modified /etc/hosts to have all servers as I did in the previous articles. So this time I also added mymysql03.

MySQL InnoDB Cluster Creation

We gonna use the latest server that was acting as Primary and we will create the new MySQL InnoDB Cluster:

MySQL  mymysql02:33060+   JS  cluster=dba.createCluster('myWordpressIDC')
A new InnoDB cluster will be created on instance 'mymysql02:3306'.
Disabling super_read_only mode on instance 'mymysql02:3306'.
Validating instance configuration at mymysql02:3306…
This instance reports its own address as mymysql02:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mymysql02:33061'.
Use the localAddress option to override.
Creating InnoDB cluster 'myWordpressIDC' on 'mymysql02:3306'…
Adding Seed Instance…
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

And now we can add the two other servers:

MySQL  mymysql02:33060+   JS  cluster.addInstance('mymysql01')
...
MySQL  mymysql02:33060+   JS  cluster.addInstance('mymysql02')

MySQL Router

When MySQL InnoDB Cluster is up and running, we need to modify the routers on our webservers (as we deleted the metadata):

[root@mywordpress01 ~]# mysqlrouter --bootstrap clusteradmin@mymysql02 --user mysqlrouter \
                       --force --conf-use-gr-notifications
[root@mywordpress02 ~]# mysqlrouter --bootstrap clusteradmin@mymysql02 --user mysqlrouter \
                       --force --conf-use-gr-notifications

[root@mywordpress01 ~]# systemctl restart mysqlrouter
[root@mywordpress02 ~]# systemctl restart mysqlrouter

Conclusion

It is very easy to achieve a full automatic High Availability using MySQL InnoDB Cluster and it’s possible to start with a smaller environment and grow easily when needed. And in OCI, this is also very easy.

Of course it would be nice to see how does that work, isn’t it ?

Check this video to see MySQL 8.0 InnoDB Cluster and WordPress in action on OCI: