Deploying MySQL and phpMyAdmin Management in Kubernetes(En)

In the past, I often set up MySQL databases on VMs to serve as back-end databases for many application services. However, After entering the age of containerized services, I haven’t had the chance to practically deploy such databases. So, I thought of documenting how to deploy a standalone MySQL Database in a Kubernetes in this article, and manage it through phpMyAdmin (which is the basic combination I used to do).

Next, we’ll outline the following steps:

1. Advantages of deploying MySQL in K8S
2. Deploying MySQL DB standalone
3. Deploying PhpMyAdmin (PMA)
4. Conclusion

1. Advantages of deploying MySQL in K8S

Many years ago, when I first encountered MySQL DB, physical machines were predominantly used for deployments due to performance concerns. It wasn’t until VMware emerged that, after years of practical experience, DBAs began to migrate production MySQL databases to virtual machines, unlocking advantages previously inaccessible with physical machines.

As more and more applications embrace containerization, customers are increasingly deploying and managing databases in Kubernetes platforms to improve efficiency in accessing backend databases for their services. Running database management solutions within container platforms offers the following advantages:

(1) Resource Isolation: Kubernetes inherently provides resource isolation through namespaces. With proper design, unrelated services won’t compete for the resources needed by the database.

(2) Dynamic Scaling: Leveraging Kubernetes’ resource management capabilities, such as Vertical Pod Autoscaler (VPA) and Horizontal Pod Autoscaler (HPA), enables dynamic scaling to efficiently handle varying resource demands.

(3) Environment Consistency: Compared to traditional OS installations, all configurations and management methods are consistent within the Kubernetes platform.

(4) Easy Maintenance: There’s no need for additional monitoring solutions; integration with Grafana/Prometheus allows for straightforward monitoring alongside other platform resources.

2. Deploying MySQL DB standalone

In practice, data will be stored using StorageClass. However, if you prefer to create local PVs, you can modify the following section regarding StorageClass.

#-----------------------------------
# S2-1. SVC
#-----------------------------------
[master]# vim mysql-svc.yaml
kind: Service
apiVersion: v1
metadata:
  name: mysql
  namespace: mysql-std
  labels:
    app: mysql
    type: standalone
spec:
  type: NodePort
  ports:
  - name: server
    port: 3306
    protocol: TCP
    targetPort: 3306
    nodePort: 31009
  selector:
    app: mysql
    type: standalone

#-----------------------------------
# S2-2. Statefulset
#-----------------------------------
[master]# vim mysql-sts.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: mysql-std
  labels:
    app: mysql
    type: standalone
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
      type: standalone
  serviceName: mysql
  template:
    metadata:
      labels:
        app: mysql
        type: standalone
    spec:
      # nodeSelector:
      #   app: database
      containers:
      - name: mysql
        image: mysql:latest
        imagePullPolicy: IfNotPresent
        ports:
        - name: server
          containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: "mysql123"
        - name: MYSQL_DATABASE
          value: "app"
        - name: MYSQL_USER
          value: "albert"
        - name: MYSQL_PASSWORD
          value: "albert1122"
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
      #  - name: mysql-conf
      #    mountPath: /etc/mysql/my.cnf
      #    subPath: my.cnf
      #  - name: log
      #    mountPath: /var/log/mysqld.log
        resources:
          limits:
            memory: "4Gi"
            cpu: "2"
          requests:
            memory: "512Mi"
            cpu: "1"
  volumeClaimTemplates:
  - metadata:
      name: data
      labels:
        app: mysql
        type: standalone
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: managed-nfs-storage
      resources:
        requests:
          storage: 5Gi

#-----------------------------------
# S2-3. deploy
#-----------------------------------
[master]# kubectl create namespace mysql-std
[master]# kubectl apply -f mysql-svc.yaml
[master]# kubectl apply -f mysql-sts.yaml
[master]# kubectl get all -n mysql-std
[master]# kubectl get pvc -n mysql-std

[master]# kubectl exec -it mysql-0 /bin/bash
bash-4.4# mysql -u root -p

3. Deploying PhpMyAdmin (PMA)

Typically, I don’t manage databases through commands every time. It becomes highly inefficient, especially when dealing with numerous databases simultaneously.

Therefore, after deploying the database management service, I often deploy an additional graphical management interface to complement it, such as MySQL + PMA combination.

#------------------------------------------
# S3-1. Create deployment file
#------------------------------------------
[master]# vim pma-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: phpmyadmin-deployment
  labels:
    app: phpmyadmin
spec:
  replicas: 1
  selector:
    matchLabels:
      app: phpmyadmin
  template:
    metadata:
      labels:
        app: phpmyadmin
    spec:
      containers:
        - name: phpmyadmin
          image: phpmyadmin/phpmyadmin
          ports:
            - containerPort: 80
          env:
            - name: PMA_HOST
              value: <mysql pod ip>
            - name: PMA_PORT
              value: "3306"

#------------------------------------------
# S3-2. svc
#------------------------------------------
[master]# vim pma-svc.yaml
apiVersion: v1
kind: Service
metadata:
  name: phpmyadmin-service
spec:
  type: NodePort
  selector:
    app: phpmyadmin
  ports:
  - protocol: TCP
    nodePort: 31008
    port: 80
    targetPort: 80

#-------------------------------------------
# S3-3. execute yaml
#-------------------------------------------
[master]# kubectl create -f pma-secret.yaml
[master]# kubectl create -f pma-svc.yaml
[master]# kubectl create -f pma-deployment.yaml

4. Conclusion

Today, I set up MySQL standalone and phpMyAdmin (PMA) in Kubernetes. You can find tons of methods online, but it’s crucial to find one that suits you best, something you can build on later.

This article covers the basics. Next time, I’ll dive into setting up a more practical Master-Slave architecture. Hope these articles help you out.

That’s a wrap for now. Catch you in the next one!