mescloud/scripts/backup-db.ps1

#Requires -Version 5.1
<#
.SYNOPSIS
    MES Cloud 数据库全量备份（gzip 压缩，不改 MySQL、不重启）

.DESCRIPTION
    放到数据库服务器上执行。每天 18:00 由计划任务触发。
    每次导出完整库并压缩，按日期保存；超过保留天数的旧文件自动删除。
    先写临时文件，成功后再替换，避免备份失败时把上一份可用备份写坏。

    备份目录：
      E:\MES_DB_Backup\mes_cloud_yyyyMMdd.sql.gz
      E:\MES_DB_Backup\backup.log

    手动用法：
      powershell -ExecutionPolicy Bypass -File .\backup-db.ps1
      powershell -ExecutionPolicy Bypass -File .\backup-db.ps1 -Install
#>
param(
    [switch]$Install,
    [string]$BackupDir = "",
    [int]$KeepDaysParam = -1
)

$ErrorActionPreference = "Stop"
try {
    [Console]::OutputEncoding = [System.Text.Encoding]::UTF8
    $OutputEncoding = [System.Text.Encoding]::UTF8
} catch { }

. (Join-Path $PSScriptRoot "db-config.ps1")
$dbCfg = Read-MesDbConfig
$DbHost = [string]$dbCfg.host
$DbPort = [int]$dbCfg.port
$DbName = [string]$dbCfg.database
$DbUser = [string]$dbCfg.user
$DbPassword = [string]$dbCfg.password

# ========== 备份目录等（连接信息请改 db-config.json 或页面） ==========
$BackupRoot = "E:\MES_DB_Backup"
$KeepDays = 7                         # 保留最近几天的备份，更早的自动删掉
$TaskName = "MES_Cloud_DB_Backup"
$TaskTime = "18:00"
# 体积特别大、可接受不进备份的表，按需取消注释：
# $IgnoreTables = @("mes_adw300_data")
$IgnoreTables = @()
# ========================================

if (-not [string]::IsNullOrWhiteSpace($BackupDir)) {
    $BackupRoot = $BackupDir.Trim().TrimEnd("\")
}
if ($KeepDaysParam -ge 0) {
    $KeepDays = $KeepDaysParam
}

$LogFile = Join-Path $BackupRoot "backup.log"
$CnfFile = Join-Path $env:TEMP ("mes_backup_{0}.cnf" -f $PID)
$Stamp = Get-Date -Format "yyyyMMdd"
$OutGz = Join-Path $BackupRoot ("mes_cloud_{0}.sql.gz" -f $Stamp)

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $line = "{0} [{1}] {2}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Level, $Message
    Write-Host $line
    if (-not (Test-Path $BackupRoot)) {
        New-Item -ItemType Directory -Path $BackupRoot -Force | Out-Null
    }
    Add-Content -Path $LogFile -Value $line -Encoding UTF8
}

function Rotate-LogIfNeeded {
    if ((Test-Path $LogFile) -and ((Get-Item $LogFile).Length -gt 5MB)) {
        $bak = $LogFile + ".old"
        if (Test-Path $bak) { Remove-Item $bak -Force }
        Move-Item $LogFile $bak -Force
    }
}

function Find-MysqlTool {
    param([string]$Name)
    $cmd = Get-Command $Name -ErrorAction SilentlyContinue
    if ($cmd -and $cmd.Source) { return $cmd.Source }

    $binRoots = @(
        "C:\Program Files\MySQL",
        "C:\Program Files (x86)\MySQL",
        "C:\mysql",
        "D:\MySQL",
        "E:\MySQL",
        "C:\Program Files\MariaDB"
    )
    foreach ($root in $binRoots) {
        if (-not (Test-Path $root)) { continue }
        $hit = Get-ChildItem -Path $root -Filter ($Name + ".exe") -Recurse -ErrorAction SilentlyContinue |
            Where-Object { $_.Directory.Name -eq "bin" } |
            Select-Object -First 1
        if ($hit) { return $hit.FullName }
    }
    return $null
}

