MySQL开发库与Staging库手动数据迁移方案咨询：全量+增量更新

一、全量数据迁移 #

直接使用MySQL官方工具mysqldump完成全量迁移，彻底规避information_schema的可靠性问题：

• 导出开发库：

  mysqldump -u dev_user -pdev_pass --databases dev_db --single-transaction --routines --triggers > full_data_dump.sql
  

  --single-transaction保证InnoDB表导出时的数据一致性，--routines和--triggers确保存储过程、触发器等对象一并导出。
• 导入Staging库：

  mysql -u staging_user -pstaging_pass staging_db < full_data_dump.sql
  

二、手动触发的增量同步方案 #

方案1：基于时间戳/自增ID的增量同步 #

适用于所有业务表带有updated_at时间戳字段或自增主键的场景：

1. 在Staging库创建专用同步元数据表，记录上次同步标记：

   CREATE TABLE sync_metadata (
       sync_id INT PRIMARY KEY AUTO_INCREMENT,
       last_sync_time DATETIME,
       last_sync_max_id INT,
       sync_type ENUM('full', 'incremental') NOT NULL,
       created_at DATETIME DEFAULT CURRENT_TIMESTAMP
   );
   

2. 手动触发同步时：
   • 时间戳模式：查询开发库中updated_at大于上次同步时间的数据，用REPLACE INTO或INSERT ... ON DUPLICATE KEY UPDATE写入Staging库，完成后更新last_sync_time为当前时间。
   • 自增ID模式：查询开发库中主键ID大于上次记录的最大ID的数据，同步后更新last_sync_max_id为本次同步的最大ID。
3. 优势：实现简单，无额外MySQL配置要求，完全手动可控。

方案2：基于Binlog的增量同步 #

适用于需要捕获所有变更（包括DELETE、DDL）的场景，需先开启开发库Binlog：

1. 开发库配置修改（my.cnf/my.ini）：

   log-bin=mysql-bin
   binlog-format=ROW
   server-id=1
   

   重启MySQL服务后，用SHOW MASTER STATUS;获取当前Binlog文件和位置，存入sync_metadata表。
2. 手动触发同步时：
   • 使用mysqlbinlog解析从上次记录位置到当前位置的Binlog：

     mysqlbinlog --start-position=1234 --stop-position=5678 mysql-bin.000001 > incremental_changes.sql
     

   • 将生成的SQL文件导入Staging库，或用Node.js的mysql2库直接解析Binlog事件并应用变更。
3. 更新sync_metadata表中的最新Binlog位置信息。
4. 优势：能捕获所有数据变更，一致性最高，不会遗漏任何操作。

方案3：使用Percona Toolkit的pt-table-sync #

专业数据同步工具，支持手动触发的全量/增量同步：

• 全量同步（首次执行）：

  pt-table-sync --execute --sync-to-master h=staging_ip,u=staging_user,p=staging_pass,D=staging_db h=dev_ip,u=dev_user,p=dev_pass,D=dev_db
  

• 增量同步（手动触发）：

  pt-table-sync --execute --sync-to-master --where "updated_at > '$(mysql -u staging_user -pstaging_pass -e "SELECT last_sync_time FROM sync_metadata ORDER BY sync_id DESC LIMIT 1;" staging_db | tail -1)'" h=staging_ip,u=staging_user,p=staging_pass,D=staging_db h=dev_ip,u=dev_user,p=dev_pass,D=dev_db
  

• 优势：自动处理数据冲突，无需编写复杂同步逻辑，可靠性强。

三、方案对比与选择 #

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