Create and connect to a MySQL cluster

This tutorial shows how to create and connect to a MySQL cluster.

Create a MySQL cluster

Before you start

• Install kbcli.

• Install KubeBlocks by kbcli or by Helm.

• Make sure the MySQL Addon is enabled. The MySQL Addon is installed and enabled by KubeBlocks by default. If you disable it when installing KubeBlocks, enable it first.

  kbcli

  kbcli addon list
  >
  NAME                           VERSION         PROVIDER    STATUS     AUTO-INSTALL
  ...
  mysql                          0.9.1           community   Enabled    true
  ...

  kubectl

  kubectl get addons.extensions.kubeblocks.io mysql
  >
  NAME    TYPE   VERSION   PROVIDER   STATUS    AGE
  mysql   Helm                        Enabled   27h

• View all the database types and versions available for creating a cluster.

  kbcli

  kbcli clusterdefinition list
  kbcli clusterversion list

  kubectl

  Make sure the mysql cluster definition is installed.

  kubectl get clusterdefinition mysql
  >
  NAME             TOPOLOGIES   SERVICEREFS   STATUS      AGE
  mysql                                       Available   85m

  View all available versions for creating a cluster.

  kubectl get clusterversions -l clusterdefinition.kubeblocks.io/name=mysql
  >
  NAME           CLUSTER-DEFINITION   STATUS      AGE
  mysql-5.7.44   mysql                Available   27h
  mysql-8.0.33   mysql                Available   27h
  mysql-8.4.2    mysql                Available   27h

• To keep things isolated, create a separate namespace called demo throughout this tutorial.

  kubectl create namespace demo

Create a cluster

KubeBlocks supports creating two types of MySQL clusters: Standalone and Replication Cluster. Standalone only supports one replica and can be used in scenarios with lower requirements for availability. For scenarios with high availability requirements, it is recommended to create a Replication Cluster, which creates a cluster with two replicas. To ensure high availability, all replicas are distributed on different nodes by default.

kbcli

1. Create a MySQL cluster.

   kbcli cluster create mycluster --cluster-definition mysql -n demo

   If you want to customize your cluster specifications, kbcli provides various options, such as setting cluster version, termination policy, CPU, and memory. You can view these options by adding --help or -h flag.

   kbcli cluster create mysql --help
   kbcli cluster create mysql -h

   If you only have one node for deploying a Replication Cluster, set the --topology-keys as null when creating a Cluster. But you should note that for a production environment, it is not recommended to deploy all replicas on one node, which may decrease the cluster availability.

   kbcli cluster create mycluster --cluster-definition mysql --topology-keys null -n demo

2. Verify whether this cluster is created successfully.

   kbcli cluster list -n demo
   >
   NAME        NAMESPACE   CLUSTER-DEFINITION   VERSION           TERMINATION-POLICY   STATUS    CREATED-TIME
   mycluster   demo        mysql                mysql-8.0.30      Delete               Running   Jul 05,2024 18:46 UTC+0800

kubectl

