Migrating your Ghost blog from SQLite3 to MySQL

Introduction

Lately, I decided to self-host my containerized Ghost blog, since I wanted to have full control over every part of the hosting, especially the Cloudflare and CDN setup.

My former hoster was using SQLite3 for every Ghost instance he was hosting. But I didn't feel comfortable with this decision, especially in the context of coming up with a backup strategy (think of file handles & locks).

The Ghost CLI, and therefor its provided backup capability, was never designed to work with containerized setups.

So long story short, I decided to migrate to MySQL 8, which is the only supported database solution for Ghost in production by the way.

Here is how I did it.

Steps

Setup the initial database

-- Create an empty database
create database your_ghost_instance_db;

-- Select it
use your_ghost_instance_db;

-- Create a new user
create user 'ghost_instance'@'%' identified by 'your-password';

-- Grant permissions to the user
grant create, alter, drop, insert, update, delete, select, references on your_ghost_instance_db.* to 'ghost_instance'@'%';

Please note, that your MySQL instance needs to be setup to allow native passwords MYSQL_NATIVE_PASSWORD. Here is an example compose.yaml.

services:
  db:
    image: mysql:8
    container_name: mysql-8
    ports:
      - 3306:3306
    volumes:
      - ./data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: your-super-secure-root-password
      MYSQL_NATIVE_PASSOWRD: ON
    restart: always
    networks:
      database: {}
      
networks:
  database:
    external: true

Let Ghost create the initial schema

Now point a fresh Ghost instance to your MySQL database and start it. It will create the initial DB schema for you.

This step is crucial, otherwise the migration prcess carried out with sqlite3mysql will fail.

Migrate the actual data

Now that we have the initial schema ready, we can use sqlite3mysql to migrate the data.

GitHub - techouse/sqlite3-to-mysql: Transfer data from SQLite to MySQL

Transfer data from SQLite to MySQL. Contribute to techouse/sqlite3-to-mysql development by creating an account on GitHub.

GitHubtechouse

Here is the full command I was able to execute successfully.

sqlite3mysql \
	--sqlite-file ghost.db \
	--mysql-database your_ghost_instance_db \
	--mysql-user ghost_instance \
	--mysql-password your_password \
	--mysql-collation utf8mb4_0900_ai_ci \
	--mysql-skip-create-tables \
	--mysql-truncate-tables

The most important parameters are mysql-skip-create-tables and mysql-truncate-tables.

Since we the schema is already in place, no need to recreate it. Btw. if you would let sqlite3mysql create the schema it will fail. Also it's important to truncate the tables to get rid of the initial demo data created.

Conclusion

And with that, your Ghost blog should be able to run on MySQL 8.

The SQLite-to-MySQL tool is a very convenient tool to have at hand. I'll use it for other potential migration projects in the future.

Happy hacking 😎