Migrate Ghost Blog from SQLite to MySQL 8 running on Kubernetes

This is second article regarding ghost blog.  

As annoying as is upgrading Ghost Blog is a must due to security patches and functional upgrades. This is especially true if you're self hosting it.

In previous article I was describing how to setup Ghost with Kubernetes on DigitalOcean. This time we're upgrading from SQLLite 3 to MYSQL 8, still running on single node. Although this configuration could easily be upgraded to multiple replicas.

I paid my price :) Starting from 0 members on 02/01/2023. I did backup the whole folder with SQLite DB but I'd have to go in with my bare hands and pluck that data out. I'm cutting my losses in favor of my time.

If you're setting up Ghost for the first time check this blog post first: https://igor.technology/installing-ghost-on-digitalocean-with-kubernetes/

If you'd like to setup newsletter emailing with Mailgun check this article: https://igor.technology/ghost-blog-setting-up-environment-variables-for-email-signup/

The process is as follows:

1. Backup and manual backup instructions. I recommend you do both or at minimal manual backup
2. Create mysql kubernetes deployment files and deploy to your cluster.
3. Update deployment file of ghost kubernetes to utilize the mysql 8 from now on.
4. Import all the exported content (if you have a new PersistentVolumeClaim restore the folders and files from your backup).

I'd also recommend to tar the complete content folder. The way to access the pod from kubectl is:

kubectl exec -ti ghost-blog-podname /bin/bash
cd /var/lib/ghost
tar cvf ghost_backup.tar.gz content/

Then copy the file to local computer:

kubectl cp ghost-blog-podname:/var/lib/ghost/ghost_backup.tar.gz .

Kubernetes Config

Prerequisite: Create Kubernetes secret.yaml and deploy to kubernetes:

apiVersion: v1
kind: Secret
metadata:
  name: ghost-secrets
  namespace: default
type: Opaque
stringData:
  password: mypass
  url: https://mydomain.com
  mail_from: My Name <postmaster@mydomain.com>
  mailgun_username: postmaster@mydomain.com
  mailgun_password: mypassword

kubectl create -f secret.yaml

MySQL8 Kubernetes deployment

The mysql8 creates a new service, volume and deployment.

apiVersion: v1
kind: Service
metadata:
  name: ghost-mysql
  namespace: default
  labels:
    app: ghost
spec:
  ports:
    - port: 3306
  selector:
    app: ghost
    tier: mysql
  clusterIP: None
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: blog-mysql
  namespace: default
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
  storageClassName: do-block-storage
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: ghost-mysql
  namespace: default
  labels:
    app: ghost
spec:
  selector:
    matchLabels:
      app: ghost
      tier: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: ghost
        tier: mysql
    spec:
      containers:
        - image: mysql:8.0.31-debian
          name: mysql
          env:
            - name: MYSQL_DATABASE
              value: "ghost"
            - name: MYSQL_USER
              value: "ghost"
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: ghost-secrets
                  key: password
          ports:
            - containerPort: 3306
              name: mysql
          volumeMounts:
            - name: mysql-persistent-storage
              mountPath: /var/lib/mysql
      volumes:
        - name: mysql-persistent-storage
          persistentVolumeClaim:
            claimName: blog-mysql

Deploy MySQL8 onto your cluster: kubectl apply -f ghost-mysql.yaml

Check if everything is working by entering pods console: kubectl exec -ti ghost-mysql-xxxx /bin/bash. Login to with mysql client: mysql -u root -p mypassword

Modify Ghost Blog deployment

piVersion: apps/v1
kind: Deployment
metadata:
  name: blog
  labels:
    app: blog
spec:
  replicas: 1
  selector:
    matchLabels:
      app: blog
  template:
    metadata:
      labels:
        app: blog
    spec:
      containers:
        - name: blog
          image: ghost:5.26.4
          imagePullPolicy: Always
          ports:
            - containerPort: 2368
          env:
            - name: url
              valueFrom:
                secretKeyRef:
                  name: ghost-secrets
                  key: url
            - name: database__client
              value: mysql
            - name: database__connection__host
              value: ghost-mysql
            - name: database__connection__user
              value: root
            - name: database__connection__password
              valueFrom:
                secretKeyRef:
                  name: ghost-secrets
                  key: password
            - name: database__connection__database
              value: ghost
            - name: mail__transport
              value: SMTP
            - name: mail__from
              valueFrom:
                secretKeyRef:
                  name: ghost-secrets
                  key: mail_from
            - name: mail__options__service
              value: Mailgun
            - name: mail__options__auth__user
              valueFrom:
                secretKeyRef:
                  name: ghost-secrets
                  key: mailgun_username
            - name: mail__options__auth__pass
              valueFrom:
                secretKeyRef:
                  name: ghost-secrets
                  key: mailgun_password
          volumeMounts:
            - mountPath: /var/lib/ghost/content
              name: content
      volumes:
        - name: content
          persistentVolumeClaim:
            claimName: blog-content

The Ghost Blog deployment assumes you've already had a PersistentVolumeClaim where all the ghost blog files live. So I'm excluding it from here.

Importing the data

After all is done importing the exported JSON should restore all prior written articles back. All we have left to do is to re-import the design and we're done.

In case there are no pictures/files/media in the articles you'd have to copy backed up files under folder /var/lib/ghost/content.  

Complete project on GitHub