tkmind_go/deploy/sync-h5-db-105.sh

#!/usr/bin/env bash
# 将本地 H5 用户数据同步到 105 RDS（数据库连接参照 tkmind shared/config/local.json）
#
# 用法：
#   ./deploy/sync-h5-db-105.sh              # 完整：建表 + 导数据 + 更新 .env + 同步用户目录
#   ./deploy/sync-h5-db-105.sh --schema-only
#   ./deploy/sync-h5-db-105.sh --data-only
#   ./deploy/sync-h5-db-105.sh --no-restart
#   ./deploy/sync-h5-db-105.sh --baseline-only   # 仅重置生产权限基线
set -euo pipefail

ROOT="$(cd "$(dirname "$0")/.." && pwd)"
H5_DIR="${ROOT}/ui/h5"
DEPLOY_ENV="${ROOT}/deploy/h5-105.env"
LOCAL_DB_ENV="${ROOT}/.env.local"

H5_DEPLOY_HOST="${H5_DEPLOY_HOST:-root@120.26.184.105}"
H5_REMOTE_DIR="${H5_REMOTE_DIR:-/root/tkmind_go/ui/h5}"
H5_SYSTEMD_SERVICE="${H5_SYSTEMD_SERVICE:-goose-h5}"
TKMIND_SHARED_CONFIG="/root/tkmind/shared/config/local.json"
H5_RDS_DATABASE="${H5_RDS_DATABASE:-goose}"
LOCAL_MYSQL_DATABASE="${LOCAL_MYSQL_DATABASE:-goose}"

SCHEMA_ONLY=0
DATA_ONLY=0
BASELINE_ONLY=0
NO_RESTART=0

for arg in "$@"; do
  case "$arg" in
    --schema-only) SCHEMA_ONLY=1 ;;
    --data-only) DATA_ONLY=1 ;;
    --baseline-only) BASELINE_ONLY=1 ;;
    --no-restart) NO_RESTART=1 ;;
    -h|--help)
      sed -n '2,10p' "$0"
      exit 0
      ;;
    *)
      echo "未知参数: $arg" >&2
      exit 1
      ;;
  esac
done

if [[ -f "${DEPLOY_ENV}" ]]; then
  set -a
  # shellcheck disable=SC1090
  source "${DEPLOY_ENV}"
  set +a
fi

if [[ -f "${LOCAL_DB_ENV}" ]]; then
  set -a
  # shellcheck disable=SC1090
  source "${LOCAL_DB_ENV}"
  set +a
fi

LOCAL_MYSQL_USER="${MYSQL_USER:-boot}"
LOCAL_MYSQL_PASSWORD="${MYSQL_PASSWORD:-888888}"
LOCAL_MYSQL_HOST="${MYSQL_HOST:-localhost}"
LOCAL_MYSQL_PORT="${MYSQL_PORT:-3306}"

# 仅同步用户/计费数据；权限表不同步，导入后强制应用生产安全基线
H5_DATA_TABLES=(
  h5_users
  h5_user_wallets
  h5_user_path_grants
  h5_user_sessions
  h5_session_billing_state
  h5_usage_records
  h5_billing_ledger
)

ssh_cmd() {
  ssh -o ConnectTimeout=15 -o BatchMode=yes "${H5_DEPLOY_HOST}" "$@"
}

b64() {
  printf '%s' "$1" | base64 | tr -d '\n'
}

read_rds_config() {
  ssh_cmd "node -e \"
const fs=require('fs');
const cfg=JSON.parse(fs.readFileSync('${TKMIND_SHARED_CONFIG}','utf8'));
const db=cfg.database||{};
const out={
  host:db.host||'',
  port:db.port||3306,
  user:db.account||db.user||'boot',
  password:db.password||'',
  database:'${H5_RDS_DATABASE}'
};
process.stdout.write(JSON.stringify(out));
\""
}

transform_paths() {
  local file="$1"
  sed -i.bak \
    -e 's|/Users/john/PycharmProjects/goose/ui/h5/temp/|/root/tkmind_go/ui/h5/MindSpace/|g' \
    -e 's|/Users/john/PycharmProjects/goose/ui/h5/MindSpace/|/root/tkmind_go/ui/h5/MindSpace/|g' \
    -e 's|/Users/john/PycharmProjects/goose/ui/h5/users/|/root/tkmind_go/ui/h5/users/|g' \
    "${file}"
  rm -f "${file}.bak"
}

