Configure MariaDB ColumnStore for high-performance analytics workloads

#!/usr/bin/env bash
set -euo pipefail

# ---------------------------------------------------------------------------
# MariaDB ColumnStore single-node installer
# Prepares OS, installs MariaDB + ColumnStore engine, starts cluster services,
# hardens root and creates an analytics ETL user.
# ---------------------------------------------------------------------------

# ------------------------------ Colors --------------------------------------
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

log_info()  { echo -e "${GREEN}[OK]${NC} $*"; }
log_warn()  { echo -e "${YELLOW}[WARN]${NC} $*"; }
log_err()   { echo -e "${RED}[ERROR]${NC} $*" >&2; }

usage() {
    cat <<EOF
Usage: sudo $0 -a <analytics_subnet_cidr_prefix> -p <analytics_password>

  -a   Subnet allowed to connect for analytics ETL user, e.g. 10.0.1.%
  -p   Password for the analytics_etl MySQL user
  -h   Show this help

Example:
  sudo $0 -a "10.0.1.%" -p 'Cx7!qLm2Vr9Tze'
EOF
    exit 1
}

# ------------------------------ Arg parsing ---------------------------------
ANALYTICS_HOST=""
ANALYTICS_PASS=""

while getopts "a:p:h" opt; do
    case "$opt" in
        a) ANALYTICS_HOST="$OPTARG" ;;
        p) ANALYTICS_PASS="$OPTARG" ;;
        h) usage ;;
        *) usage ;;
    esac
done

[ -z "$ANALYTICS_HOST" ] && usage
[ -z "$ANALYTICS_PASS" ] && usage

# ------------------------------ Prereqs -------------------------------------
if [ "$(id -u)" -ne 0 ]; then
    log_err "This script must be run as root (use sudo)."
    exit 1
fi

TOTAL_STEPS=9
STEP=0
next_step() { STEP=$((STEP+1)); echo -e "\n${GREEN}[${STEP}/${TOTAL_STEPS}]${NC} $*"; }

# ------------------------------ Rollback ------------------------------------
CLEANUP_NEEDED=0
cleanup() {
    if [ "$CLEANUP_NEEDED" -eq 1 ]; then
        log_err "Installation failed. Attempting rollback..."
        systemctl stop mariadb-columnstore-cmapi 2>/dev/null || true
        systemctl stop mariadb 2>/dev/null || true
        log_warn "Rollback complete. Packages left installed for inspection; remove manually if needed."
    fi
}
trap cleanup ERR

# ------------------------------ Distro detection ----------------------------
next_step "Detecting operating system..."
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) PKG_MGR="apt"; PKG_INSTALL="apt install -y" ;;
        almalinux|rocky|centos|rhel|ol|fedora) PKG_MGR="dnf"; PKG_INSTALL="dnf install -y" ;;
        amzn) PKG_MGR="yum"; PKG_INSTALL="yum install -y" ;;
        *) log_err "Unsupported distro: $ID"; exit 1 ;;
    esac
else
    log_err "/etc/os-release not found. Cannot detect distro."
    exit 1
fi
log_info "Detected distro: $ID ($PKG_MGR)"
CLEANUP_NEEDED=1

# ------------------------------ Kernel tuning -------------------------------
next_step "Applying kernel parameters for ColumnStore..."
SYSCTL_FILE="/etc/sysctl.d/99-columnstore.conf"
cat > "$SYSCTL_FILE" <<EOF
vm.swappiness = 1
vm.max_map_count = 262144
net.core.somaxconn = 4096
fs.file-max = 6553600
EOF
chmod 644 "$SYSCTL_FILE"
sysctl --system > /dev/null
log_info "Kernel parameters applied."

# ------------------------------ Resource limits -----------------------------
next_step "Setting resource limits for mysql user..."
LIMITS_FILE="/etc/security/limits.d/99-mariadb-columnstore.conf"
cat > "$LIMITS_FILE" <<EOF
mysql soft nofile 65536
mysql hard nofile 65536
mysql soft nproc 32768
mysql hard nproc 32768
EOF
chmod 644 "$LIMITS_FILE"
log_info "Resource limits configured at $LIMITS_FILE"

# ------------------------------ Prerequisites check -------------------------
next_step "Checking required tools (curl)..."
if ! command -v curl >/dev/null 2>&1; then
    log_warn "curl not found, installing..."
    if [ "$PKG_MGR" = "apt" ]; then
        apt update
        $PKG_INSTALL curl
    else
        $PKG_INSTALL curl
    fi
fi
log_info "curl available."

# ------------------------------ Add MariaDB repo ----------------------------
next_step "Adding official MariaDB repository..."
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash

if [ "$PKG_MGR" = "apt" ]; then
    apt update
elif [ "$PKG_MGR" = "dnf" ]; then
    dnf makecache
else
    yum makecache
