Overview

If you were one of the unfortunate ones that got stuck on Mariadb after Ghost CMS officially announced the MySQL 8 requirement, this guide is for you. If you are setting up self-hosted Ghost CMS for the first time, you can reference this guide.

There is an official migration guide where you can see other community members' guides if you prefer to use theirs.

In this guide, we will be migrating from Mariadb 11 to MySQL 8. Unfortunately, Mariadb and MySQL used to support each other's sqldumps, but in their latest versions they have drifted apart drastically and there is no longer a direct pathway from one to the other without using enterprise solutions. If there is a method/tool you are aware of, please let me know in the comments. Thankfully, Ghost has a pretty good export/import feature that makes the process easy.

There is no way to import analytic data. This will be reset, so be advised.

If you need to make any changes to your ghost config such as supporting emojis (charset utf8mb4), make sure you implement those changes in config.production.json and in the docker-compose.yml environmental variables before initializing the database.

For this guide, we will be using Docker Compose. If you are using Docker Run, you will need to translate accordingly.

Here is a breakdown of the migration:

1. Create a sqldump for backup.
2. Create a file-level backup of your docker folder.
3. Create a snapshot of your system if possible.
4. Export ghost data.
5. Stop docker containers.
6. Create a temporary content directory.
7. Create a new database directory for MySQL.
8. Edit docker config to replace Mariadb with MySQL.
9. Start docker containers to initialize the database.
10. Import ghost data.
11. Stop docker containers.
12. Set ghost container volume back to the original content directory.
13. Start docker containers.
14. Set your theme back if on a custom theme.

---

Backups and Exports

1. SQL dump

Let's start by creating a sql dump of our Mariadb Database. You will need to know the service name of your mariadb container and the root database user's password. This can all be found in your docker-compose.yml. Additionally, make sure your container is running.

1. Get a bash shell in the container.

docker compose exec -it servicename bash

2. Dump the database.

mariadb-dump -u root -p --all-databases > dump.sql

3. You will be prompted to enter the root database user password.

4. Exit the container's shell.

exit

5. Copy the dump from the container to your host.

docker compose cp servicename:/dump.sql dump.sql

---

2. File-level backup

Next, we are going to create a zip of our entire docker directory.

1. If you are in the docker directory currently, go up one level.

cd ..

Otherwise, go to the parent directory of your docker files. For example, if your docker directory is /etc/docker/ghost you will want to be in the /etc/docker directory.

cd /etc/docker

2. If not installed, install zip/unzip.

sudo apt update
sudo apt install zip unzip -y

3. Zip your docker directory.

sudo zip -r backup.zip ghost

---

3. Create a snapshot

If your hosting platfrom allows you to take a snapshot or clone your machine, go ahead and do that.

---

4. Exporting ghost data

