DEV Community

Janak Shrestha
Janak Shrestha

Posted on

Day 66: Deploy MySQL on Kubernetes

#100daysofdevops #kodekloud #kubernetes #devops

A new MySQL server needs to be deployed on Kubernetes cluster. The Nautilus DevOps team was working on to gather the requirements. Recently they were able to finalize the requirements and shared them with the team members to start working on it. Below you can find the details:

1.) Create a PersistentVolume mysql-pv, its capacity should be 250Mi, set other parameters as per your preference.

2.) Create a PersistentVolumeClaim to request this PersistentVolume storage. Name it as mysql-pv-claim and request a 250Mi of storage. Set other parameters as per your preference.

3.) Create a deployment named mysql-deployment, use any mysql image as per your preference. Mount the PersistentVolume at mount path /var/lib/mysql.

4.) Create a NodePort type service named mysql and set nodePort to 30007.

5.) Create a secret named mysql-root-pass having a key pair value, where key is password and its value is YUIidhb667, create another secret named mysql-user-pass having some key pair values, where first key is username and its value is kodekloud_sam, second key is password and value is TmPcZjtRQx, create one more secret named mysql-db-url, key name is database and value is kodekloud_db1

6.) Define some environment variables within the container:

a.) name: MYSQL_ROOT_PASSWORD, should pick value from secretKeyRef name: mysql-root-pass and key: password

b.) name: MYSQL_DATABASE, should pick value from secretKeyRef name: mysql-db-url and key: database

c.) name: MYSQL_USER, should pick value from secretKeyRef name: mysql-user-pass key key: username

d.) name: MYSQL_PASSWORD, should pick value from secretKeyRef name: mysql-user-pass and key: password

Understanding the Deployment Architecture

Components Overview

Component Specification
PersistentVolume mysql-pv – 250Mi storage, hostPath
PersistentVolumeClaim mysql-pv-claim – 250Mi request
Deployment mysql-deployment – 1 replica
Container mysql-container – mysql:8.0 image
Service mysql – NodePort 30007
Secrets mysql-root-pass, mysql-user-pass, mysql-db-url

Architecture Diagram 

┌─────────────────────────────────────────────────────────────────────────────┐
│                         Kubernetes Cluster                                 │
│                                                                              │
│  ┌────────────────────────────────────────────────────────────────────────┐ │
│  │  PersistentVolume: mysql-pv (250Mi)                                  │ │
│  │  - hostPath: /mnt/mysql-data                                         │ │
│  │  - StorageClass: manual                                              │ │
│  └────────────────────────────────────────────────────────────────────────┘ │
│                                    │                                         │
│                                    ▼                                         │
│  ┌────────────────────────────────────────────────────────────────────────┐ │
│  │  PersistentVolumeClaim: mysql-pv-claim (250Mi)                       │ │
│  │  - Bound to: mysql-pv                                                │ │
│  │  - AccessMode: ReadWriteOnce                                         │ │
│  └────────────────────────────────────────────────────────────────────────┘ │
│                                    │                                         │
│                                    ▼                                         │
│  ┌────────────────────────────────────────────────────────────────────────┐ │
│  │  Deployment: mysql-deployment                                        │ │
│  │  ┌──────────────────────────────────────────────────────────────────┐ │ │
│  │  │  Pod: mysql-deployment-xxxxxxxxxx-xxxxx                        │ │ │
│  │  │  ┌────────────────────────────────────────────────────────────┐ │ │ │
│  │  │  │  Container: mysql-container                               │ │ │ │
│  │  │  │  Image: mysql:8.0                                         │ │ │ │
│  │  │  │  Port: 3306                                               │ │ │ │
│  │  │  │  Volume Mount: /var/lib/mysql                             │ │ │ │
│  │  │  │  Environment Variables:                                   │ │ │ │
│  │  │  │  - MYSQL_ROOT_PASSWORD: from secret                      │ │ │ │
│  │  │  │  - MYSQL_DATABASE: from secret                           │ │ │ │
│  │  │  │  - MYSQL_USER: from secret                               │ │ │ │
│  │  │  │  - MYSQL_PASSWORD: from secret                           │ │ │ │
│  │  │  └────────────────────────────────────────────────────────────┘ │ │ │
│  │  └──────────────────────────────────────────────────────────────────┘ │ │
│  └────────────────────────────────────────────────────────────────────────┘ │
│                                    │                                         │
│                                    ▼                                         │
│  ┌────────────────────────────────────────────────────────────────────────┐ │
│  │  Service: mysql                                                       │ │
│  │  - Type: NodePort                                                    │ │
│  │  - NodePort: 30007                                                   │ │
│  │  - TargetPort: 3306                                                  │ │
│  └────────────────────────────────────────────────────────────────────────┘ │
│                                    │                                         │
│                                    ▼                                         │
│                    mysql://<node-ip>:30007                                │
└─────────────────────────────────────────────────────────────────────────────┘
Enter fullscreen mode Exit fullscreen mode

Step-by-Step Implementation

Step 1: Create the PersistentVolume

PersistentVolumes provide storage resources in the cluster.

