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.
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.
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
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/hoststo have all servers as I did in the previous articles. So this time I also added
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')
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
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: