共417行

同步两台mysql数据库一些表数据

2026-06-30 09:18:57

详细需求

  1. 有服务器A，B，已配置好在A上可以免密ssh登录上B；
  2. A上通过docker安装了mysql数据库db1;
  3. B上安装了mysql数据库db2；
  4. 把db2里面某几张表同步到db1里面；
  5. 同步策略：db2上表有外键的话，同步到db1上去掉外键
  6. 按天（yyyy-MM-dd）保存日志

前提

GRANT ALL PRIVILEGES ON *.* TO 'B_MYSQL_USER'@'%' WITH GRANT OPTION;
-- 刷新权限
FLUSH PRIVILEGES;

最终脚本如下

#!/bin/bash
# 同步 B(Windows):db2 指定表 -> A(Linux):db1(Docker)，自动去除外键约束
# 方案：B(windows cmd) mysqldump -> SSH 管道 -> A 本地 .sql -> Docker MySQL 导入 -> ALTER TABLE DROP FOREIGN KEY

set -eo pipefail

# ===== 配置 =====
B_HOST="xxx"
B_MYSQL_USER="xxx"
B_MYSQL_PASS="xxx"

# Windows 端 mysqldump / mysql 已加入 PATH，直接使用命令名
B_MYSQLDUMP="mysqldump"
B_MYSQL="mysql"

A_CONTAINER="xxx"
A_MYSQL_USER="xxx"
A_MYSQL_PASS="xxx"
A_TMP_DIR="/home/ykyk/wmslog/tmp"

# 要同步的表列表（按依赖顺序排列，父表在前）
TABLES=(
    "wms_customer_user"
    "wms_floor"
    "wms_good_type"
    "wms_goods_name"
    "wms_order"
    "wms_order_code_list"
    "wms_order_peel"
    "wms_weighing_order"
    "wms_warehouse"
    "wms_warehouse_position"
)

# 需要同步的源/目标库对：源库:目标库
DB_PAIRS=(
    "zywms:dw_zywms"
    "zywms_back:zywms_back"
)

# 按天归档目录
DATE_DIR="$A_TMP_DIR/$(date +%Y-%m-%d)"
LOG_FILE="$DATE_DIR/wms_log.log"
ERROR_LOG="$DATE_DIR/wms_error.log"

mkdir -p "$DATE_DIR"

# ===== 过滤 docker exec mysql -p 密码告警（stderr） =====
filter_mysql_stderr() {
    grep -v 'Using a password on the command line interface can be insecure' || true
}

# ===== 解析 dump SQL 提取外键约束名 =====
extract_fk_names() {
    local sql_file="$1"
    grep -oE "CONSTRAINT \`[^\`]+\` FOREIGN KEY" "$sql_file" \
        | sed -E 's/CONSTRAINT `([^`]+)` FOREIGN KEY/\1/'
}

# ===== 连接字符串封装（Windows cmd 兼容） =====
build_b_dump_cmd() {
    local SRC_DB="$1"
    local TABLE="$2"
    printf '%s -h127.0.0.1 -P3306 -u%s -p%s --single-transaction --quick --no-create-db --complete-insert --skip-triggers %s %s' \
        "$B_MYSQLDUMP" "$B_MYSQL_USER" "$B_MYSQL_PASS" "$SRC_DB" "$TABLE"
}

build_b_mysql_cmd() {
    local SRC_DB="$1"
    printf '%s -h127.0.0.1 -P3306 -u%s -p%s -D %s -e "SELECT 1 AS ping;"' \
        "$B_MYSQL" "$B_MYSQL_USER" "$B_MYSQL_PASS" "$SRC_DB"
}

# 在 Windows 上执行远程命令
exec_b_cmd() {
    local cmd_text="$1"
    ssh -o StrictHostKeyChecking=no "$B_HOST" 'cmd /c '"$cmd_text"
}

