Containers

Updating Ghost from SQlite to MySQL8 - now with comments!

PTS

Aug 18, 2022 • 6 min read

Yes, Ghost now has commenting as a built in feature! Under the 'Membership' part of the settings page, self-hosters wishing to utilize this new comments feature need to set up a membership tier as it requires your website users to create an account and be logged in. This membership tier can be free, or can be paid, your choice, but it's worth knowing that off the bat.

Up until recently I'd been using a basic docker installation of Ghost which used the built in SQlite database (no separate container needed). You can check out the article I wrote on what I believe to be the easiest Ghost installation method here.

The problem with this is two-fold:

SQlite is not as robust or as fast as having a separate MySQL, MariaDB or PostgreSQL db container serving content
As of the recently released Ghost v5.9.4, SQlite is no longer supported - in fact, only MySQL8 is. You can stay on 5.8.3 (the latest version available with SQlite support) but you won't get any new features as they roll out, which can be limiting

So how do we make the conversion to a database container without losing our hard work? This article will show you, and it's probably easier than you'd think.

Prerequisites

You will need to have, be able to do, or provide the items below:

An existing Ghost installation which still supports SQlite (such as 5.8.3) and access to the app folder tree
Docker and docker-compose installed on your machine
Some sort of access and relevant permissions to manage and create new directories

Exporting our existing Ghost information

The first thing we're going to do is navigate to our Ghost settings page, and click the 'Labs' button:

From that screen, we're going to click 'Export' and save the populated file on our machine:

Next, we're going to use file explorer (or whatever you use) to navigate to our existing Ghost file tree. Assuming you have created previous posts, have made some changes to your theme/have a custom one, and have changed the 'routes.yaml' file, you need to copy the following and keep them somewhere safe:

images, settings and themes

These will be needed for your new instance

Setting up our containers

The database container

I use multiple ghost instances, so I set up a single MySQL database container and used a PhpMyAdmin container to create multiple users and databases inside that container. If you want to follow that method then you can read my article here, for now though I'm going to focus on a single database, single ghost instance docker-compose stack.

Create a 'ghost' folder somewhere on your system
Inside that folder, create a 'docker-compose.yml' file, a 'db' folder and an 'app' folder
Copy/paste the database container's compose file below into your .yml

services:
## DB for Ghost container
  # MySQL8
  ghost-db:
    container_name: ghost-db
    image: mysql:8
    command: --mysql-native-password=ON
    environment:
      MYSQL_ROOT_PASSWORD: [rootpasswordgoeshere]
      MYSQL_USER: [usernamegoeshere]
      MYSQL_PASSWORD: [userpasswordgoeshere]
      MYSQL_DATABASE: ghost
    ports:
      - 3306:3306 #change port before the `:` as necessary
    volumes:
      - ./db:/var/lib/mysql
    restart: unless-stopped

don't forget to change the variables inside the [ ] brackets

The above creates a mysql8 container, allows for native password authentication, and creates the database's root password, user, and user password. Go ahead and spin it up in the way you prefer (using Portainer or SSH and docker-compose up -d).

💡

A previous version of mysql:8 had the command --authentication_policy=mysql_native_password which has since been deprecated. The plugin now needs to be actively switched on, hence the command you see above

The Ghost container

Add the below (minus services) to the docker-compose.yml you created in the previous step

services:  
  ghost: #change as necessary
    image: ghost:latest
    container_name: ghost #change as necessary
    ports:
      - '2369:2368' #assuming you already have your existing ghost on port 2368
    environment:
      database__client: mysql
      database__connection__port: 3306
      database__connection__host: ghost-db #check if IP is required
      database__connection__user: [usernamegoeshere]
      database__connection__password: [userpasswordgoeshere]
      database__connection__database: ghost
      #the below needs to be changed - either to your hostIP:port, or the URL you plan to use
      url: 192.168.X.XX:2369
      # contrary to the default mentioned in the linked documentation, this image defaults to NODE_ENV=production (so development mode needs to be explicitly specified if desired)
      #NODE_ENV: development
    volumes:
      - ./app:/var/lib/ghost/content
    restart: unless-stopped

check the # comments and change out the info in [ ] brackets

This creates the ghost instance we will begin to use. Some notes:

If using the database container name as database__connection__host:, then database__connection__port: will be 3306. If however you have used the machine IP for the host, then you must make sure that the connection port matches the mapped port in the ghost-db compose file
[usernamegoeshere] and [userpasswordgoeshere] need to be the same as set for the database container

💡

You can only use the container name for connection__host if both containers are on the same docker network. If they're not, then you will need to use the host machine's IP address here

Another docker-compose up -d command should now spin up the Ghost container. Give it a few minutes to finish doing it's thing, then in your browser navigate to the URL you specified in the ghost environment block, followed by /ghost (i.e. 192.168.1.100/ghost).

Importing your previous content

You should get the first screen for creating your username and password again:

💡

WARNING: do not use the same email address and password as your previous instance. When we come to import that content, it could break this new Ghost installation

Navigate again to the 'Labs' page and this time click the Choose file button next to 'Import Content'. Select the file you previously exported, click Import and let it do it's thing.

You might think at this point that it's done, but if you've customized or even just changed your theme, if you had images, and if you had changed the routes, your site still doesn't have the functionality it used to.

Locate the folders you copied earlier
Use them to overwrite the newly created folders in /ghost/app
Restart your Ghost instance
Once it's back up, go back to your settings, click Design > Change Theme > Advanced

In the dropdown box, locate the theme you just copied into the folder structure, and click ACTIVATE.

Congratulations, you've just migrated your Ghost instance from SQlite to MySQL8, and you can keep up with newer versions as they're released.