Migrate your Ghost SQLite Database to MySQL

Recently I saw some users on Twitter who were upset that the Docker community maintainers for the Ghost Docker image changed the default database from sqlite3 to MySQL. Since this was only a minor version update many of the sites powered by Ghost went down as soon as they updated their Docker image.

Since this blog here was also affected by this change I will now describe a way how you can migrate your containerized Ghost installation from sqlite3 to MySQL.

Note: You can follow along if you installed Ghost via docker-compose on a standalone host.

Export the Ghost content file

Your Ghost Admin Panel -> Settings -> Labs -> Export you content as .json file

Stop your ghost container

docker-compose down

Backup your data and remove any existing local database volume

Generally, it is a good idea to create a backup before any migration. For our task it could be something as simple as

1cp -R /your/ghost/data /tmp/ghost_migration

Modify your docker-compose.yml file

 1services:
 2  ghost:
 3    image: ghost:5.10.1
 4    container_name: ghost
 5    restart: unless-stopped
 6    volumes:
 7      - "/data/ghost:/var/lib/ghost/content"
 8    environment:
 9      database__client: mysql
10      database__connection__host: db
11      database__connection__user: root
12      database__connection__password: $ghost_mysql_password
13      database__connection__database: ghost
14    depends_on:
15      - db
16
17  db:
18    image: mysql:8.0
19    container_name: ghost-mysql-db
20    restart: unless-stopped
21    environment:
22      MYSQL_ROOT_PASSWORD: $ghost_mysql_password
23    networks:
24      - internal
25    volumes:
26      - ghost_db:/var/lib/mysql
27
28volumes:
29  ghost_db:
30
31networks:
32  internal:

In my case, I’ve added a new service called ‘db’ in this file and linked it with my ghost container. The following snippet is the bare minimum of what you need for your container to start successfully. I assume you already have a way to handle your traffic for the ghost container. If not I can recommend traefik as a proxy.

Remember to change the password for the database connection between those two containers.

Reconfigure your Ghost site

Go to your website and navigate to your admin panel (/ghost) and create the same user/password that you already had on your old setup.

Go to Settings -> Lab -> Import content -> Upload the JSON file that you downloaded on step 1 -> Import

After the migration, I would recommend creating a backup mechanism for the content folder and backup the .json file regularly.

Sources

Ghost Default Database Change - https://github.com/docker-library/ghost/pull/323

Traefik Reverse Proxy - https://traefik.io/traefik/