# ===== 函数：同步单表 =====
sync_table_no_fk() {
    local SRC_DB="$1"
    local DST_DB="$2"
    local TABLE="$3"
    local START_TIME=$(date +%s)

    echo "[$(date '+%F %T')] [$SRC_DB->$DST_DB] Starting sync: $TABLE" >> "$LOG_FILE"

    local local_sql="$DATE_DIR/${SRC_DB}_${TABLE}.sql"

    # Step 1: B(Windows) 导出，通过 SSH 管道传到 A 本地文件
    local dump_cmd
    dump_cmd=$(build_b_dump_cmd "$SRC_DB" "$TABLE")

    echo "[$(date '+%F %T')]   (1/3) B:$B_HOST $SRC_DB.$TABLE -> $local_sql" >> "$LOG_FILE"
    if ! exec_b_cmd "$dump_cmd" > "$local_sql" 2>> "$ERROR_LOG"; then
        echo "[$(date '+%F %T')] FAILED (dump): $SRC_DB.$TABLE" >> "$ERROR_LOG"
        return 1
    fi

    if [ ! -s "$local_sql" ]; then
        echo "[$(date '+%F %T')] FAILED (empty dump): $SRC_DB.$TABLE" >> "$ERROR_LOG"
        return 1
    fi

    # Step 2: A 上 Docker MySQL 导入（FK 检查已关闭）
    echo "[$(date '+%F %T')]   (2/3) docker exec mysql import $DST_DB.$TABLE" >> "$LOG_FILE"
    if ! docker exec -i "$A_CONTAINER" \
        mysql -u"$A_MYSQL_USER" -p"$A_MYSQL_PASS" \
        --max_allowed_packet=256M \
        --init-command="SET FOREIGN_KEY_CHECKS=0;" \
        "$DST_DB" < "$local_sql" 2> >(filter_mysql_stderr >> "$ERROR_LOG"); then
        echo "[$(date '+%F %T')] FAILED (import): $DST_DB.$TABLE" >> "$ERROR_LOG"
        return 1
    fi

    # Step 3: 明确删除外键
    local fk_list
    fk_list=$(extract_fk_names "$local_sql")
    if [ -n "$fk_list" ]; then
        echo "[$(date '+%F %T')]   (3/3) drop foreign keys: $(echo "$fk_list" | tr '\n' ' ')" >> "$LOG_FILE"
        for fk in $fk_list; do
            docker exec -i "$A_CONTAINER" \
                mysql -u"$A_MYSQL_USER" -p"$A_MYSQL_PASS" "$DST_DB" \
                -e "ALTER TABLE \`$TABLE\` DROP FOREIGN KEY \`$fk\`;" \
                2> >(filter_mysql_stderr >> "$ERROR_LOG") || {
                echo "[$(date '+%F %T')] FAILED (drop FK $fk): $DST_DB.$TABLE" >> "$ERROR_LOG"
                return 1
            }
        done
    else
        echo "[$(date '+%F %T')]   (3/3) no foreign keys to drop" >> "$LOG_FILE"
    fi

    # SQL 文件已归档到按天目录，不删除
    local END_TIME=$(date +%s)
    local DURATION=$((END_TIME - START_TIME))
    echo "[$(date '+%F %T')] Completed: $SRC_DB->$DST_DB $TABLE (${DURATION}s)" >> "$LOG_FILE"
    return 0
}

# ===== 连通性预检 =====
for PAIR in "${DB_PAIRS[@]}"; do
    SRC_DB="${PAIR%%:*}"
    echo "========== Precheck Start [$SRC_DB]: $(date '+%F %T') ==========" >> "$LOG_FILE"
    precheck_cmd=$(build_b_mysql_cmd "$SRC_DB")
    echo "Precheck cmd [$SRC_DB]: $precheck_cmd" >> "$LOG_FILE"
    if ! exec_b_cmd "$precheck_cmd" 2>> "$ERROR_LOG"; then
        echo "[FATAL] Cannot connect to B MySQL $SRC_DB on ($B_HOST)." >> "$ERROR_LOG"
        echo "         请确认：" >> "$ERROR_LOG"
        echo "         1) B 服务器上 mysql 存在且 PATH 正确" >> "$ERROR_LOG"
        echo "         2) 账户 '$B_MYSQL_USER' 密码 '$B_MYSQL_PASS' 正确" >> "$ERROR_LOG"
        echo "         3) B MySQL 3306 端口开放且允许 TCP 连接" >> "$ERROR_LOG"
        echo "         4) 源库 $SRC_DB 在 B 上存在" >> "$ERROR_LOG"
        exit 1
    fi
    echo "========== Precheck OK [$SRC_DB] ==========" >> "$LOG_FILE"
done

