Upgrading Ghost from SQLite to MySQL: guide

Developer help
1

Recently, I finally managed to upgrade my old Ghost instance running the 4.x.x release on SQLite to the latest 5.x.x release on MySQL.

I had attempted this upgrade multiple times without success, but now everything works perfectly. Here’s how I did it:

  1. Backup everything, just in case.
  2. Upgrade to version 5.8.3. AFAIK this is the latest version that still supports SQLite.
  3. Set up a fresh MySQL instance.
  4. Create the database and user.
    • Create a database named ghost.
    • Create a user ghost with the appropriate permissions for the ghost database.
    • Use the collation utf8mb4_0900_ai_ci for the database.
  5. Modify your config.production.json file to point to the new MySQL instance:
  "database": {
    "client": "mysql",
    "connection": {
      "host": "your-mysql-host.domain.com",
      "user": "ghost",
      "password": "password",
      "database": "ghost",
    }
  },
  1. Restart Ghost. Ghost will run migrations on the new database and create the required tables.
  2. Download the ghost.db SQLite database file from your Ghost instance.
  3. Download and install the sqlite3-to-mysql tool.
  4. Run sqlite3mysql --sqlite-file 'ghost.db' --mysql-host your-mysql-host.domain.com --mysql-user ghost --mysql-database ghost --mysql-skip-create-tables --without-foreign-keys --mysql-truncate-tables. This tool will populate your MySQL database using the backup.
  5. Restart Ghost, Check that everything is running smoothly.
  6. Perform incremental upgrades. I didn’t attempt to upgrade in one leap. Instead, I upgraded in steps: 5.8.3→5.39.0→5.57.2→5.76.1→5.105.0
  7. …
  8. PROFIT!

I know the official guides recommend a full reinstall, but I preferred avoiding the hassle of dealing with media files and other settings.

Hope this helps!

2 Likes
2

Considering the improvements SQLite has made over the last few years, I think it’s really unfortunate that Ghost has dropped support for it. While MySQL is a popular and well-known tool, it still represents a significant dependency: one more server, one more piece of software, and one more potential headache.

5 Likes
3

I’m also wondering about the deprecation of sqlite, what exactly is not supported? Given that:

SQLite supports the JSON5 standard since the 3.42.0 version. 12 Feb 2024

Since some of us are developers, maybe we can do a community support for this, small blogs don’t need a separate databse server.

I can volunteer!
R

4

Just wanted to clarify that sqlite is still supported. I have a dev instance using sqlite and found this page while researching how to migrate my sqlite db to MySQL for a production instance.

5

sqlite3 is only supported in development. There is at least one bug I’m aware of with sqlite3 date encoding that means you wouldn’t want to run it in production. There may be more. You don’t need a separate database server, but you very much should run mysql for anything you care about.

3 Likes
6

you very much should run mysql for anything you care about

Well, there is zero reasons to run mysql for a what is actually a static site given the options like LiteStream. Instead, I’m forced to run my own mysql (because managed options are expensive), manage backups and upgrades instead of taking care of a single file.

I get where it comes from, self-hosted people like me are not a target audience of ghost, and dual-db support is a unnecessary overhead for a dev team trying to make some money out of it.

Still sad.

P.S.: MySQL is resource hog. My entire blog is fit into less than 50MiB, however, MySQL requires at least 512MiB of RAM for a what can be fit into a fraction of it.

7

One of the biggest challenges of making an open-source project is maintaining features that you -as the core developer- don’t use at all. You always rely on others or you need to learn those even though you don’t -want to- use it. That’s a big friction.

I made some open source projects and was always open any type of contributions, if they just pass the tests and have some documentation. But since I have no clue if that feature contributed by someone else that I don’t use at all, still works, it’s always hesitating to make a change and a release without breaking those features. If you have more and more parts those are not under your control, then at some point you got stuck. I think Ghost team also feels similar and tries to avoid this.

I am also not a fan of MySQL. I personally prefer Postgres on all of my projects (including Synaps Media). Yes, a simpler DB server with less resource usage could be used in Ghost. But some can say similar things about Node.js too. Rust could run with 10MB of memory, instead of 300MB. At the end, these are the comfort zones of the core developers, right? And Ghost is not a technical fantasy project, it’s completely focused on a product mission: Making the best blogging/newsletter software. This mission is about the end product, not the technology behind. Making open-source and enabling self-hosting is also part of the mission, and yes, “easy to self-host” would be an important parameter. But we can argue that, MySQL is the most well-known DB solution out there for self-hosted environments. It uses a few hundreds of more RAM but ecosystem around it is huge. There are thousands of mysql hosting services by variety of prices. And many tools for managing mysql.

And please consider this: If Ghost team works on supporting multi-db architecture, by spending weeks (or months) on it, the only benefit would be a little more available resources on some self-hosted environments (arguably, many self-hosters would still prefer Mysql). But instead, if Ghost team works on -let’s say- comment moderation, internal review notes on Ghost editor, previewing themes… (you probably have more wishes too), then “everyone” would benefit from that effort.

So, I think the decision of the core team about sticking to MySQL can’t be summarized to make more money out of it. Instead, I think it’s more related to where to spend their -limited- money (a.k.a. time) at the current stage.

I think it also worths to quote @Hannah’s explanation here about DB support with the Ghost v5 release:

Over the coming months we plan to deliver a range of DB optimisations and other improvements leveraging MySQL8 features. As a small team, we’re not able to commit to providing optimisation and compatibility fixes across a wide range of databases where knex does not provide interoperability and therefore for 5.0 we are clarifying that we only officially support MySQL8.

If anyone is interested in contributing to the long term maintenance of interoperability with other databases, we’d love to work with you.

Ghost 5.0: Breaking Changes · Issue #14446 · TryGhost/Ghost · GitHub

2 Likes
8

Actually, not that much. Cheapest options starting from 15$/month, except of free tiers of major clouds.