Deploying a MySQL Group Replication Cluster with ProxySQL Using KubeBlocks

MySQL Group Replication (MGR) ensures high availability and fault tolerance by synchronizing data across multiple MySQL instances. It provides automatic failover, promoting a secondary node to primary in case of failure, ensuring continuous availability.

ProxySQL is a high-performance MySQL proxy that acts as a middleware between MySQL clients and database servers. It provides features such as query routing, load balancing, query caching, and seamless failover. When combined with MGR, ProxySQL enhances cluster performance and enables efficient traffic management.

This guide explains how to deploy a MySQL Group Replication (MGR) cluster with ProxySQL integration using KubeBlocks, simplifying the process of managing 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 configuration approach to simplify MySQL cluster management. Below is an example configuration to deploy a MySQL Group Replication cluster with three MySQL nodes and two ProxySQL instances.

Apply the following YAML configuration:

kubectl apply -f - <<EOF
apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: example-mysql-cluster
  namespace: demo
spec:
  clusterDef: mysql
  topology: mgr-proxysql
  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
    - name: proxysql
      serviceVersion: 2.4.4
      replicas: 2
      resources:
        limits:
          cpu: '0.5'
          memory: 0.5Gi
        requests:
          cpu: '0.5'
          memory: 0.5Gi
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-proxysql: Configures the cluster for MySQL Group Replication with ProxySQL integration.
componentSpecs: Defines the components in the cluster:
- Component 'mysql':
  - serviceVersion: 8.0.35: Specifies the MySQL version to deploy. Here, version 8.0.35 is used.
  - replicas: 3: Sets the number of MySQL instances (in this case, 3).
- Component 'proxysql':
  - serviceVersion: 2.4.4: Specifies the ProxySQL version to deploy. Here, version 2.4.4 is used.
  - replicas: 2: Sets the number of ProxySQL instances (in this case, 2).

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   9s
example-mysql-cluster   mysql                Delete               Running    2m33s

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 11,2025 11:58 UTC+0800
NAMESPACE   CLUSTER-DEFINITION   TOPOLOGY       STATUS    TERMINATION-POLICY
demo        mysql                mgr-proxysql   Running   Delete

Endpoints:
COMPONENT   INTERNAL                                                                     EXTERNAL
mysql       example-mysql-cluster-mysql.demo.svc.cluster.local:3306                      <none>
proxysql    example-mysql-cluster-proxysql-proxy-ordinal-0.demo.svc.cluster.local:6032   <none>
            example-mysql-cluster-proxysql-proxy-ordinal-0.demo.svc.cluster.local:6033
            example-mysql-cluster-proxysql-proxy-ordinal-1.demo.svc.cluster.local:6032
            example-mysql-cluster-proxysql-proxy-ordinal-1.demo.svc.cluster.local:6033
            example-mysql-cluster-proxysql-proxy-server.demo.svc.cluster.local:6033

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-34.ap-southeast-1.compute.internal/10.0.3.34     Feb 11,2025 12:47 UTC+0800
mysql       8.0.35            example-mysql-cluster-mysql-1      secondary   Running   ap-southeast-1c   ip-10-0-3-228.ap-southeast-1.compute.internal/10.0.3.228   Feb 11,2025 12:47 UTC+0800
mysql       8.0.35            example-mysql-cluster-mysql-2      secondary   Running   ap-southeast-1c   ip-10-0-3-187.ap-southeast-1.compute.internal/10.0.3.187   Feb 11,2025 12:47 UTC+0800
proxysql    2.4.4             example-mysql-cluster-proxysql-0   <none>      Running   ap-southeast-1c   ip-10-0-3-228.ap-southeast-1.compute.internal/10.0.3.228   Feb 11,2025 12:49 UTC+0800
proxysql    2.4.4             example-mysql-cluster-proxysql-1   <none>      Running   ap-southeast-1c   ip-10-0-3-187.ap-southeast-1.compute.internal/10.0.3.187   Feb 11,2025 12:49 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>
proxysql                        500m / 500m          512Mi / 512Mi           <none>         <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-beta.26
proxysql    proxysql-mysql-1.0.0           docker.io/apecloud/proxysql:2.4.4

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

Checking Cluster Roles

To verify the roles of MySQL instances (e.g., primary and secondary), run:

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
XKNv07D612

Connect via ProxySQL

Use ProxySQL to connect to the MySQL cluster:

kubectl exec -it -n demo example-mysql-cluster-mysql-0 -c mysql -- mysql -h example-mysql-cluster-proxysql-proxy-server.demo.svc.cluster.local -P6033 -uroot -pXKNv07D612

Connect Directly to MySQL

Alternatively, connect directly to the MySQL instance:

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

Check Group Replication Status

