qlib/scripts/mysql_recover_and_grant.sh

#!/usr/bin/env bash
# ============================================================
# 本地 MariaDB：恢复 root 访问 + 创建与项目配置一致的 qlib 账号
#
# 背景
#   - 本机 MacPorts mariadb-10.11 的 root@localhost 密码未知，
#     且 unix_socket 认证也不可用（以 _mysql 运行同样被拒），
#     因此无法创建项目所需的 qlib 账号。
#   - 服务由 launchd 托管（KeepAlive=true），直接 kill 会自动重启。
#
# 做法（标准 root 密码找回流程）
#   1) 停掉 launchd 托管的正式实例（释放 datadir 咨询锁）
#   2) 以 _mysql 用户启动临时实例：--skip-grant-tables --skip-networking
#      （不监听 TCP，仅本机私密 socket）
#   3) 在临时实例上重置 root@localhost 密码，并创建 qlib@localhost /
#      qlib@127.0.0.1（密码取自项目根 .env 的 MYSQL_PASSWORD）
#   4) 关闭临时实例，重新启动 launchd 正式实例
#   5) 用正式实例验证 qlib 账号 TCP 登录
#
# 用法（需要 sudo，会短暂中断本机数据库服务）
#   sudo bash scripts/mysql_recover_and_grant.sh
#
# 幂等：可重复执行；若 qlib 账号已可用则直接跳过，不做任何改动。
# ============================================================
set -uo pipefail

MDB=/opt/local/lib/mariadb-10.11/bin
DATADIR=/opt/local/var/db/mariadb-10.11
MYSQLUSER=_mysql
RUN=/tmp/qlib-mariadb-recover
TMP_SOCK=${RUN}/recover.sock
TMP_PID=${RUN}/recover.pid
MAIN_PID_FILE=${DATADIR}/summer.pid
MAIN_SOCK=/opt/local/var/run/mariadb-10.11/mysqld.sock
LAUNCHD_LABEL=system/org.macports.mariadb-10.11-server
PLIST=/opt/local/etc/LaunchDaemons/org.macports.mariadb-10.11-server/org.macports.mariadb-10.11-server.plist
SERVICE_STOPPED=0
# 口令一律不硬编码（AGENT.md §33/§34：密钥只放 .env，禁止提交）：
#   RECOVER_PW —— root 复位后的口令：取环境变量 MARIADB_ROOT_PW，未设则随机生成（见下）
#   QLIB_PW    —— 业务账号口令：运行时从根目录 .env 的 MYSQL_PASSWORD 读取（见下）
RECOVER_PW="${MARIADB_ROOT_PW:-}"
QLIB_PW=""

log()  { echo "==> $*"; }
warn() { echo "!!! $*" >&2; }

if [ "$(id -u)" -ne 0 ]; then
  warn "需要 root 权限：sudo bash $0"
  exit 1
fi

# ---------- 凭据读取：不落盘、不硬编码 ----------
ENV_FILE="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)/.env"
if [ -f "${ENV_FILE}" ]; then
  QLIB_PW="$(sed -n 's/^MYSQL_PASSWORD=//p' "${ENV_FILE}" \
    | head -1 | tr -d '\r' \
    | sed -e 's/^"//' -e 's/"$//' -e "s/^'//" -e "s/'$//")"
fi
if [ -z "${QLIB_PW}" ]; then
  warn "未从 ${ENV_FILE} 读到 MYSQL_PASSWORD（见 config.yaml database.mysql.password_env）"
  exit 1
fi
# SQL 里口令以单引号字面量拼接：含单引号会破坏语句，直接拒绝而不是生成坏 SQL
case "${QLIB_PW}" in
  *"'"*)
    warn "MYSQL_PASSWORD 含单引号，本脚本的 SQL 拼接不安全；请用 mysql 客户端手工授权"
    exit 1
    ;;
esac

# root 复位口令：不设弱口令，未显式提供则随机生成并由脚本末尾打印一次
if [ -z "${RECOVER_PW}" ]; then
  RECOVER_PW="$(openssl rand -base64 24 2>/dev/null | tr -dc 'A-Za-z0-9' | cut -c1-24)"
fi
if [ -z "${RECOVER_PW}" ]; then
  warn "无法生成 root 口令（openssl 不可用）：请显式设置 MARIADB_ROOT_PW 后重试"
  exit 1
fi

# ---------- 步骤 0：若 qlib 账号已可用，直接结束（幂等、零改动） ----------
if "${MDB}/mariadb" -h 127.0.0.1 -P 3306 -u qlib -p"${QLIB_PW}" \
     -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='qlib';" \
     >/tmp/qlib-pregrant.out 2>/dev/null; then
  log "qlib 账号已可登录本机 MariaDB，表数：$(cat /tmp/qlib-pregrant.out)"
  log "无需任何改动，退出。"
  rm -f /tmp/qlib-pregrant.out
  exit 0
fi

stop_service() {
  log "停止 launchd 托管的 MariaDB"
  launchctl bootout "${LAUNCHD_LABEL}" 2>/dev/null || \
    launchctl unload -w "${PLIST}" 2>/dev/null || \
    warn "launchctl 停止未成功，继续尝试"
  for _ in $(seq 1 60); do
    pgrep -x mariadbd >/dev/null 2>&1 || pgrep -x mysqld >/dev/null 2>&1 || break
    sleep 0.5
  done
  if pgrep -x mariadbd >/dev/null 2>&1 || pgrep -x mysqld >/dev/null 2>&1; then
    warn "实例未在 30s 内退出，强制结束"
    pkill -x mariadbd 2>/dev/null || true
    pkill -x mysqld  2>/dev/null || true
    sleep 3
  fi
  # 清掉残留 socket/pid，避免临时实例或重启冲突
  rm -f "${MAIN_SOCK}" 2>/dev/null || true
  [ -f "${MAIN_PID_FILE}" ] && mv -f "${MAIN_PID_FILE}" "${MAIN_PID_FILE}.pre-recover" 2>/dev/null || true
  SERVICE_STOPPED=1
  log "服务已停止"
}

