Topologies
Operations
Backup And Restores
Custom Secret
Monitoring
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.
Before proceeding, ensure the following:
kubectl create ns demo
namespace/demo created
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:
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).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).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
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
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
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
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
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
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.
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.
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.
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
In this guide, you learned how to: