Day 66: MySQL on Kubernetes - Complete Database Deployment with Secrets & Persistent Storage 🗄️

Welcome back! 👋 Day 66 of the 100 Days Cloud DevOps Challenge, and today we're deploying MySQL database on Kubernetes! This is complete production database setup - persistent storage, secrets management, and external access. Let's deploy! 🎯

🎯 The Mission - Production MySQL Deployment

📋 TASK TICKET #DEV-8066 - MySQL Database on Kubernetes
Priority: CRITICAL | Type: Database Infrastructure
Server: Jump Host | Cluster: Kubernetes

REQUIREMENT:
- Deploy MySQL database server
- Persistent data storage
- Secure credential management
- External access capability
- Production-ready configuration

SPECIFICATIONS:
1. PersistentVolume: mysql-pv
   - Capacity: 250Mi

2. PersistentVolumeClaim: mysql-pv-claim
   - Request: 250Mi

3. Deployment: mysql-deployment
   - Image: MySQL (any version)
   - Mount: PV at /var/lib/mysql

4. Service: mysql
   - Type: NodePort
   - NodePort: 30007

5. Secrets (3 total):
   a. mysql-root-pass
      - password: YUIidhb667
   b. mysql-user-pass
      - username: kodekloud_gem
      - password: B4zNgHA7Ya
   c. mysql-db-url
      - database: kodekloud_db5

6. Environment Variables:
   - MYSQL_ROOT_PASSWORD (from mysql-root-pass)
   - MYSQL_DATABASE (from mysql-db-url)
   - MYSQL_USER (from mysql-user-pass)
   - MYSQL_PASSWORD (from mysql-user-pass)

SUCCESS CRITERIA:
- All resources created
- MySQL running with persistent storage
- Secrets properly configured
- Database accessible via NodePort

This is production database infrastructure! 🏗️

🤔 Why This Architecture?

Complete Database Stack:

✅ Production Requirements:
- Persistent Storage (survive restarts)
- Secrets Management (secure credentials)
- External Access (NodePort service)
- Environment Config (via secrets)
- Data Safety (PersistentVolume)

Architecture:
External Client (Port 30007)
        ↓
NodePort Service (mysql)
        ↓
Deployment (mysql-deployment)
        ↓
Pod (MySQL container)
    ├─ Secrets (credentials)
    └─ PV (data storage)

🛠️ Complete Implementation

Step 1: Access Jump Host

# SSH to jump host
ssh user@jump_host

# Verify kubectl
kubectl version --short
kubectl get nodes

Environment ready! ✅

Step 2: Create Secrets

# Secret 1: mysql-root-pass
kubectl create secret generic mysql-root-pass \
  --from-literal=password=YUIidhb667

# Secret 2: mysql-user-pass
kubectl create secret generic mysql-user-pass \
  --from-literal=username=kodekloud_gem \
  --from-literal=password=B4zNgHA7Ya

# Secret 3: mysql-db-url
kubectl create secret generic mysql-db-url \
  --from-literal=database=kodekloud_db5

Expected output:

secret/mysql-root-pass created
secret/mysql-user-pass created
secret/mysql-db-url created

Secrets created! 🔐

Verify secrets:

# List secrets
kubectl get secrets | grep mysql

Expected output:

mysql-db-url       Opaque   1      30s
mysql-root-pass    Opaque   1      30s
mysql-user-pass    Opaque   2      30s

All secrets exist! ✅

Step 3: Create PersistentVolume

# Create PersistentVolume YAML
cat > mysql-pv.yaml << 'EOF'
apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv
spec:
  capacity:
    storage: 250Mi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /mnt/data/mysql
  persistentVolumeReclaimPolicy: Retain
EOF

# Apply PV
kubectl apply -f mysql-pv.yaml

Expected output:

persistentvolume/mysql-pv created

Verify PV:

kubectl get pv mysql-pv

Expected output:

NAME       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS      STORAGECLASS   AGE
mysql-pv   250Mi      RWO            Retain           Available                  10s

PV created! ✅

Step 4: Create PersistentVolumeClaim

# Create PVC YAML
cat > mysql-pvc.yaml << 'EOF'
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 250Mi
EOF

# Apply PVC
kubectl apply -f mysql-pvc.yaml

Expected output:

persistentvolumeclaim/mysql-pv-claim created

