<# .SYNOPSIS 用 archer 导出某个 MySQL 库中的全部表,按行数分片并压缩。 .DESCRIPTION 自动遍历 information_schema 里指定库的所有基础表,逐表调用 archer: --shard-by count --format dump --compress gzip 每张表输出到独立目录 \\,因为 archer 的 manifest.jsonl / state.json 是「按输出目录」维护的,混在一起会互相覆盖。 断点续跑:archer 自己会在同目录 + 同配置下续跑,脚本默认跳过 state.json 中 complete=true 的表。需要重导某张表时用 -Restart。 .PARAMETER Source 源库 DSN,形如 user:pass@tcp(host:3306)/db。默认取环境变量 ARCHER_SOURCE。 .PARAMETER Schema 要导出的库名。默认取 DSN 中的库名。 .PARAMETER OutDir 输出根目录。默认 <仓库根>\export。 .PARAMETER ShardRows 每个分片文件的行数,默认 1000000。表行数小于该值时只有 1 个分片。 .PARAMETER ChunkSize 每次 SELECT / 事务 / checkpoint 的行数,默认 5000。 .PARAMETER Format 分片格式 dump|csv|jsonl,默认 dump(LOAD DATA 兼容 TSV)。 .PARAMETER Compress 压缩算法 none|gzip|zstd,默认 gzip。 .PARAMETER Tables 只导这些表(表名数组),默认全会话库内所有基础表。 .PARAMETER Exclude 排除的表名通配符,如 'tmp_*'。 .PARAMETER Retries 单表失败后的重试次数,默认 3(0 表示不重试)。archer 的 state.json 会续跑, 因此重试是安全的:Windows 上偶发 state.json rename "Access is denied" (杀毒/索引器短暂占用句柄),重试一次即恢复。 .PARAMETER Restart 删除已存在的表输出目录后重新导出(默认会跳过已完成 / 续跑未完成的表)。 .PARAMETER DryRun 只打印将要执行的 archer 命令,不实际执行。 .PARAMETER Verify 导出后逐表比对 state.json 的 rows_done 与源表 count(*)。 注意 rows_done 现在是累计值(含续跑前已提交的行数),因此续跑过的表也能与源表 正确对齐;升级前的旧 checkpoint 只记录上次运行行数,首次校验可能仍报一次差异, 重新完成后即恢复。 .PARAMETER ProgressMode archer 的 --progress 模式:auto|on|off,默认 auto。脚本把 stdout+stderr 重定向进 \.
.log,重定向时 auto 不输出进度;设为 on 可让日志每秒 追加一行完整进度,便于 Get-Content -Wait / tail 观察长任务。 .EXAMPLE .\export-all.ps1 -Source 'root:pass@tcp(s1.void01.com:3306)/lexis' .EXAMPLE .\export-all.ps1 -Source 'root:pass@tcp(s1.void01.com:3306)/lexis' ` -OutDir D:\dump -ShardRows 250000 -Verify .EXAMPLE .\export-all.ps1 -Source 'root:pass@tcp(s1.void01.com:3306)/lexis' ` -ProgressMode on #> [CmdletBinding()] param( [string]$Source = $env:ARCHER_SOURCE, [string]$Schema = '', [string]$OutDir = '', [int]$ShardRows = 1000000, [int]$ChunkSize = 5000, [ValidateSet('dump', 'csv', 'jsonl')] [string]$Format = 'dump', [ValidateSet('none', 'gzip', 'zstd')] [string]$Compress = 'gzip', [string[]]$Tables = @(), [string]$Exclude = '', [string]$Archer = '', [string]$Mysql = '', [int]$Retries = 3, [ValidateSet('auto', 'on', 'off')] [string]$ProgressMode = 'auto', [switch]$Restart, [switch]$DryRun, [switch]$Verify ) $ErrorActionPreference = 'Stop' # --------------------------------------------------------------------------- # 路径与可执行文件 # --------------------------------------------------------------------------- $root = Split-Path -Parent $PSScriptRoot if (-not $Archer) { $candidates = @( (Join-Path $root 'dist\archer_windows_amd64.exe'), (Join-Path $root 'dist\archer.exe') ) $Archer = $candidates | Where-Object { Test-Path $_ } | Select-Object -First 1 if (-not $Archer) { $cmd = Get-Command archer.exe -ErrorAction SilentlyContinue if ($cmd) { $Archer = $cmd.Source } } } if (-not $Archer -or -not (Test-Path $Archer)) { throw "找不到 archer 可执行文件,请先运行 scripts\build.ps1,或用 -Archer 指定路径" } if (-not $Mysql) { $cmd = Get-Command mysql.exe -ErrorAction SilentlyContinue if ($cmd) { $Mysql = $cmd.Source } elseif (Test-Path 'C:\portable-apps\mysql-8.3.0-winx64\bin\mysql.exe') { $Mysql = 'C:\portable-apps\mysql-8.3.0-winx64\bin\mysql.exe' } } if (-not $Source) { throw "必须提供 -Source 'user:pass@tcp(host:3306)/db'(或设置环境变量 ARCHER_SOURCE)" } # user:pass@tcp(host:port)/db $m = [regex]::Match($Source, '^(?[^:@/]*)(:(?[^@]*))?@tcp\((?[^:)]+)(:(?\d+))?\)/(?[^?]*)') if (-not $m.Success) { throw "无法解析 DSN,期望形如 user:pass@tcp(host:3306)/db,实际: $Source" } $dbUser = $m.Groups['user'].Value $dbPass = $m.Groups['pass'].Value $dbHost = $m.Groups['host'].Value $dbPort = if ($m.Groups['port'].Success) { $m.Groups['port'].Value } else { '3306' } if (-not $Schema) { $Schema = $m.Groups['db'].Value } if (-not $Schema) { throw "DSN 中没有库名,且未提供 -Schema" } if (-not $OutDir) { $OutDir = Join-Path $root 'export' } $OutDir = [System.IO.Path]::GetFullPath($OutDir) $schemaDir = Join-Path $OutDir $Schema # 日志写在 $OutDir 下,先确保它存在 New-Item -ItemType Directory -Force -Path $OutDir | Out-Null # --------------------------------------------------------------------------- # mysql 客户端查询(只用于列举表名与校验行数) # --------------------------------------------------------------------------- function Invoke-MysqlQuery { param([Parameter(Mandatory)][string]$Sql) if (-not $Mysql -or -not (Test-Path $Mysql)) { throw "找不到 mysql.exe,无法查询 information_schema;请用 -Tables 显式列出表名,或用 -Mysql 指定路径" } $prev = $env:MYSQL_PWD $env:MYSQL_PWD = $dbPass try { $lines = & $Mysql --protocol=TCP "--host=$dbHost" "--port=$dbPort" ` "--user=$dbUser" --default-character-set=utf8mb4 -N -B -e $Sql 2>&1 $code = $LASTEXITCODE } finally { $env:MYSQL_PWD = $prev } if ($code -ne 0) { throw "mysql 查询失败(exit $code): $($lines -join ' ')" } # 过滤 mysql 客户端自身的 [Warning] 行 return @($lines | Where-Object { "$_" -notmatch '^mysql: \[Warning\]' }) } # --------------------------------------------------------------------------- # 收集表清单 # --------------------------------------------------------------------------- # -File 调用时 -Tables a,b 会作为一个字符串传入,这里按逗号拆开 $Tables = @($Tables | ForEach-Object { $_ -split ',' } | ForEach-Object { $_.Trim() } | Where-Object { $_ }) if ($Tables.Count -gt 0) { $tables = $Tables } else { if ($DryRun) { Write-Host "解析表清单..." } $tables = Invoke-MysqlQuery ("SELECT table_name FROM information_schema.tables " + "WHERE table_schema = '$Schema' AND table_type = 'BASE TABLE' ORDER BY table_name") } if ($Exclude) { $tables = $tables | Where-Object { $_ -notlike $Exclude } } $tables = @($tables | Where-Object { $_ }) if ($tables.Count -eq 0) { throw "库 $Schema 中没有匹配的表" } # --------------------------------------------------------------------------- # 主流程 # --------------------------------------------------------------------------- Write-Host '' Write-Host "archer : $Archer" Write-Host "schema : $Schema ($dbHost`:$dbPort)" Write-Host "out dir : $schemaDir" Write-Host "shard : count / $ShardRows rows, chunk $ChunkSize, $Format + $Compress" Write-Host "tables : $($tables.Count)" Write-Host '' $failed = @() $skipped = @() $ok = @() $idx = 0 foreach ($table in $tables) { $idx++ $prefix = "[$idx/$($tables.Count)] $table" $tableDir = Join-Path $schemaDir $table $stateFile = Join-Path $tableDir 'state.json' # 已完成则跳过(archer 对 complete=true 的目录会直接退出,这里提前判断避免无谓启动) if (-not $Restart -and (Test-Path $stateFile)) { try { $state = Get-Content -Raw -Path $stateFile | ConvertFrom-Json } catch { $state = $null } if ($state -and $state.complete) { Write-Host "$prefix 跳过(已完成,rows_done=$($state.rows_done))" $skipped += $table continue } Write-Host "$prefix 续跑(rows_done=$($state.rows_done))" } if ($Restart -and (Test-Path $tableDir)) { Write-Host "$prefix -Restart:删除 $tableDir" Remove-Item -Recurse -Force $tableDir } $argv = @( '--source', $Source, '--table', $table, '--where', '1=1', '--file', $tableDir, '--format', $Format, '--compress', $Compress, '--shard-by', 'count', '--shard-rows', "$ShardRows", '--chunk-size', "$ChunkSize", '--progress', $ProgressMode, '--statistics' ) if ($DryRun) { Write-Host "$prefix" Write-Host (" " + $Archer + " " + ($argv -join ' ')) $ok += $table continue } $log = Join-Path $OutDir "$Schema.$table.log" $sw = [System.Diagnostics.Stopwatch]::StartNew() # archer 会往 stderr 打提示;PowerShell 在 EAP=Stop 下会把原生 stderr 当成终止性错误, # 这里临时降级为 Continue,并把 stdout+stderr 一起落日志。 $prevEap = $ErrorActionPreference $ErrorActionPreference = 'Continue' $code = 0 try { $attempt = 0 while ($true) { $attempt++ & $Archer @argv 2>&1 | Out-File -FilePath $log -Encoding utf8 $code = $LASTEXITCODE if ($code -eq 0 -or $attempt -gt $Retries) { break } Write-Host "$prefix 第 $attempt 次失败(exit $code),重试中(archer 自动续跑)..." -ForegroundColor Yellow Start-Sleep -Seconds 2 } } finally { $ErrorActionPreference = $prevEap } $sw.Stop() if ($code -ne 0) { Write-Host "$prefix 失败(exit $code,耗时 $([int]$sw.Elapsed.TotalSeconds)s)—— 见 $log" -ForegroundColor Red Get-Content $log -Tail 5 | ForEach-Object { Write-Host " $_" } $failed += [pscustomobject]@{ Table = $table; Code = $code; Log = $log } continue } # 汇总本次分片情况 $manifest = Join-Path $tableDir 'manifest.jsonl' $shards = 0; $rows = 0; $bytes = 0 if (Test-Path $manifest) { foreach ($line in Get-Content $manifest) { if (-not $line.Trim()) { continue } $e = $line | ConvertFrom-Json $shards++ $rows += [int64]$e.rows $bytes += [int64]$e.bytes } } Write-Host ("$prefix 完成:{0} 分片, {1} 行, {2:N1} MB, {3}s" -f ` $shards, $rows, ($bytes / 1MB), [int]$sw.Elapsed.TotalSeconds) $ok += $table } # --------------------------------------------------------------------------- # 校验 # --------------------------------------------------------------------------- if ($Verify -and -not $DryRun -and $ok.Count -gt 0) { Write-Host '' Write-Host '== 行数校验 ==' foreach ($table in $ok) { $stateFile = Join-Path (Join-Path $schemaDir $table) 'state.json' if (-not (Test-Path $stateFile)) { continue } $state = Get-Content -Raw -Path $stateFile | ConvertFrom-Json $want = [int64]$state.rows_done $got = [int64](Invoke-MysqlQuery "SELECT COUNT(*) FROM ``$Schema``.``$table``" | Select-Object -First 1) if ($want -eq $got) { Write-Host (" {0,-34} {1,10} OK" -f $table, $got) } else { Write-Host (" {0,-34} 导出 {1} / 源 {2} <== DIFF" -f $table, $want, $got) -ForegroundColor Yellow } } } Write-Host '' Write-Host ("完成:成功 {0},跳过 {1},失败 {2};输出 $schemaDir" -f $ok.Count, $skipped.Count, $failed.Count) if ($failed.Count -gt 0) { $failed | ForEach-Object { Write-Host (" 失败 {0} (exit {1}) 日志 {2}" -f $_.Table, $_.Code, $_.Log) } exit 1 } exit 0