Migrating from one database to another, ghost blog now on MySQL8 and with added comments functionality

Updating Ghost from SQlite to MySQL8 - now with comments!

Containers Aug 18, 2022

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:

  1. SQlite is not as robust or as fast as having a separate MySQL, MariaDB or PostgreSQL db container serving content
  2. 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:

  1. An existing Ghost installation which still supports SQlite (such as 5.8.3) and access to the app folder tree
  2. Docker and docker-compose installed on your machine
  3. 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:latest
    command: --authentication_policy=mysql_native_password
    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).

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.


The easiest way to self-host Ghost with Docker
Setting up Ghost with a single docker container, persistent files and access to all your style sheets and files? It can be done!
Tips and tricks for those who self-host Ghost Blog
Coding tips and tricks to get the most out of your ghost theme, using CSS, scripts and the built-in handlebars language of ghost

PTS

PTS fell down the selfhosted rabbit hole after buying his first NAS in October 2020, only intending to use it as a Plex server. Find him on the Synology discord channel https://discord.gg/vgSq5pcT

Have some feedback or something to add? Comments are welcome!

Please note comments should be respectful, and may be moderated