Verify PVC:

kubectl get pvc mysql-pv-claim

Expected output:

NAME             STATUS   VOLUME     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
mysql-pv-claim   Bound    mysql-pv   250Mi      RWO                           10s

PVC bound to PV! ✅

Step 5: Create MySQL Deployment

# Create Deployment YAML
cat > mysql-deployment.yaml << 'EOF'
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-deployment
  labels:
    app: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:5.7
        ports:
        - containerPort: 3306
          name: mysql
        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

# Apply Deployment
kubectl apply -f mysql-deployment.yaml

Expected output:

deployment.apps/mysql-deployment created

Deployment created! 🎉

Watch deployment:

kubectl get pods -l app=mysql --watch

Expected progression:

NAME                                READY   STATUS              RESTARTS   AGE
mysql-deployment-7d6c9f8b9c-x7k2m   0/1     Pending             0          0s
mysql-deployment-7d6c9f8b9c-x7k2m   0/1     ContainerCreating   0          2s
mysql-deployment-7d6c9f8b9c-x7k2m   1/1     Running             0          30s

Press Ctrl+C when Running.

Step 6: Create NodePort Service

# Create Service YAML
cat > mysql-service.yaml << 'EOF'
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  type: NodePort
  selector:
    app: mysql
  ports:
  - protocol: TCP
    port: 3306
    targetPort: 3306
    nodePort: 30007
EOF

# Apply Service
kubectl apply -f mysql-service.yaml

Expected output:

service/mysql created

Service created! 🎉

Verify service:

kubectl get service mysql

Expected output:

NAME    TYPE       CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
mysql   NodePort   10.96.100.50    <none>        3306:30007/TCP   10s

Service exposing MySQL! ✅

Step 7: Verify Complete Deployment

# Check all resources
kubectl get deployment mysql-deployment
kubectl get pods -l app=mysql
kubectl get service mysql
kubectl get pvc mysql-pv-claim
kubectl get pv mysql-pv
kubectl get secrets | grep mysql

Expected output:

NAME               READY   UP-TO-DATE   AVAILABLE   AGE
mysql-deployment   1/1     1            1           2m

NAME                                READY   STATUS    RESTARTS   AGE
mysql-deployment-7d6c9f8b9c-x7k2m   1/1     Running   0          2m

NAME    TYPE       CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
mysql   NodePort   10.96.100.50    <none>        3306:30007/TCP   1m

NAME             STATUS   VOLUME     CAPACITY   ACCESS MODES   AGE
mysql-pv-claim   Bound    mysql-pv   250Mi      RWO            3m

NAME       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   AGE
mysql-pv   250Mi      RWO            Retain           Bound    4m

mysql-db-url       Opaque   1      5m
mysql-root-pass    Opaque   1      5m
mysql-user-pass    Opaque   2      5m

All resources created! ✅

Step 8: Verify MySQL Functionality

# Check MySQL logs
kubectl logs -l app=mysql --tail=20

Expected output:

2025-01-11T10:00:00.123456Z 0 [Note] mysqld: ready for connections.
Version: '5.7.44'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

MySQL ready! ✅

Test MySQL connection:

# Get pod name
POD_NAME=$(kubectl get pod -l app=mysql -o jsonpath='{.items[0].metadata.name}')

# Test root login
kubectl exec -it $POD_NAME -- mysql -u root -pYUIidhb667 -e "SHOW DATABASES;"

Expected output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| kodekloud_db5      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Database created! ✅

Test user login:

# Test user credentials
kubectl exec -it $POD_NAME -- mysql -u kodekloud_gem -pB4zNgHA7Ya -e "SELECT DATABASE();"

Expected output:

+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+

User authenticated! ✅

Verify environment variables:

# Check env vars in pod
kubectl exec $POD_NAME -- env | grep MYSQL

Expected output:

MYSQL_ROOT_PASSWORD=YUIidhb667
MYSQL_DATABASE=kodekloud_db5
MYSQL_USER=kodekloud_gem
MYSQL_PASSWORD=B4zNgHA7Ya

All environment variables set! ✅

Test database access:

# Use the database
kubectl exec -it $POD_NAME -- mysql -u kodekloud_gem -pB4zNgHA7Ya kodekloud_db5 -e "CREATE TABLE test (id INT); INSERT INTO test VALUES (1); SELECT * FROM test;"

