Upgrade Ghost with SQLite to MySQL

Since Ghost made the change from it's built in SQLite database to MySQL 8 I was forced to upgrade to the latest version of Ghost to keep everything up-to-date and secure since there was a critical update recently.

When I set this site up I wanted it lightweight and easy as possible, so I used another tutorial back in the days to make it work with traefik. The only tutorial I found was using the "old" version 5.9.2-alpine.

Thanks to this Site for writing a tutorial which helped me initially to upgrade to MySQL. But since I was using multiple services with traefik this wasn't a complete step-by-step guide for me.

Step 1: Backup your old posts

Go to Settings -> Labs -> Export all Content. This inclued all posts, drafts, theme and settings but not the registered subscribers. Since I don't have any subscribed users there is no need to back them up. You probably have to export them out of your SQLite db I guess, but I'm not sure about that.

When you've donwloaded all your backup files, stop the container. I used Portainer for that. Containers -> Mark your old ghost container -> Stop.

Step 2: Prepare folder structure

Because I didn't want to delete the old volumes before switching I created a new folder in my docker directory:

sudo mkdir /opt/containers/new-ghost
sudo mkdir /opt/containers/new-ghost/app
sudo mkdir /opt/containers/new-ghost/db

Keep in mind that you probably need to delete the container instance with portainer first if you are not going to rename your container/service in the .yml file below. Thats why I renamed the new container and put it in a different directory.

Step 3: Prepare docker-compose.yml file

Because MySQL is not included in the docker ghost image you need to add mysql as a service to your docker file:

sudo nano /opt/containers/new-ghost/docker-compose.yml

version: '3.3'

services:
  mysql:
    image: mysql:8.0 #minimum supported version by ghost
    container_name: mysql-blog
    volumes:
      - ./database:/var/lib/mysql
    networks:
     - default # this depends on your traefik configuration
    environment:
      - MYSQL_ROOT_PASSWORD=your_root_password # Change that
      - MYSQL_DATABASE=ghost
      - MYSQL_USER=ghost
      - MYSQL_PASSWORD=mysql_db_password # Change that
    restart: unless-stopped
  ghost:
    image: ghost:latest
    restart: unless-stopped
    ports:
      - 2368
    depends_on:
     - mysql
    volumes:
      - ./app:/var/lib/ghost/content
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.ghost.entrypoints=http"
      - "traefik.http.routers.ghost.rule=Host(`pawnda.de`,`www.pawnda.de`)"
      - "traefik.http.middlewares.ghost-https-redirect.redirectscheme.scheme=https"
      - "traefik.http.routers.ghost.middlewares=ghost-https-redirect"
      - "traefik.http.routers.ghost-secure.entrypoints=https"
      - "traefik.http.routers.ghost-secure.rule=Host(`pawnda.de`, `www.pawnda.de`)"
      - "traefik.http.routers.ghost-secure.tls=true"
      - "traefik.http.routers.ghost-secure.tls.certresolver=http"
      - "traefik.http.routers.ghost-secure.service=ghost"
      - "traefik.http.services.ghost.loadbalancer.server.port=2368"
      - "traefik.docker.network=proxy"
    networks:
      - default # this depends on your traefik configuration
      - proxy   # this depends on your traefik configuration
    environment:
      - url=https://pawnda.de
      - database__client=mysql
      - database__connection__host=mysql-blog
      - database__connection__user=ghost
      - database__connection__password=mysql_db_password # MYSQL_PASSWORD
      - database__connection__database=ghost # MYSQL_USER
    container_name: ghost-latest
networks:
   proxy:
      external: true # this depends on your traefik configuration

As I mentioned above, I am using traefik to host multiple services on one VPS/IP. I'm not going to explain how to set up traefik in advance but I consider you to do so if you are in the same environment as I am. Please make sure you edit the .yml file with the networks in your traefik-config.

Step 4: Pull and Start

Now you can compose your .yml file with the following command:

docker-compose -f docker-compose.yml up -d

Your container should be downloaded, created and start up automatically. If you are using Portainer like me you should see the container there up and running. You can check there too if ghost is getting an internal network address like 127.0.0.x which could be different because of your traefik configuration.

Step 5: Import your backup data

All your content is currently missing on the new ghost site so open up your website like this: https://pawnda.de/ghost/ and re-create your user.

Then got to Settings -> Labs -> Import content and import the previously downloaded .json file.

Thats it! Done...