function Compress-DumpToGzip {
    param(
        [string]$MysqldumpExe,
        [string[]]$DumpArgs,
        [string]$DestGz
    )
    $tmpGz = $DestGz + ".tmp"
    if (Test-Path $tmpGz) { Remove-Item $tmpGz -Force }

    $psi = New-Object System.Diagnostics.ProcessStartInfo
    $psi.FileName = $MysqldumpExe
    $psi.Arguments = ($DumpArgs | ForEach-Object {
        '"' + ($_ -replace '"', '\"') + '"'
    }) -join " "
    $psi.UseShellExecute = $false
    $psi.RedirectStandardOutput = $true
    $psi.RedirectStandardError = $true
    $psi.CreateNoWindow = $true

    $proc = New-Object System.Diagnostics.Process
    $proc.StartInfo = $psi
    $errBuilder = New-Object System.Text.StringBuilder
    $handler = {
        if (-not [string]::IsNullOrEmpty($EventArgs.Data)) {
            [void]$Event.MessageData.AppendLine($EventArgs.Data)
        }
    }
    $errEvent = Register-ObjectEvent -InputObject $proc -EventName ErrorDataReceived -Action $handler -MessageData $errBuilder

    if (-not $proc.Start()) {
        throw "无法启动 mysqldump"
    }
    $proc.BeginErrorReadLine()

    $outStream = $proc.StandardOutput.BaseStream
    $fileStream = [System.IO.File]::Create($tmpGz)
    $gzip = New-Object System.IO.Compression.GZipStream($fileStream, [System.IO.Compression.CompressionLevel]::Fastest)
    try {
        $outStream.CopyTo($gzip)
    } finally {
        $gzip.Dispose()
        $fileStream.Dispose()
    }
    $proc.WaitForExit()
    Unregister-Event -SourceIdentifier $errEvent.Name -ErrorAction SilentlyContinue | Out-Null
    $errText = $errBuilder.ToString()
    if ($errText) {
        Write-Log ("mysqldump 输出: {0}" -f $errText.Trim()) "WARN"
    }
    if ($proc.ExitCode -ne 0) {
        if (Test-Path $tmpGz) { Remove-Item $tmpGz -Force }
        throw ("mysqldump 失败，退出码 {0}" -f $proc.ExitCode)
    }
    if (-not (Test-Path $tmpGz) -or (Get-Item $tmpGz).Length -lt 100) {
        if (Test-Path $tmpGz) { Remove-Item $tmpGz -Force }
        throw "备份文件过小，可能失败"
    }
    if (Test-Path $DestGz) { Remove-Item $DestGz -Force }
    Move-Item $tmpGz $DestGz -Force
}

function Remove-OldBackups {
    if ($KeepDays -le 0) {
        Write-Log "未启用过期删除（KeepDays=0）"
        return
    }
    $expire = (Get-Date).Date.AddDays(-$KeepDays)
    Get-ChildItem -Path $BackupRoot -File -Filter "mes_cloud_*.sql.gz" -ErrorAction SilentlyContinue | ForEach-Object {
        if ($_.Name -match "mes_cloud_(\d{8})\.sql\.gz") {
            $day = [datetime]::ParseExact($Matches[1], "yyyyMMdd", $null)
            if ($day -lt $expire) {
                Remove-Item $_.FullName -Force
                Write-Log ("已删除过期备份: {0}" -f $_.Name)
            }
        }
    }
    # 清理此前增量方案留下的目录（若有）
    foreach ($oldDir in @("full", "incr")) {
        $p = Join-Path $BackupRoot $oldDir
        if (Test-Path $p) {
            Remove-Item $p -Recurse -Force
            Write-Log ("已删除旧目录: {0}" -f $p)
        }
    }
}