Make sure all containers are up and running. Go to your ghost admin url (https://domain.com/ghost) and login.

1. Go to Settings > Advanced > Import/Export.

2. On the Export tab, click on "Export Content."

3. This will generate a glorious JSON file of all your content data.

4. Now, while still in settings, navigate to Membership > Analytics.

5. Click "Export."

6. This will export all of your analytic data.

Be advised, ghost does not support importing analytic data. After migration, your analytic data will be reset and start new.

7. Exit settings and go back to the ghost dashboard.

8. Click on "Members" on the left menu.

9. Click the gear icon at the top right toolbar of the Members page.

10. Click "Export all members."

11. This will export all your members that have signed up on your site.

---

Database Migration Phase 1

After you have backed up and exported your data, we can move on to the first phase of migration.

5. Stop docker containers

docker compose down -v

6. Create a temporary content directory

Essentially, we need ghost to think it has no data so it can initialize the database and build all the tables.

In your docker-compose.yml, find the ghost service and edit the local volume to a different folder.

For example, if your ghost service looks like this:

services:
  ghost:
    image: ghost:latest
    restart: always
    ports:
      - "127.0.0.1:2368:2368"
    depends_on:
      - db
    environment:
      PUID: 1002
      PGID: 1002
      url: https://domain.com
      database__client: mysql
      database__connection__host: db
      database__connection__user: ghost
      database__connection__password: password
      database__connection__database: ghostdb
      database__connection__charset: utf8mb4
    volumes:
      - ./content:/var/lib/ghost/content
      - ./config.production.json:/var/lib/ghost/config.production.json
    networks:
      - default
      - internal

Edit the volume like this:

services:
  ghost:
    image: ghost:latest
    restart: always
    ports:
      - "127.0.0.1:2368:2368"
    depends_on:
      - db
    environment:
      PUID: 1002
      PGID: 1002
      url: https://domain.com
      database__client: mysql
      database__connection__host: db
      database__connection__user: ghost
      database__connection__password: password
      database__connection__database: ghostdb
      database__connection__charset: utf8mb4
    volumes:
      - ./content-temp:/var/lib/ghost/content
      - ./config.production.json:/var/lib/ghost/config.production.json
    networks:
      - default
      - internal

We changed the local directory from ./content to ./content-temp.

---

7. Create a new directory for MySQL

Since we are moving to an entirely new database, we need to create a new folder for the MySQL docker container to use.

In your docker-compose.yml, find your Mariadb service and edit the local volume to a different directory.

For example, if your yaml looks like this:

  db:
    image: mariadb:latest
    ports:
      - 127.0.0.1:3306:3306
    restart: always
    environment:
      PUID: 1002
      PGID: 1002
      MYSQL_ROOT_PASSWORD: password
      MYSQL_USER: ghost
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: ghostdb
    volumes:
      - ./db/mariadb:/var/lib/mysql
    networks:
      - internal

Change it to:

  db:
    image: mariadb:latest
    ports:
      - 127.0.0.1:3306:3306
    restart: always
    environment:
      PUID: 1002
      PGID: 1002
      MYSQL_ROOT_PASSWORD: password
      MYSQL_USER: ghost
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: ghostdb
    volumes:
      - ./db/mysql:/var/lib/mysql
    networks:
      - internal

We changed the local volume from ./db/mariadb to ./db/mysql.

8. Edit docker-compose.yml to change Mariadb to MySQL

Editing the same service for Mariadb we were just working on in step 7, we will need to change the image from Mariadb to MySQL.

For example, your docker-compose.yml Mariadb should look like this now:

  db:
    image: mariadb:latest
    ports:
      - 127.0.0.1:3306:3306
    restart: always
    environment:
      PUID: 1002
      PGID: 1002
      MYSQL_ROOT_PASSWORD: password
      MYSQL_USER: ghost
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: ghostdb
    volumes:
      - ./db/mysql:/var/lib/mysql
    networks:
      - internal

Change it to this:

  db:
    image: mysql:8
    ports:
      - 127.0.0.1:3306:3306
    restart: always
    environment:
      PUID: 1002
      PGID: 1002
      MYSQL_ROOT_PASSWORD: password
      MYSQL_USER: ghost
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: ghostdb
    volumes:
      - ./db/mysql:/var/lib/mysql
    networks:
      - internal

We changed the image from image: mariadb:latest to image: mysql:8.

---

Database Migration Phase 2

9. Start docker containers to initialize the database

Now that we have created a temporary content directory, a new database directory, and changed the container from Mariadb to MySQL, we can start the containers to initialize the database.

1. Start the containers.

docker compose up -d

---

10. Import ghost data

Once your dockers have started up and the database has been initialized, you have a brand new Ghost instance using MySQL 8. You will need to do an initial setup at your management URL: https://domain.com/ghost.

Be sure to use the same email and site name you used on your original site.

1. After logging in, navigate to Settings > Advanced > Import/Export.

2. On the Import tab, click on "Universal import."

3. Select the glorious JSON file we exported.

4. Wait for an email that your data has been imported.

5. Once your data has been imported, exit settings and go back to the ghost dashboard.

6. Click on "Members" in the left menu.

7. Click the gear icon at the top right toolbar of the Members page.

8. Click "Import members."

9. Upload your csv member list we exported.

10. Choose the members you want to import or leave default to import all members.

---

11. Stop docker containers

After you have imported all of your data, you can now shut everything down.

1. Stop docker containers.

docker compose down -v

---

12. Set ghost container volume back to the original content directory

Now, lets fix our content directory so we can get our images and themes back.

In your docker-compose.yml, we need to set the ghost local content volume back to the correct directory.

For example, if you followed my schema and your service config looks like this:

services:
  ghost:
    image: ghost:latest
    restart: always
    ports:
      - "127.0.0.1:2368:2368"
    depends_on:
      - db
    environment:
      PUID: 1002
      PGID: 1002
      url: https://domain.com
      database__client: mysql
      database__connection__host: db
      database__connection__user: ghost
      database__connection__password: password
      database__connection__database: ghostdb
      database__connection__charset: utf8mb4
    volumes:
      - ./content-temp:/var/lib/ghost/content
      - ./config.production.json:/var/lib/ghost/config.production.json
    networks:
      - default
      - internal

Change it to this:

services:
  ghost:
    image: ghost:latest
    restart: always
    ports:
      - "127.0.0.1:2368:2368"
    depends_on:
      - db
    environment:
      PUID: 1002
      PGID: 1002
      url: https://domain.com
      database__client: mysql
      database__connection__host: db
      database__connection__user: ghost
      database__connection__password: password
      database__connection__database: ghostdb
      database__connection__charset: utf8mb4
    volumes:
      - ./content:/var/lib/ghost/content
      - ./config.production.json:/var/lib/ghost/config.production.json
    networks:
      - default
      - internal

We change the volume from ./content-temp to ./content.

---

13. Start docker containers

Once we have set everything back to normal, we can start our containers back up.

1. Start docker containers.

docker compose up -d

---

14. Set your theme back if using a custom theme

If you are using a custom theme you will need to reactivate it.

1. Go to your ghost management URL: https://domain.com/ghost.

2. Go to Settings > Site > Design & Branding.

3. Depending on your version of ghost, you will see a theme section here or you will need to go into the "Design & Branding" option and select customize.

4. Activate your custom theme.

---

Finishing up

After migration there are a couple of things to do before being back to normal.

1. The user that you used to setup the new site will lose its profile details. You will need to set it back up, profile pic and all. All other users should be okay, this only happens because we overrode the old user that owned the site.

2. Remove the default "Coming soon" post that ghost generates.

3. Remove the "News" tag that ghost generates.

4. Remove the internal "import*" tag that ghost generates.

The asterisk represents a wildcard.

That's it! You have migrated from Mariadb 11 to MySQL 8.

Thanks for reading!

- SH3LL

CONNECT