# ===== 主流程 =====
echo "========== Sync Start: $(date '+%F %T') ==========" >> "$LOG_FILE"
echo "Date dir: $DATE_DIR" >> "$LOG_FILE"

# 关闭 A 上 Docker MySQL 的外键检查
docker exec -i "$A_CONTAINER" \
    mysql -u"$A_MYSQL_USER" -p"$A_MYSQL_PASS" \
    -e "SET GLOBAL FOREIGN_KEY_CHECKS=0;" \
    2> >(filter_mysql_stderr >> "$ERROR_LOG")

# 按序同步每个库对的每张表
FAILED_TABLES=""
for PAIR in "${DB_PAIRS[@]}"; do
    SRC_DB="${PAIR%%:*}"
    DST_DB="${PAIR##*:}"
    echo "---------- Sync pair: $SRC_DB -> $DST_DB ----------" >> "$LOG_FILE"
    for TABLE in "${TABLES[@]}"; do
        sync_table_no_fk "$SRC_DB" "$DST_DB" "$TABLE" || \
            FAILED_TABLES="$FAILED_TABLES ${SRC_DB}->${DST_DB}:$TABLE"
    done
done

# 恢复外键检查
docker exec -i "$A_CONTAINER" \
    mysql -u"$A_MYSQL_USER" -p"$A_MYSQL_PASS" \
    -e "SET GLOBAL FOREIGN_KEY_CHECKS=1;" \
    2> >(filter_mysql_stderr >> "$ERROR_LOG")

echo "========== Sync End: $(date '+%F %T') ==========" >> "$LOG_FILE"

# 报告结果
if [ -n "$FAILED_TABLES" ]; then
    echo "[WARNING] Failed tables:$FAILED_TABLES" >> "$LOG_FILE"
    exit 1
else
    echo "[SUCCESS] All tables synced successfully" >> "$LOG_FILE"
fi

脚本详细介绍

一、整体作用

这是一个 跨平台的 MySQL 数据库同步脚本，用于：

二、执行流程图

┌─ A (Linux) ─────────────────────────────────────────────────┐
│                                                              │
│   ssh B  "cmd /c mysqldump ..."   │  A 本地临时文件         │
│   (免密)                           ▼   (按天归档)          │
│  ┌─────────────────┐    ┌────────────────────┐               │
│  │ Windows Server  │    │ /home/ykyk/wmslog/ │               │
│  │  MySQL (B_DB)   │───▶│ tmp/YYYY-MM-DD/    │               │
│  └─────────────────┘    │  zywms_TABLE.sql   │               │
│                         └──────────┬─────────┘               │
│                                    │ docker exec mysql <      │
│                                    ▼                         │
│                         ┌────────────────────┐               │
│                         │ Docker MySQL (A_DB)│               │
│                         │  SET FK_CHECKS=0   │               │
│                         │  ALTER TABLE DROP FK│               │
│                         └────────────────────┘               │
└──────────────────────────────────────────────────────────────┘

三、分模块讲解

1. 配置区（第 7-44 行）

B_HOST="xxx"          # 源端 Windows 主机
B_MYSQL_USER="xxx"
B_MYSQL_PASS="xxx"

B_MYSQLDUMP="mysqldump"       # Windows 端已加入 PATH
B_MYSQL="mysql"

A_CONTAINER="xxx"    # Docker 容器 ID
A_MYSQL_USER="xxx"
A_MYSQL_PASS="xxx"
A_TMP_DIR="/home/ykyk/wmslog/tmp"

TABLES=( ... )                # 要同步的表（顺序：父表在前）
DB_PAIRS=(                    # 源库:目标库 对
    "zywms:dw_zywms"
    "zywms_back:zywms_back"
)

DATE_DIR="$A_TMP_DIR/$(date +%Y-%m-%d)"   # 按天归档
LOG_FILE="$DATE_DIR/wms_log.log"
ERROR_LOG="$DATE_DIR/wms_error.log"

关键设计：

2. 工具函数区（第 48-78 行）

filter_mysql_stderr — 过滤噪音日志

filter_mysql_stderr() {
    grep -v 'Using a password on the command line interface can be insecure' || true
}

过滤 Docker MySQL 在命令行传 -p 时的那行安全警告，避免污染错误日志。

extract_fk_names — 从 dump 里解析外键名

