memind/scripts/sync-local-db-to-rds.mjs

#!/usr/bin/env node
/**
 * Sync local MySQL (source) → RDS (target).
 * Keeps tables listed in KEEP_TARGET where RDS has richer production data.
 *
 * Usage:
 *   node scripts/sync-local-db-to-rds.mjs
 *   node scripts/sync-local-db-to-rds.mjs --dry-run
 */
import mysql from 'mysql2/promise';
import path from 'node:path';
import fs from 'node:fs';
import { fileURLToPath } from 'node:url';

const root = path.join(path.dirname(fileURLToPath(import.meta.url)), '..');
const dryRun = process.argv.includes('--dry-run');

function loadEnvFile(filePath) {
  if (!fs.existsSync(filePath)) return;
  for (const line of fs.readFileSync(filePath, 'utf8').split('\n')) {
    const trimmed = line.trim();
    if (!trimmed || trimmed.startsWith('#')) continue;
    const eq = trimmed.indexOf('=');
    if (eq < 0) continue;
    const key = trimmed.slice(0, eq).trim();
    const value = trimmed.slice(eq + 1).trim();
    if (!process.env[key]) process.env[key] = value;
  }
}

loadEnvFile(path.join(root, '../../.env.local'));
loadEnvFile(path.join(root, '.env'));

const sourceUrl =
  process.env.SYNC_SOURCE_DATABASE_URL ?? 'mysql://boot:password@127.0.0.1:3306/tkmind';
const targetUrl =
  process.env.SYNC_TARGET_DATABASE_URL ??
  process.env.PLAZA_PROD_DATABASE_URL ??
  'mysql://boot:%40Abc888888@rm-uf6h1j53vtuxi78i90o.mysql.rds.aliyuncs.com:3306/goose';

/** RDS wins — do not overwrite from local. */
const KEEP_TARGET = new Set(['h5_publication_views']);

function serializeRow(row) {
  const out = {};
  for (const [key, value] of Object.entries(row)) {
    if (value instanceof Date) out[key] = value;
    else if (value !== null && typeof value === 'object') out[key] = JSON.stringify(value);
    else out[key] = value;
  }
  return out;
}

async function copyTable(source, target, table) {
  const [rows] = await source.query(`SELECT * FROM \`${table}\``);
  if (rows.length === 0) {
    if (!dryRun) await target.query(`DELETE FROM \`${table}\``).catch(() => {});
    return 0;
  }
  if (dryRun) return rows.length;

  await target.query('SET FOREIGN_KEY_CHECKS=0');
  await target.query(`DELETE FROM \`${table}\``);
  const batchSize = 200;
  let copied = 0;
  for (let i = 0; i < rows.length; i += batchSize) {
    const chunk = rows.slice(i, i + batchSize).map(serializeRow);
    const cols = Object.keys(chunk[0]);
    const placeholders = chunk.map(() => `(${cols.map(() => '?').join(', ')})`).join(', ');
    const values = chunk.flatMap((row) => cols.map((col) => row[col]));
    await target.query(
      `INSERT INTO \`${table}\` (${cols.map((c) => `\`${c}\``).join(', ')}) VALUES ${placeholders}`,
      values,
    );
    copied += chunk.length;
  }
  await target.query('SET FOREIGN_KEY_CHECKS=1');
  return copied;
}

async function main() {
  const source = await mysql.createConnection(sourceUrl);
  const target = await mysql.createConnection(targetUrl);

  const [localTables] = await source.query('SHOW TABLES');
  const [remoteTables] = await target.query('SHOW TABLES');
  const remoteSet = new Set(remoteTables.map((r) => Object.values(r)[0]));
  const tables = localTables
    .map((r) => Object.values(r)[0])
    .filter((t) => remoteSet.has(t))
    .sort();

  console.log(`源库: ${sourceUrl.replace(/:[^:@/]+@/, ':***@')}`);
  console.log(`目标: ${targetUrl.replace(/:[^:@/]+@/, ':***@')}`);
  console.log(`保留 RDS 数据（不覆盖）: ${[...KEEP_TARGET].join(', ') || '(无)'}`);
  console.log('');

  const summary = [];
  for (const table of tables) {
    if (KEEP_TARGET.has(table)) {
      const [[r]] = await target.query(`SELECT COUNT(*) AS c FROM \`${table}\``);
      summary.push({ table, action: 'keep-rds', rows: Number(r.c) });
      continue;
    }
    const [[l]] = await source.query(`SELECT COUNT(*) AS c FROM \`${table}\``);
    const localCount = Number(l.c);
    const copied = await copyTable(source, target, table);
    summary.push({ table, action: dryRun ? 'would-sync' : 'synced', rows: copied, localCount });
  }

  console.log('TABLE\tACTION\tROWS');
  for (const row of summary) {
    console.log(`${row.table}\t${row.action}\t${row.rows}`);
  }

  await source.end();
  await target.end();
  console.log(dryRun ? '\n(dry-run，未写入)' : '\n✅ 同步完成');
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});