Kubernetes Deployment: Deploying MySQL databases on the GKE

Deploy MySQL database on the GKE

This blog marks the first installment in this comprehensive five-part series on Kubernetes deployments. The remaining four parts are outlined below.

Kubernetes Deployment: Deploy NodeJS application on the GKE

Kubernetes Deployment: Automating NodeJS Deployment on GKE with GitHub Actions

Kubernetes Deployment: Deploy ReactJS application on the GKE

Kubernetes Deployment: GKE ReactJS Automation via GitHub Actions

In this first blog, we will deploy the MySQL database on GKE as a single replica with a persistent volume claim, which means that the database can store data persistently even if the Pod or Node it is running on is terminated or restarted. This deployment will provide a more robust and reliable setup for storing data that can be accessed by other applications running on the same Kubernetes cluster. We will cover the steps required to set up the GKE cluster, create and configure a persistent volume, configure the MySQL deployment to use the persistent volume claim and expose it as a service so that it can be accessed by other pods in the cluster. By the end of this blog, you will have a working MySQL database that can store data persistently and be used as a data store for your Kubernetes-based applications.

Before proceeding with this blog, it is recommended that you have a basic understanding of Docker and Kubernetes, including the concepts of deployments, services, pods, persistent volume claims, persistent volumes, secrets, config maps, and clusters. A basic understanding of these concepts will help you follow along with the steps in this blog and better understand the reasons behind each step.

To complete the steps in this blog, you will need access to a Google Cloud Platform (GCP) account. If you do not have a GCP account, you can create one for free using this link. As of the time of writing, GCP is offering a free tier account that provides you with $300 worth of free credits for 90 days. This free tier account will give you access to all the GCP services required to follow along with the steps in this blog, including creating and managing a GKE cluster, creating a persistent volume claim, and deploying a MySQL database on the cluster.

Create GCP Project and enable the APIs

For better management of resources, we will create a project named Kubernetes Applications Demo. This step is optional as you can use the default created project My First Project or any other project created by you.

To start the project creation, you can open the Cloud Shell by clicking on the Activate Cloud Shell button in the top right corner of the GCP console. Once the Cloud Shell is open, you can create a new GCP project using the following command.

gcloud projects create kubernetes-applications-demo --name="Kubernetes Appications Demo"

Once the project creation is done successfully, we need to link the billing account with the newly created project. Use the following command to get the list of available billing accounts.

gcloud beta billing accounts list 

# Returns following response
ACCOUNT_ID: <billing-account-no>
NAME: My Billing Account
OPEN: True
MASTER_ACCOUNT_ID:

Use the following command to link the project with any billing account as per your preference. For more information, please refer to the link.

gcloud beta billing projects link kubernetes-applications-demo --billing-account <billing-account-no>

Now set the project as the default one using the following command.

gcloud config set project kubernetes-applications-demo

Enabling the container.googleapis.com API is necessary for using Google Kubernetes Engine (GKE) because this API provides the necessary infrastructure and services to create and manage Kubernetes clusters on GCP. It allows you to perform a variety of tasks, such as creating and managing nodes, scaling node pools, deploying and updating applications, and managing networking and security.

Please use the following command to enable the API.

gcloud services enable container.googleapis.com

Creating the GKE cluster

A GKE cluster is a group of virtual machines (VMs) managed by Google and runs containerized applications. It allows you to easily deploy, manage, and scale your applications without having to worry about the underlying infrastructure. You can think of it as a ready-to-use computing environment that can help you run your applications with ease.
please refer to this link for more details about the cluster and its architecture.

There are two ways we can create a cluster autopilot and standard. Right now we are going with autopilot configuration, for more details about cluster modes, please refer to this link.

Use the following command to create the cluster, and choose the region you prefer.

gcloud container clusters create-auto kubernetes-demo --region=asia-south1

Once the cluster creation process is done, you can check the created cluster using the following command.

gcloud container clusters list

# Command returns following response
NAME: kubernetes-demo
LOCATION: asia-south1
MASTER_VERSION: 1.24.9-gke.3200
MASTER_IP: 34.93.42.221
MACHINE_TYPE: e2-medium
NODE_VERSION: 1.24.9-gke.3200
NUM_NODES: 2
STATUS: RUNNING

GKE Cluster

Once the cluster is created for further resource creation, cluster credentials are required; use the following command to get the cluster credentials. Please do remember whenever you reopen the Cloud shell need to add this command so that you get access to manage the resources.

gcloud container clusters get-credentials kubernetes-demo 
--region asia-south1 --project kubernetes-applications-demo