cat > mysql-pv.yaml << 'EOF'
apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv
spec:
  capacity:
    storage: 250Mi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /mnt/mysql-data
  storageClassName: manual
EOF
Enter fullscreen mode Exit fullscreen mode

Key Configuration Points:

Element Value Purpose
capacity.storage 250Mi Storage capacity
accessModes ReadWriteOnce Single node read-write access
hostPath.path /mnt/mysql-data Host directory for storage
storageClassName manual Manual provisioning class

Step 2: Create the PersistentVolumeClaim

PersistentVolumeClaims request storage from PersistentVolumes.

cat > mysql-pvc.yaml << 'EOF'
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 250Mi
  storageClassName: manual
EOF
Enter fullscreen mode Exit fullscreen mode

Key Configuration Points:

Element Value Purpose
name mysql-pv-claim Claim name
accessModes ReadWriteOnce Must match PV
requests.storage 250Mi Storage requested
storageClassName manual Must match PV

Step 3: Create the Secrets

Secrets store sensitive information securely.

# Secret for root password
cat > mysql-root-pass-secret.yaml << 'EOF'
apiVersion: v1
kind: Secret
metadata:
  name: mysql-root-pass
type: Opaque
data:
  password: WVVJaWRoYjY2Nw==
EOF

# Secret for user credentials
cat > mysql-user-pass-secret.yaml << 'EOF'
apiVersion: v1
kind: Secret
metadata:
  name: mysql-user-pass
type: Opaque
data:
  username: a29kZWtsb3VkX3NhbQ==
  password: VG1QY1pqdFJReA==
EOF

# Secret for database URL
cat > mysql-db-url-secret.yaml << 'EOF'
apiVersion: v1
kind: Secret
metadata:
  name: mysql-db-url
type: Opaque
data:
  database: a29kZWtsb3VkX2RiMQ==
EOF
Enter fullscreen mode Exit fullscreen mode

Secret Values:
| Secret | Key | Value | Base64 Encoded |
|--------|-----|-------|----------------|
| mysql-root-pass | password | YUIidhb667 | WVVJaWRoYjY2Nw== |
| mysql-user-pass | username | kodekloud_sam | a29kZWtsb3VkX3NhbQ== |
| mysql-user-pass | password | TmPcZjtRQx | VG1QY1pqdFJReA== |
| mysql-db-url | database | kodekloud_db1 | a29kZWtsb3VkX2RiMQ== |

Step 4: Create the Deployment

The deployment defines the MySQL pod specification.

cat > mysql-deployment.yaml << 'EOF'
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql-container
        image: mysql:8.0
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-root-pass
              key: password
        - name: MYSQL_DATABASE
          valueFrom:
            secretKeyRef:
              name: mysql-db-url
              key: database
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: mysql-user-pass
              key: username
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-user-pass
              key: password
        volumeMounts:
        - name: mysql-storage
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-storage
        persistentVolumeClaim:
          claimName: mysql-pv-claim
EOF
Enter fullscreen mode Exit fullscreen mode

Environment Variables:

Variable Source Purpose
MYSQL_ROOT_PASSWORD mysql-root-pass secret Root user password
MYSQL_DATABASE mysql-db-url secret Default database name
MYSQL_USER mysql-user-pass secret Custom database user
MYSQL_PASSWORD mysql-user-pass secret Custom user password

Step 5: Create the Service

The service exposes MySQL to the network.

cat > mysql-service.yaml << 'EOF'
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  type: NodePort
  selector:
    app: mysql
  ports:
    - port: 3306
      targetPort: 3306
      nodePort: 30007
EOF
Enter fullscreen mode Exit fullscreen mode

Key Configuration Points:

Element Value Purpose
type NodePort External access
selector.app mysql Routes to MySQL pods
port 3306 Service port
targetPort 3306 Container port
nodePort 30007 Node port for external access

Step 6: Apply All Configurations 

kubectl apply -f mysql-pv.yaml
kubectl apply -f mysql-pvc.yaml
kubectl apply -f mysql-root-pass-secret.yaml
kubectl apply -f mysql-user-pass-secret.yaml
kubectl apply -f mysql-db-url-secret.yaml
kubectl apply -f mysql-deployment.yaml
kubectl apply -f mysql-service.yaml
Enter fullscreen mode Exit fullscreen mode

Output:

persistentvolume/mysql-pv created
persistentvolumeclaim/mysql-pv-claim created
secret/mysql-root-pass created
secret/mysql-user-pass created
secret/mysql-db-url created
deployment.apps/mysql-deployment created
service/mysql created
Enter fullscreen mode Exit fullscreen mode

Step 7: Verify All Resources 

kubectl get pv
Enter fullscreen mode Exit fullscreen mode

Output:

NAME       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                     STORAGECLASS
mysql-pv   250Mi      RWO            Retain           Bound    default/mysql-pv-claim    manual
Enter fullscreen mode Exit fullscreen mode 
kubectl get pvc
Enter fullscreen mode Exit fullscreen mode

Output:

