Deploying a MySQL Group Replication Cluster Using KubeBlocks

MySQL Group Replication (MGR) offers high availability and scalability by synchronizing data across multiple MySQL instances. It ensures that all nodes in the cluster participate in replication seamlessly, with automatic failover and self-healing capabilities. This guide walks you through deploying a MySQL Group Replication cluster using KubeBlocks, which simplifies the management and deployment of MySQL clusters in Kubernetes.

Prerequisites

Before proceeding, ensure the following:

• Environment Setup:
  • A Kubernetes cluster is up and running.
  • The kubectl CLI tool is configured to communicate with your cluster.
  • KubeBlocks CLI and KubeBlocks Operator are installed. Follow the installation instructions here.
• Namespace Preparation: To keep resources isolated, create a dedicated namespace for this tutorial:

kubectl create ns demo
namespace/demo created

Deploying the MySQL Group Replication Cluster

KubeBlocks uses a declarative approach to manage MySQL clusters. Below is an example configuration for deploying a MySQL Group Replication cluster with three nodes.

Apply the following YAML configuration to deploy a MySQL Group Replication (MGR) cluster:

kubectl apply -f - <<EOF
apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: example-mysql-cluster
  namespace: demo
spec:
  clusterDef: mysql
  topology: mgr
  terminationPolicy: Delete
  componentSpecs:
    - name: mysql
      serviceVersion: 8.0.35
      replicas: 3
      resources:
        limits:
          cpu: '0.5'
          memory: 0.5Gi
        requests:
          cpu: '0.5'
          memory: 0.5Gi
      volumeClaimTemplates:
        - name: data
          spec:
            storageClassName: ""
            accessModes:
              - ReadWriteOnce
            resources:
              requests:
                storage: 20Gi
EOF

Explanation of Key Fields:

• clusterDef: mysql: Specifies the ClusterDefinition CR for the MySQL cluster. The ClusterDefinition 'mysql' contains multiple topologies, such as 'semisync', 'semisync-proxysql', 'mgr', 'mgr-proxysql', 'orc', 'orc-proxysql'.
• topology: mgr: Configures the cluster to use MySQL Group Replication.
• replicas: 3: Deploys three MySQL instances (one primary and two secondary).
• Resource Limits:
  • Each MySQL instance is allocated 500m CPU and 512Mi memory.
  • Each MySQL instance is provisioned with a persistent volume of 20Gi.

Verifying the Deployment

1. Check the Cluster Status

Monitor the status of the MySQL cluster as it is created:

kubectl get cluster -n demo -w

Example Output:

NAME                    CLUSTER-DEFINITION   TERMINATION-POLICY   STATUS     AGE
example-mysql-cluster   mysql                Delete               Creating   2s
example-mysql-cluster   mysql                Delete               Running    96s

2. Detailed Cluster Information

To get detailed information about the deployed cluster, use the following command:

kbcli cluster describe example-mysql-cluster -n demo

Example Output:

Name: example-mysql-cluster	 Created Time: Feb 10,2025 22:23 UTC+0800
NAMESPACE   CLUSTER-DEFINITION   TOPOLOGY   STATUS    TERMINATION-POLICY
demo        mysql                mgr        Running   Delete

Endpoints:
COMPONENT   INTERNAL                                                  EXTERNAL
mysql       example-mysql-cluster-mysql.demo.svc.cluster.local:3306   <none>

Topology:
COMPONENT   SERVICE-VERSION   INSTANCE                        ROLE        STATUS    AZ                NODE                                                       CREATED-TIME
mysql       8.0.35            example-mysql-cluster-mysql-0   primary     Running   ap-southeast-1c   ip-10-0-3-155.ap-southeast-1.compute.internal/10.0.3.155   Feb 10,2025 22:23 UTC+0800
mysql       8.0.35            example-mysql-cluster-mysql-1   secondary   Running   ap-southeast-1c   ip-10-0-3-204.ap-southeast-1.compute.internal/10.0.3.204   Feb 10,2025 22:23 UTC+0800
mysql       8.0.35            example-mysql-cluster-mysql-2   secondary   Running   ap-southeast-1c   ip-10-0-3-75.ap-southeast-1.compute.internal/10.0.3.75     Feb 10,2025 22:23 UTC+0800

Resources Allocation:
COMPONENT   INSTANCE-TEMPLATE   CPU(REQUEST/LIMIT)   MEMORY(REQUEST/LIMIT)   STORAGE-SIZE   STORAGE-CLASS
mysql                           500m / 500m          512Mi / 512Mi           data:20Gi      <none>