Expected output:

+------+
| id   |
+------+
|    1 |
+------+

Database fully functional! ✅

Verify persistent storage:

# Check mounted volume
kubectl exec $POD_NAME -- df -h /var/lib/mysql

Expected output:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        20G  500M   19G   3% /var/lib/mysql

Check data directory:

kubectl exec $POD_NAME -- ls -lh /var/lib/mysql/

Expected output:

total 188M
-rw-r----- 1 mysql mysql   56 Jan 11 10:00 auto.cnf
-rw------- 1 mysql mysql 1.7K Jan 11 10:00 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Jan 11 10:00 ca.pem
drwxr-x--- 2 mysql mysql 4.0K Jan 11 10:00 kodekloud_db5
-rw-r----- 1 mysql mysql  56M Jan 11 10:00 ib_logfile0
-rw-r----- 1 mysql mysql  56M Jan 11 10:00 ib_logfile1
-rw-r----- 1 mysql mysql  76M Jan 11 10:00 ibdata1
drwxr-x--- 2 mysql mysql 4.0K Jan 11 10:00 mysql
drwxr-x--- 2 mysql mysql 4.0K Jan 11 10:00 performance_schema
drwxr-x--- 2 mysql mysql 4.0K Jan 11 10:00 sys

Data persisted! ✅

Step 9: Test External Access

# Get node IP
NODE_IP=$(kubectl get nodes -o jsonpath='{.items[0].status.addresses[?(@.type=="InternalIP")].address}')
echo "MySQL accessible at: $NODE_IP:30007"

# Test connection from jump host (if mysql client installed)
mysql -h $NODE_IP -P 30007 -u kodekloud_gem -pB4zNgHA7Ya -e "SHOW DATABASES;"

If mysql client not installed:

# Test with pod
kubectl run mysql-client --image=mysql:5.7 --rm -it --restart=Never -- mysql -h $NODE_IP -P 30007 -u kodekloud_gem -pB4zNgHA7Ya -e "SHOW DATABASES;"

External access working! ✅

Step 10: Complete Verification

# Comprehensive verification script
cat > verify-mysql.sh << 'EOF'
#!/bin/bash
echo "=== MySQL Kubernetes Deployment Verification ==="
echo ""
echo "1. Secrets Status:"
kubectl get secrets | grep mysql | awk '{print "   " $1 ": " $2 " (" $3 " keys)"}'
echo ""
echo "2. PersistentVolume Status:"
kubectl get pv mysql-pv -o custom-columns=NAME:.metadata.name,CAPACITY:.spec.capacity.storage,STATUS:.status.phase,CLAIM:.spec.claimRef.name
echo ""
echo "3. PersistentVolumeClaim Status:"
kubectl get pvc mysql-pv-claim -o custom-columns=NAME:.metadata.name,STATUS:.status.phase,VOLUME:.spec.volumeName,CAPACITY:.status.capacity.storage
echo ""
echo "4. Deployment Status:"
kubectl get deployment mysql-deployment
echo ""
echo "5. Pod Status:"
kubectl get pods -l app=mysql
echo ""
echo "6. Service Status:"
kubectl get service mysql
echo ""
echo "7. Environment Variables Check:"
POD_NAME=$(kubectl get pod -l app=mysql -o jsonpath='{.items[0].metadata.name}')
echo "   Pod: $POD_NAME"
kubectl exec $POD_NAME -- env | grep MYSQL_ | sed 's/^/   /'
echo ""
echo "8. Database Verification:"
DB_CHECK=$(kubectl exec $POD_NAME -- mysql -u root -pYUIidhb667 -e "SHOW DATABASES;" 2>/dev/null | grep kodekloud_db5)
if [ -n "$DB_CHECK" ]; then
    echo "   ✓ Database 'kodekloud_db5' exists"
else
    echo "   ✗ Database 'kodekloud_db5' not found"
fi
echo ""
echo "9. User Authentication:"
USER_CHECK=$(kubectl exec $POD_NAME -- mysql -u kodekloud_gem -pB4zNgHA7Ya -e "SELECT 1;" 2>/dev/null)
if [ -n "$USER_CHECK" ]; then
    echo "   ✓ User 'kodekloud_gem' can authenticate"
else
    echo "   ✗ User authentication failed"
