CFN Cloud
2025-10-07

Create a MySQL Database

Run a single-instance MySQL with PVC, Deployment, and Service.

This example starts a single MySQL instance in the cluster with persistent storage. It is a good baseline for learning how stateful workloads behave in Kubernetes, but it is not production-ready. You will use a Deployment here for simplicity and later switch to a StatefulSet for stability.

This quick start adds secrets, initialization, connectivity checks, and basic operational practices so you can run a small database safely in a dev or test environment.

What you will build

  • A PVC for persistent storage.
  • A single MySQL Pod with a root password stored in a Secret.
  • A ClusterIP Service for in-cluster access.

Create a namespace

Keep databases isolated from app workloads:

kubectl create namespace db

Create a Secret

Store the root password in a Secret instead of plain YAML:

kubectl -n db create secret generic mysql-auth \
  --from-literal=MYSQL_ROOT_PASSWORD='ChangeMe123!'

Core resources

Below is a minimal Deployment, PVC, and Service:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pvc
  namespace: db
spec:
  accessModes: ["ReadWriteOnce"]
  resources:
    requests:
      storage: 10Gi
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
  namespace: db
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql:8.0
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-auth
                  key: MYSQL_ROOT_PASSWORD
          ports:
            - containerPort: 3306
          volumeMounts:
            - name: data
              mountPath: /var/lib/mysql
      volumes:
        - name: data
          persistentVolumeClaim:
            claimName: mysql-pvc
---
apiVersion: v1
kind: Service
metadata:
  name: mysql
  namespace: db
spec:
  selector:
    app: mysql
  ports:
    - port: 3306
      targetPort: 3306

Apply it:

kubectl apply -f mysql.yaml

Verify Pod and PVC

kubectl get pods -n db
kubectl get pvc -n db
kubectl get svc -n db

If the Pod stays Pending, check PVC binding and StorageClass availability.

Connect with a temporary client

Start a short-lived client Pod to verify connectivity:

kubectl run -it --rm mysql-client \
  --namespace db \
  --image=mysql:8.0 \
  --command -- bash

Inside the client:

mysql -h mysql.db.svc.cluster.local -u root -p

Create a database and table:

CREATE DATABASE IF NOT EXISTS demo;
USE demo;
CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(64));
INSERT INTO items VALUES (1, 'hello');

Health checks

Add readiness and liveness probes so the Service only routes to healthy Pods. MySQL can take time to initialize, so use a reasonable delay:

livenessProbe:
  exec:
    command: ["mysqladmin", "ping", "-h", "127.0.0.1"]
  initialDelaySeconds: 30
  periodSeconds: 10
readinessProbe:
  exec:
    command: ["mysqladmin", "ping", "-h", "127.0.0.1"]
  initialDelaySeconds: 10
  periodSeconds: 5

For larger datasets, increase initialDelaySeconds to avoid premature restarts. If the liveness probe is too aggressive, MySQL can get stuck in a restart loop during recovery.

Resource requests

Databases are sensitive to CPU and IO throttling. Set requests and limits:

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

Tune based on workload and node size.

Security and least privilege

Avoid using the root account for applications. Create a dedicated user with limited grants, and rotate credentials periodically. Limit network access to the database with NetworkPolicy and keep the Service internal unless you have a strong reason to expose it.

If you see permission errors on the data directory, set a fsGroup in the Pod security context so the MySQL process can write to the volume.

Volume reclaim policy

PVC deletion can remove data if the storage class uses Delete reclaim policy. For important data, prefer Retain so you can recover volumes manually. Check the policy in kubectl get storageclass.

Document which classes are safe for stateful data.

StorageClass selection

If your cluster has multiple StorageClasses, pick one that fits database IO needs (often SSD-backed). You can set it on the PVC:

spec:
  storageClassName: fast-ssd
  accessModes: ["ReadWriteOnce"]
  resources:
    requests:
      storage: 10Gi

Check available classes:

kubectl get storageclass

Initialize schema and users

For a quick dev database, you can run SQL once after startup. A common pattern is to exec into the Pod and apply a script:

