# Setup MySQL with Wordpress in k8s - Easy migrate or not !!
###### tags: `research` `devops` `reliable` `tutorials`

`HELLO, LONG TIME NO SEE I JUST BRING BACK AFTER THE BREAK STRUGGLE ISSUE ON IMPLEMENTATION AND PROVISIONING AND ALSO I JUST FINISHING THE FLAT OF CAPSTONE PROJECT FOR SCALING PROJECT. SO I JUST WRITE A BLOG FOR GIVE EXPERIENCE, TAKE A BREAK AFTER ISSUE I MET ON ALONG LAST WEEK. TERRIBLE AND STRESSFUL`


So i just want a talk it hard or not maybe base on your mindset. LOL and look below for meet some mistake when i met on provision progress :smiley:. Stay avoid it and don't make struggle mistake like me :face_with_finger_covering_closed_lips:

## The mindset on project:
- Migrate the on-prem wordpress to k8s wordpress, this is not to hard but also it will make you have some confuse and not understand when setup the another tools in K8s
- With wordpress, it always have legacy db - MySQL intergration with itself. So not only wordpress, we need to move mysql and datainside go to the k8s.

## Step by step of progress when migrate and why i have struggle with that.

#### Setup the wordpress and mysql with k8s.
- So by the way working with k8s, you can deployed with raw YAML file or working it with terraform - both of them will work and use for specify situation


- On my situation it just use compress between raw YAML and Terraform. So you can understand is Terraform will have to access k8s with credentials and run YAML file inside. That all :+1: and go to detail that.
1. First of all you need to configure driver for your k8s - Because on my situation, i work on k8s managed Azure is AKS. So i need to install driver for using the external object of Azure in AKS, especially about Storage things.
This is preparing for create PVC for mounting data of MySQL On-Prem into MySQL K8s. And struggle is currently starting :smile:. So before that you just use az-cli or az powershell (Tools of Azure) for enable driver for you cluster. Step for do it

az login # That step requires for getting the subscription
 az account set --subscription <subscription-wherer-aks-use> # Change the subscription for your az-cli
 az aks show -n <name-of-aks> -g <resource-group-of-aks> | jq -r ".storageProfile.blobCsiDriver"

if it not enable, you can use this to update

az aks update --enable-blob-driver -n <name-of-aks> -g <resource-group-of-aks>

**NOTICE: This Process will take a couple minutes, so don't worry just wait to see the result blob drive is enabled !**

2. So for optimize the time for create blob with `script`, you can go directly to `Azure portal` and create that on `Storage Account` which you want
On situation it will ask you optional about `Anonymous access level`
of blob but for securing i choose `private`

**NOTICE: Do not put anything this kind into the blob right know. Because it will cause failure when MySQL runs. Remmember about that (This is my mistake) :satellite:**

3. So go for Terraform and YAML file to create wordpress and MySQL, this is the nightmare is started LOL. But don't worry i will note about that. Go detail for the script. I just put the main.tf but as you Terraform. Go check my [Terraform blog](https://hackmd.io/7M0GBhCARJuyWJLxN_vCdQ)

resource "random_string" "credentials_website" {
  length      = 15
  min_lower   = 5
  min_upper   = 5
  min_numeric = 5
  special     = false
}

resource "random_uuid" "website_uuid" {
}

resource "kubernetes_secret" "credentials_website" {
  metadata {
    name = "credentials-website"
  }

  data = {
    "WEBSITE_DATABASE_ROOT_PASSWORD" = random_string.credentials_website.result
  }
}

resource "kubernetes_secret" "website_storage_account" {
  metadata {
    name = "website-storage-account"
  }

  data = {
    "accountName" = var.remote_state.website_storage_account_name
    "accountKey"  = var.remote_state.website_storage_account_key
  }
}

resource "kubernetes_persistent_volume" "website_mysql" {
  metadata {
    name = "website-mysql"
  }
  spec {
    capacity = {
      storage = "5Gi"
    }
    access_modes       = ["ReadWriteOnce"]
    storage_class_name = "azureblob"
    mount_options = [
      "-o allow_other", "--file-cache-timeout-in-seconds=120"
    ]
    persistent_volume_source {
      csi {
        driver        = "blob.csi.azure.com"
        volume_handle = "website-mysql-${random_uuid.website_uuid.result}"
        volume_attributes = {
          "containerName" = "website-mysql"
        }
        node_stage_secret_ref {
          name      = "website-storage-account"
          namespace = "default"
        }
      }
    }
  }
  depends_on = [kubernetes_secret.website_storage_account]
}

