MySQL 200GB InnoDB大表修改数据类型及主从环境数据同步问题

哥们，你这个问题太典型了——大表变更+持续写入+主从架构，确实不能只靠简单的建表复制就完事，我给你一套经过生产环境验证的方案，把增量数据和主从同步的坑都填上：

1. 前期准备：创建新表并确认主库binlog状态 #

首先还是基于原表结构创建新表，修改目标字段类型：

CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);

因为你是主从架构，主库肯定已经开启了binlog（否则从库同步不了），这一步的操作会自动同步到所有从库，从库会自动创建WorkingTableNew并修改字段类型，不用单独操作从库。

2. 记录同步起点，执行全量数据迁移 #

为了后续能精准同步增量数据，先记录当前主库的binlog位置（这一步要在全量插入前执行）：

SHOW MASTER STATUS;

把结果里的File（比如mysql-bin.000123）和Position（比如123456）记下来，这是全量数据同步的起点。

然后执行全量数据插入：

INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;

SQL_NO_CACHE可以避免查询时占用过多缓存，提升大表数据复制的速度。这一步的操作同样会同步到从库，从库的WorkingTableNew会同步全量数据。

3. 同步增量数据（核心步骤） #

全量同步完成后，这段时间里原表的新增、更新数据还没同步到新表，这里有两种稳妥的方式：

• 方式一：用mysqlbinlog解析增量
  用之前记录的binlog位置，解析这段时间的变更SQL，然后应用到新表：

  mysqlbinlog --start-position=123456 --stop-position=XXXXXX mysql-bin.000123 | mysql -u your_user -p your_db
  

  注意stop-position要选在锁表前的最新位置，你可以再次执行SHOW MASTER STATUS获取。
• 方式二：用Percona Toolkit的pt-table-sync（更省心）
  这个工具会自动对比新旧表的差异，同步所有新增、更新的数据，还能避免锁表：

  pt-table-sync --execute --verbose h=localhost,D=your_database,t=WorkingTable h=localhost,D=your_database,t=WorkingTableNew
  

  它会自动处理主键冲突、数据不一致的情况，生产环境用这个更稳妥。

4. 切换表：最小化锁表时间 #

为了避免最后切换时还有写入，我们需要短暂锁原表（只阻止写，允许读），同步最后一点增量，然后完成重命名：

-- 1. 给原表加读锁，阻止新的写入
LOCK TABLES WorkingTable READ;

-- 2. 同步最后一批增量（确保没有遗漏）
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable WHERE id > (SELECT MAX(id) FROM WorkingTableNew);
-- 或者再跑一次pt-table-sync，确保数据完全一致

-- 3. 重命名表完成切换
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;

-- 4. 解锁表，恢复写入
UNLOCK TABLES;

这一步的锁表时间极短，只会影响最后几秒的写入（如果业务对延迟敏感，建议选低峰期操作），而且所有操作会同步到从库，从库会自动完成表的重命名。

5. 主从架构的额外检查 #

切换完成后，要确认所有从库的状态：

• 用SHOW SLAVE STATUS\G检查从库的Seconds_Behind_Master，确保没有延迟。
• 在主从分别执行CHECKSUM TABLE WorkingTable;，对比校验值，确认数据完全一致。

6. 收尾工作 #

切换后观察1-2天，确认业务读写正常、数据没有问题，再删除旧表：

DROP TABLE WorkingTableOld;

如果担心出问题，可以先把旧表备份到其他地方，或者保留一周再删除。

内容的提问来源于stack exchange，提问作者Sandeep B J