Images:
COMPONENT   COMPONENT-DEFINITION          IMAGE
mysql       mysql-mgr-8.0-1.0.0           docker.io/apecloud/mysql:8.0.35
                                          docker.io/apecloud/mysqld-exporter:0.15.1
                                          apecloud-registry.cn-zhangjiakou.cr.aliyuncs.com/apecloud/kubeblocks-tools:1.0.0

Show cluster events: kbcli cluster list-events -n demo example-mysql-cluster

Checking Cluster Roles

To verify the roles of the MySQL instances ('primary' and 'secondary'), use the following command:

kubectl get pods -n demo -o jsonpath='{range .items[*]}{.metadata.name}{"\t"}{.metadata.labels.kubeblocks\.io/role}{"\n"}{end}'

Example Output:

example-mysql-cluster-mysql-0	primary
example-mysql-cluster-mysql-1	secondary
example-mysql-cluster-mysql-2	secondary

Connecting to the MySQL Cluster

KubeBlocks automatically creates a secret containing the MySQL root credentials. Retrieve the credentials with the following commands:

kubectl get secrets -n demo example-mysql-cluster-mysql-account-root -o jsonpath='{.data.username}' | base64 -d
root

kubectl get secrets -n demo example-mysql-cluster-mysql-account-root -o jsonpath='{.data.password}' | base64 -d
q95G8nd87K

Connect to the Primary Node

To connect to the cluster's primary node, use the MySQL client:

kubectl exec -it -n demo example-mysql-cluster-mysql-0 -c mysql -- mysql -h example-mysql-cluster-mysql.demo.svc.cluster.local -uroot -pq95G8nd87K

Check Group Replication Status

Run the following query to check the status of the group replication cluster:

mysql> SELECT * FROM performance_schema.replication_group_members;

Example Output:

+---------------------------+--------------------------------------+--------------------------------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                                                        | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------------------------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a17c375d-e7ba-11ef-8b01-3aa4e0d3963f | example-mysql-cluster-mysql-1.example-mysql-cluster-mysql-headless |        3306 | ONLINE       | SECONDARY   | 8.0.35         | XCom                       |
| group_replication_applier | a99688a7-e7ba-11ef-be5b-de475d052d4a | example-mysql-cluster-mysql-0.example-mysql-cluster-mysql-headless |        3306 | ONLINE       | PRIMARY     | 8.0.35         | XCom                       |
| group_replication_applier | c4403516-e7ba-11ef-8f11-8a79c903edf0 | example-mysql-cluster-mysql-2.example-mysql-cluster-mysql-headless |        3306 | ONLINE       | SECONDARY   | 8.0.35         | XCom                       |
+---------------------------+--------------------------------------+--------------------------------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

The roles in the output should match the roles shown in the kubectl output.

Failover Testing

Trigger a Failover

To test the MySQL Group Replication failover mechanism, delete the primary node:

kubectl delete pod example-mysql-cluster-mysql-0 -n demo
pod "example-mysql-cluster-mysql-0" deleted

This triggers a failover, and one of the secondary nodes will be promoted to the primary role.

Verify the New Roles

Run the following command to check the updated roles:

kubectl get pods -n demo -o jsonpath='{range .items[*]}{.metadata.name}{"\t"}{.metadata.labels.kubeblocks\.io/role}{"\n"}{end}'

Example Output:

example-mysql-cluster-mysql-0
example-mysql-cluster-mysql-1	secondary
example-mysql-cluster-mysql-2	primary

Once the deleted pod ('example-mysql-cluster-mysql-0') is recreated, it will rejoin the cluster as a secondary node:

example-mysql-cluster-mysql-0	secondary
example-mysql-cluster-mysql-1	secondary
example-mysql-cluster-mysql-2	primary

This demonstrates how the failover mechanism ensures high availability by automatically promoting a secondary instance to the primary role in case of failure.

Cleanup

To remove all created resources, delete the MySQL cluster along with its namespace:

kubectl delete cluster example-mysql-cluster -n demo
kubectl delete ns demo

Summary

In this guide, you learned how to:

• Deploy a MySQL Group Replication cluster using KubeBlocks.
• Verify the cluster's state and role assignments.
• Connect to the primary node and check the replication status.
• Test the failover mechanism to ensure high availability. By leveraging KubeBlocks, managing MySQL Group Replication clusters in Kubernetes becomes efficient and straightforward, enabling you to achieve high availability and scalability for your database workloads.