fi
echo ""
echo "10. Persistent Storage:"
MOUNT_CHECK=$(kubectl exec $POD_NAME -- df -h /var/lib/mysql | tail -1 | awk '{print $6}')
if [ "$MOUNT_CHECK" = "/var/lib/mysql" ]; then
    echo "   ✓ PersistentVolume mounted at /var/lib/mysql"
else
    echo "   ✗ Volume mount issue"
fi
echo ""
echo "11. Service Connectivity:"
NODE_IP=$(kubectl get nodes -o jsonpath='{.items[0].status.addresses[?(@.type=="InternalIP")].address}')
echo "   External Access: mysql -h $NODE_IP -P 30007 -u kodekloud_gem -pB4zNgHA7Ya"
echo ""
echo "12. Verification Summary:"
DEPLOY_READY=$(kubectl get deployment mysql-deployment -o jsonpath='{.status.readyReplicas}')
POD_STATUS=$(kubectl get pod -l app=mysql -o jsonpath='{.items[0].status.phase}')
SVC_PORT=$(kubectl get service mysql -o jsonpath='{.spec.ports[0].nodePort}')
PVC_STATUS=$(kubectl get pvc mysql-pv-claim -o jsonpath='{.status.phase}')

if [ "$DEPLOY_READY" = "1" ] && [ "$POD_STATUS" = "Running" ] && [ "$SVC_PORT" = "30007" ] && [ "$PVC_STATUS" = "Bound" ]; then
    echo "    ✓ Deployment ready (1/1)"
    echo "    ✓ Pod running successfully"
    echo "    ✓ Service exposed on NodePort 30007"
    echo "    ✓ PersistentVolume bound"
    echo "    ✓ All secrets configured"
    echo "    ✓ Database accessible"
    echo "    ✓ ALL CHECKS PASSED"
else
    echo "    ✗ Some checks failed"
fi
EOF

chmod +x verify-mysql.sh
./verify-mysql.sh

Expected output:

=== MySQL Kubernetes Deployment Verification ===

1. Secrets Status:
   mysql-db-url: Opaque (1 keys)
   mysql-root-pass: Opaque (1 keys)
   mysql-user-pass: Opaque (2 keys)

2. PersistentVolume Status:
NAME       CAPACITY   STATUS   CLAIM
mysql-pv   250Mi      Bound    mysql-pv-claim

3. PersistentVolumeClaim Status:
NAME             STATUS   VOLUME     CAPACITY
mysql-pv-claim   Bound    mysql-pv   250Mi

4. Deployment Status:
NAME               READY   UP-TO-DATE   AVAILABLE   AGE
mysql-deployment   1/1     1            1           5m

5. Pod Status:
NAME                                READY   STATUS    RESTARTS   AGE
mysql-deployment-7d6c9f8b9c-x7k2m   1/1     Running   0          5m

6. Service Status:
NAME    TYPE       CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
mysql   NodePort   10.96.100.50    <none>        3306:30007/TCP   4m

7. Environment Variables Check:
   Pod: mysql-deployment-7d6c9f8b9c-x7k2m
   MYSQL_ROOT_PASSWORD=YUIidhb667
   MYSQL_DATABASE=kodekloud_db5
   MYSQL_USER=kodekloud_gem
   MYSQL_PASSWORD=B4zNgHA7Ya

8. Database Verification:
   ✓ Database 'kodekloud_db5' exists

9. User Authentication:
   ✓ User 'kodekloud_gem' can authenticate

10. Persistent Storage:
   ✓ PersistentVolume mounted at /var/lib/mysql

11. Service Connectivity:
   External Access: mysql -h 172.17.0.2 -P 30007 -u kodekloud_gem -pB4zNgHA7Ya

12. Verification Summary:
    ✓ Deployment ready (1/1)
    ✓ Pod running successfully
    ✓ Service exposed on NodePort 30007
    ✓ PersistentVolume bound
    ✓ All secrets configured
    ✓ Database accessible
    ✓ ALL CHECKS PASSED

All systems operational! 🎊

🔍 Understanding the Complete Architecture

Component Interaction

Complete MySQL Stack:

External Client
      ↓ Port 30007
NodePort Service (mysql)
      ↓ Port 3306
Deployment (mysql-deployment)
      ↓
