AkurAI-EC2 / bin/akurai-mariadbctl

Latest change 2b11dd5386e6d27f84b7d17ac9b1e47bd4dfdf96 - Add akurai-mariadbctl; delegate 'akurai-ec2 mariadb' to the docker stack by Ólafur Búi Ólafsson

#!/bin/sh
# akurai-mariadbctl — operate the local /opt/akurai-mariadb docker compose
# MariaDB stack (mariadb-prod :3306, mariadb-dev :3307). Same layout on Titan
# and the AkurAI EC2 host; on EC2 drive it remotely via
# `akurai-ec2 mariadb <command...>`.
#
# Secret handling: passwords never travel via argv. create-user reads the
# password from stdin (pipe it from a 0400 secret file or PassVault-derived
# material); root credentials stay inside the containers' /run/secrets files.
set -eu

ROOT=/opt/akurai-mariadb
SUDO=""
[ "$(id -u)" -ne 0 ] && ! docker info >/dev/null 2>&1 && SUDO="sudo"

usage() {
    cat <<'EOF'
usage: akurai-mariadbctl <command> [args]

  status                       compose state, memory, databases per instance
  health                       healthcheck verdict (exit 1 unless both healthy)
  up | down | restart          compose lifecycle for the whole stack
  root-shell [prod|dev]        interactive root SQL shell
  sql <prod|dev> [database]    execute SQL from stdin as root
  create-db <name> [prod|dev]  CREATE DATABASE IF NOT EXISTS (utf8mb4)
  create-user <user> <db> [prod|dev]
                               create user + scoped grant; password on stdin
  dump <database> [prod|dev]   mariadb-dump to stdout (pipe to a file)
  logs [prod|dev] [lines]      container logs (default 50 lines)
EOF
    exit "${1:-0}"
}

container() {
    case "${1:-prod}" in
        prod) echo mariadb-prod ;;
        dev) echo mariadb-dev ;;
        *) echo "unknown instance: $1 (use prod|dev)" >&2; exit 2 ;;
    esac
}

root_sql() { # $1 container; SQL on stdin
    $SUDO docker exec -i "$1" sh -c 'exec mariadb -uroot -p"$(cat /run/secrets/root.pass)" --batch'
}

cmd="${1:-}"; [ $# -gt 0 ] && shift
case "$cmd" in
    status)
        (cd "$ROOT" && $SUDO docker compose ps)
        $SUDO docker stats --no-stream --format '{{.Name}} mem={{.MemUsage}} cpu={{.CPUPerc}}' mariadb-prod mariadb-dev 2>/dev/null || true
        for instance in prod dev; do
            c=$(container $instance)
            printf '%s databases: ' "$instance"
            echo 'SHOW DATABASES' | root_sql "$c" 2>/dev/null | grep -vE '^(Database|information_schema|performance_schema|mysql|sys)$' | tr '\n' ' '
            echo
        done
        ;;
    health)
        ok=0
        for c in mariadb-prod mariadb-dev; do
            state=$($SUDO docker inspect -f '{{.State.Health.Status}}' "$c" 2>/dev/null || echo missing)
            echo "$c: $state"
            [ "$state" = healthy ] || ok=1
        done
        exit $ok
        ;;
    up) (cd "$ROOT" && $SUDO docker compose up -d) ;;
    down) (cd "$ROOT" && $SUDO docker compose down) ;;
    restart) (cd "$ROOT" && $SUDO docker compose restart) ;;
    root-shell)
        c=$(container "${1:-prod}")
        exec $SUDO docker exec -it "$c" sh -c 'exec mariadb -uroot -p"$(cat /run/secrets/root.pass)"'
        ;;
    sql)
        [ $# -ge 1 ] || usage 2
        c=$(container "$1")
        if [ $# -ge 2 ]; then
            $SUDO docker exec -i "$c" sh -c 'exec mariadb -uroot -p"$(cat /run/secrets/root.pass)" --batch "$1"' sh "$2"
        else
            root_sql "$c"
        fi
        ;;
    create-db)
        [ $# -ge 1 ] || usage 2
        c=$(container "${2:-prod}")
        printf 'CREATE DATABASE IF NOT EXISTS `%s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n' "$1" | root_sql "$c"
        echo "created: $1 on ${2:-prod}"
        ;;
    create-user)
        [ $# -ge 2 ] || usage 2
        user="$1"; db="$2"; c=$(container "${3:-prod}")
        password=$(cat)
        [ -n "$password" ] || { echo "empty password on stdin" >&2; exit 2; }
        printf "CREATE USER IF NOT EXISTS '%s'@'%%' IDENTIFIED BY '%s';\nGRANT ALL PRIVILEGES ON \`%s\`.* TO '%s'@'%%';\nFLUSH PRIVILEGES;\n" \
            "$user" "$password" "$db" "$user" | root_sql "$c"
        echo "created: $user -> $db on ${3:-prod}"
        ;;
    dump)
        [ $# -ge 1 ] || usage 2
        c=$(container "${2:-prod}")
        $SUDO docker exec -i "$c" sh -c 'exec mariadb-dump -uroot -p"$(cat /run/secrets/root.pass)" --single-transaction "$1"' sh "$1"
        ;;
    logs)
        c=$(container "${1:-prod}")
        $SUDO docker logs --tail "${2:-50}" "$c"
        ;;
    ''|-h|--help|help) usage ;;
    *) echo "unknown command: $cmd" >&2; usage 2 ;;
esac