MySQL表记录从Dev到QA再到Prod的自动化迁移方案咨询

核心思路 #

放弃手动修改mysqldump生成的SQL文件，改用导出原始数据→字段批量替换→智能导入的自动化流程，用脚本串联全环节，同时借助MySQL的INSERT ... ON DUPLICATE KEY UPDATE语法，处理QA环境已有记录的更新/保留需求。

具体实现步骤 #

1. 精准导出目标数据 #

不用mysqldump生成INSERT语句，直接导出为CSV格式（更易做字段替换），支持全表或部分记录导出：

• 导出全表：

  mysql -h dev-rds-host -u dev-user -p'dev-pass' dev-db -e "SELECT * FROM target_table INTO OUTFILE '/tmp/target_table_dev.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
  

• 导出筛选后的部分记录：

  mysql -h dev-rds-host -u dev-user -p'dev-pass' dev-db -e "SELECT * FROM target_table WHERE created_at > '2024-01-01' INTO OUTFILE '/tmp/target_table_dev.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
  

若RDS不允许直接导出到服务器文件，可改用mysql -e "SELECT ..." > /tmp/target_table_dev.csv直接输出到本地机器。

2. 批量替换环境特定字段 #

根据环境映射关系批量修改字段值，简单场景用Shell的sed即可，复杂映射用Python脚本处理：

• Shell替换示例：

  # 替换vpc_id和subnet_id的环境值
  sed -i 's/"dev-vpc-123"/"qa-vpc-456"/g' /tmp/target_table_dev.csv
  sed -i 's/"dev-subnet-789"/"qa-subnet-012"/g' /tmp/target_table_dev.csv
  

• Python多字段映射示例：

  import csv
  
  # 定义环境字段映射表
  env_mappings = {
      "vpc_id": {"dev-vpc-123": "qa-vpc-456", "dev-vpc-456": "qa-vpc-789"},
      "subnet_id": {"dev-subnet-789": "qa-subnet-012"}
  }
  
  # 读取原始数据并替换
  with open('/tmp/target_table_dev.csv', 'r') as infile, open('/tmp/target_table_qa.csv', 'w', newline='') as outfile:
      reader = csv.DictReader(infile)
      writer = csv.DictWriter(outfile, fieldnames=reader.fieldnames)
      writer.writeheader()
      for row in reader:
          for field, mapping in env_mappings.items():
              if row[field] in mapping:
                  row[field] = mapping[row[field]]
          writer.writerow(row)
  

3. 智能导入处理冲突记录 #

利用MySQL的INSERT ... ON DUPLICATE KEY UPDATE语法，实现"无记录则插入，有记录则更新指定字段"的逻辑：

-- 创建临时表（与目标表结构一致）
CREATE TEMPORARY TABLE temp_target_table LIKE target_table;
-- 导入处理后的CSV数据
LOAD DATA INFILE '/tmp/target_table_qa.csv' INTO TABLE temp_target_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
-- 合并到目标表：主键冲突时仅更新环境相关字段
INSERT INTO target_table SELECT * FROM temp_target_table
ON DUPLICATE KEY UPDATE
    vpc_id = VALUES(vpc_id),
    subnet_id = VALUES(subnet_id),
    updated_at = NOW(); -- 可选：更新时间戳
-- 删除临时表
DROP TEMPORARY TABLE temp_target_table;

4. 脚本串联全流程 #

写一个Shell脚本整合所有步骤，支持参数化配置，方便复用：

#!/bin/bash

# 配置参数
DEV_HOST="dev-rds-host"
DEV_USER="dev-user"
DEV_PASS="dev-pass"
DEV_DB="dev-db"
QA_HOST="qa-rds-host"
QA_USER="qa-user"
QA_PASS="qa-pass"
QA_DB="qa-db"
TARGET_TABLE="target_table"
FILTER_CONDITION="created_at > '2024-01-01'" # 留空则导出全表
VPC_MAP="dev-vpc-123:qa-vpc-456"
SUBNET_MAP="dev-subnet-789:qa-subnet-012"

# 导出Dev数据
if [ -n "$FILTER_CONDITION" ]; then
    mysql -h $DEV_HOST -u $DEV_USER -p$DEV_PASS $DEV_DB -e "SELECT * FROM $TARGET_TABLE WHERE $FILTER_CONDITION > '/tmp/${TARGET_TABLE}_dev.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
else
    mysql -h $DEV_HOST -u $DEV_USER -p$DEV_PASS $DEV_DB -e "SELECT * FROM $TARGET_TABLE INTO OUTFILE '/tmp/${TARGET_TABLE}_dev.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
fi

# 替换环境字段
OLD_VPC=$(echo $VPC_MAP | cut -d':' -f1)
NEW_VPC=$(echo $VPC_MAP | cut -d':' -f2)
OLD_SUBNET=$(echo $SUBNET_MAP | cut -d':' -f1)
NEW_SUBNET=$(echo $SUBNET_MAP | cut -d':' -f2)
sed -i "s/\"$OLD_VPC\"/\"$NEW_VPC\"/g" /tmp/${TARGET_TABLE}_dev.csv
sed -i "s/\"$OLD_SUBNET\"/\"$NEW_SUBNET\"/g" /tmp/${TARGET_TABLE}_dev.csv

# 导入到QA环境
mysql -h $QA_HOST -u $QA_USER -p$QA_PASS $QA_DB << EOF
CREATE TEMPORARY TABLE temp_$TARGET_TABLE LIKE $TARGET_TABLE;
LOAD DATA INFILE '/tmp/${TARGET_TABLE}_dev.csv' INTO TABLE temp_$TARGET_TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
INSERT INTO $TARGET_TABLE SELECT * FROM temp_$TARGET_TABLE
ON DUPLICATE KEY UPDATE
    vpc_id = VALUES(vpc_id),
    subnet_id = VALUES(subnet_id),
    updated_at = NOW();
DROP TEMPORARY TABLE temp_$TARGET_TABLE;
EOF

# 清理临时文件
rm /tmp/${TARGET_TABLE}_dev.csv

场景覆盖说明 #

1. QA无对应记录：直接插入处理后的新数据
2. QA已有记录（主键冲突）：仅更新指定的环境字段及需要同步的字段，保留其他原有数据
3. 仅迁移部分记录：在导出步骤添加WHERE筛选条件即可实现
4. 多字段多值替换：扩展sed命令或Python映射表即可支持更多字段

注意事项 #

• 确保Dev和QA环境的表结构完全一致，否则导入会失败
• 数据库密码建议用环境变量或密钥管理工具存储，不要硬编码在脚本中
• 迁移前先在测试环境验证逻辑，避免影响业务数据
• 大数据量迁移建议分批次处理，避免锁表或超时

内容的提问来源于stack exchange，提问作者anurag