resource "kubernetes_persistent_volume" "website_wp" {
  metadata {
    name = "website-wp"
  }
  spec {
    capacity = {
      "storage" = "5Gi"
    }
    access_modes       = ["ReadWriteOnce"]
    storage_class_name = "azureblob"
    mount_options = [
      "-o allow_other", "--file-cache-timeout-in-seconds=120"
    ]
    persistent_volume_source {
      csi {
        driver        = "blob.csi.azure.com"
        volume_handle = "website-wp-${random_uuid.website_uuid.result}"
        volume_attributes = {
          "containerName" = "website-wp"
        }
        node_stage_secret_ref {
          name      = "website-storage-account"
          namespace = "default"
        }
      }
    }
  }
  depends_on = [kubernetes_secret.website_storage_account]
}

resource "kubernetes_persistent_volume_claim" "website_mysql" {
  metadata {
    name = "website-mysql"
  }
  spec {
    access_modes = ["ReadWriteOnce"]
    resources {
      requests = {
        "storage" = "5Gi"
      }
    }
    volume_name        = kubernetes_persistent_volume.website_mysql.metadata[0].name
    storage_class_name = "azureblob"
  }
  wait_until_bound = true
  depends_on       = [kubernetes_persistent_volume.website_mysql]
}

resource "kubernetes_config_map" "website_mysql" {
  metadata {
    name = "website-mysql-conf"
  }
  data = {
    "my.cnf" = "${file("${path.module}/data/my.cnf")}"
  }
}

resource "kubernetes_persistent_volume_claim" "website_wp" {
  metadata {
    name = "website-wp"
  }
  spec {
    access_modes = ["ReadWriteOnce"]
    resources {
      requests = {
        "storage" = "5Gi"
      }
    }
    volume_name        = kubernetes_persistent_volume.website_wp.metadata[0].name
    storage_class_name = "azureblob"
  }
  wait_until_bound = true
  depends_on       = [kubernetes_persistent_volume.website_wp]
}

resource "kubectl_manifest" "mysql_service" {
  yaml_body  = <<YAML
apiVersion: v1
kind: Service
metadata:
  name: website-mysql-service
  labels:
    app: website-mysql-service
spec:
  type: ClusterIP
  selector:
    app: website-mysql
  ports:
    - port: 3306
      protocol: TCP
YAML
  depends_on = [kubernetes_secret.website_storage_account, kubernetes_persistent_volume_claim.website_mysql]
}

resource "kubectl_manifest" "mysql_pod" {
  yaml_body  = <<YAML
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: website-mysql
spec:
  selector:
    matchLabels:
      app: website-mysql
  serviceName: website-mysql-service
  replicas: 1
  template:
    metadata:
      labels:
        app: website-mysql
    spec:
      nodeSelector:
        pool: defaultpool
      containers:
      - name: mysql-server
        image: mysql:5.7
        ports:
        - name: mysql
          containerPort: 3306
        args:
          - "--defaults-file=/mysql/conf/my.cnf"
          - "--ignore-db-dir=lost+found"
        env:
          - name: MYSQL_ROOT_PASSWORD
            valueFrom:
              secretKeyRef:
                name: credentials-website
                key: WEBSITE_DATABASE_ROOT_PASSWORD 
          - name: MYSQL_DATABASE
            value: "wordpress"
        volumeMounts:
        - name: website-mysql-data
          mountPath: /mysql/website
        - name: website-mysql-conf
          mountPath: /mysql/conf
          readOnly: true
      volumes:
        - name: website-mysql-data
          persistentVolumeClaim:
            claimName: website-mysql
        - name: website-mysql-conf
          configMap:
            name: website-mysql-conf
            items:
              - key: "my.cnf"
                path: "my.cnf"
YAML
  depends_on = [kubernetes_secret.credentials_website, kubernetes_persistent_volume_claim.website_mysql]
}

resource "kubectl_manifest" "wp_service" {
  yaml_body  = <<YAML
apiVersion: v1
kind: Service
metadata:
  name: website-wp-service
  labels:
    app: website-wp-service
spec:
  type: ClusterIP
  selector:
    app: website-wp
  ports:
    - name: wp-http
      protocol: TCP
      port: 80
      targetPort: 80
    - name: wp-https
      protocol: TCP
      port: 443
      targetPort: 443
YAML
  depends_on = [kubectl_manifest.mysql_pod, kubectl_manifest.mysql_service]
}

