KubeBlocks
BlogsKubeBlocks Cloud
Overview
Quickstart

Topologies

MySQL Semi-Synchronous Cluster
MySQL Cluster with ProxySQL
MySQL Group Replication Cluster
MySQL Group Replication with ProxySQL
MySQL Cluster with Orchestrator
MySQL with Orchestrator & ProxySQL

Operations

Lifecycle Management
Vertical Scaling
Horizontal Scaling
Volume Expansion
Manage MySQL Services
Minor Version Upgrade
Modify MySQL Parameters
Planned Switchover in MySQL
Decommission MySQL Replica
Recovering MySQL Replica

Backup And Restores

Create BackupRepo
Create Full Backup
Scheduled Backups
Scheduled Continuous Backup
Restore MySQL Cluster
Restore with PITR

Custom Secret

Custom Password
Custom Password Policy

TLS

MySQL Cluster with TLS
MySQL Cluster with User-Provided TLS
MySQL Cluster with mTLS

Monitoring

Observability for MySQL Clusters

Advanced Pod Management

Custom Scheduling Policies
Custom Pod Resources
Pod Management Parallelism
Using OnDelete for Controlled Pod Updates
Gradual Rolling Update
  1. Prerequisites
  2. Deploying the MySQL Semi-Synchronous Cluster
  3. Verifying the Deployment
    1. Check Cluster Status
    2. Detailed Cluster Information
  4. Connecting to the MySQL Cluster
    1. Connect via ProxySQL
    2. Connect Directly to MySQL
  5. Testing Semi-Synchronous Replication
    1. Verify Pod Roles
    2. Check Replication Status
    3. Primary Node
    4. Replica Node
  6. Failover Testing
    1. Trigger a Failover
    2. Verify the Updated Roles
  7. Cleanup
  8. Summary

Deploying a MySQL Semi-Synchronous Cluster and ProxySQL with KubeBlocks

Semi-synchronous replication enhances data consistency between the primary and replica nodes by ensuring that the primary waits for acknowledgment from at least one replica before committing transactions.

ProxySQL is a high-performance MySQL proxy that acts as a middleware between MySQL clients and servers. It provides advanced features such as query routing, load balancing, query caching, and high availability. When combined with a MySQL semi-synchronous cluster, ProxySQL ensures seamless failover and efficient traffic management, resulting in optimal performance and reliability.

This guide walks you through deploying a MySQL semi-synchronous replication cluster integrated with ProxySQL using KubeBlocks.

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 Semi-Synchronous Cluster

KubeBlocks uses a declarative configuration approach to manage MySQL clusters. Below is an example configuration for deploying a MySQL cluster with 2 MySQL nodes (1 primary, 1 replica) and 2 ProxySQL instances.

Apply the following configuration:

kubectl apply -f - <<EOF
apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: example-mysql-cluster
  namespace: demo
spec:
  clusterDef: mysql
  topology: semisync-proxysql
  terminationPolicy: Delete
  componentSpecs:
    - name: mysql
      serviceVersion: 8.0.35
      replicas: 2
      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 cluster. The ClusterDefinition 'mysql' contains multiple topologies, such as 'semisync', 'semisync-proxysql', 'mgr', 'mgr-proxysql', 'orc', 'orc-proxysql'.
  • topology: semisync-proxysql: Configures a semi-synchronous replication MySQL cluster 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: 2: Sets the number of MySQL instances (in this case, 2).
    • 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

Check Cluster Status

Once the cluster is deployed, check its status:

kubectl get cluster -n demo -w

Expected Output:

NAME                    CLUSTER-DEFINITION   TERMINATION-POLICY   STATUS    AGE
example-mysql-cluster   mysql                Delete               Creating   8s
example-mysql-cluster   mysql                Delete               Running   3m1s

Detailed Cluster Information

To get detailed information about the cluster:

kbcli cluster describe example-mysql-cluster -n demo

Expected Output:

Name: example-mysql-cluster	 Created Time: Feb 10,2025 08:32 UTC+0800
NAMESPACE   CLUSTER-DEFINITION   TOPOLOGY            STATUS    TERMINATION-POLICY
demo        mysql                semisync-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-1b   ip-10-0-2-221.ap-southeast-1.compute.internal/10.0.2.221   Feb 10,2025 08:32 UTC+0800
mysql       8.0.35            example-mysql-cluster-mysql-1      secondary   Running   ap-southeast-1a   ip-10-0-1-188.ap-southeast-1.compute.internal/10.0.1.188   Feb 10,2025 08:32 UTC+0800
proxysql    2.4.4             example-mysql-cluster-proxysql-0   <none>      Running   ap-southeast-1b   ip-10-0-2-221.ap-southeast-1.compute.internal/10.0.2.221   Feb 10,2025 08:34 UTC+0800
proxysql    2.4.4             example-mysql-cluster-proxysql-1   <none>      Running   ap-southeast-1a   ip-10-0-1-188.ap-southeast-1.compute.internal/10.0.1.188   Feb 10,2025 08:34 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-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

Data Protection:
BACKUP-REPO   AUTO-BACKUP   BACKUP-SCHEDULE   BACKUP-METHOD   BACKUP-RETENTION   RECOVERABLE-TIME

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

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
22mue70Hx6

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 -p22mue70Hx6

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 -p22mue70Hx6

Testing Semi-Synchronous Replication

Verify Pod Roles

List all pods in the cluster and check their roles:

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

Expected Output:

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

Check Replication Status

Verify the replication status for the primary and replica nodes:

Primary Node

Run the following command on the primary node:

kubectl exec -it -n demo example-mysql-cluster-mysql-0 -c mysql -- mysql -h example-mysql-cluster-mysql-0.example-mysql-cluster-mysql-headless.demo.svc.cluster.local -uroot -p22mue70Hx6 -e "show status like 'Rpl%_status';"

Expected Output:

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | ON    |
| Rpl_semi_sync_source_status  | OFF   |
+------------------------------+-------+

Explanation:

  • "Rpl_semi_sync_replica_status: ON": This indicates that the secondary instance is acting as a semi-synchronous replica and is actively receiving and acknowledging changes from the primary instance.
  • "Rpl_semi_sync_source_status: OFF": This indicates that the secondary instance is not acting as a source (or master) in the replication setup.

Replica Node

Run the following command on the replica node:

kubectl exec -it -n demo example-mysql-cluster-mysql-0 -c mysql -- mysql -h example-mysql-cluster-mysql-1.example-mysql-cluster-mysql-headless.demo.svc.cluster.local -uroot -p22mue70Hx6 -e "show status like 'Rpl%_status';"

Expected Output:

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | OFF   |
| Rpl_semi_sync_source_status  | ON    |
+------------------------------+-------+

Explanation:

  • "Rpl_semi_sync_source_status: ON": This indicates that the primary instance is configured for semi-synchronous replication as the source (or master).
  • "Rpl_semi_sync_replica_status: OFF": This indicates that the primary instance is not acting as a replica in the replication setup.

Failover Testing

Trigger a Failover

To test the failover mechanism, delete the primary pod:

kubectl delete pod example-mysql-cluster-mysql-1 -n demo

This will trigger a failover, and the secondary instance will be promoted to the primary role. You can verify the new roles of the pods:

Verify the Updated Roles

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

Expected Output:

example-mysql-cluster-mysql-0	primary
example-mysql-cluster-mysql-1

After some time, the deleted pod will be recreated and rejoin the cluster as a replica:

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

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 semi-synchronous replication cluster with ProxySQL using KubeBlocks.
  • Verify the cluster's roles and replication status.
  • Test failover mechanisms for high availability. By combining a MySQL semi-synchronous cluster with ProxySQL, you can achieve seamless failover, efficient traffic management, and enhanced reliability for production-grade deployments.

© 2025 ApeCloud PTE. Ltd.