Create a namespace

Namespaces are used to manage the resources, usually when working on different environments such as development, stage, and production. These environment-specific files are differentiated using the namespaces.

Please use the following commands to create the namespace and set it as default.

kubectl create namespace development

kubectl config set-context --current --namespace=development

Create a folder for configuration management

To keep all the MySQL configuration files in the same place, create a folder mysql-config and move into the folder using the following commands.

mkdir mysql-config
cd mysql-config

For the resource configuration file creation, we can use the terminal-based commands or use the editor provided by the GCP.

Kubernetes Configuration

Now let’s proceed toward creating Kubernetes configuration files that will be used to create the Kubernetes resources.

We are going to create the following configurations.

• Secret
• Persistent Volume Claim
• ConfigMap
• Deployment
• Service

Secret:

A Secret is an object that contains a small amount of sensitive data, such as passwords or keys. This is the data you don’t want to include Pod definition or deployment definition files. While configuring the MySQL deployment we need the MySQL database password, so to store the password we will use a Secret. You can refer to this link for more information about Secrets.

Below is the configuration file.

apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
  labels:
    name: mysql-secret
    app: mysql-gke-deployment
  namespace: development
type: Opaque
data:
  root-password: d2ludGVy

In a Secret, we can store only base64 encoded values. Right now password for the database root user is winter , so we can encode it in the cloud shell using the following command.

echo ‘winter’ | base64

# which returns following response
d2ludGVy

Create the file mysql-secret.yaml and use the following command to create a Secret.

kubectl create -f mysql-secret.yaml

We can check the created secret using the following command or the same can be verified using the UI.

kubectl describe secret mysql-secret -n development

GKE Secret

Persistent Volume Claim

Lets us first understand why we need the Persistent volume (PV). The data stored in the MySQL Pod is ephemeral if due to some reason if the Pod is recreated, then the data stored on that Pod is lost. To persist that data, it will be stored in the Persistent Volume.

The Persistent Volume Claim (PVC) is a request to create persistent volume resources. We don’t need to create the PV manually; once PVC is created then, the PV associated with that PVC is created automatically. Please refer to this link for more information about PV and PVC.

Below is the configuration file for PVC.

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-data-store
  labels:
    name: mysql-data-store
    app: mysql-gke-deployment
  namespace: development
spec:
  resources:
    requests:
      storage: 3Gi
    limits:
      storage: 5Gi
  accessModes:
    - ReadWriteOnce
  storageClassName: standard-rwo

Create the file mysql-persistent-volume-claim.yaml and execute the following command to create the PVC.

kubectl create -f mysql-persistent-volume-claim.yaml

Once the resources are provisioned, we can the status of PVC using the following command or the same can be verified using the UI.

kubectl describe pvc mysql-data-store -n development

GKE Storage

As we can check its status is Pending; once this PVC is consumed by any resource, it will be turned to Bound.

ConfigMap

ConfigMap is an object used to store the non-confidential data in key and value format, It is usually used to store environment variables that are utilized by Kubernetes configuration files. In the current configuration, we are using the config maps to preload the data while MySQL pod creation. config map is used to store the MySQL queries that we want to be executed during the pod creation, so once the Pod is created required MySQL structure and data are available. For more details about the config maps please refer to this link.

Below is the configuration file.

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-preload-data-config
  labels:
    name: mysql-data-store
    app: mysql-gke-deployment
  namespace: development
