数据增量推送技术方案文档

数据增量推送技术方案文档

1. 项目背景

• 需求：将本地 MySQL 数据库中的烟感数据（三张核心表）增量推送到远程 MySQL 数据库。
• 环境：本地 MySQL 8.0.42，远程 MySQL 环境相同。
• 策略：基于 create_time 时间戳的增量提取，采用 REPLACE INTO 模式保证数据幂等性（防重复），分批次推送防止数据库过载。

---

2. 数据库信息对照表

项目	本地数据库 (源)	远程数据库 (目标)
IP地址	192.168..	219...*
端口	1000*	30
数据库名	bfm	yangan_*
用户名	ro*	yangan*
同步表名	a*_device, a*_event_inst, a*_unit	同左

---

3. 核心脚本逻辑 (push_yangan_data.sh)

脚本采用了 mysqldump 提取数据配合 mysql 客户端导入。

关键技术亮点：

• 断点续传：记录最后一次成功推送的 create_time 至 .txt 文件，下次运行自动衔接。
• 特殊字符处理：通过 export MYSQL_PWD 环境变量处理含 #@! 等符号的强密码，规避 Shell 转义错误。
• 分批控制：设置 BATCH_SIZE=500 和 sleep，确保百万级数据推送时不会导致数据库连接堆积或锁表。

# 核心同步指令示例
#!/bin/bash

# ========================================================
# 1. 基础配置
# ========================================================
LOCAL_BIN="/usr/local/mysql/bin/mysql"
LOCAL_SOCK="/mnt/sdc/mysql/mysql.sock"
LOCAL_USER="*****"
LOCAL_PWD='********'
LOCAL_DB="***"

REMOTE_HOST="************"
REMOTE_PORT="******"
REMOTE_USER="*****"
REMOTE_PWD='*******'
REMOTE_DB="******"

# 运行参数
LOG_FILE="/var/log/yangan_push.log"
LAST_TIME_FILE="/var/log/yangan_last_sync_time.txt"
BATCH_SIZE=500  # INSERT模式建议每批500条，防止SQL语句超长或卡死
SLEEP_TIME=1    # 批次间停顿

TABLES=("*_device" "*_event_inst" "*_unit")

# ========================================================
# 2. 初始检查
# ========================================================
if [ ! -f "$LAST_TIME_FILE" ]; then
    echo "2026-01-26 00:00:00" > "$LAST_TIME_FILE"
fi

LAST_TIME=$(cat "$LAST_TIME_FILE")
START_RUN_TIME=$(date "+%Y-%m-%d %H:%M:%S")

echo "--------------------------------------------------" >> $LOG_FILE
echo "[$START_RUN_TIME] 开始同步任务 (INSERT模式)" >> $LOG_FILE

# 导出远程密码变量，避免命令行显示密码和特殊字符报错
export MYSQL_PWD=$REMOTE_PWD

# ========================================================
# 3. 核心同步循环
# ========================================================
for TABLE in "${TABLES[@]}"; do
    echo "[$(date "+%H:%M:%S")] 正在同步表: $TABLE" >> $LOG_FILE
    
    # 获取待同步的总数
    TOTAL_COUNT=$($LOCAL_BIN -u$LOCAL_USER -p"$LOCAL_PWD" -S $LOCAL_SOCK $LOCAL_DB -N -s -e "SELECT COUNT(*) FROM $TABLE WHERE create_time > '$LAST_TIME';")
    
    echo "表 $TABLE 待推送增量: $TOTAL_COUNT 条" >> $LOG_FILE
    
    if [ "$TOTAL_COUNT" -eq 0 ]; then
        continue
    fi

    OFFSET=0
    while [ $OFFSET -lt $TOTAL_COUNT ]; do
        # 生成带 REPLACE INTO 的增量数据 SQL 脚本
        # --skip-comments: 不输出注释
        # --no-create-info: 不输出建表语句
        # --replace: 使用 REPLACE INTO 代替 INSERT，防止主键冲突
        # --extended-insert: 批量插入模式，提高效率
        # --hex-blob: 处理二进制字段（如果有）
        
        TMP_SQL="/tmp/${TABLE}_push.sql"
        
        /usr/local/mysql/bin/mysqldump -u$LOCAL_USER -p"$LOCAL_PWD" -S $LOCAL_SOCK \
            $LOCAL_DB $TABLE \
            --no-create-info \
            --replace \
            --skip-comments \
            --extended-insert \
            --hex-blob \
            --where="create_time > '$LAST_TIME' ORDER BY create_time ASC LIMIT $OFFSET, $BATCH_SIZE" > "$TMP_SQL"

        # 推送到远程政数局数据库
        $LOCAL_BIN -h$REMOTE_HOST -P$REMOTE_PORT -u$REMOTE_USER $REMOTE_DB < "$TMP_SQL" 2>>$LOG_FILE
        
        if [ $? -eq 0 ]; then
            echo "进度: $TABLE 已推送前 $((OFFSET + BATCH_SIZE > TOTAL_COUNT ? TOTAL_COUNT : OFFSET + BATCH_SIZE)) 条" >> $LOG_FILE
        else
            echo "错误: 表 $TABLE 在 OFFSET $OFFSET 处推送失败" >> $LOG_FILE
        fi

        OFFSET=$((OFFSET + BATCH_SIZE))
        rm -f "$TMP_SQL"
        sleep $SLEEP_TIME
    done
done

# 更新最后同步时间点
echo "$START_RUN_TIME" > "$LAST_TIME_FILE"
echo "[$START_RUN_TIME] 同步任务结束。" >> $LOG_FILE

---

4. 部署与维护手册

A. 初始环境准备

1. 创建路径：mkdir -p /root/scripts
2. 建立日志文件：

• touch /var/log/yangan_push.log（记录详细推送进度）
• touch /var/log/yangan_last_sync_time.txt（存储同步时间戳）

3. 设定起始时间：

• echo "2026-01-26 00:00:00" > /var/log/yangan_last_sync_time.txt

B. 定时任务配置

通过 crontab -e 配置每日凌晨自动运行：

# 每天凌晨 01:30 执行增量推送，并将系统级报错记录到专门日志中
30 01 * * * /bin/bash /root/scripts/push_yangan_data.sh >> /var/log/yangan_cron_error.log 2>&1

C. 日志巡检指令

• 检查昨晚同步了多少条：tail -n 50 /var/log/yangan_push.log
• 确认当前同步到的时间点：cat /var/log/yangan_last_sync_time.txt
• 检查异常报错：cat /var/log/yangan_cron_error.log

---

5. 常见问题 (FAQ)

• Q: 为什么日志里有 unknown variable 'local-infile=1'？

• A: 这是因为本地 my.cnf 配置了客户端参数，而 mysqldump 工具不识别该参数。此报错不影响数据导出和推送结果，可直接忽略。

• Q: 如果某天服务器宕机，数据会丢吗？

• A: 不会。脚本会读取 last_sync_time.txt。只要该文件记录的是宕机前的时间，下次启动后脚本会自动补推从该时间点至今的所有数据。

• Q: 为什么有些表在日志里没有出现？

• A: 说明该表在上次同步到本次运行之间没有 create_time 的新增记录，脚本会自动跳过无增量的表。

---

文档说明结束。