Swap from SQLite to MySQL 8 for Ghost

Ghost Aug 27, 2022

I am releasing this post with no restrictions, which means you do not need an account to view! If you would like to register to get all future blog posts in your email OR (optionally) if you wish to support me, click on the 'Free Membership' button on the bottom right!

Whenever Ghost released 5.0 back in May, they made a change so that Ghost no longer supports SQLite3 or MySQL 5.

I didn't mind running SQLite until the recent 5.9 changes on the Docker image for Ghost changed all of that. So now, if you try to run Ghost via SQLite3 on your Docker container, it will stop working.

Obviously, if you still want your site to run, you would either need to continue to use a older version of Ghost (Eg. Anything <5.9) OR change to MySQL 8.

Unfortunely, if you are running a modern day control panel like Plesk or cPanel, you may not be able to use MySQL 8 as it may have only been distrbuted with MariaDB. MariaDB is not supported on Ghost either. Solely Oracle's MySQL 8.

So if you already have MariaDB installed and you do not want to go through the hassle of going through the task of switching database engines and potentially causing issues for non-Ghost sites, then carry out the following:

⚠️
You are going to be destorying your current site.

Back it up.

To back it up, you will want to go to https://<yoursite>ghost/#/settings/labs and click on the 'Export' button to export the CSV.

This will not export images. To do that, go to your content folder via SSH. You would have set this when you installed Plesk if you followed my guide!

Copy this out to a folder in your home directory /home/<username>/ghost_backup

If you have memberships, this may break some or all functions with it. I don't have any paid members to know whether this does or not. 
🚨
I am not an expert in Docker, npm, node.js or related technologies. What I am documenting here is what worked for me. YMMV

This will involve creating 2 Docker containers:

  • A new Ghost docker container
  • MySQL 8 Docker container.

We'll quicky go through the MySQL 8 Docker set up.

  • Volume Mapping should set to /var/lib/mysql and the 2nd box should be set to /var/docker/<container_name>/mysql

    • You can put this anywhere that you like, but I advise keeping it within the /var/ directory for ease of access.
  • Uncheck the 'Automatic Port mapping' and you will be presented with 2 ports.

    • Port 3306 would already be used by MariaDB so change this to another port. I chose 3307.
    • Port 33060 would also be used by MariaDB so change this to 33061

Where it has Environment Variables, add the following:

  • MYSQL_ROOT_PASSWORD and in the 2nd box, add in your root password. This is caps sensitive, so ensure this is strong.
🚨
Yes, I know using root as the username is bad practice. If I knew how to improve this, I would!

Now with the database server created, we will need to create the database, for this, you will need to drop into the shell for this container.

In SSH, connect to your server ensure that you are on root user.

  • Type docker exec -it <mysql_docker_container> /bin/bash/ eg: docker exec -it mysqleight_docker /bin/bash and press enter.
  • Type mysql -p and enter in your MYSQL_ROOT_PASSWORD that you set earlier. Now you should see mysql >
  • Type CREATE DATABASE db_name; (Replace db_name with the new name of your database)
  • Type exit; to drop out of the MySQL shell and then Ctrl/Cmd+D to drop out of the docker container.

Now to set up Ghost.

Create a Ghost container as per my guide here:

https://padraig.blog/how-to-install-ghost-5-on-plesk-obsidian/

Sidenote: The above link should be a bookmark and look fancy and shit, but because I am using a .blog TLD, it breaks! Mental! I have already reported it to the Ghost devs but they are not looking at it. 🙄

When you are setting the "Environment Variables", you will need to make the following additions:

⚠️
Ensure there are 2 underscores for spaces here!
  • database__client - mysql
  • database__connection__host - 123.123.123.123 (Your Server IP address)
  • database__connection__user - root
  • database__connection__password - **************
  • database__connection__database - db_name
  • database__connection__port - 3007

Press "OK" and this will create a blank new Ghost site.

Create your site as per normal, then go to https://<site>/ghost/#/settings/labs to import your content.

Then copy your content folder back to /var/ghost/my_new_awesome_ghost_container/content - Restart the Ghost container and most (if not all), your posts, content, themes and everything should be back.

You can update the Ghost container as you would have before.

Tags

Padraig Fahy

A tech support guy by day and a gamer by night!