data:
  init.sql: |
    CREATE DATABASE IF NOT EXISTS`book-management-db`;
    USE `book-management-db`;
    DROP TABLE IF EXISTS `books`;
    CREATE TABLE `books`(
     `id`int NOT NULL AUTO_INCREMENT,
     `title`varchar(255) NOT NULL,
     `author`varchar(255) NOT NULL,
     `language`varchar(255) DEFAULT NULL,
     `created_at`datetime DEFAULT CURRENT_TIMESTAMP,
     `created_by`varchar(255) DEFAULT NULL,
     `updated_at`datetime DEFAULT CURRENT_TIMESTAMP,
     `updated_by` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
    insert into books(`title`,`author`,`language`) values('Harry Potter','J.K.Rowling','English');`

Right now for demonstration purposes, we are only adding a single record in the books table.

Create the file mysql-config-map.yaml and use the following command to create a config map.

kubectl create -f mysql-config-map.yaml

we can check the created config map using the following command or verify the same using the UI.

kubectl describe configmap mysql-preload-data-config -n development

GKE ConfigMap

Deployment

Deployment is a process by using we define the desired state of Pods and ReplicaSets. It allows you to describe the life cycle of the application, it allows you to define the number of pods, and what images should be used for the creation of a Pod. Deployment Controller handles the update of resources defined in the deployment file. For more details about deployment, please refer following links.

• Deployments
• ReplicaSets
• Pods

The complete deployment configuration file will be looked as below.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-deploy
  labels:
    name: mysql-deploy
    app: mysql-gke-deployment
  namespace: development
spec:
  replicas: 1
  selector:
    matchLabels:
      name: mysql-pod
      app: mysql-gke-deployment
  template:
    metadata:
      name: mysql-pod
      labels:
        name: mysql-pod
        app: mysql-gke-deployment
    spec:
      containers:
        - name: mysql
          image: mysql:8.0.32
          ports:
            - containerPort: 3306
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secret
                  key: root-password
          volumeMounts:
            - name: mysql-initdb
              mountPath: /docker-entrypoint-initdb.d
            - name: mysql-data
              mountPath: "/var/lib/mysql"
              subPath: "mysql"
      volumes:
        - name: mysql-initdb
          configMap:
            name: mysql-preload-data-config
        - name: mysql-data
          persistentVolumeClaim:
            claimName: mysql-data-store

Create the file mysql-deployment.yaml and execute the following command to create the deployment.

kubectl create -f mysql-deployment.yaml

Once the deployment is created we can check its status using the following command or the same can be verified using UI.

kubectl describe deployment mysql-deploy -n development

GKE Workload

Service:

Service is a method for exposing deployed application which is running on one or more pods in the cluster. There are 3 types of services available NodePort, LoadBalancer, and ClusterIP, which is also the default one, selected when no service type is defined. For our task, we are going to use the ClusterIP service that will make the database accessible within the Pod.
For more information about the service, please refer to this link.

Below is the configuration file.

apiVersion: v1
kind: Service
metadata:
  name: mysql-service
  labels:
    name: mysql-service
    app: mysql-gke-deployment
  namespace: development
spec:
  type: ClusterIP
  selector:
    name: mysql-pod
    app: mysql-gke-deployment
  ports:
    - port: 3306
      targetPort: 3306

Create the file mysql-service.yaml and use the following command to create the service file.

kubectl create -f mysql-service.yaml

Created service can be verified using the following command, or the same can be verified using the UI.

kubectl describe service mysql-service -n development

GKE Service

Once the service is created, our MySQL database is ready to be consumed using the cluster IP, but it will be accessible within the same cluster only.

Verification of the MySQL data:

We can check the MySQL data and structure by connecting to the deployed Pod.

Please set the default namespace using the following command.

kubectl config set-context --current --namespace=development

Use the following command to connect with the MySQL database.

kubectl exec --stdin --tty pod/mysql-deploy-7bc6bb6cc6-r689s -- /bin/bash

mysql -p #enter the password defined in the secret configuration

Once connected to the Pod, we can use the MySQL commands to verify the MySQL structure and data. Right now, for demonstration purposes, only a single record was added to the books table; please update the database structure and data as per your preference.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| book-management-db |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)


mysql> use book-management-db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from books;
+----+---------------+-------------+----------+---------------------+------------+---------------------+------------+
| id | title         | author      | language | created_at          | created_by | updated_at          | updated_by |
+----+---------------+-------------+----------+---------------------+------------+---------------------+------------+
|  1 | Harry Potter | J.K.Rowling | English  | 2023-03-12 10:59:40 | NULL       | 2023-03-12 10:59:40 | NULL       |
+----+---------------+-------------+----------+---------------------+------------+---------------------+------------+
1 row in set (0.00 sec)

Removing GKE resources

Suppose you achieve your goal and want to delete resources to reduce the billing. Use the following commands to remove the resources.

#To delete deployments,services,pods,replicasets
kubectl delete all --all -n development 

#To delete the configmap mysql-preload-data-config
kubectl delete configmap/mysql-preload-data-config -n development

#To delete the secret mysql-secret
kubectl delete secret/mysql-secret -n development

#To delete the persistent volum claim mysql-data-store
kubectl delete persistentvolumeclaim/mysql-data-store -n development

Conclusion

We have successfully deployed the MySQL instance on the Google Kubernetes Engine(GKE). We have also used the Persistent Volume to persist the data. We have used Secrets and ConfigMaps to store the MySQL configuration. All the configuration files used for this setup are provided in the repository, please refer to this GitHub link.

In Part II of the Kubernetes deployment series, we deployed the NodeJS application to the GKE and accessed the deployed MySQL database in this blog. To access Part II of the Kubernetes deployment series, please refer to this URL.