fi
log_info "MariaDB repository configured."

# ------------------------------ Install packages ----------------------------
next_step "Installing MariaDB server and ColumnStore engine packages..."
if [ "$PKG_MGR" = "apt" ]; then
    DEBIAN_FRONTEND=noninteractive $PKG_INSTALL mariadb-server mariadb-plugin-columnstore libboost-all-dev jq
elif [ "$PKG_MGR" = "dnf" ]; then
    $PKG_INSTALL MariaDB-server MariaDB-columnstore-engine boost jq
else
    $PKG_INSTALL MariaDB-server MariaDB-columnstore-engine boost jq
fi
log_info "Packages installed."

# ------------------------------ Firewall configuration ----------------------
next_step "Configuring firewall for MariaDB / ColumnStore CMAPI ports..."
if command -v ufw >/dev/null 2>&1 && ufw status | grep -q "Status: active"; then
    ufw allow 3306/tcp
    ufw allow 8640/tcp
    log_info "UFW rules added (3306, 8640)."
elif command -v firewall-cmd >/dev/null 2>&1 && systemctl is-active --quiet firewalld; then
    firewall-cmd --permanent --add-port=3306/tcp
    firewall-cmd --permanent --add-port=8640/tcp
    firewall-cmd --reload
    log_info "firewalld rules added (3306, 8640)."
else
    log_warn "No active firewall manager detected; skipping firewall rules."
fi

# ------------------------------ Start services ------------------------------
next_step "Starting MariaDB and ColumnStore cluster services..."
systemctl enable --now mariadb

# CMAPI service name is consistent across distros for this package
if systemctl list-unit-files | grep -q mariadb-columnstore-cmapi; then
    systemctl enable --now mariadb-columnstore-cmapi
else
    log_warn "mariadb-columnstore-cmapi service unit not found; check package install."
fi

# Bring up the columnstore cluster (single node)
if command -v mcs >/dev/null 2>&1; then
    mcs cluster start || log_warn "mcs cluster start returned non-zero; check 'mcs cluster status'."
    sleep 3
    mcs cluster status || true
else
    log_warn "'mcs' CLI not found; skipping cluster start step."
fi
log_info "Services started."

# ------------------------------ Secure install + ETL user -------------------
next_step "Hardening root account and creating analytics ETL user..."

# Non-interactive secure-installation equivalent: lock down root remote access,
# remove anonymous users and test DB via direct SQL rather than the prompt-based script,
# since this script must run unattended.
mariadb -e "DELETE FROM mysql.global_priv WHERE User='';" 2>/dev/null || true
mariadb -e "DROP DATABASE IF EXISTS test;" 2>/dev/null || true
mariadb -e "FLUSH PRIVILEGES;"

mariadb -e "CREATE DATABASE IF NOT EXISTS analytics_db;"
mariadb -e "CREATE USER IF NOT EXISTS 'analytics_etl'@'${ANALYTICS_HOST}' IDENTIFIED BY '${ANALYTICS_PASS}';"
mariadb -e "GRANT SELECT, INSERT, CREATE, DROP ON analytics_db.* TO 'analytics_etl'@'${ANALYTICS_HOST}';"
mariadb -e "FLUSH PRIVILEGES;"
log_info "Analytics ETL user '${ANALYTICS_HOST}' created with scoped privileges on analytics_db."

# ------------------------------ Verification ---------------------------------
next_step "Running verification checks..."

VERIFY_OK=1

if systemctl is-active --quiet mariadb; then
    log_info "mariadb service is active."
else
    log_err "mariadb service is NOT active."
    VERIFY_OK=0
fi

if systemctl list-unit-files | grep -q mariadb-columnstore-cmapi; then
    if systemctl is-active --quiet mariadb-columnstore-cmapi; then
        log_info "mariadb-columnstore-cmapi service is active."
    else
        log_err "mariadb-columnstore-cmapi service is NOT active."
        VERIFY_OK=0
    fi
fi

if mariadb -e "SELECT ENGINE FROM information_schema.ENGINES WHERE ENGINE='Columnstore';" 2>/dev/null | grep -qi columnstore; then
    log_info "ColumnStore engine is registered in MariaDB."
else
    log_warn "ColumnStore engine not detected in information_schema.ENGINES. Verify manually."
fi

if mariadb -e "SELECT User,Host FROM mysql.global_priv WHERE User='analytics_etl';" 2>/dev/null | grep -q analytics_etl; then
    log_info "analytics_etl user verified in mysql.global_priv."
else
    log_warn "Could not verify analytics_etl user; check manually."
fi

if [ "$VERIFY_OK" -eq 1 ]; then
    CLEANUP_NEEDED=0
    log_info "MariaDB ColumnStore installation completed successfully."
else
    log_err "Verification failed. Review logs above."
    exit 1
fi

trap - ERR
exit 0