The Ghost update that made me switch DB engines

Hosting one's own things is often exploratory, it is often fun, there's some troubleshooting involved from time to time... and then there are annoying chores no one wants to do. This is firmly in the latter category.

I've been running Ghost using the MariaDB database engine since day one. It's a drop-in replacement for MySQL which I've been using for every application in my stack that asked for MySQL. I have never had any issues with it. Quite the contrary: the same DB with the same contents in MariaDB tended to take up less HDD space than when it was in MySQL, and when writing my own queries, some extra features came in handy a few times.

But lately I've noticed this warning when updating Ghost to newer versions:

"MySQL 8 will be required from Ghost 5 onward"

To be fair, their recommended stack did always ask for MySQL, but there never was an issue. Up to 4.46, which just failed on update and had to be rolled back. The issue was caused by some discrepancy between MariaDB and MySQL.

And even though that bug was eventually fixed, and I have previously said that I don't particularly care about Ghost's new features, I do want the blogs to run smoothly (and as securely as possible, so running a vintage version with no patches is not a way to go). So I have decided to grit my teeth and make the switch.

Switching from MariaDB to MySQL, by parts

I have found some walkthrough on how to do this; it involved dumping the entire DB, uninstalling it, installing the new one and importing. I didn't want to go that route; instead, I wanted to import the blog DBs into a MySQL instance running in Docker, switch the blogs to it one by one, checking all the time whether something broke; then upgrading to Ghost 5.

BTW before the upgrade I had my Ghost blogs and MariaDB installed directly in the host OS; after the change, Ghost stays as it was, but the DB will be running in Docker.

Without further ado, this is the relevant part of my compose file:

version: '3.7'

    image: mysql:8
      - 3309:3306
    restart: unless-stopped
      - shared-net
      - /data/some_path/mysql_files:/var/lib/mysql
      - MYSQL_ROOT_PASSWORD=myepicrootp4$$word

First thing I did was trying to export the DBs and re-importing them like suggested in the article above; but for some reason, that did not work. Parts of it failed, then something got messed up, so I decided to wipe the entire MySQL instance and start over. 😅

First, I dumped the blog databases, one by one, using mysqldump.

mysqldump -u root --lock-tables=0 --add-drop-database blog_db_name > blog_db_name.sql

Then I connected to MySQL using the excellent DBeaver, manually created a DB with the same name, then ran the exported dump. This time, there was no issue.

Then I had to re-create Ghost's DB user. I found its connection info in the Ghost install folder, in config.production.json. Copied the name and password, created the user, then granted it all rights on the newly created DB.

CREATE USER 'ghost-337'@'%' IDENTIFIED BY 'pw31337';
GRANT ALL PRIVILEGES ON blog_db_name.* TO 'ghost-337'@'%';

Next: updating the config. I only needed to change the port and host.

Then I did a ghost stop and a ghost start, to reload the blog with the new connection config. I verified in browser that the page indeed works as expected.

Time for the upgrade to v5. But first, a little check: I'm paranoid and wanted to double-check that I'm actually connecting to the new DB, not the old one by accident. So in DBeaver I loaded the migrations table and checked the latest migrations.

Migrations while on v4; contents migrated from MariaDB.

Then I ran the upgrade (ghost update) and after it finished, I checked again. There were new records, so yeah, I'm definitely connected to the new DB. Woot. 🤘🏻

Migrations on v5.

(The updater did warn me there are 2 deprecation warnings for my theme, but they're insignificant and I want to eventually replace the theme anyway, so I ignored them.)

Finally, I shut down the original MariaDB service (and double-checked the blogs still work.) I will not be uninstalling it yet; maybe later, when I'm sure I no longer need it.

Expected issues

I have had issues with this setup - DB in docker, blogs outside of it on the host machine. There were network errors, which ended up forcing me to install the DB on the host as well.

And now I'm back to the originally problematic setup. Maybe the errors will come back, maybe not - we'll see. If worse comes to worst, I'll move the DB outside Docker again.