To check the status of the Group Replication cluster, run the following query:

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 with ProxySQL using KubeBlocks.
Verify the cluster’s deployment and role assignments.
Connect to the cluster via ProxySQL or directly.
Check the replication status and test failover mechanisms. By combining MySQL Group Replication with ProxySQL, this setup ensures high availability, seamless failover, and efficient traffic management, making it ideal for production-grade deployments.

Deploying a MySQL Group Replication Cluster with ProxySQL Using KubeBlocks

This guide explains how to deploy a MySQL Group Replication (MGR) cluster with ProxySQL integration using KubeBlocks, simplifying the process of managing 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

Apply the following YAML configuration:

kubectl apply -f - <<EOF
apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: example-mysql-cluster
  namespace: demo
spec:
  clusterDef: mysql
  topology: mgr-proxysql
  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
    - name: proxysql
      serviceVersion: 2.4.4
      replicas: 2
      resources:
        limits:
          cpu: '0.5'
          memory: 0.5Gi
        requests:
          cpu: '0.5'
          memory: 0.5Gi
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-proxysql: Configures the cluster for MySQL Group Replication with ProxySQL integration.
componentSpecs: Defines the components in the cluster:
- Component 'mysql':
  - serviceVersion: 8.0.35: Specifies the MySQL version to deploy. Here, version 8.0.35 is used.
  - replicas: 3: Sets the number of MySQL instances (in this case, 3).
- Component 'proxysql':
  - serviceVersion: 2.4.4: Specifies the ProxySQL version to deploy. Here, version 2.4.4 is used.
  - replicas: 2: Sets the number of ProxySQL instances (in this case, 2).

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   9s
example-mysql-cluster   mysql                Delete               Running    2m33s

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 11,2025 11:58 UTC+0800
NAMESPACE   CLUSTER-DEFINITION   TOPOLOGY       STATUS    TERMINATION-POLICY
demo        mysql                mgr-proxysql   Running   Delete

Endpoints:
COMPONENT   INTERNAL                                                                     EXTERNAL
mysql       example-mysql-cluster-mysql.demo.svc.cluster.local:3306                      <none>
proxysql    example-mysql-cluster-proxysql-proxy-ordinal-0.demo.svc.cluster.local:6032   <none>
            example-mysql-cluster-proxysql-proxy-ordinal-0.demo.svc.cluster.local:6033
            example-mysql-cluster-proxysql-proxy-ordinal-1.demo.svc.cluster.local:6032
            example-mysql-cluster-proxysql-proxy-ordinal-1.demo.svc.cluster.local:6033
            example-mysql-cluster-proxysql-proxy-server.demo.svc.cluster.local:6033

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-34.ap-southeast-1.compute.internal/10.0.3.34     Feb 11,2025 12:47 UTC+0800
mysql       8.0.35            example-mysql-cluster-mysql-1      secondary   Running   ap-southeast-1c   ip-10-0-3-228.ap-southeast-1.compute.internal/10.0.3.228   Feb 11,2025 12:47 UTC+0800
mysql       8.0.35            example-mysql-cluster-mysql-2      secondary   Running   ap-southeast-1c   ip-10-0-3-187.ap-southeast-1.compute.internal/10.0.3.187   Feb 11,2025 12:47 UTC+0800
proxysql    2.4.4             example-mysql-cluster-proxysql-0   <none>      Running   ap-southeast-1c   ip-10-0-3-228.ap-southeast-1.compute.internal/10.0.3.228   Feb 11,2025 12:49 UTC+0800
proxysql    2.4.4             example-mysql-cluster-proxysql-1   <none>      Running   ap-southeast-1c   ip-10-0-3-187.ap-southeast-1.compute.internal/10.0.3.187   Feb 11,2025 12:49 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>
proxysql                        500m / 500m          512Mi / 512Mi           <none>         <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-beta.26
proxysql    proxysql-mysql-1.0.0           docker.io/apecloud/proxysql:2.4.4

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

Checking Cluster Roles

To verify the roles of MySQL instances (e.g., primary and secondary), run:

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
XKNv07D612

Connect via ProxySQL

Use ProxySQL to connect to the MySQL cluster:

kubectl exec -it -n demo example-mysql-cluster-mysql-0 -c mysql -- mysql -h example-mysql-cluster-proxysql-proxy-server.demo.svc.cluster.local -P6033 -uroot -pXKNv07D612

Connect Directly to MySQL

Alternatively, connect directly to the MySQL instance:

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

Check Group Replication Status

To check the status of the Group Replication cluster, run the following query:

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 with ProxySQL using KubeBlocks.
Verify the cluster’s deployment and role assignments.
Connect to the cluster via ProxySQL or directly.
Check the replication status and test failover mechanisms. By combining MySQL Group Replication with ProxySQL, this setup ensures high availability, seamless failover, and efficient traffic management, making it ideal for production-grade deployments.