Pod (MySQL 5.7)
  ├─ Secrets (Environment Variables)
  │   ├─ MYSQL_ROOT_PASSWORD → mysql-root-pass
  │   ├─ MYSQL_DATABASE → mysql-db-url
  │   ├─ MYSQL_USER → mysql-user-pass
  │   └─ MYSQL_PASSWORD → mysql-user-pass
  │
  └─ PersistentVolume (Data Storage)
      ├─ PVC: mysql-pv-claim (250Mi)
      ├─ PV: mysql-pv (250Mi)
      └─ Mount: /var/lib/mysql

Secret Management

Three secrets with specific purposes:

# Secret 1: Root password (admin access)
mysql-root-pass:
  password: YUIidhb667

# Secret 2: Application user (app access)
mysql-user-pass:
  username: kodekloud_gem
  password: B4zNgHA7Ya

# Secret 3: Database name (config)
mysql-db-url:
  database: kodekloud_db5

Environment variable injection:

env:
- name: MYSQL_ROOT_PASSWORD
  valueFrom:
    secretKeyRef:
      name: mysql-root-pass   # Secret name
      key: password           # Key within secret

# MySQL reads env vars on startup:
# 1. Creates root user with MYSQL_ROOT_PASSWORD
# 2. Creates database MYSQL_DATABASE
# 3. Creates user MYSQL_USER
# 4. Grants permissions with MYSQL_PASSWORD

Storage Architecture

PersistentVolume flow:

1. PersistentVolume (mysql-pv)
   - Capacity: 250Mi
   - Storage class: (default)
   - Path: /mnt/data/mysql (hostPath)
   - Status: Available → Bound

2. PersistentVolumeClaim (mysql-pv-claim)
   - Requests: 250Mi
   - Access mode: ReadWriteOnce
   - Binds to: mysql-pv
   - Status: Bound

3. Pod Volume Mount
   - PVC: mysql-pv-claim
   - Mount path: /var/lib/mysql
   - MySQL data files stored here

Data persistence:
Pod restart → Data survives ✓
Pod delete/recreate → Data survives ✓
Node reboot → Data survives ✓ (if PV uses network storage)

Service Exposure

NodePort service:

Service: mysql
Type: NodePort
Selector: app=mysql

Port mapping:
- ClusterIP: 10.96.100.50:3306 (internal)
- NodePort: <any-node-ip>:30007 (external)
- TargetPort: 3306 (container)

Access methods:
1. From within cluster:
   mysql.default.svc.cluster.local:3306

2. From external (any node):
   <node-ip>:30007

3. From pods:
   mysql:3306

💡 Key Takeaways

✨ PersistentVolume ensures data survives pod restarts
✨ Secrets securely store database credentials
✨ Environment variables configure MySQL at startup
✨ NodePort enables external database access
✨ Deployment manages pod lifecycle
✨ Volume binding connects PV to PVC to Pod
✨ Complete stack production-ready database
✨ Security credentials never in plain text

🎓 Quick Interview Questions

Q: Why use PersistentVolume instead of emptyDir for MySQL? A: emptyDir data lost on pod restart/delete. MySQL database needs persistence. PersistentVolume survives pod lifecycle, stores data permanently, enables database backups, disaster recovery.

Q: What's difference between PV and PVC? A: PV is storage resource (admin creates, cluster-wide). PVC is storage request (user creates, namespace-scoped). PVC binds to PV. Like: PV=available apartment, PVC=rental agreement.

Q: Why separate secrets for root and user passwords? A: Principle of least privilege. Root for admin tasks (rarely used). User for application (regular access). Separate secrets enable different RBAC policies, rotation schedules, audit trails.

Q: How does MySQL know to create database and user on startup? A: MySQL official image checks environment variables on first run: MYSQL_DATABASE creates DB, MYSQL_USER + MYSQL_PASSWORD creates user, MYSQL_ROOT_PASSWORD sets root password. Only runs if /var/lib/mysql empty.

