You are looking at the documentation of a prior release. To read the documentation of the latest release, please
visit here.
New to KubeDB? Please start here.
Connecting to a MySQL Cluster
KubeDB creates separate services for primary and secondary replicas. In this tutorial, we are going to show you how to connect your application with primary or secondary replicas using those services.
Before You Begin
Read mysql group replication concept to learn about MySQL Group Replication.
You need to have a Kubernetes cluster, and the kubectl command-line tool must be configured to communicate with your cluster. If you do not already have a cluster, you can create one by using kind.
Now, install KubeDB cli on your workstation and KubeDB operator in your cluster following the steps here.
To keep things isolated, this tutorial uses a separate namespace called
demothroughout this tutorial. Run the following command to prepare your cluster for this tutorial:$ kubectl create ns demo namespace/demo createdYou need to have a mysql client. If you don’t have a mysql client install in your local machine, you can install from here
Note: YAML files used in this tutorial are stored in guides/mysql/clients/yamls folder in GitHub repository kubedb/docs.
Deploy MySQL Cluster
Here, we are going to deploy a MySQL group replication using a supported version by KubeDB operator. Then we are going to connect with the cluster using two separate services.
Below is the YAML of MySQL group replication with 3 members (one is a primary member and the two others are secondary members) that we are going to create.
apiVersion: kubedb.com/v1alpha2
kind: MySQL
metadata:
name: my-group
namespace: demo
spec:
version: "8.0.27"
replicas: 3
topology:
mode: GroupReplication
group:
name: "dc002fc3-c412-4d18-b1d4-66c1fbfbbc9b"
storageType: Durable
storage:
storageClassName: "standard"
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
terminationPolicy: WipeOut
Let’s create the MySQL CR we have shown above,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2022.10.18/docs/guides/mysql/clients/yamls/group-replication.yaml
mysql.kubedb.com/my-group created
KubeDB operator watches for MySQL objects using Kubernetes API. When a MySQL object is created, KubeDB operator will create a new StatefulSet and two separate Services for client connection with the cluster. The services have the following format:
<mysql-object-name>has both read and write operation.<mysql-object-name>-standbyhas only read operation.
Now, wait for the MySQL is going to Running state and also wait for SatefulSet and services going to the Ready state.
$ watch -n 3 kubectl get my -n demo my-group
Every 3.0s: kubectl get my -n demo my-group suaas-appscode: Wed Sep 9 10:54:34 2020
NAME VERSION STATUS AGE
my-group 8.0.27 Running 16m
$ watch -n 3 kubectl get sts -n demo my-group
ery 3.0s: kubectl get sts -n demo my-group suaas-appscode: Wed Sep 9 10:53:52 2020
NAME READY AGE
my-group 3/3 15m
$ kubectl get service -n demo
my-group ClusterIP 10.109.133.141 <none> 3306/TCP 31s
my-group-pods ClusterIP None <none> 3306/TCP 31s
my-group-standby ClusterIP 10.110.47.184 <none> 3306/TCP 31s
If you describe the object, you can find more details here,
$ kubectl dba describe my -n demo my-group
Name: my-group
Namespace: demo
CreationTimestamp: Mon, 15 Mar 2021 18:18:35 +0600
Labels: <none>
Annotations: kubectl.kubernetes.io/last-applied-configuration={"apiVersion":"kubedb.com/v1alpha2","kind":"MySQL","metadata":{"annotations":{},"name":"my-group","namespace":"demo"},"spec":{"replicas":3,"storage":{"...
Replicas: 3 total
Status: Provisioning
StorageType: Durable
Volume:
StorageClass: standard
Capacity: 1Gi
Access Modes: RWO
Paused: false
Halted: false
Termination Policy: WipeOut
StatefulSet:
Name: my-group
CreationTimestamp: Mon, 15 Mar 2021 18:18:35 +0600
Labels: app.kubernetes.io/component=database
app.kubernetes.io/instance=my-group
app.kubernetes.io/managed-by=kubedb.com
app.kubernetes.io/name=mysqls.kubedb.com
Annotations: <none>
Replicas: 824635568200 desired | 3 total
Pods Status: 2 Running / 1 Waiting / 0 Succeeded / 0 Failed
Service:
Name: my-group
Labels: app.kubernetes.io/component=database
app.kubernetes.io/instance=my-group
app.kubernetes.io/managed-by=kubedb.com
app.kubernetes.io/name=mysqls.kubedb.com
Annotations: <none>
Type: ClusterIP
IP: 10.109.133.141
Port: primary 3306/TCP
TargetPort: db/TCP
Endpoints: 10.244.0.15:3306
Service:
Name: my-group-pods
Labels: app.kubernetes.io/component=database
app.kubernetes.io/instance=my-group
app.kubernetes.io/managed-by=kubedb.com
app.kubernetes.io/name=mysqls.kubedb.com
Annotations: <none>
Type: ClusterIP
IP: None
Port: db 3306/TCP
TargetPort: db/TCP
Endpoints: 10.244.0.15:3306,10.244.0.17:3306,10.244.0.19:3306
Service:
Name: my-group-standby
Labels: app.kubernetes.io/component=database
app.kubernetes.io/instance=my-group
app.kubernetes.io/managed-by=kubedb.com
app.kubernetes.io/name=mysqls.kubedb.com
Annotations: <none>
Type: ClusterIP
IP: 10.110.47.184
Port: standby 3306/TCP
TargetPort: db/TCP
Endpoints: 10.244.0.17:3306
Auth Secret:
Name: my-group-auth
Labels: app.kubernetes.io/component=database
app.kubernetes.io/instance=my-group
app.kubernetes.io/managed-by=kubedb.com
app.kubernetes.io/name=mysqls.kubedb.com
Annotations: <none>
Type: kubernetes.io/basic-auth
Data:
username: 4 bytes
password: 16 bytes
AppBinding:
Metadata:
Annotations:
kubectl.kubernetes.io/last-applied-configuration: {"apiVersion":"kubedb.com/v1alpha2","kind":"MySQL","metadata":{"annotations":{},"name":"my-group","namespace":"demo"},"spec":{"replicas":3,"storage":{"accessModes":["ReadWriteOnce"],"resources":{"requests":{"storage":"1Gi"}},"storageClassName":"standard"},"storageType":"Durable","terminationPolicy":"WipeOut","topology":{"group":{"name":"dc002fc3-c412-4d18-b1d4-66c1fbfbbc9b"},"mode":"GroupReplication"},"version":"8.0.27"}}
Creation Timestamp: 2021-03-15T12:18:35Z
Labels:
app.kubernetes.io/component: database
app.kubernetes.io/instance: my-group
app.kubernetes.io/managed-by: kubedb.com
app.kubernetes.io/name: mysqls.kubedb.com
Name: my-group
Namespace: demo
Spec:
Client Config:
Service:
Name: my-group
Path: /
Port: 3306
Scheme: mysql
URL: tcp(my-group:3306)/
Parameters:
API Version: appcatalog.appscode.com/v1alpha1
Kind: StashAddon
Stash:
Addon:
Backup Task:
Name: mysql-backup-8.0.27
Params:
Name: args
Value: --all-databases --set-gtid-purged=OFF
Restore Task:
Name: mysql-restore-8.0.27
Secret:
Name: my-group-auth
Type: kubedb.com/mysql
Version: 8.0.27
Events:
Type Reason Age From Message
---- ------ ---- ---- -------
Normal Successful 2m KubeDB Operator Successfully created governing service
Normal Successful 2m KubeDB Operator Successfully created service for primary/standalone
Normal Successful 2m KubeDB Operator Successfully created service for secondary replicas
Normal Successful 2m KubeDB Operator Successfully created database auth secret
Normal Successful 2m KubeDB Operator Successfully created StatefulSet
Normal Successful 2m KubeDB Operator Successfully created appbinding
Normal Successful 2m KubeDB Operator Successfully patched StatefulSet
Our database cluster is ready to connect.
How KubeDB distinguish between primary and secondary Replicas
KubeDB add a sidecar into the pod beside the database container. This sidecar is used to add a label into the pod to distinguish between primary and secondary replicas. The label is added into the pods as follows:
mysql.kubedb.com/role:primaryare added for primary.mysql.kubedb.com/role:secondaryare added for secondary.
Let’s verify that the mysql.kubedb.com/role:<primary/secondary> label are added into the StatefulSet’s replicas,
$ kubectl get pods -n demo -l app.kubernetes.io/name=mysqls.kubedb.com,app.kubernetes.io/instance=my-group -A -o=custom-columns='Name:.metadata.name,Labels:metadata.labels,PodIP:.status.podIP'
Name Labels PodIP
my-group-0 map[controller-revision-hash:my-group-55b9f49f98 app.kubernetes.io/name:mysqls.kubedb.com app.kubernetes.io/instance:my-group mysql.kubedb.com/role:primary statefulset.kubernetes.io/pod-name:my-group-0] 10.244.1.8
my-group-1 map[controller-revision-hash:my-group-55b9f49f98 app.kubernetes.io/name:mysqls.kubedb.com app.kubernetes.io/instance:my-group mysql.kubedb.com/role:secondary statefulset.kubernetes.io/pod-name:my-group-1] 10.244.2.11
my-group-2 map[controller-revision-hash:my-group-55b9f49f98 app.kubernetes.io/name:mysqls.kubedb.com app.kubernetes.io/instance:my-group mysql.kubedb.com/role:secondary statefulset.kubernetes.io/pod-name:my-group-2] 10.244.2.13
You can see from the above output that the my-group-0 pod is selected as a primary member in our existing database cluster. It has the mysql.kubedb.com/role:primary label and the podIP is 10.244.1.8. Besides, the rest of the replicas are selected as a secondary member which has mysql.kubedb.com/role:secondary label.
KubeDB creates two separate services(already shown above) to connect with the database cluster. One for connecting to the primary replica and the other for secondaries. The service who is dedicated to connecting to the primary has both permissions of read and write operation and the service who is dedicated to other secondaries has only the permission of read operation.
You can find the service which selects for primary replica have the following selector,
$ kubectl get svc -n demo my-group -o json | jq '.spec.selector'
{
"app.kubernetes.io/instance": "my-group",
"app.kubernetes.io/managed-by": "kubedb.com",
"app.kubernetes.io/name": "mysqls.kubedb.com",
"kubedb.com/role": "primary"
}
If you get the endpoint of the above service, you will see the podIP of the primary replica,
$ kubectl get endpoints -n demo my-group
NAME ENDPOINTS AGE
my-group 10.244.1.8:3306 5h49m
You can also find the service which selects for secondary replicas have the following selector,
$ kubectl get svc -n demo my-group-replicas -o json | jq '.spec.selector'
{
"app.kubernetes.io/name": "mysqls.kubedb.com",
"app.kubernetes.io/instance": "my-group",
"mysql.kubedb.com/role": "secondary"
}
If you get the endpoint of the above service, you will see the podIP of the secondary replicas,
$ kubectl get endpoints -n demo my-group-replicas
NAME ENDPOINTS AGE
my-group-replicas 10.244.2.11:3306,10.244.2.13:3306 5h53m
Connecting Information
KubeDB operator has created a new Secret called my-group-auth (format: {mysql-object-name}-auth) for storing the password for mysql superuser. This secret contains a username key which contains the username for MySQL superuser and a password key which contains the password for MySQL superuser.
Now, you can connect to this database from your terminal using the mysql user and password.
$ kubectl get secrets -n demo my-group-auth -o jsonpath='{.data.\username}' | base64 -d
root
$ kubectl get secrets -n demo my-group-auth -o jsonpath='{.data.\password}' | base64 -d
RmxLjEomvE6tVj4-
You can connect to any of these group members. In that case, you just need to specify the hostname of that member Pod (either PodIP or the fully-qualified-domain-name for that Pod using any of the services) by --host flag.
Connecting with Primary Replica
The primary replica has both the permission of read and write operation. So the clients are able to perform both operations using the service my-group which select primary replica(already shown above). First, we will insert data into the database cluster, then we will see whether we insert into the cluster using my-group service.
At first, we are going to port-forward the service to connect to the database cluster from the outside of the cluster.
Let’s port-forward the my-group service using the following command,
$ kubectl port-forward service/my-group -n demo 8081:3306
Forwarding from 127.0.0.1:8081 -> 3306
Forwarding from [::1]:8081 -> 3306
For testing purpose, we need to have a mysql client to connect with the cluster. If you don’t have a client in your local machine, you can install from here
Write Operation :
# create a database on cluster
$ mysql -uroot -pRmxLjEomvE6tVj4- --port=8081 --host=127.0.0.1 -e "CREATE DATABASE playground;"
mysql: [Warning] Using a password on the command line interface can be insecure.
# create a table
$ mysql -uroot -pRmxLjEomvE6tVj4- --port=8081 --host=127.0.0.1 -e "CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));"
mysql: [Warning] Using a password on the command line interface can be insecure.
# insert a row
$ mysql -uroot -pRmxLjEomvE6tVj4- --port=8081 --host=127.0.0.1 -e "INSERT INTO playground.equipment (type, quant, color) VALUES ('slide', 2, 'blue');"
mysql: [Warning] Using a password on the command line interface can be insecure.
Read Operation :
# read data from cluster
$ mysql -uroot -pRmxLjEomvE6tVj4- --port=8081 --host=127.0.0.1 -e "SELECT * FROM playground.equipment;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-------+-------+
| id | type | quant | color |
+----+-------+-------+-------+
| 1 | slide | 2 | blue |
+----+-------+-------+-------+
You can see from the above output that both write and read operations are performed successfully using primary pod selector service named my-group.
Connection with Secondary Replicas
The secondary replica has only the permission of read operation. So the clients are able to perform only read operation using the service my-group-replicas which select only secondary replicas(already shown above). First, we will try to insert data into the database cluster, then we will read existing data from the cluster using my-group-replicas service.
At first, we are going to port-forward the service to connect to the database cluster from the outside of the cluster.
Let’s port-forward the my-group-replicas service using the following command,
$ kubectl port-forward service/my-group-replicas -n demo 8080:3306
Forwarding from 127.0.0.1:8080 -> 3306
Forwarding from [::1]:8080 -> 3306
Write Operation:
# in our database cluster we have created a database and a table named playground and equipment respectively. so we will try to insert data into it.
# insert a row
$ mysql -uroot -pRmxLjEomvE6tVj4- --port=8080 --host=127.0.0.1 -e "INSERT INTO playground.equipment (type, quant, color) VALUES ('slide', 3, 'black');"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement
Read Operation:
# read data from cluster
$ mysql -uroot -pRmxLjEomvE6tVj4- --port=8080 --host=127.0.0.1 -e "SELECT * FROM playground.equipment;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-------+-------+
| id | type | quant | color |
+----+-------+-------+-------+
| 1 | slide | 2 | blue |
+----+-------+-------+-------+
You can see from the above output that only read operations are performed successfully using secondary pod selector service named my-group-replicas. No data is inserted by using this service. The error --super-read-only indicates that the secondary pod has only read permission.
Automatic Failover
To test automatic failover, we will force the primary Pod to restart. Since the primary member (Pod) becomes unavailable, the rest of the members will elect a new primary for these group. When the old primary comes back, it will join the group as a secondary member.
First, delete the primary pod my-gorup-0 using the following command,
$ kubectl delete pod my-group-0 -n demo
pod "my-group-0" deleted
Now wait for a few minute to automatically elect the primary replica and also wait for the services endpoint update for new primary and secondary replicas,
$ kubectl get pods -n demo -l app.kubernetes.io/name=mysqls.kubedb.com,app.kubernetes.io/instance=my-group -A -o=custom-columns='Name:.metadata.name,Labels:metadata.labels,PodIP:.status.podIP'
Name Labels PodIP
my-group-0 map[controller-revision-hash:my-group-55b9f49f98 app.kubernetes.io/name:mysqls.kubedb.com app.kubernetes.io/instance:my-group mysql.kubedb.com/role:secondary statefulset.kubernetes.io/pod-name:my-group-0] 10.244.2.18
my-group-1 map[controller-revision-hash:my-group-55b9f49f98 app.kubernetes.io/name:mysqls.kubedb.com app.kubernetes.io/instance:my-group mysql.kubedb.com/role:secondary statefulset.kubernetes.io/pod-name:my-group-1] 10.244.2.11
my-group-2 map[controller-revision-hash:my-group-55b9f49f98 app.kubernetes.io/name:mysqls.kubedb.com app.kubernetes.io/instance:my-group mysql.kubedb.com/role:primary statefulset.kubernetes.io/pod-name:my-group-2] 10.244.2.13
You can see from the above output that my-group-2 pod is elected as a primary automatically and the others become secondary.
If you get the endpoint of the my-group service, you will see the podIP of the primary replica,
$ kubectl get endpoints -n demo my-group
NAME ENDPOINTS AGE
my-group 10.244.2.13:3306 111m
If you get the endpoint of the my-group-replicas service, you will see the podIP of the secondary replicas,
$ kubectl get endpoints -n demo my-group-replicas
NAME ENDPOINTS AGE
my-group-replicas 10.244.2.11:3306,10.244.2.18:3306 112m
Cleaning up
Clean what you created in this tutorial.
$ kubectl patch -n demo my/my-group -p '{"spec":{"terminationPolicy":"WipeOut"}}' --type="merge"
$ kubectl delete -n demo my/my-group
$ kubectl delete ns demo
Next Steps
- Detail concepts of MySQL object.
- Detail concepts of MySQLDBVersion object.
- Want to hack on KubeDB? Check our contribution guidelines.