start_service() {
  log "重新启动 launchd 托管的 MariaDB"
  launchctl bootstrap system "${PLIST}" 2>/dev/null || \
    launchctl load -w "${PLIST}" 2>/dev/null || \
    warn "launchctl 启动未成功"
  for _ in $(seq 1 120); do
    if "${MDB}/mariadb" -h 127.0.0.1 -P 3306 -u qlib -p"${QLIB_PW}" -N -e "SELECT 1;" >/dev/null 2>&1; then
      SERVICE_STOPPED=0
      log "服务已恢复，qlib 账号可登录"
      return 0
    fi
    sleep 1
  done
  warn "服务未在 120s 内就绪，请检查：sudo launchctl print ${LAUNCHD_LABEL}"
  return 1
}

# 任何情况下退出都要把正式服务拉起来
cleanup() {
  local rc=$?
  [ -f "${TMP_PID}" ] && kill "$(cat "${TMP_PID}")" 2>/dev/null || true
  sleep 2
  if [ "${SERVICE_STOPPED}" -eq 1 ]; then
    warn "脚本异常中断，正在恢复正式服务…"
    start_service || true
  fi
  rm -rf "${RUN}"
  return $rc
}
trap cleanup EXIT

# ---------- 步骤 1：停正式实例 ----------
stop_service

# ---------- 步骤 2：起临时实例（skip-grant-tables） ----------
log "启动临时实例（--skip-grant-tables，不监听 TCP）"
rm -rf "${RUN}"
install -d -o "${MYSQLUSER}" -g "${MYSQLUSER}" -m 0770 "${RUN}"
sudo -u "${MYSQLUSER}" "${MDB}/mariadbd" \
  --datadir="${DATADIR}" \
  --socket="${TMP_SOCK}" \
  --pid-file="${TMP_PID}" \
  --skip-grant-tables \
  --skip-networking \
  >"${RUN}/server.log" 2>&1 &
for _ in $(seq 1 60); do
  [ -S "${TMP_SOCK}" ] && break
  sleep 0.5
done
if [ ! -S "${TMP_SOCK}" ]; then
  warn "临时实例启动失败，日志："
  tail -30 "${RUN}/server.log" >&2
  exit 1
fi
log "临时实例就绪：${TMP_SOCK}"

# ---------- 步骤 3：重置 root 密码 + 创建 qlib 账号 ----------
log "重置 root@localhost 密码并创建 qlib 账号"
"${MDB}/mariadb" --socket="${TMP_SOCK}" -u root <<SQL
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY '${RECOVER_PW}';
CREATE USER IF NOT EXISTS 'qlib'@'localhost' IDENTIFIED BY '${QLIB_PW}';
CREATE USER IF NOT EXISTS 'qlib'@'127.0.0.1' IDENTIFIED BY '${QLIB_PW}';
ALTER USER 'qlib'@'localhost' IDENTIFIED BY '${QLIB_PW}';
ALTER USER 'qlib'@'127.0.0.1' IDENTIFIED BY '${QLIB_PW}';
GRANT ALL PRIVILEGES ON \`qlib\`.* TO 'qlib'@'localhost';
GRANT ALL PRIVILEGES ON \`qlib\`.* TO 'qlib'@'127.0.0.1';
SELECT user, host, plugin FROM mysql.user WHERE user IN ('root','qlib');
SELECT COUNT(*) AS qlib_tables FROM information_schema.tables WHERE table_schema='qlib';
FLUSH PRIVILEGES;
SQL
SQL_RC=$?
if [ "${SQL_RC}" -ne 0 ]; then
  warn "授权 SQL 执行失败（rc=${SQL_RC}），将恢复正式服务"
  exit 1
fi

# ---------- 步骤 4：关临时实例 ----------
log "关闭临时实例"
if [ -f "${TMP_PID}" ]; then
  kill "$(cat "${TMP_PID}")" 2>/dev/null || true
  for _ in $(seq 1 40); do
    pgrep -x mariadbd >/dev/null 2>&1 || break
    sleep 0.5
  done
fi
pkill -x mariadbd 2>/dev/null || true
sleep 2
rm -f "${TMP_SOCK}" "${TMP_PID}" 2>/dev/null || true

# ---------- 步骤 5：重启正式实例并验证 ----------
start_service || exit 1

log "验证 qlib 账号（TCP 127.0.0.1:3306）"
"${MDB}/mariadb" -h 127.0.0.1 -P 3306 -u qlib -p"${QLIB_PW}" \
  -e "SELECT CURRENT_USER() AS whoami; SELECT COUNT(*) AS qlib_tables FROM information_schema.tables WHERE table_schema='qlib';"

echo ""
echo "============================================================"
echo " 完成"
echo "   root@localhost 密码已重置为：${RECOVER_PW}"
echo "   qlib 账号：user=qlib db=qlib（密码与项目 .env 一致）"
echo ""
echo " 建议尽快改掉 root 密码："
echo "   ${MDB}/mariadb -h 127.0.0.1 -u root -p"
echo "   MariaDB> ALTER USER 'root'@'localhost' IDENTIFIED BY '<新密码>';"
echo "============================================================"