Q: What happens if PVC requests more storage than PV provides? A: PVC stays Pending (can't bind). PV must have ≥ requested capacity. Solution: increase PV size or reduce PVC request. Dynamic provisioning auto-creates matching PV.

🚀 Production Enhancements

1. StatefulSet for HA

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql
  replicas: 3
  selector:
    matchLabels:
      app: mysql
  template:
    # ... pod spec
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 10Gi

2. Add Resource Limits

resources:
  requests:
    memory: "1Gi"
    cpu: "500m"
  limits:
    memory: "2Gi"
    cpu: "1000m"

3. Health Checks

livenessProbe:
  exec:
    command:
    - mysqladmin
    - ping
    - -h
    - localhost
  initialDelaySeconds: 30
  periodSeconds: 10
readinessProbe:
  exec:
    command:
    - mysql
    - -h
    - localhost
    - -u
    - root
    - -p$(MYSQL_ROOT_PASSWORD)
    - -e
    - "SELECT 1"
  initialDelaySeconds: 5
  periodSeconds: 5

4. ConfigMap for MySQL Configuration

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config
data:
  my.cnf: |
    [mysqld]
    max_connections=200
    innodb_buffer_pool_size=1G
    character-set-server=utf8mb4

5. Backup CronJob

apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-backup
spec:
  schedule: "0 2 * * *"  # 2 AM daily
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: backup
            image: mysql:5.7
            command:
            - /bin/sh
            - -c
            - mysqldump -h mysql -u root -p$MYSQL_ROOT_PASSWORD --all-databases > /backup/backup-$(date +%Y%m%d).sql
            env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-root-pass
                  key: password
            volumeMounts:
            - name: backup
              mountPath: /backup
          volumes:
          - name: backup
            persistentVolumeClaim:
              claimName: backup-pvc
          restartPolicy: OnFailure

6. StorageClass for Dynamic Provisioning

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: mysql-storage
provisioner: kubernetes.io/aws-ebs
parameters:
  type: gp2
  fsType: ext4

🔧 Troubleshooting

Issue 1: PVC Pending

# Check PVC status
kubectl describe pvc mysql-pv-claim

# Common causes:
# - No PV available with sufficient capacity
# - Access mode mismatch
# - StorageClass mismatch

# Solution: Check PV
kubectl get pv
kubectl describe pv mysql-pv

Issue 2: MySQL Won't Start

# Check pod logs
kubectl logs -l app=mysql

# Common errors:
# - Permission denied on /var/lib/mysql
# - Invalid root password
# - Port already in use

# Check events
kubectl describe pod -l app=mysql

Issue 3: Can't Connect Externally

# Verify service
kubectl get service mysql

# Check NodePort
kubectl describe service mysql | grep NodePort

# Test connectivity
telnet <node-ip> 30007

# Check firewall
sudo firewall-cmd --list-ports
sudo firewall-cmd --add-port=30007/tcp --permanent

Issue 4: Data Not Persisting

# Check volume mount
kubectl exec -l app=mysql -- df -h /var/lib/mysql

# Verify PVC bound
kubectl get pvc mysql-pv-claim

# Check PV
kubectl get pv mysql-pv

📊 MySQL Monitoring

Check MySQL status:

# MySQL version
kubectl exec -l app=mysql -- mysql --version

# Database size
kubectl exec -l app=mysql -- mysql -u root -pYUIidhb667 -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;"

#Active connections
kubectl exec -l app=mysql -- mysql -u root -pYUIidhb667 -e "SHOW PROCESSLIST;"
Server status
kubectl exec -l app=mysql -- mysql -u root -pYUIidhb667 -e "SHOW STATUS LIKE '%thread%';"

🎉 Final Thoughts

You've successfully deployed a complete MySQL database on Kubernetes! This is production-grade infrastructure.

What you accomplished: ✅ Created 3 secrets for credential management
✅ Set up persistent storage with PV/PVC
✅ Deployed MySQL with environment variables
✅ Exposed database via NodePort service
✅ Verified full database functionality

Real-world impact:

• Data Persistence: Survives pod restarts

• Security: Credentials in secrets, not code

• Accessibility: External access via NodePort

• Production Ready: Complete database stack

• Scalability: Foundation for StatefulSet HA

Skills acquired:

• PersistentVolume/PVC management

• Secrets creation and injection

• Database deployment patterns

• Service exposure strategies

• Complete stack orchestration

This is production database infrastructure! 💪

---

Day: 66/100
Challenge: KodeKloud Cloud DevOps
Date: January 11, 2025
Topic: MySQL on Kubernetes

How do you manage databases in Kubernetes? Share your data persistence strategies! 🗄️