resource "kubectl_manifest" "wp_pod" {
  yaml_body  = <<YAML
apiVersion: apps/v1
kind: Deployment
metadata:
  name: website-wp
spec:
  selector:
    matchLabels:
      app: website-wp
  serviceName: website-wp-service
  replicas: 1
  template:
    metadata:
      labels:
        app: website-wp
    spec:
      nodeSelector:
        pool: defaultpool
      containers:
      - name: wordpress
        image: wordpress:5.7.2-php7.4-apache
        resources:
          limits:
            cpu: 400m
            memory: 450Mi
          requests:
            cpu: 300m
            memory: 300Mi
        ports:
        - containerPort: 80
          name: wp-http
          protocol: TCP
        - containerPort: 443
          name: wp-https
          protocol: TCP
        env:
          - name: WORDPRESS_DB_HOST
            value: "website-mysql-service"
          - name: WORDPRESS_DB_USER
            value: "root"
          - name: WORDPRESS_DB_PASSWORD
            valueFrom:
              secretKeyRef:
                key: WEBSITE_DATABASE_ROOT_PASSWORD
                name: credentials-website
          - name: WORDPRESS_DB_NAME
            value: "wordpress"
        volumeMounts:
        - name: website-wp-data
          mountPath: /var/www/html
      volumes:
        - name: website-wp-data
          persistentVolumeClaim:
            claimName: website-wp
YAML
  depends_on = [kubectl_manifest.mysql_pod, kubectl_manifest.mysql_service]
}

resource "kubectl_manifest" "wp_ingress" {
  yaml_body  = <<YAML
apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: website-wp
spec:
  ingressClassName: nginx
  tls:
    - hosts:
        - ${var.website_wp_admin}
      secretName: https-certificate
  rules:
    - host: ${var.website_wp_admin}
      http:
        paths:
          - path: "/"
            pathType: Prefix
            backend:
              service:
                name: website-wp-service
                port:
                  number: 80
YAML
  depends_on = [kubectl_manifest.mysql_pod, kubectl_manifest.mysql_service]
}

With the script i put it inhere, the step about that will go from
- Go to create the password with random func of Terraform
- K8s will work with data-mounting by many storage, which type can help you mounting way for helping you configure Pods or workload of K8s-things via **Secret and ConfigMap (As you know you know :smiley:)**. So with dangerous information about the password and credential you need priority to choice secret. So second block terraform, Is create secret with random password
- So go to stupid things when use configure Azure, it will not clearly to you for choice with exactly optional for create Storage Class and PVC on it :crocodile: (So in my opinion, Actually Microsoft create this thing to confuse)
*On this step you need to add more thing create what storage class what you want and it will is it but **Remember: Driver need to enable**.*

resource "kubernetes_storage_class" "azureblob_csi_nfs" {
  metadata {
    name = "azureblob"
  }
  storage_provisioner = "blob.csi.azure.com"
  reclaim_policy      = "Retain"
  parameters = {
    skuName = "Standard_LRS"
  }
  mount_options = [
    "-o allow_other", "--file-cache-timeout-in-seconds=120",
    "--use-attr-cache=true", "--cancel-list-on-mount-seconds=10",
    "-o attr_timeout=120", "-o entry_timeout=12",
    "-o negative_timeout=120", "--log-level=LOG_WARNING",
  ]
}