1. Create a MySQL cluster.

   KubeBlocks implements a Cluster CRD to define a cluster. Here is an example of creating a Replication Cluster.

   If you only have one node for deploying a Replication Cluster, set spec.affinity.topologyKeys as null. But for a production environment, it is not recommended to deploy all replicas on one node, which may decrease the cluster availability.

   cat <<EOF | kubectl apply -f -
   apiVersion: apps.kubeblocks.io/v1alpha1
   kind: Cluster
   metadata:
     name: mycluster
     namespace: demo
   spec:
     clusterDefinitionRef: mysql
     clusterVersionRef: mysql-8.0.33
     terminationPolicy: Delete
     affinity:
       podAntiAffinity: Preferred
       topologyKeys:
       - kubernetes.io/hostname
     tolerations:
       - key: kb-data
         operator: Equal
         value: 'true'
         effect: NoSchedule
     componentSpecs:
     - name: mysql
       componentDefRef: mysql
       enabledLogs:
       - error
       - slow
       disableExporter: true
       replicas: 2
       serviceAccountName: kb-mycluster
       resources:
         limits:
           cpu: '0.5'
           memory: 0.5Gi
         requests:
           cpu: '0.5'
           memory: 0.5Gi
       volumeClaimTemplates:
       - name: data
         spec:
           accessModes:
           - ReadWriteOnce
           resources:
             requests:
               storage: 20Gi
   EOF

   Field	Definition
   spec.clusterDefinitionRef	It specifies the name of the ClusterDefinition for creating a specific type of cluster.
   spec.clusterVersionRef	It is the name of the cluster version CRD that defines the cluster version.
   spec.terminationPolicy	It is the policy of cluster termination. The default value is Delete. Valid values are DoNotTerminate, Delete, WipeOut. For the detailed definition, you can refer to Termination Policy.
   spec.affinity	It defines a set of node affinity scheduling rules for the cluster's Pods. This field helps control the placement of Pods on nodes within the cluster.
   spec.affinity.podAntiAffinity	It specifies the anti-affinity level of Pods within a component. It determines how pods should spread across nodes to improve availability and performance.
   spec.affinity.topologyKeys	It represents the key of node labels used to define the topology domain for Pod anti-affinity and Pod spread constraints.
   spec.tolerations	It is an array that specifies tolerations attached to the cluster's Pods, allowing them to be scheduled onto nodes with matching taints.
   spec.componentSpecs	It is the list of components that define the cluster components. This field allows customized configuration of each component within a cluster.
   spec.componentSpecs.componentDefRef	It is the name of the component definition that is defined in the cluster definition and you can get the component definition names with kubectl get clusterdefinition mysql -o json \| jq '.spec.componentDefs[].name'.
   spec.componentSpecs.name	It specifies the name of the component.
   spec.componentSpecs.disableExporter	It defines whether the monitoring function is enabled.
   spec.componentSpecs.replicas	It specifies the number of replicas of the component.
   spec.componentSpecs.resources	It specifies the resource requirements of the component.

   For the details of different parameters, you can refer to API docs.

   Run the following commands to see the created MySQL cluster object:

   kubectl get cluster mycluster -n demo -o yaml

2. Verify whether this cluster is created successfully.

   kubectl get cluster mycluster -n demo
   >
   NAME        CLUSTER-DEFINITION   VERSION           TERMINATION-POLICY   STATUS    AGE
   mycluster   mysql                mysql-8.0.30      Delete               Running   6m53s

Connect to a MySQL Cluster

kbcli

kbcli cluster connect mycluster  --namespace demo

kubectl

You can use kubectl exec to exec into a Pod and connect to a database.

KubeBlocks operator creates a new Secret called mycluster-conn-credential to store the connection credential of the MySQL cluster. This secret contains the following keys:

• username: the root username of the MySQL cluster.
• password: the password of the root user.
• port: the port of the MySQL cluster.
• host: the host of the MySQL cluster.
• endpoint: the endpoint of the MySQL cluster and it is the same as host:port.

1. Run the command below to get the username and password for the kubectl exec command.

   kubectl get secrets mycluster-conn-credential -n demo -o jsonpath='{.data.username}' | base64 -d
   >
   root

   kubectl get secrets mycluster-conn-credential -n demo -o jsonpath='{.data.password}' | base64 -d
   >
   b8wvrwlm

2. Exec into the Pod mycluster-mysql-0 and connect to the database using username and password.

   kubectl exec -ti mycluster-mysql-0 -n demo -- bash
   
   mysql -u root -p b8wvrwlm

port-forward

You can also port forward the service to connect to a database from your local machine.

1. Run the following command to port forward the service.

   kubectl port-forward svc/mycluster-mysql 3306:3306 -n demo

2. Open a new terminal and run the following command to connect to the database.

   mysql -uroot -pb8wvrwlm

For the detailed database connection guide, refer to Connect database.