dump_local_data() {
  local dump_file="$1"
  echo "==> 导出本地库 ${LOCAL_MYSQL_DATABASE} 的 H5 表..."
  MYSQL_PWD="${LOCAL_MYSQL_PASSWORD}" mysqldump \
    -h "${LOCAL_MYSQL_HOST}" -P "${LOCAL_MYSQL_PORT}" -u "${LOCAL_MYSQL_USER}" \
    --single-transaction --complete-insert --no-create-info --set-gtid-purged=OFF \
    "${LOCAL_MYSQL_DATABASE}" "${H5_DATA_TABLES[@]}" >"${dump_file}"
  transform_paths "${dump_file}"
  echo "    导出 $(wc -l <"${dump_file}" | tr -d ' ') 行 SQL"
}

init_remote_schema() {
  local rds_b64="$1"
  echo "==> 在 RDS 初始化 H5 表结构..."
  ssh_cmd "cd '${H5_REMOTE_DIR}' && H5_RDS_B64='${rds_b64}' node --input-type=module -e \"
import { createDbPool, initSchema } from './db.mjs';
const cfg = JSON.parse(Buffer.from(process.env.H5_RDS_B64, 'base64').toString('utf8'));
process.env.MYSQL_HOST = cfg.host;
process.env.MYSQL_PORT = String(cfg.port);
process.env.MYSQL_USER = cfg.user;
process.env.MYSQL_PASSWORD = cfg.password;
process.env.MYSQL_DATABASE = cfg.database;
const pool = createDbPool();
await initSchema(pool);
await pool.end();
console.log('schema ok');
\""
}

import_remote_data() {
  local rds_b64="$1"
  local remote_dump="/tmp/h5_data_sync.sql"
  echo "==> 导入数据到 RDS..."
  scp -q "${DUMP_FILE}" "${H5_DEPLOY_HOST}:${remote_dump}"
  local tables_json tables_b64
  tables_json="$(printf '%s\n' "${H5_DATA_TABLES[@]}" | node -e "const fs=require('fs'); process.stdout.write(JSON.stringify(fs.readFileSync(0,'utf8').trim().split('\\n')))")"
  tables_b64="$(b64 "${tables_json}")"
  ssh_cmd "cd '${H5_REMOTE_DIR}' && H5_RDS_B64='${rds_b64}' H5_DUMP_FILE='${remote_dump}' H5_TABLES_B64='${tables_b64}' node --input-type=module -e \"
import fs from 'node:fs';
import { createDbPool } from './db.mjs';
const cfg = JSON.parse(Buffer.from(process.env.H5_RDS_B64, 'base64').toString('utf8'));
const tables = JSON.parse(Buffer.from(process.env.H5_TABLES_B64, 'base64').toString('utf8'));
process.env.MYSQL_HOST = cfg.host;
process.env.MYSQL_PORT = String(cfg.port);
process.env.MYSQL_USER = cfg.user;
process.env.MYSQL_PASSWORD = cfg.password;
process.env.MYSQL_DATABASE = cfg.database;
const pool = createDbPool();
const sql = fs.readFileSync(process.env.H5_DUMP_FILE, 'utf8');
await pool.query('SET FOREIGN_KEY_CHECKS=0').catch(() => {});
for (const table of tables) {
  await pool.query('TRUNCATE TABLE ??', [table]).catch(() => {});
}
const statements = sql
  .split(/;\\s*\\n/)
  .map(s => s.trim())
  .filter(s => s && !/^\\/\\*!|^SET @|^SET @@/i.test(s));
for (const stmt of statements) {
  await pool.query(stmt);
}
await pool.query('SET FOREIGN_KEY_CHECKS=1').catch(() => {});
await pool.end();
fs.unlinkSync(process.env.H5_DUMP_FILE);
console.log('import ok');
\""
}

