Switch Ghost Database From SQLite3 to MySQL8

Hello, geeks! I’ve faced with this task recently and spend some time with it because official ghost documentation is pretty foggy on this topic.

At start I had production ghost blog service with 100+ posts deployed via the official docker image inside a kubernetes cluster with sqlite database stored in PersistentVolume. At finish I need to have the same service with a cloud managed database.

Official way

As descibed in How to migrate from SQLite3 we need to perform a full reinstall of Ghost. First step in this reinstallation is to perform a backup via ghost-cli(ghost backup command), database will be extracted to JSON/CSV files and packed in zip file along with static content(images, videos, etc). And this is what we have as a result of doing it inside a container:

blog-84f446d9b-5x8h8:/var/lib/ghost$ ghost backup

Love open source? We’re hiring JavaScript Engineers to work on Ghost full-time.
https://careers.ghost.org


Process manager 'systemd' will not run on this system, defaulting to 'local'
? Ghost instance is not currently running. Would you like to start it? No
A SystemError occurred.

Message: Ghost instance is not currently running

Debug Information:
    OS: Alpine Linux, v3.17.4
    Node Version: v16.20.1
    Ghost Version: 5.54.0
    Ghost-CLI Version: 1.24.2
    Environment: production
    Command: 'ghost backup'

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/ghost-cli/ for troubleshooting.

Comand Line Interface works only with ghost software contolled by systemd, but having systemd inside a container is redundant.

So we can do it only manually through ghost admin web interface. This action has a several steps, so minimal downtime will be around 30 minutes

Via mysql-to-sqlite3 migration tool

Since I need only database migration, I can reduce downtime and manual work by using a simple python tool to transfer data from sqlite to mysql.

1. Start container with mysql and mysql-to-sqlite3 utility inside kubernetes cluster:

% kubectl -n blog run mysql --image mysql:8.0.31-debian -it --rm bash

root@mysql:/# apt-get update && apt-get install -y python3-pip && \
  pip install sqlite3-to-mysql

2. Place maintenance stub on website to prevent content loss

(depends on your load balancer)

3. Copy a database file from the ghost blog pod to the mysql pod:

% kubectl -n blog cp --retries=10 blog-84f446d9b-5x8h8:/var/lib/ghost/content/data/ghost.db /tmp/ghost.db
% kubectl -n blog cp --retries=10 /tmp/ghost.db mysql:/ghost.db
% rm /tmp/ghost.db

% kubectl -n blog exec blog-84f446d9b-5x8h8 -- md5sum /var/lib/ghost/content/data/ghost.db
fee07fabdbf9e076dfaab0763f74e26a  /var/lib/ghost/content/data/ghost.db
% kubectl -n blog exec mysql -- md5sum /ghost.db
fee07fabdbf9e076dfaab0763f74e26a  /ghost.db

4. Run sqlite to mysql migration

root@mysql:/# sqlite3mysql -X -f ghost.db -h 192.168.0.1 -d blog -u blog -p 
MySQL password:
....
2023-08-16 09:36:04 INFO     Done!

5. Change ghost envs in statefulset (deployment) spec

...
        env:
         - name: database__client
-          value: sqlite3
-        - name: database__connection__filename
-          value: content/data/ghost.db
+          value: mysql
+        - name: database__connection__host
+          value: 192.168.0.1
+        - name: database__connection__user
+          value: blog
+        - name: database__connection__database
+          value: blog
...

6. Remove maintenance stub from website

That’s it!

Choose a way of migration you like most, and have I wish you success with it

Make love, not war ☮