kubectl exec -n db deploy/mysql -- \
  mysql -u root -p -e "CREATE DATABASE appdb; CREATE USER 'app'@'%' IDENTIFIED BY 'AppPass123!';"

For repeatable environments, consider using an init container to load SQL or move to a chart that supports initdb scripts.

Configuration overrides

MySQL defaults are not always good for production. You can override configs by mounting a custom config file:

volumes:
  - name: mysql-config
    configMap:
      name: mysql-config
volumeMounts:
  - name: mysql-config
    mountPath: /etc/mysql/conf.d
    readOnly: true

Then create a ConfigMap with settings like max_connections or slow_query_log.

Connection pooling

Applications should use a connection pool to avoid exhausting MySQL. If you see too many connections, increase max_connections carefully and tune pool sizes in your app to match the workload.

PVC expansion

If your storage class supports expansion, you can grow the PVC by editing spec.resources.requests.storage. Monitor resize events and make sure the filesystem is expanded inside the Pod if required by the storage driver.

Access from other Pods

Use the Service DNS name for in-cluster access:

mysql -h mysql.db.svc.cluster.local -u root -p

If you need to limit access, add a NetworkPolicy that only allows traffic from your app namespace.

You can pass the host via environment variables:

env:
  - name: MYSQL_HOST
    value: mysql.db.svc.cluster.local

Backup basics

Even for a single instance, define a backup approach. A simple logical dump is enough for dev:

kubectl exec -n db deploy/mysql -- \
  mysqldump -u root -p demo > backup.sql

Store backups outside the cluster.

Restore test

Backups are only useful if you can restore them. Test a restore in a separate namespace or a new Pod:

kubectl exec -n db deploy/mysql -- \
  mysql -u root -p demo < backup.sql

Validate that critical tables and indexes exist after restore. Run this periodically so you know the process works before an incident.

Monitoring basics

At minimum, watch Pod restarts, disk usage, and latency. You can expose MySQL metrics with a sidecar exporter later, but even basic kubectl top and MySQL SHOW STATUS queries are enough to catch early issues.

Check slow queries with:

SHOW VARIABLES LIKE 'slow_query%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Keep an eye on log volume. If logs grow too quickly, set rotation policies on the node or ship logs to a centralized system.

Upgrade path

Avoid in-place upgrades without testing. Create a staging namespace, restore a backup, and validate the upgrade. Then apply the same process in production with a maintenance window.

Cleanup

If this is just a demo, remove the resources and PVC when done:

kubectl delete -n db deploy/mysql svc/mysql pvc/mysql-pvc secret/mysql-auth

Remember that PVC deletion removes data, so double-check before cleaning up.

When to switch to StatefulSet

  • You need stable network identity per replica.
  • You want automatic PVC provisioning per replica.
  • You are adding replicas or want better restart guarantees.

Deployments work for quick experiments; StatefulSets are safer for production.

Scaling considerations

Scaling a Deployment with a single PVC is not safe for MySQL because multiple Pods would mount the same volume. If you need replicas, move to StatefulSet or a chart that supports replication.

Common issues

  • Pod Pending: PVC not bound or StorageClass missing.
  • CrashLoopBackOff: wrong password or low memory.
  • Connection refused: MySQL not ready or Service selector mismatch.
  • Disk full: expand the PVC if supported, or clean up old data.

Quick diagnostics:

kubectl describe pod -n db <pod-name>
kubectl logs -n db <pod-name>

Practical notes

  • Start with a quick inventory: kubectl get nodes, kubectl get pods -A, and kubectl get events -A.
  • Compare desired vs. observed state; kubectl describe usually explains drift or failed controllers.
  • Keep names, labels, and selectors consistent so Services and controllers can find Pods.

Quick checklist

  • The resource matches the intent you described in YAML.
  • Namespaces, RBAC, and images are correct for the target environment.
  • Health checks and logs are in place before promotion.
  • Backups and restore procedures are documented.
  • Credentials are stored in Secrets, not plain text.

Wrap-up: databases need boring discipline

If you’re putting a database in Kubernetes, do the boring stuff:

  • requests/limits that match reality
  • backups + restore drills
  • a plan for upgrades and failover

That’s what keeps the 3am pages away.

References

View original