update_remote_env() {
  local rds_b64="$1"
  echo "==> 更新 105 H5 .env（RDS 连接）..."
  local admin_user_b64 admin_pass_b64
  admin_user_b64="$(b64 "${H5_ADMIN_USERNAME:-admin}")"
  admin_pass_b64="$(b64 "${H5_ADMIN_PASSWORD:-}")"
  ssh_cmd "H5_RDS_B64='${rds_b64}' H5_ADMIN_USER_B64='${admin_user_b64}' H5_ADMIN_PASS_B64='${admin_pass_b64}' node -e \"
const fs=require('fs');
const cfg=JSON.parse(Buffer.from(process.env.H5_RDS_B64,'base64').toString('utf8'));
const adminUser=Buffer.from(process.env.H5_ADMIN_USER_B64,'base64').toString('utf8');
const adminPass=Buffer.from(process.env.H5_ADMIN_PASS_B64,'base64').toString('utf8');
const envPath='${H5_REMOTE_DIR}/.env';
const lines=fs.existsSync(envPath)?fs.readFileSync(envPath,'utf8').split('\\n'):[];
const updates={
  MYSQL_HOST:cfg.host,
  MYSQL_PORT:String(cfg.port),
  MYSQL_USER:cfg.user,
  MYSQL_PASSWORD:cfg.password,
  MYSQL_DATABASE:cfg.database,
  DATABASE_URL:'mysql://'+encodeURIComponent(cfg.user)+':'+encodeURIComponent(cfg.password)+'@'+cfg.host+':'+cfg.port+'/'+cfg.database,
  H5_USERS_ROOT:'/root/tkmind_go/ui/h5/users',
  H5_SIGNUP_BALANCE_CENTS:'1000',
  H5_PUBLIC_BASE_URL:'https://go.tkmind.cn',
};
if(adminUser) updates.H5_ADMIN_USERNAME=adminUser;
if(adminPass) updates.H5_ADMIN_PASSWORD=adminPass;
const keys=new Set(Object.keys(updates));
const kept=[];
for(const line of lines){
  const m=line.match(/^([A-Z0-9_]+)=/);
  if(m && keys.has(m[1])) continue;
  kept.push(line);
}
while(kept.length && kept[kept.length-1]==='') kept.pop();
for(const [k,v] of Object.entries(updates)) kept.push(k+'='+v);
fs.writeFileSync(envPath, kept.join('\\n')+'\\n');
console.log('env updated');
\""
}

migrate_remote_publish_dir() {
  echo "==> 迁移远端 temp → MindSpace（若存在）..."
  ssh_cmd "if [[ -d '${H5_REMOTE_DIR}/temp' && ! -d '${H5_REMOTE_DIR}/MindSpace' ]]; then mv '${H5_REMOTE_DIR}/temp' '${H5_REMOTE_DIR}/MindSpace'; fi"
}