extract_fk_names() {
    local sql_file="$1"
    grep -oE "CONSTRAINT \`[^\`]+\` FOREIGN KEY" "$sql_file" \
        | sed -E 's/CONSTRAINT `([^`]+)` FOREIGN KEY/\1/'
}

用正则提取 CONSTRAINT fk_xxx FOREIGN KEY 中的外键名，供后续 ALTER TABLE DROP FOREIGN KEY 使用。

build_b_dump_cmd / build_b_mysql_cmd — 生成 Windows 端命令

build_b_dump_cmd() {
    local SRC_DB="$1"
    local TABLE="$2"
    printf '%s -h127.0.0.1 -P3306 -u%s -p%s --single-transaction --quick --no-create-db --complete-insert --skip-triggers %s %s' \
        "$B_MYSQLDUMP" "$B_MYSQL_USER" "$B_MYSQL_PASS" "$SRC_DB" "$TABLE"
}

exec_b_cmd — 跨平台远程执行

exec_b_cmd() {
    local cmd_text="$1"
    ssh -o StrictHostKeyChecking=no "$B_HOST" 'cmd /c '"$cmd_text"
}

这是跨平台的核心技巧：

3. 核心同步函数 sync_table_no_fk（第 81-140 行）

这是同步一张表的主逻辑，分三步：

Step 1：从 B 导出到 A 本地

exec_b_cmd "$dump_cmd" > "$local_sql" 2>> "$ERROR_LOG"

Step 2：导入 A 的 Docker MySQL

docker exec -i "$A_CONTAINER" \
    mysql -u"$A_MYSQL_USER" -p"$A_MYSQL_PASS" \
    --max_allowed_packet=256M \
    --init-command="SET FOREIGN_KEY_CHECKS=0;" \
    "$DST_DB" < "$local_sql"

Step 3：显式删除外键

for fk in $fk_list; do
    docker exec -i "$A_CONTAINER" \
        mysql ... -e "ALTER TABLE \`$TABLE\` DROP FOREIGN KEY \`$fk\`;"
done

这是比 sed 更可靠的做法：先解析出每个 FK 名，再用 ALTER TABLE DROP FOREIGN KEY 明确删除。相比正则过滤 dump 文本，这一步能保证：

最后，SQL 文件保留在 DATE_DIR 里作为归档和故障排查依据，不自动删除。

4. 连通性预检（第 142-158 行）

在真正同步前，先对每个 DB_PAIRS 的源库做一次 SELECT 1，失败时直接 exit 1 并打印诊断信息。好处：

5. 主流程（第 160-196 行）

docker exec ... SET GLOBAL FOREIGN_KEY_CHECKS=0     # 关闭 A 的全局外键检查

for PAIR in "${DB_PAIRS[@]}"; do                    # 外层：库对
    for TABLE in "${TABLES[@]}"; do                 # 内层：表
        sync_table_no_fk "$SRC_DB" "$DST_DB" "$TABLE"
    done
done

docker exec ... SET GLOBAL FOREIGN_KEY_CHECKS=1     # 恢复

# 汇总失败表列表，exit 1 方便调度系统感知

四、一些设计亮点

  1. 跨平台 SSH 兼容：利用 'cmd /c ' + 纯净文本 的拼接方案，让 bash 脚本可以稳定驱动 Windows cmd，避免多层引号地狱。
  2. 可靠的 FK 去除策略：SET FK_CHECKS=0 + ALTER TABLE DROP FK 两步走，比传统的 sed 文本过滤更稳妥。
  3. 按天归档：DATE_DIR 让日志和 dump 文件自动按日期归类，方便回溯和做历史对比。
  4. 错误不中断：主循环中单表失败不直接 exit，而是把失败记录到 FAILED_TABLES，最后汇总退出，最大化同步吞吐量。
  5. 预检机制：启动即做连通性检查，避免长任务跑一半才失败。
  6. 清晰的日志：日志里每一条都带时间戳、库对、表名，便于 grep。

五、执行后的目录结构示例

/home/ykyk/wmslog/tmp/
├── 2026-06-30/
│   ├── wms_log.log
│   ├── wms_error.log
│   ├── zywms_wms_customer_user.sql
│   ├── zywms_wms_floor.sql
│   ├── ...
│   ├── zywms_back_wms_customer_user.sql
│   └── zywms_back_wms_floor.sql

六、使用注意事项