This is hard to mount_options and understand what situation for doing that. I just said some thing about information, it just shortly in side [this blog](https://learn.microsoft.com/en-us/azure/aks/azure-csi-blob-storage-provision?tabs=mount-nfs%2Csecret) and [blob CSI repo](https://github.com/kubernetes-sigs/blob-csi-driver/tree/master) for example. So you need to find exactly repo to understand theory and why they use that kind paramter and what tech is used inside. The technologies behind is `blobfuse` and `blobfuse 2` (some optional but this is popular)

*Go for that you will need create PVC for them and PVC for them is need you to set again mount optional **(Too bad for duplicate them but fact, it will not work if you don't because when you go to inside pod it will run with MySQL user but with BlobFuse it need root**. Hard thing to understand if you do wrong and so focus to doing that to bypass this before the pod MySQL can go)*


*The curious thing will not stop in here, when you running MySQL pod in non't optional on MySQL when use BlobFuse PVC - IDK why the heck is Storage Mounting will create before the MySQL Running (Error cause in here and crashloopback container) - On Docker it not happening (Too Bad :-1:). So that why you need to more config this kind `"--defaults-file=/mysql/conf/my.cnf" "--ignore-db-dir=lost+found"` on config **PARAM MYSQL** for bypass this error when running that one with blob storage in K8s. The new my.cnf is*

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /mysql/website/data
secure-file-priv= NULL
default-authentication-plugin=mysql_native_password

# Custom config should go here
!includedir /etc/mysql/conf.d/

So go to running that with terraform :coffee: and see the result. Hope you can work perfectly like i said :smiling_face_with_smiling_eyes_and_hand_covering_mouth:

**Notice: Easily with Docker LOL, do remember set optional for both of SC and PVC for bypass the non root work with blobfuse inside and one more thing blobfuse will be change the file to state ??? when you run ls -la for file in folder. But it oke, On container you can't erase but storage blob you can use
Need to rechange config for running this pods and everything will be okay, 99% i ensure about that :smiling_face_with_smiling_eyes_and_hand_covering_mouth:**

- So pass the noisy thing, the prrety other is quite easily LOL. So on this step you need to do next is dump MySQL DB --> To SQL file

mysqldump -u <user-access-mysql> -p <database-name> > <database-name>.sql

Remember change something about URL if you need to configure HTTP - HTTPS (just optinal if you have LOL)

- If you need to concern more about configuration, one more thing you need to focus is `APACHE2 .htaccess`for about webserver configure and `wp-admin` for about Wordpress configure, the default configure is okay but some kind situation you need to understand about that too.

#### Preparing the thing what you want, Moving onprem --> K8s. Go live it
- Azure offer for users can mount data into blob by using az-cli or azcopy but for easily interactive with AzureBlob --> [azcopy-tools](https://learn.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10) for copy data from your machine into AzureBlob
- After install azcopy, login azcopy with your azure account

azcopy login

- Login succeed, so you need generate SAS Token for each BlobStorage which want to mount data inside by UI
**NOTICE: Need to set purpose role and what public ip address of your local pc where storage the data want to be mounted**
- After generate SAS token, Now you are having SAS token, so you just need read command in [azcopy copy](https://learn.microsoft.com/en-us/azure/storage/common/storage-ref-azcopy-copy) for writing script to mounting data from local into blob storage. Example

    azcopy copy <directory-or-file-to-mount> <blob-sas-url> --recursive

**You can change the route of path where you want data to mounted in before Blob SAS Token**

- First of all, MySQL is package in file dump from On-Opem. So mount that to blob with

    azcopy copy <database.sql> <blob-mysql-url> --recursive

After your mount process complete:
- Check the blobstorage have your file
- Access mysql to import sql file into mysql on AKS by command
`mysql -u <user-access-mysql> -p < <database.sql>`
- Access mysql to check mysql have wordpress database (password mysql will store inside tfstate file or aks secret credentials-bravowebsite)

    mysql -u <user-access-mysql> -p
    use wordpress;
    show tables;

- Secondly with WP, Just need mount only pluggin and upload folder in wp-contents folder into wp Azureblob

    azcopy copy <directory-pluggin> <blob-wp-url-pluggin> --recursive
    azcopy copy <directory-upload> <blob-wp-url-uploads> --recursive

- After complete, the wordpress website will have full data and pluggin of wordpress older --> For some reason in AKS, wordpress will be load slowly because loading to much pluggin. Just need to waiting for cache on browser on first request and wordpress will work greate but it will slower than physical system

## Conclusion
- So that all thing i want to share about i know about to create or migrate WP from on-prem to k8s (AKS)
- This not to hard but it need to more clearly on driver and anything relate with storage of Azure
- So this is job i do about 4-5 months ago, so I don't remember too much if missing somthing but it all clearly i want to share.
- Maybe on next time, can we go to deploy Wordpress and learn Apache2 config and WP-Admin. So see you about that on that session

## Reference
- [Example - WP and MySQL](https://kubernetes.io/docs/tutorials/stateful-application/mysql-wordpress-persistent-volume/)
- [Use Azure Blob storage Container Storage Interface (CSI) driver](https://learn.microsoft.com/en-us/azure/aks/azure-blob-csi)
- [Create and use a volume with Azure Blob storage in Azure Kubernetes Service (AKS)](https://learn.microsoft.com/en-us/azure/aks/azure-csi-blob-storage-provision?tabs=mount-nfs%2Csecret)
- [Example - CSI Azure](https://github.com/kubernetes-sigs/blob-csi-driver/tree/master/deploy/example)
- [azure-storage-fuse](https://github.com/Azure/azure-storage-fuse)
- [Az-Copy V10](https://learn.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10)