sync_user_dirs() {
  migrate_remote_publish_dir
  echo "==> 同步用户 workspace 目录..."
  for user_dir in "${H5_DIR}/MindSpace"/* "${H5_DIR}/users"/*; do
    [[ -d "${user_dir}" ]] || continue
    base="$(basename "${user_dir}")"
    if [[ "${user_dir}" == *"/MindSpace/"* ]]; then
      rsync -az "${user_dir}/" "${H5_DEPLOY_HOST}:${H5_REMOTE_DIR}/MindSpace/${base}/"
    else
      rsync -az "${user_dir}/" "${H5_DEPLOY_HOST}:${H5_REMOTE_DIR}/users/${base}/"
    fi
  done
}

apply_production_security_baseline() {
  local rds_b64="$1"
  echo "==> 应用生产安全基线（role=user 默认权限，清除用户级权限覆盖）..."
  ssh_cmd "cd '${H5_REMOTE_DIR}' && H5_RDS_B64='${rds_b64}' node --input-type=module -e \"
import { createDbPool } from './db.mjs';
import { applyProductionSecurityBaseline } from './security-baseline.mjs';
const cfg = JSON.parse(Buffer.from(process.env.H5_RDS_B64, 'base64').toString('utf8'));
process.env.MYSQL_HOST = cfg.host;
process.env.MYSQL_PORT = String(cfg.port);
process.env.MYSQL_USER = cfg.user;
process.env.MYSQL_PASSWORD = cfg.password;
process.env.MYSQL_DATABASE = cfg.database;
const pool = createDbPool();
await applyProductionSecurityBaseline(pool);
await pool.end();
console.log('security baseline ok');
\""
}

verify_remote() {
  local rds_b64="$1"
  echo "==> 验证 RDS 数据与 john 会话策略..."
  ssh_cmd "cd '${H5_REMOTE_DIR}' && H5_RDS_B64='${rds_b64}' node --input-type=module -e \"
import { createDbPool } from './db.mjs';
import { createUserAuth } from './user-auth.mjs';
import fs from 'node:fs';
const cfg = JSON.parse(Buffer.from(process.env.H5_RDS_B64, 'base64').toString('utf8'));
process.env.MYSQL_HOST = cfg.host;
process.env.MYSQL_PORT = String(cfg.port);
process.env.MYSQL_USER = cfg.user;
process.env.MYSQL_PASSWORD = cfg.password;
process.env.MYSQL_DATABASE = cfg.database;
for (const line of fs.readFileSync('.env','utf8').split('\\n')) {
  const t=line.trim(); if(!t||t.startsWith('#')) continue;
  const eq=t.indexOf('='); if(eq<0) continue;
  if(!process.env[t.slice(0,eq)]) process.env[t.slice(0,eq)]=t.slice(eq+1);
}
const pool = createDbPool();
const [users] = await pool.query('SELECT username, role FROM h5_users ORDER BY username');
const [usage] = await pool.query('SELECT COUNT(*) AS cnt FROM h5_usage_records');
const [roleShell] = await pool.query(
  \\\"SELECT allowed FROM h5_capability_grants WHERE subject_type='role' AND subject_id='user' AND capability_key='shell'\\\"
);
console.log('users:', users.map(u => u.username + '(' + u.role + ')').join(', '));
console.log('usage_records:', usage[0].cnt);
console.log('role shell:', roleShell[0]?.allowed ?? 'default');
const auth = createUserAuth(pool, { usersRoot: process.env.H5_USERS_ROOT, h5Root: process.cwd() });
const [john] = await pool.query(\\\"SELECT id FROM h5_users WHERE username='john'\\\");
if (john[0]) {
  const policy = await auth.getAgentSessionPolicy(john[0].id);
  const dev = policy.extensionOverrides?.find(e => e.name === 'developer');
  console.log('john developer tools:', dev?.available_tools?.join(',') ?? 'none');
  console.log('john goose_mode:', policy.gooseMode ?? 'null');
}
await pool.end();
\""
}

echo "======================================"
echo "H5 数据库 → 105 RDS 同步"
echo "参照: ${TKMIND_SHARED_CONFIG}"
echo "目标库: ${H5_RDS_DATABASE}"
echo "======================================"

RDS_JSON="$(read_rds_config)"
RDS_B64="$(b64 "${RDS_JSON}")"
echo "RDS: $(echo "${RDS_JSON}" | node -e "const c=JSON.parse(require('fs').readFileSync(0,'utf8')); console.log(c.host+'/'+c.database)")"

if [[ "${BASELINE_ONLY}" -eq 1 ]]; then
  apply_production_security_baseline "${RDS_B64}"
else
  DUMP_FILE="$(mktemp /tmp/h5_data_XXXXXX.sql)"
  trap 'rm -f "${DUMP_FILE}"' EXIT

  if [[ "${DATA_ONLY}" -eq 0 ]]; then
    init_remote_schema "${RDS_B64}"
  fi

  if [[ "${SCHEMA_ONLY}" -eq 0 ]]; then
    dump_local_data "${DUMP_FILE}"
    import_remote_data "${RDS_B64}"
    sync_user_dirs
    apply_production_security_baseline "${RDS_B64}"
  fi
fi

update_remote_env "${RDS_B64}"

if [[ "${NO_RESTART}" -eq 0 ]]; then
  echo "==> 重启 ${H5_SYSTEMD_SERVICE}..."
  ssh_cmd "systemctl restart '${H5_SYSTEMD_SERVICE}'"
  sleep 2
fi

verify_remote "${RDS_B64}"
ssh_cmd "curl -s http://127.0.0.1:8080/api/status && echo ' ← h5'" || true

echo ""
echo "✅ H5 已连接 RDS 并完成数据同步"