function Install-BackupTask {
    $scriptPath = $PSCommandPath
    $arg = '-NoProfile -ExecutionPolicy Bypass -File "{0}"' -f $scriptPath
    $action = New-ScheduledTaskAction -Execute "powershell.exe" -Argument $arg
    $trigger = New-ScheduledTaskTrigger -Daily -At ([datetime]::ParseExact($TaskTime, "HH:mm", $null))
    $principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest
    $settings = New-ScheduledTaskSettingsSet -StartWhenAvailable -AllowStartIfOnBatteries -DontStopIfGoingOnBatteries -ExecutionTimeLimit (New-TimeSpan -Hours 6)
    Register-ScheduledTask -TaskName $TaskName -Action $action -Trigger $trigger -Principal $principal -Settings $settings -Force | Out-Null
    Write-Log ("已注册计划任务 [{0}]，每天 {1} 执行: {2}" -f $TaskName, $TaskTime, $scriptPath)
}

# ---------- 主流程 ----------
try {
    if ($Install) {
        $isAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).
            IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
        if (-not $isAdmin) {
            Start-Process -FilePath "powershell.exe" -Verb RunAs -ArgumentList @(
                "-NoProfile", "-ExecutionPolicy", "Bypass", "-File", $PSCommandPath, "-Install"
            )
            return
        }
        New-Item -ItemType Directory -Path $BackupRoot -Force | Out-Null
        Install-BackupTask
        return
    }

    Rotate-LogIfNeeded
    New-Item -ItemType Directory -Path $BackupRoot -Force | Out-Null
    Write-Log "======== 备份任务开始 ========"

    $mysqldump = Find-MysqlTool "mysqldump"
    if (-not $mysqldump) { throw "找不到 mysqldump.exe，请把 MySQL 的 bin 目录加入 PATH" }
    Write-Log ("mysqldump: {0}" -f $mysqldump)

    Write-MesMysqlCnf -Config $dbCfg -CnfPath $CnfFile
    Write-Log ("连接 MySQL {0}:{1} 库 {2} 账号 {3}" -f $DbHost, $DbPort, $DbName, $DbUser)

    $baseArgs = @(
        "--defaults-extra-file=$CnfFile",
        "--single-transaction",
        "--quick",
        "--routines",
        "--triggers",
        "--hex-blob",
        "--default-character-set=utf8mb4",
        "--max-allowed-packet=1G",
        "--set-gtid-purged=OFF"
    )
    foreach ($t in $IgnoreTables) {
        $baseArgs += ("--ignore-table={0}.{1}" -f $DbName, $t)
    }

    $extraAttempts = New-Object System.Collections.Generic.List[object]
    $extraAttempts.Add([string[]]@())
    $extraAttempts.Add([string[]]@("--column-statistics=0"))

    Write-Log ("开始全量备份（gzip），目标: {0}" -f $OutGz)
    $lastError = $null
    $ok = $false
    foreach ($extra in $extraAttempts) {
        $dumpArgs = $baseArgs + $extra + @("--databases", $DbName)
        try {
            Compress-DumpToGzip -MysqldumpExe $mysqldump -DumpArgs $dumpArgs -DestGz $OutGz
            $ok = $true
            break
        } catch {
            $lastError = $_
            Write-Log ("参数尝试失败，将换一套参数重试: {0}" -f $_.Exception.Message) "WARN"
        }
    }
    if (-not $ok) { throw $lastError }

    $sizeMb = [math]::Round((Get-Item $OutGz).Length / 1MB, 2)
    Write-Log ("全量备份完成: {0} ({1} MB)" -f $OutGz, $sizeMb)
    Remove-OldBackups
    Write-Log "======== 备份任务结束 ========"
    exit 0
} catch {
    Write-Log $_.Exception.Message "ERROR"
    if ($_.ScriptStackTrace) { Write-Log $_.ScriptStackTrace "ERROR" }
    exit 1
} finally {
    if (Test-Path $CnfFile) {
        Remove-Item $CnfFile -Force -ErrorAction SilentlyContinue
    }
}