Creating a MySQL Sharding Cluster and High Availability Features

![](https://lh5.googleusercontent.com/4WiSkxX-XBqARVqQ0No-1tZ31op90LAUkTco3FdIO1mFScNOTVtMCgnjaO8SRUmms-6MAb46CzxlXDhLBqAAAmbx26atJnu4t1FTTALZx_CbUPqrCxjL746DW4TD42-03Ac9VB2c) | | Note: Each shard corresponds to a single MySQL instance. Currently, WQ-RDS supports a maximum of 64 shards. | All of the shards are built with Kubernetes Statefulset, Services, Storage Class, configmap, secrets and MySQL. WQ-RDS manages the entire lifecycle of the sharding cluster. Advantages of the sharding cluster are obvious: * Scale out queries per second (QPS) and transactions per second (TPS) * Scale out storage capacity: gain more storage by distributing data to multiple nodes ### Create a MySQL Sharding Cluster Let's create a Kubernetes cluster with 8 shards. ``` kubectl create -f mysqlshardingcluster.yaml ``` Next, create a MySQL Sharding Cluster including 8 shards. * TPR : MysqlCluster and MysqlDatabase ``` [root@k8s-master ~]# kubectl get mysqlcluster NAME KIND clustershard-c MysqlCluster.v1.mysql.orain.com ``` MysqlDatabase from clustershard-c0 to clustershard-c7 belongs to MysqlCluster clustershard-c. ``` [root@k8s-master ~]# kubectl get mysqldatabase NAME KIND clustershard-c0 MysqlDatabase.v1.mysql.orain.com clustershard-c1 MysqlDatabase.v1.mysql.orain.com clustershard-c2 MysqlDatabase.v1.mysql.orain.com clustershard-c3 MysqlDatabase.v1.mysql.orain.com clustershard-c4 MysqlDatabase.v1.mysql.orain.com clustershard-c5 MysqlDatabase.v1.mysql.orain.com clustershard-c6 MysqlDatabase.v1.mysql.orain.com clustershard-c7 MysqlDatabase.v1.mysql.orain.com ``` Next, let's look at two main features: high availability and RollingUpdate strategy. To demonstrate, we'll start by running sysbench to generate some load on the cluster. In this example, QPS metrics are generated by MySQL export, collected by Prometheus, and visualized in Grafana. ### Feature: high availability WQ-RDS handles MySQL instance crashes while protecting against data loss. When killing clustershard-c0, WQ-RDS will detect that clustershard-c0 is unavailable and replace clustershard-c0 on failed machine, taking about 35 seconds on average.

This section describes the process of creating a MySQL sharding cluster with 8 shards using Kubernetes commands and WQ-RDS. It highlights the advantages of sharding, such as scaling out QPS/TPS and storage capacity. The post then focuses on the high availability feature of WQ-RDS, showcasing how it automatically detects and replaces crashed MySQL instances (e.g., clustershard-c0) to prevent data loss and maintain uptime.