Upgrade Ghost with SQLite to MySQL

This blog is running Ghost. No big secret there. Recently Ghost did a minor release update, breaking support for the lite SQLite3 database format, which this blog was using.

When I set up the blog, I chose to use SQLite3, because it's lightweight, simple, but still performed pretty good. I assumed that it would be supported for the life-time of the Ghost major version 5. Oh, how wrong I was. During the update from 5.8 to 5.9, Ghost had made SQLite3 completely unsupported and now only supports using MySQL. The problem with this is, that they did not make any migration tools for this change. Especially, since my install is based on Docker (with compose). But you can switch to MySQL and still keep all your content.

So here is a step by step guide on how to do this.

Step 1: Backup your old installation.

Make sure your install is running 5.8. Then go to Settings -> Labs -> Export all content. This will give you a json file with all the content from the database. If you have members, you need to do Export Members too.

When you have these files, then turn of your container with

sudo docker compose down

Now you need to backup the old database file, just in case, as it does not get detected by newer versions. It's in config/data/ghost.db.

Step 2: Prepare for new database.

MySQL is not included in the Ghost docker images, so you need to add it as a separate image. I did this in my Ghosts docker compose file, since I have no other container using MySQL.

Below is my docker compose file for the updated Ghost docker install (change to match your install):

version: '3.1'
services:
  ghost:
    container_name: blog
    image: ghost:5
    volumes:
      - /mnt/media/ghost/blog/:/var/lib/ghost/content/
    environment:
      - url=https://blog.strits.dk
      - database__client=mysql
      - database__connection__host=mysql-blog
      - database__connection__user=ghost
      - database__connection__password=supersecret
      - database__connection__database=ghost
    restart: unless-stopped
    depends_on:
      - mysql
    labels: #these are only for Traefik ssl
      - traefik.http.routers.blog-http.entrypoints=web
      - traefik.http.routers.blog-http.rule=Host(`blog.strits.dk`)
      - traefik.http.routers.blog-http.middlewares=blog-https
      - traefik.http.middlewares.blog-https.redirectscheme.scheme=https
      - traefik.http.routers.blog.rule=Host(`blog.strits.dk`)
      - traefik.http.routers.blog.tls=true #sets the service to use TLS
      - traefik.http.routers.blog.tls.certresolver=letsEncrypt #references our certificatesResolvers in traefik.yml

  mysql:
    image: mysql:8.0 #minimum supported version by ghost
    container_name: mysql-blog
    volumes:
      - /mnt/media/ghost/mysql:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=root #insecure I know
      - MYSQL_DATABASE=ghost
      - MYSQL_USER=ghost
      - MYSQL_PASSWORD=supersecret
    restart: unless-stopped

As you can see, this compose file mounts a volume into the MySQL container. We need to make sure that this volume exists and accessible.

Step 3: Start and setup

Now we can download the images and start the container.

sudo docker compose pull
sudo docker compose up -d

Because there is no known database, this will take a few minutes to start up, as it's creating a new database. Once it's started you need to go to the blogs webpage to setup your admin user.

Now that you are in, you have a completely clean blog, just like a new install.

Step 4: Import your content

All your content, authors and members are currently missing on this site. So now we need to import the stuff you exported.

So go to Settings -> Labs -> Import content. Select the json file with your content and press Import. Do the same for Members in Import Members.

You may get a couple of warnings, like user already exists or Theme not available. The user error is likely because your new user has the same name as the old user, so that can be discarded. The theme warning is because the user theme you use have been changed from the original, as far as I know. I also discarded this warning.

Conclusion

While there is no direct upgrade path for docker users to upgrade from SQLite3 or other database systems, to a supported setup with MySQL, getting it done is fairly easy as shown in this post.