Configuring HA for MySQL in VMware Data Services Manager v1.3.2

I continue my journey to learn more about VMware Data Services Manager (DSM). Today, I turn my attention to clustering MySQL to make it highly available on vSphere infrastructure. This is subtlety different to the previous exercise of enabling HA on PostgreSQL. With the MySQL HA implementation, there is no requirement to deploy an additional VM to behave as the witness/quorum such as the PG_Monitor in the case of PostgreSQL. To make MySQL highly available, MySQL InnoDB Cluster is used. This utilizes a feature called group replication to synchronize the different read replicas. It also supports the concept of a virtual or cluster IP address, defined at the time that the cluster is created. This IP address is a sort of “floating” IP address and  redirects connections to whichever database is the primary database in the group. Let’s see how to set this up next.

The Database creation is much the same as we have seen previously. Login to DSM Provider appliance. Navigate to the Databases view and click on the option to create a database. This launches the wizard shown below. This time we are choosing MySQL. I gave it the name mysql-1. We begin by deploying a standalone MySQL database.

If this standalone MySQL database deploys successfully, it will show up with details similar to the following.

Note that in the above view, there is a tab called ‘Cluster Settings’. This is where Read Replicas for the MySQL database can be added. When viewed, it displays a HA Status of Unconfigured, naturally enough.

To make the standalone MySQL database highly available, click on the “+ Create” link on the right hand side of the ‘Replication’ section of the page. This will launch the ‘Create Read Replica’ wizard. You will need to provide a name for the new VM that will be cloned for the read replica. It is at this point that the Cluster IP (virtual / floating IP address) for the MySQL Cluster is also provided.

The original standalone MySQL database VM will now be cloned. When the operation begins, the Replication fields in the Cluster Settings view should populate with information similar to the following:

The original standalone database is marked as ‘Primary. The Cluster IP is also displayed prominently. If the clone operation succeeds, and the primary and replica are able to communicate successfully, the replica should show a ‘Replication Status’ of Active. However, we are not finished just yet.

Like we saw with the PostgreSQL cluster, the ‘HA Status’ remains Incomplete. This is because the requirement for a fully HA compliant database is to have two replicas, and there is currently only one. Thus, it is necessary to click on ‘+ Create’ once more and add another Read Replica. After initiating this task, the replication view in Cluster Settings should now look similar to this.

Assuming there are no issues with the deployment of the second Read Replica, once everything synchronizes within the replication group and the replication status changed to Active on all replicas, the ‘HA Status’ should now show Complete. This may take a couple of minutes.

The MySQL database is now fully clustered. We can also query this directly on the database itself using a SQL client. The performance_schema database holds the replication group members. You will have to scroll right to see the complete output as it is quite long.

SQL > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 01362ce6-9be8-11ed-a79e-005056a4a759 | mysql-replica-2.rainpole.com |        3306 | ONLINE       | SECONDARY   | 8.0.30         | MySQL                      |
| group_replication_applier | 7f176503-9be3-11ed-a695-005056a4c8a8 | mysql-1.rainpole.com         |        3306 | ONLINE       | PRIMARY     | 8.0.30         | MySQL                      |
| group_replication_applier | caa2cbd1-9be6-11ed-a815-005056a47460 | mysql-replica-1.rainpole.com |        3306 | ONLINE       | SECONDARY   | 8.0.30         | MySQL                      |
+---------------------------+--------------------------------------+------------------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.0796 sec)

As we can see, the primary and replicas are all online, and the VM/DB with the FQDN of mysql-1.rainpole.com is the primary. Assume now that we needed to do something with the VM that is currently hosting the primary DB. We may wish to increase the CPU, Memory or disk capacity for example. We can simply promote one of the replica VM/DBs to primary using the VMware Data Services Manager UI. This is a Database Action available from the drop-down. In this case we are promoting mysql-replica-1.rainpole.com to primary.

If the operation completes successfully, then we should see a new primary in the DSM UI for this database.

Success! One of the replicas is now the primary. We can also verify this directly on the database.

SQL > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 01362ce6-9be8-11ed-a79e-005056a4a759 | mysql-replica-2.rainpole.com |        3306 | ONLINE       | SECONDARY   | 8.0.30         | MySQL                      |
| group_replication_applier | 7f176503-9be3-11ed-a695-005056a4c8a8 | mysql-1.rainpole.com         |        3306 | ONLINE       | SECONDARY   | 8.0.30         | MySQL                      |
| group_replication_applier | caa2cbd1-9be6-11ed-a815-005056a47460 | mysql-replica-1.rainpole.com |        3306 | ONLINE       | PRIMARY     | 8.0.30         | MySQL                      |
+---------------------------+--------------------------------------+------------------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.0565 sec)

Or an easier way to view it might be as a list as follows:

SQL > SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
              CHANNEL_NAME: group_replication_applier
                MEMBER_ID: 01362ce6-9be8-11ed-a79e-005056a4a759
              MEMBER_HOST: mysql-replica-2.rainpole.com
              MEMBER_PORT: 3306
              MEMBER_STATE: ONLINE
              MEMBER_ROLE: SECONDARY
            MEMBER_VERSION: 8.0.30
MEMBER_COMMUNICATION_STACK: MySQL
*************************** 2. row ***************************
              CHANNEL_NAME: group_replication_applier
                MEMBER_ID: 7f176503-9be3-11ed-a695-005056a4c8a8
              MEMBER_HOST: mysql-1.rainpole.com
              MEMBER_PORT: 3306
              MEMBER_STATE: ONLINE
              MEMBER_ROLE: SECONDARY
            MEMBER_VERSION: 8.0.30
MEMBER_COMMUNICATION_STACK: MySQL
*************************** 3. row ***************************
              CHANNEL_NAME: group_replication_applier
                MEMBER_ID: caa2cbd1-9be6-11ed-a815-005056a47460
              MEMBER_HOST: mysql-replica-1.rainpole.com
              MEMBER_PORT: 3306
              MEMBER_STATE: ONLINE
              MEMBER_ROLE: PRIMARY
            MEMBER_VERSION: 8.0.30
MEMBER_COMMUNICATION_STACK: MySQL
3 rows in set (0.0599 sec)

That completes the overview of enabling a clustered/highly available MySQL database in VMware Data Services Manager (DSM). We are always interested to hear any comments and feedback on our products. Please reach out if you wish to share your observations and thoughts on VMware DSM.