322 lines
9.8 KiB
Markdown
322 lines
9.8 KiB
Markdown
# 数据库本地/云端切换方案
|
||
|
||
## 当前配置
|
||
|
||
| | 本地 MySQL | 腾讯云 CynosDB |
|
||
|---|---|---|
|
||
| 地址 | `127.0.0.1:3306` | `gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com:24936` |
|
||
| 用户 | `root` | `root` |
|
||
| 密码 | `123456` | `!Rjb12191` |
|
||
| 数据库 | `agent_db` | `agent_db` |
|
||
|
||
---
|
||
|
||
## 一、切换到云端（本地 → 云）
|
||
|
||
### 步骤 1：导出本地最新数据
|
||
|
||
```bash
|
||
mysqldump -h 127.0.0.1 -P 3306 -u root -p123456 \
|
||
--single-transaction \
|
||
--set-gtid-purged=OFF \
|
||
--databases agent_db \
|
||
> agent_db_local.sql
|
||
```
|
||
|
||
### 步骤 2：导入云端
|
||
|
||
```bash
|
||
mysql -h gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com \
|
||
-P 24936 -u root -p'!Rjb12191' \
|
||
--default-character-set=utf8mb4 \
|
||
agent_db < agent_db_local.sql
|
||
```
|
||
|
||
### 步骤 3：修改配置
|
||
|
||
编辑 `backend\.env`：
|
||
```
|
||
DATABASE_URL=mysql+pymysql://root:!Rjb12191@gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com:24936/agent_db?charset=utf8mb4
|
||
```
|
||
|
||
### 步骤 4：重启服务
|
||
|
||
```powershell
|
||
powershell -ExecutionPolicy Bypass -File .\scripts\restart_api_worker.ps1
|
||
```
|
||
|
||
---
|
||
|
||
## 二、切换到本地（云 → 本地）
|
||
|
||
### 步骤 1：导出云端数据
|
||
|
||
```bash
|
||
mysqldump -h gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com \
|
||
-P 24936 -u root -p'!Rjb12191' \
|
||
--single-transaction \
|
||
--set-gtid-purged=OFF \
|
||
--databases agent_db \
|
||
> agent_db_cloud.sql
|
||
```
|
||
|
||
### 步骤 2：导入本地
|
||
|
||
```bash
|
||
mysql -h 127.0.0.1 -P 3306 -u root -p123456 \
|
||
--default-character-set=utf8mb4 \
|
||
agent_db < agent_db_cloud.sql
|
||
```
|
||
|
||
> 如本地尚无 `agent_db`：先 `CREATE DATABASE agent_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`
|
||
|
||
### 步骤 3：修改配置
|
||
|
||
编辑 `backend\.env`：
|
||
```
|
||
DATABASE_URL=mysql+pymysql://root:123456@127.0.0.1:3306/agent_db?charset=utf8mb4
|
||
```
|
||
|
||
### 步骤 4：重启服务
|
||
|
||
```powershell
|
||
powershell -ExecutionPolicy Bypass -File .\scripts\restart_api_worker.ps1
|
||
```
|
||
|
||
---
|
||
|
||
## 三、一键切换脚本
|
||
|
||
在 `backend\` 目录下创建 `switch_db.ps1`：
|
||
|
||
```powershell
|
||
param(
|
||
[ValidateSet("local","cloud")]
|
||
[string]$target = "local"
|
||
)
|
||
|
||
$env_path = "D:\aaa\aiagent\backend\.env"
|
||
|
||
if ($target -eq "local") {
|
||
$url = "mysql+pymysql://root:123456@127.0.0.1:3306/agent_db?charset=utf8mb4"
|
||
} else {
|
||
$url = "mysql+pymysql://root:!Rjb12191@gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com:24936/agent_db?charset=utf8mb4"
|
||
}
|
||
|
||
(Get-Content $env_path -Encoding UTF8) -replace '^DATABASE_URL=.*$', "DATABASE_URL=$url" |
|
||
Set-Content $env_path -Encoding UTF8
|
||
|
||
Write-Host "switched to: $target"
|
||
Write-Host "restarting..."
|
||
& "D:\aaa\aiagent\backend\scripts\restart_api_worker.ps1"
|
||
```
|
||
|
||
用法：
|
||
```powershell
|
||
# 切到本地
|
||
.\switch_db.ps1 local
|
||
|
||
# 切到云端
|
||
.\switch_db.ps1 cloud
|
||
```
|
||
|
||
---
|
||
|
||
## 四、数据同步方案
|
||
|
||
日常使用中，本地和云端两套库会各自产生增量数据，以下提供三种同步策略。
|
||
|
||
### 策略对比
|
||
|
||
| 方案 | 适用场景 | 复杂度 | 数据安全 |
|
||
|---|---|---|---|
|
||
| A. 定时全量同步 | 单主库为主，另一库为备份/测试 | 低 | 高（无删除风险） |
|
||
| B. 增量同步脚本 | 两库都有写入，需合并 | 中 | 中（依赖时间戳） |
|
||
| C. 主从复制 | 云端为主，本地只读 | 高 | 最高 |
|
||
|
||
---
|
||
|
||
### 方案 A：定时全量同步（推荐）
|
||
|
||
**思路**：以一方为主，定时导出全量覆盖另一方。适合"云端开发调试 → 同步到本地备份"或"本地开发 → 推到云端上线"的单一方向。
|
||
|
||
在 `backend\` 创建 `sync_db.ps1`：
|
||
|
||
```powershell
|
||
param(
|
||
[ValidateSet("local2cloud","cloud2local")]
|
||
[string]$direction = "local2cloud"
|
||
)
|
||
|
||
$dump_file = "D:\aaa\aiagent\agent_db_sync.sql"
|
||
|
||
if ($direction -eq "local2cloud") {
|
||
Write-Host "=== 本地 同步到 云端 ==="
|
||
|
||
# 1. 导出本地
|
||
Write-Host "[1/3] 导出本地..."
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" `
|
||
-h 127.0.0.1 -P 3306 -u root -p123456 `
|
||
--single-transaction --set-gtid-purged=OFF `
|
||
--databases agent_db > $dump_file
|
||
if ($LASTEXITCODE -ne 0) { Write-Host "ERROR: 导出失败"; exit 1 }
|
||
|
||
# 2. 导入云端（先删后建，保证完全一致）
|
||
Write-Host "[2/3] 导入云端..."
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" `
|
||
-h gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com -P 24936 `
|
||
-u root -p'!Rjb12191' `
|
||
--default-character-set=utf8mb4 < $dump_file
|
||
if ($LASTEXITCODE -ne 0) { Write-Host "ERROR: 导入失败"; exit 1 }
|
||
|
||
# 3. 清理
|
||
Write-Host "[3/3] 完成"
|
||
Remove-Item $dump_file -ErrorAction SilentlyContinue
|
||
|
||
} else {
|
||
Write-Host "=== 云端 同步到 本地 ==="
|
||
|
||
# 1. 导出云端
|
||
Write-Host "[1/3] 导出云端..."
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" `
|
||
-h gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com -P 24936 `
|
||
-u root -p'!Rjb12191' `
|
||
--single-transaction --set-gtid-purged=OFF `
|
||
--databases agent_db > $dump_file
|
||
if ($LASTEXITCODE -ne 0) { Write-Host "ERROR: 导出失败"; exit 1 }
|
||
|
||
# 2. 丢弃本地并重建
|
||
Write-Host "[2/3] 导入本地..."
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" `
|
||
-h 127.0.0.1 -P 3306 -u root -p123456 `
|
||
-e "DROP DATABASE IF EXISTS agent_db; CREATE DATABASE agent_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" 2>NUL
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" `
|
||
-h 127.0.0.1 -P 3306 -u root -p123456 `
|
||
--default-character-set=utf8mb4 agent_db < $dump_file
|
||
if ($LASTEXITCODE -ne 0) { Write-Host "ERROR: 导入失败"; exit 1 }
|
||
|
||
# 3. 清理
|
||
Write-Host "[3/3] 完成"
|
||
Remove-Item $dump_file -ErrorAction SilentlyContinue
|
||
}
|
||
|
||
Write-Host "同步完成: $direction"
|
||
```
|
||
|
||
用法：
|
||
```powershell
|
||
# 本地开发完，推到云端
|
||
.\sync_db.ps1 local2cloud
|
||
|
||
# 云端有最新数据，拉到本地
|
||
.\sync_db.ps1 cloud2local
|
||
```
|
||
|
||
> 可配合 Windows 任务计划程序（`taskschd.msc`）每天凌晨自动执行。
|
||
|
||
---
|
||
|
||
### 方案 B：增量同步（按更新时间合并）
|
||
|
||
**思路**：只同步有变化的行，避免全量传输。要求表有 `updated_at` 字段。
|
||
|
||
`sync_incremental.ps1`：
|
||
```powershell
|
||
param(
|
||
[string]$since = (Get-Date).AddHours(-1).ToString("yyyy-MM-dd HH:mm:ss")
|
||
)
|
||
|
||
$local = @{ Host="127.0.0.1"; Port="3306"; User="root"; Pass="123456" }
|
||
$cloud = @{ Host="gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com"; Port="24936"; User="root"; Pass="!Rjb12191" }
|
||
|
||
# 需要同步的业务表（排除日志/缓存/向量）
|
||
$tables = @(
|
||
"agents", "workflows", "workflow_versions", "teams", "team_members",
|
||
"goals", "model_configs", "data_sources", "node_templates",
|
||
"agent_schedules", "alert_rules", "users", "workspaces",
|
||
"agent_permissions", "tool_registry", "tools"
|
||
)
|
||
|
||
foreach ($table in $tables) {
|
||
Write-Host "Syncing: $table"
|
||
|
||
$dump_file = "D:\aaa\aiagent\temp_${table}.sql"
|
||
|
||
# 导出本地增量（按 updated_at 过滤）
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" `
|
||
-h $local.Host -P $local.Port -u $local.User -p$local.Pass `
|
||
--single-transaction --set-gtid-purged=OFF --no-create-info `
|
||
--where="updated_at >= '$since'" `
|
||
agent_db $table > $dump_file 2>NUL
|
||
|
||
# 导入云端（先删后插，防止主键冲突）
|
||
$pk_col = @{ agents="id"; workflows="id"; workflow_versions="id"; teams="id"; team_members="id"; goals="id"; users="id" }
|
||
$pk = $pk_col[$table]
|
||
if ($pk) {
|
||
$ids = Select-String -Path $dump_file -Pattern "VALUES\s*\((.*?)\)" -AllMatches |
|
||
ForEach-Object { $_.Matches } |
|
||
ForEach-Object { "'" + $_.Groups[1].Value.Split(",")[0].Trim("'") + "'" }
|
||
$id_list = $ids -join ","
|
||
if ($id_list) {
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" `
|
||
-h $cloud.Host -P $cloud.Port -u $cloud.User -p$cloud.Pass `
|
||
-e "DELETE FROM agent_db.$table WHERE $pk IN ($id_list)" 2>NUL
|
||
}
|
||
}
|
||
|
||
& "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" `
|
||
-h $cloud.Host -P $cloud.Port -u $cloud.User -p$cloud.Pass `
|
||
--default-character-set=utf8mb4 agent_db < $dump_file 2>NUL
|
||
|
||
Remove-Item $dump_file -ErrorAction SilentlyContinue
|
||
}
|
||
Write-Host "增量同步完成 (since: $since)"
|
||
```
|
||
|
||
用法：
|
||
```powershell
|
||
# 同步最近1小时的变更
|
||
.\sync_incremental.ps1
|
||
|
||
# 同步2026-06-21之后的所有变更
|
||
.\sync_incremental.ps1 -since "2026-06-21 00:00:00"
|
||
```
|
||
|
||
> **注意**：增量同步无法检测 `DELETE` 操作。如需完整一致性，定期用方案A做一次全量覆盖。
|
||
|
||
---
|
||
|
||
### 方案 C：云端为主、本地只读（主从复制思路）
|
||
|
||
如果开发流程是"线上跑业务、本地做数据分析/调试"，可用此方案：
|
||
|
||
1. 本地定期从云端全量拉取（方案A的 `cloud2local` 方向）
|
||
2. 本地不做写入操作，避免冲突
|
||
3. 如需在本地改配置/Agent，改完后用方案A推回云端
|
||
|
||
此方案最简单且无数据不一致风险，推荐日常开发使用。
|
||
|
||
---
|
||
|
||
### 同步策略选择建议
|
||
|
||
| 场景 | 推荐方案 |
|
||
|---|---|
|
||
| 日常开发：本地改完上线 | `cloud2local`（拉最新）→ 本地改 → `local2cloud`（推上线） |
|
||
| 每日自动备份到本地 | 方案A `cloud2local` + Windows定时任务 |
|
||
| 本地和云端都在改（双活） | 方案B 增量同步 + 定期方案A全量校验 |
|
||
| 只把云端当主库，本地纯备份 | 方案C（最省心） |
|
||
|
||
---
|
||
|
||
## 五、重要提示
|
||
|
||
| 事项 | 说明 |
|
||
|---|---|
|
||
| 数据同步 | 导出导入是**全量覆盖**，不会合并两边增量数据 |
|
||
| 切换时机 | 建议在无活跃用户时操作，避免数据丢失 |
|
||
| 备份 | 每次切换前建议保留导出文件作为备份 |
|
||
| GTID | 导出必须加 `--set-gtid-purged=OFF`，否则导入本地会报错 |
|
||
| 本地 MySQL | 需确保 MySQL 5.7 服务已启动（`services.msc` → MySQL57） |
|
||
| 飞书服务 | 云端恢复后飞书机器人会自动重连，无需额外操作 |
|