NAME             STATUS   VOLUME     CAPACITY   ACCESS MODES   STORAGECLASS
mysql-pv-claim   Bound    mysql-pv   250Mi      RWO            manual
Enter fullscreen mode Exit fullscreen mode 
kubectl get secrets
Enter fullscreen mode Exit fullscreen mode

Output:

NAME               TYPE     DATA   AGE
mysql-db-url       Opaque   1      10s
mysql-root-pass    Opaque   1      10s
mysql-user-pass    Opaque   2      10s
Enter fullscreen mode Exit fullscreen mode 
kubectl get deployments
Enter fullscreen mode Exit fullscreen mode

Output:

NAME               READY   UP-TO-DATE   AVAILABLE   AGE
mysql-deployment   1/1     1            1           30s
Enter fullscreen mode Exit fullscreen mode 
kubectl get pods
Enter fullscreen mode Exit fullscreen mode

Output:

NAME                                READY   STATUS    RESTARTS   AGE
mysql-deployment-xxxxxxxxxx-xxxxx   1/1     Running   0          30s
Enter fullscreen mode Exit fullscreen mode 
kubectl get services
Enter fullscreen mode Exit fullscreen mode

Output:

NAME         TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
kubernetes   ClusterIP   10.43.0.1      <none>        443/TCP          71m
mysql        NodePort    10.43.148.74   <none>        3306:30007/TCP   10s
Enter fullscreen mode Exit fullscreen mode

MySQL Configuration

Database Credentials

User Password Database
root YUIidhb667 All databases
kodekloud_sam TmPcZjtRQx kodekloud_db1

Connecting to MySQL

From Inside the Cluster 

kubectl exec -it <pod-name> -- mysql -u root -pYUIidhb667
Enter fullscreen mode Exit fullscreen mode

From Outside the Cluster 

mysql -u root -pYUIidhb667 -h <node-ip> -P 30007
Enter fullscreen mode Exit fullscreen mode

Using the Service Name 

mysql -u root -pYUIidhb667 -h mysql.default.svc.cluster.local -P 3306
Enter fullscreen mode Exit fullscreen mode

Troubleshooting

PersistentVolume Not Binding 

# Check PV and PVC status
kubectl get pv
kubectl get pvc

# Check storage class mismatch
kubectl describe pv mysql-pv
kubectl describe pvc mysql-pv-claim

# Ensure both have same storageClassName
Enter fullscreen mode Exit fullscreen mode

Pod Not Starting 

# Check pod status
kubectl get pods

# Check pod details
kubectl describe pod <pod-name>

# Check pod logs
kubectl logs <pod-name>
Enter fullscreen mode Exit fullscreen mode

Secret Not Found 

# Check secrets exist
kubectl get secrets

# Check secret details
kubectl describe secret mysql-root-pass
Enter fullscreen mode Exit fullscreen mode

Service Not Accessible 

# Check service status
kubectl get services

# Check endpoints
kubectl get endpoints mysql

# Check node port
kubectl get service mysql -o yaml | grep nodePort
Enter fullscreen mode Exit fullscreen mode

Best Practices

1. Storage Management

Use PersistentVolumes for database storage to ensure data persistence across pod restarts and node failures.

2. Secret Management

Store sensitive credentials in Kubernetes Secrets rather than in plain text in configuration files or environment variables.

3. Resource Management

Set resource requests and limits for the MySQL container:

resources:
  requests:
    memory: "256Mi"
    cpu: "500m"
  limits:
    memory: "512Mi"
    cpu: "1"
Enter fullscreen mode Exit fullscreen mode

4. Configuration Management

Use ConfigMaps for non-sensitive MySQL configuration:

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config
data:
  my.cnf: |
    [mysqld]
    max_connections = 100
    innodb_buffer_pool_size = 128M
Enter fullscreen mode Exit fullscreen mode

5. Backup and Recovery

Implement regular database backups using tools like mysqldump or dedicated backup operators.

Useful Commands Reference

Command Purpose
kubectl get pv List PersistentVolumes
kubectl get pvc List PersistentVolumeClaims
kubectl get secrets List Secrets
kubectl get deployments List Deployments
kubectl get pods List Pods
kubectl get services List Services
kubectl describe pod <pod-name> Detailed Pod information
kubectl logs <pod-name> View Pod logs
kubectl exec -it <pod-name> -- /bin/bash Access Pod shell
kubectl exec -it <pod-name> -- mysql -u root -p Access MySQL
kubectl delete deployment mysql-deployment Delete Deployment
kubectl apply -f <file> Apply configuration

Summary

In this challenge, we successfully:

  1. Created a PersistentVolume with 250Mi capacity for MySQL data storage
  2. Created a PersistentVolumeClaim to request the storage
  3. Deployed MySQL using the mysql:8.0 image
  4. Configured environment variables using Kubernetes Secrets
  5. Mounted the persistent volume at /var/lib/mysql
  6. Exposed MySQL using a NodePort service on port 30007

This deployment pattern demonstrates a complete stateful application setup on Kubernetes, following best practices for storage management, secret handling, and service exposure. The skills learned here are transferable to other stateful workloads like PostgreSQL, MongoDB, and Redis.

Top comments (0)