Ghost - How to migrate from MariaDB to MySQL in a Docker stack

This blog is powered by Ghost. It's run from a Docker compose stack and initially uses a MariaDB database server.

Since Ghost's version 5 the instance is complaining about MariaDB, because

MySQL 8 is the only supported database in production.

therefore it's time to migrate to a MySQL database server.

Here's how to do this.

Ghost using MariaDB

This is Ghost's docker-compose.yml file with the MariaDB service:

#---
version: '3.9'

services:
  ghost:
    image: ghost:5
    container_name: ghost
    restart: unless-stopped
    depends_on:
      - ghost-database
    environment:
      database__client: mysql
      database__connection__host: ghost-database
      database__connection__user: ${MARIADB_USER}
      database__connection__password: ${MARIADB_PASSWORD}
      database__connection__database: ${MARIADB_DATABASE}
      privacy__useUpdateCheck: false
      privacy__useGravatar: false
      privacy__useRpcPing: true
      privacy__useStructuredData: true
      url: http://localhost/
    ports:
      - 8080:2368
    volumes:
      - ./ghost-data:/var/lib/ghost/content
    networks:
      - ghost-external
      - ghost-internal

  ghost-database:
    image: mariadb:10.8
    container_name: ghost-database
    restart: unless-stopped
    environment:
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      MARIADB_DATABASE: ${MARIADB_DATABASE}
      MARIADB_USER: ${MARIADB_USER}
      MARIADB_PASSWORD: ${MARIADB_PASSWORD}
    volumes:
      - ./database:/var/lib/mysql
    networks:
      - ghost-internal


networks:
  ghost-internal:
    external: false
  ghost-external:
    external: true

Add MySQL service

Let's add the MySQL service to the compose file:

  ghost-db:
    image: mysql:8.0
    container_name: ghost-db
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MARIADB_DATABASE}
      MYSQL_USER: ${MARIADB_USER}
      MYSQL_PASSWORD: ${MARIADB_PASSWORD}
    volumes:
      - ./ghost-database:/var/lib/mysql
    networks:
      - ghost-internal

Migrate script

Run following migration script. This script will

• Start the database services
• Dump the data from the MariaDB service
• Import the dump into the MySQL service
• Stop the database services

#!/usr/bin/env bash

set -o errexit
set -o pipefail
set -o nounset
#set -o xtrace

DOCKER="/usr/bin/docker"
DOCKER_COMPOSE="${DOCKER} compose"
DOCKER_COMPOSE_EXEC="${DOCKER_COMPOSE} exec"

GZIP="/usr/bin/gzip"

MARIADB_SERVICE="ghost-database"
MYSQL_SERVICE="ghost-db"

CREDITAL_ENVS=".env"
SQL_DUMP="ghost.sql.gz"

function startDatabaseServices() {
    echo "Starting database services"
    ${DOCKER_COMPOSE} up -d ${MARIADB_SERVICE} ${MYSQL_SERVICE}
}

function sourceCredentials() {
    echo "Sourcing credentials"
    #shellcheck disable=SC1090
    source "${CREDITAL_ENVS}"
}

function dumpMariaDB() {
    echo "Waiting for ${MARIADB_SERVICE}"
    local WAIT_COMMAND="${DOCKER_COMPOSE_EXEC} ${MARIADB_SERVICE} mysql --user=root --password=${MARIADB_ROOT_PASSWORD} --database=${MARIADB_DATABASE} --execute 'select 1'"
    waitUntil "${WAIT_COMMAND}"

    echo "Dumping ${MARIADB_SERVICE}"
    ${DOCKER_COMPOSE_EXEC} ${MARIADB_SERVICE} mysqldump --user="${MARIADB_USER}" --password="${MARIADB_PASSWORD}" "${MARIADB_DATABASE}" | ${GZIP} -9 -f >"${SQL_DUMP}"
}

function importMySQL() {
    echo "Waiting for ${MYSQL_SERVICE}"
    local WAIT_COMMAND="${DOCKER_COMPOSE_EXEC} ${MYSQL_SERVICE} mysql --user=root --password=${MARIADB_ROOT_PASSWORD} --database=${MARIADB_DATABASE} --execute 'select 1'"
    waitUntil "${WAIT_COMMAND}"

    echo "Importing ${MYSQL_SERVICE}"
    ${GZIP} -c -d -f "${SQL_DUMP}" | ${DOCKER_COMPOSE_EXEC} -T ${MYSQL_SERVICE} mysql --user="${MARIADB_USER}" --password="${MARIADB_PASSWORD}" --database="${MARIADB_DATABASE}"
}

function stopDatabaseServices() {
    echo "Stopping database services"
    ${DOCKER_COMPOSE} stop ${MARIADB_SERVICE} ${MYSQL_SERVICE}
}

function waitUntil() {
    command="${1}"
    timeout="${2:-30}"

    i=1
    until eval "${command}"; do
        ((i++))

        if [ "${i}" -gt "${timeout}" ]; then
            echo "command was never successful, aborting due to ${timeout}s timeout!"
            exit 1
        fi

        sleep 1
    done
}

function main() {
    startDatabaseServices
    sourceCredentials
    dumpMariaDB
    importMySQL
    stopDatabaseServices
}

main

Remove MariaDB service from stack

After the migration the MariaDB service can be removed from the stack. This boils the compose file down to:

#---
version: '3.9'

services:
  ghost:
    image: ghost:5
    container_name: ghost
    restart: unless-stopped
    depends_on:
      - ghost-db
    environment:
      database__client: mysql
      database__connection__host: ghost-db
      database__connection__user: ${MARIADB_USER}
      database__connection__password: ${MARIADB_PASSWORD}
      database__connection__database: ${MARIADB_DATABASE}
      privacy__useUpdateCheck: false
      privacy__useGravatar: false
      privacy__useRpcPing: true
      privacy__useStructuredData: true
      url: http://localhost/
    ports:
      - 8080:2368
    volumes:
      - ./ghost-data:/var/lib/ghost/content
    networks:
      - ghost-external
      - ghost-internal

  ghost-db:
    image: mysql:8.0
    container_name: ghost-db
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MARIADB_DATABASE}
      MYSQL_USER: ${MARIADB_USER}
      MYSQL_PASSWORD: ${MARIADB_PASSWORD}
    volumes:
      - ./ghost-database:/var/lib/mysql
    networks:
      - ghost-internal

networks:
  ghost-internal:
    external: false
  ghost-external:
    external: true  

Run the new stack

Finally, the new stack be started:

docker compose up -d --remove-orphans