<think>
我们正在讨论的是单机单库环境下的不停机数据迁移。用户之前提到了需要实时同步，但这次明确是在单机单库中操作，且要求不停机。这意味着我们不能停止数据库服务，同时需要将数据从旧表迁移到新表，并且在此过程中应用程序可以继续读写。
根据之前的回答，我们推荐了一些工具（如Debezium、MySQL Shell等），但那些工具在分布式环境中更常用。在单机单库环境下，我们可以采用更轻量级的方法。
在单机环境下，我们可以利用数据库自身的特性来实现。例如，MySQL提供了多种方式：
1. **在线DDL工具**：如pt-online-schema-change（Percona Toolkit）或gh-ost（GitHub开源的），但这些主要用于表结构变更，而不是数据迁移到新表。
2. **触发器+批处理**：创建一个新表，然后通过触发器捕获旧表的变更，同时分批将旧数据迁移到新表。
3. **使用MySQL的复制特性**：即使单机，也可以创建另一个实例（比如在同一台机器上启动另一个MySQL实例作为副本），然后通过主从复制来实现，但这可能有点重。
然而，用户要求的是本地操作，并且是单库（同一个数据库实例）。因此，我们考虑使用触发器+批处理的方式。
具体方案：
步骤1：创建新表（比如我们要将user表拆分为usercore和userinfo两个表）。
步骤2：给旧表（user）添加三个触发器，分别对应INSERT、UPDATE、DELETE操作。这些触发器将把变更实时应用到新表（们）上。
步骤3：分批将旧表的数据迁移到新表。在迁移过程中，由于触发器的作用，任何在迁移期间发生的写操作都会被捕获并应用到新表，从而保证数据一致性。
步骤4：迁移完成后，将应用程序的读写切换到新表，然后删除旧表和触发器。
但是，这里有一个关键点：在迁移过程中，应用程序仍然在读写旧表，而触发器会将写操作同时应用到新表。当数据迁移完成后，我们需要确保新表的数据是最新的，然后切换应用。
详细步骤：
1. 准备工作：创建新表结构（usercore和userinfo）。
2. 在旧表上创建触发器：
- AFTER INSERT：将新插入的行拆分成两个表的数据，分别插入到usercore和userinfo。
- AFTER UPDATE：更新两个新表中的对应行。
- AFTER DELETE：删除两个新表中的对应行。
3. 数据迁移：编写一个存储过程（或脚本）来分批迁移旧数据。注意，为了避免长时间锁表，我们分批提交。同时，由于触发器已经存在，在迁移过程中如果有对旧数据的更新，触发器会处理，但要注意我们迁移的数据可能被更新，因此我们需要确保迁移的数据是迁移开始时刻的快照？这有点复杂。
为了解决迁移过程中数据变更的问题，我们可以这样：
- 先记录当前的最大ID（假设有自增ID）或时间戳（如果有更新时间戳）。
- 然后迁移该时间点之前的所有数据（即历史数据）。
- 由于触发器已经存在，历史数据迁移期间发生的变更（针对这些历史数据的更新）会被触发器捕获并应用到新表，所以不会丢失。
但是，这里有一个风险：在迁移历史数据的过程中，如果有一条记录被更新，那么触发器会将更新应用到新表，而随后我们的历史数据迁移又会把旧数据覆盖到新表（因为迁移是按批进行的），导致更新被覆盖。
因此，我们需要确保迁移的数据不会覆盖掉在迁移开始之后由触发器写入的新数据。我们可以这样做：
迁移脚本在迁移每一批数据时，使用INSERT IGNORE或ON DUPLICATE KEY UPDATE？但是这样可能会有性能问题，而且如果新表的结构不同（拆分后的表）可能主键也不同。
另一种方法是：在开始迁移之前，先记录下当前的binlog位置（或者当前时间点），然后迁移所有在这个时间点之前的历史数据（此时不创建触发器），然后在迁移完成后，再创建触发器，然后捕获从记录点之后的所有变更（通过binlog解析或触发器）。但这样就需要在迁移历史数据的时候停止写操作？因为如果没有触发器，迁移过程中发生的写操作不会被记录到新表。
所以，我们需要在开始迁移历史数据之前就创建触发器，并且在迁移历史数据时，避免覆盖由触发器产生的新数据。
具体操作：
1. 创建新表。
2. 在旧表上创建触发器（用于捕获所有写操作）。
3. 开始迁移历史数据：分批从旧表读取数据，然后插入到新表（们）。注意，在插入新表时，使用INSERT IGNORE或者判断是否存在（如果存在则跳过）。因为可能该条数据在迁移之前已经被触发器的操作插入了一部分（比如有新的插入操作刚好插入了这条记录）？但是注意，我们迁移的是历史数据，而触发器是在写操作时触发的，所以如果一条历史数据在迁移的过程中被更新，那么触发器会更新新表中的对应记录（前提是这条记录已经迁移过去了），但如果还没有迁移，那么触发器就会先插入（因为更新操作找不到记录？所以触发器应该只处理新插入的数据？不对，触发器是处理写操作，包括更新）。
这个逻辑非常复杂，很容易出错。
因此，在单机环境下，更简单的方法是使用MySQL的在线DDL工具，但这里我们需要的是将数据拆分到两个表，这不仅仅是表结构变更，而是数据重组。所以在线DDL工具并不适用。
另一种思路：使用物化视图。但是MySQL不支持物化视图。
考虑到这些，我推荐使用一个轻量级的CDC工具，比如Debezium，即使是在单机环境下，因为它可以捕获binlog并且不会影响业务。但用户要求的是本地操作，并且可能希望简单。
所以，我们折中一下，采用以下方案：
步骤1：创建新表。
步骤2：开启事务，记录当前的binlog位置（或者GTID），然后导出当前时刻的历史数据（使用mysqldump或者select into outfile），然后导入到新表（们）。这个导出导入的过程需要时间，但在这段时间内，数据库还是在写入，所以我们在导入完成后，需要将导出后产生的增量数据再同步过去。
步骤3：使用binlog同步工具（比如mysqlbinlog解析并应用）将导出开始位置到导入完成位置之间的binlog应用到新表（们）。但是，如何将binlog解析成对新表的操作？这需要自己写程序，比较复杂。
然而，在同一个实例中，我们可以使用一个临时表来记录增量数据？或者使用触发器记录变更。
综合来看，对于单机单库，且要求简单的情况下，我们可以采用以下步骤：
1. 创建新表（usercore, userinfo）。
2. 在旧表上创建触发器，将写操作记录到一个增量表（delta table）中，这个表记录变更类型（insert, update, delete）以及相关的主键和变更时间等。注意，触发器只记录变更，不直接应用到新表，因为直接应用可能会和迁移冲突。
3. 执行历史数据迁移（一次性或分批）。
4. 迁移完成后，将增量表中记录的变更应用到新表（们）。
5. 然后开始实时：在应用增量期间，新的变更又会被记录到增量表，所以我们需要暂停应用（短暂停写），处理剩余的增量，然后切换到新表。
6. 删除旧表、触发器和增量表。
但这个方法需要停写，虽然时间很短，但不是完全不停机。
因此，我们回到最初的想法：在迁移历史数据的同时，通过触发器将实时写操作应用到新表。为了确保历史数据迁移期间不会覆盖实时更新，我们可以这样设计：
- 在旧表上创建触发器，当有写操作时，先检查该行数据是否已经被迁移到新表（我们可以添加一个标记字段，或者通过一个外部表来记录迁移状态）？这样太复杂。
另一种方式是：在触发器里，当有更新操作时，如果数据已经迁移到新表，那么只更新新表；如果还没有迁移，那么先将该行数据插入到新表（这样相当于提前迁移了），然后再更新。对于删除操作，如果数据已经迁移，则删除新表中的数据；如果还没有迁移，则记录下删除操作，等迁移到该条数据时跳过（因为已经被删除）。这需要记录删除操作，非常麻烦。
考虑到这些复杂性，在单机单库环境下，我推荐使用一个简单的工具：pt-archiver（Percona Toolkit的一部分），它可以实现分批迁移数据，同时不会锁定表太久，并且它可以实时地将新增的数据也迁移过去。但是，它通常用于归档，而不是拆分表。
对于拆分表的需求，我们可以这样：
1. 使用pt-archiver将旧表的数据分批迁移到一个中间表（这个中间表的结构和新表之一相同），然后再处理另一个表。但是，这需要两次扫描旧表，而且中间表需要额外创建。
针对拆分表，我们可以编写一个迁移存储过程，该存储过程将旧表中的每一行拆分成两个表的数据，然后插入到两个新表中。在迁移过程中，使用事务分批提交，并且每次提交后暂停一小段时间，以允许其他操作介入。
同时，我们创建触发器，将迁移开始后发生的写操作记录下来（比如记录到一个日志表），然后等迁移完成后，再将这些日志表中的操作应用到新表。最后，将应用切换到新表。
具体步骤：
1. 创建新表：usercore和userinfo。
2. 创建一个日志表，用于记录在迁移过程中发生的写操作。表结构可以包括：操作类型（I/U/D）、主键值、操作时间、以及相关字段的值（对于更新，需要记录更新前后的值？这样太复杂，所以只记录主键，然后重新从旧表拉取最新数据？）。
3. 在旧表上创建三个触发器：
在INSERT之后，向日志表插入一条记录，记录操作类型为'I'，主键值。
在UPDATE之后，向日志表插入一条记录，记录操作类型为'U'，主键值。
在DELETE之后，向日志表插入一条记录，记录操作类型为'D'，主键值。
4. 开始迁移历史数据：从旧表中分批读取数据，拆分成两个表的数据，然后插入到新表。注意，每次读取一批（比如1000条），然后提交，再读取下一批。这样可以减少锁的时间。
5. 迁移完成后，停止应用（短暂停止），然后处理日志表中的记录：按照时间顺序（或主键顺序）重新应用这些操作到新表。注意，在处理日志期间，新的写操作仍然会被触发器记录到日志表，所以我们需要先停止应用，然后快速处理日志，然后再将应用切换到新表。处理日志的方法：
对于每个日志记录，根据主键去旧表中取出当前数据（如果是删除，则不需要取），然后应用到新表（拆分到两个表）。
但是，这里有一个问题：同一个主键可能被记录了多次（比如多次更新），我们只需要最后的状态。所以，我们可以先对日志表按照主键分组，取最后一条操作记录（如果是删除，则忽略之前的操作；如果是更新或插入，则取最后一次更新后的状态）。
但是，这样就需要在应用停止后，对日志表进行去重和排序，然后应用。而且，如果数据量很大，日志表也会很大。
改进：在迁移历史数据的过程中，我们可以同时消费日志表（即一边迁移历史数据，一边将日志表中的操作应用到新表），但这需要保证顺序，而且需要避免重复处理。
所以，我们可以在迁移过程中，每迁移完一批数据，就去处理一下日志表（清空日志表，并将这些操作应用到新表）。这样，在迁移完成后，日志表中就只有最新一段时间内的操作了，然后我们停止应用，再处理最后一次日志。
步骤：
1. 创建新表。
2. 创建日志表（change_log）：
列：id（自增主键）, table_name（这里固定）, pk（旧表的主键值）, action（I/U/D）, processed（是否已处理，默认为0）。
3. 创建触发器，每次写操作后，向change_log插入一条记录（注意，不要记录具体数据，只记录主键和操作类型）。
4. 开始迁移历史数据：
设置一个变量last_pk = 0（假设主键是自增整数）
循环：
读取旧表从last_pk开始的一段数据（比如按主键排序，取1000条），记录最大的主键值作为新的last_pk。
将这些数据拆分成两个表，插入到新表。
然后，处理change_log表中所有processed=0的记录（按照id顺序）：
对于每一条记录，根据主键值去旧表读取当前数据（如果是删除，则旧表里没有）。
如果是插入（I）或更新（U），则重新将这条数据拆分并更新到新表（如果新表中有，则更新；没有则插入）。
如果是删除（D），则从两个新表中删除对应的数据。
然后，标记这些日志记录为processed=1。
直到迁移完所有数据。
5. 迁移完成后，暂停应用（停止对旧表的写入）。
6. 处理change_log表中剩余的未处理记录（方法同上）。
7. 将应用切换到新表，然后恢复应用。
8. 删除旧表、触发器、日志表。
这个方案可以做到几乎不停机，只在最后切换时有一个短暂的停写（秒级）。
但是，需要注意：
- 在迁移过程中，对于同一条记录，日志表中可能会有多条记录（比如多次更新），我们每次处理日志记录时，都是根据当前旧表的数据来更新新表，所以最后一次处理会覆盖之前的，因此没有问题。
- 删除操作：在最后处理日志时，如果发现是删除操作，而旧表中已经没有该记录，那么我们就在新表中删除。注意，有可能在迁移过程中，一条记录被删除，然后又被插入，这种情况也能正确处理（因为最后一条日志记录可能是插入，然后我们去旧表查，发现存在，就会重新插入新表）。
这个方案比较稳妥，而且触发器只记录主键，日志表不会太大。
现在我们用伪代码描述一下迁移过程：
1. 创建新表（usercore, userinfo）。
2. 创建日志表：
CREATE TABLE change_log (
id INT AUTO_INCREMENT PRIMARY KEY,
pk INT NOT NULL, -- 假设旧表主键是INT类型
action CHAR(1) NOT NULL, -- 'I','U','D'
processed TINYINT DEFAULT 0
);
3. 创建触发器：
-- AFTER INSERT on old_table
FOR EACH ROW
BEGIN
INSERT INTO change_log (pk, action) VALUES (NEW.id, 'I');
END;
-- AFTER UPDATE on old_table
FOR EACH ROW
BEGIN
INSERT INTO change_log (pk, action) VALUES (NEW.id, 'U');
END;
-- AFTER DELETE on old_table
FOR EACH ROW
BEGIN
INSERT INTO change_log (pk, action) VALUES (OLD.id, 'D');
END;
4. 迁移历史数据：
SET @batch_size = 1000;
SET @last_id = 0;
REPEAT
-- 迁移一批数据
INSERT INTO usercore (id, username, password, ...)
SELECT id, username, password, ...
FROM old_table
WHERE id > @last_id
ORDER BY id ASC
LIMIT @batch_size;
-- 注意：这里实际需要拆分成两个表，上面只是示例，另一个表同理
INSERT INTO userinfo (user_id, address, ...)
SELECT id, address, ...
FROM old_table
WHERE id > @last_id
ORDER BY id ASC
LIMIT @batch_size;
-- 更新last_id
SELECT MAX(id) INTO @last_id FROM (
SELECT id
FROM old_table
WHERE id > @last_id
ORDER BY id ASC
LIMIT @batch_size
) t;
-- 处理日志
-- 先获取当前未处理的日志（注意，要避免处理新增的日志，所以我们按id分段）
SET @start_log_id = (SELECT IFNULL(MIN(id),0) FROM change_log WHERE processed=0);
SET @end_log_id = (SELECT IFNULL(MAX(id),0) FROM change_log);
-- 循环处理从@start_log_id到@end_log_id的日志
-- 这里为了简单，我们可以一次处理完所有未处理的日志（如果量不大）
-- 创建一个临时表存储这些日志的主键和操作，按id排序
CREATE TEMPORARY TABLE tmp_logs AS
SELECT pk, action
FROM change_log
WHERE id BETWEEN @start_log_id AND @end_log_id
ORDER BY id;
-- 对tmp_logs按pk分组，取最大的id对应的action（但我们没有记录id，所以只能取最后一次操作？）
-- 但是，同一个pk多次出现的情况下，我们只需要最后一次操作？不，我们需要按顺序执行，因为可能先更新再删除。
-- 所以，不能分组，必须按顺序执行。但是，如果日志量大，效率会低。不过，由于我们每批只处理一次，一次最多1000条迁移，日志不会太多。
-- 游标循环处理tmp_logs
DECLARE done INT DEFAULT FALSE;
DECLARE cur_pk INT;
DECLARE cur_action CHAR(1);
DECLARE cur CURSOR FOR SELECT pk, action FROM tmp_logs ORDER BY id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO cur_pk, cur_action;
IF done THEN
LEAVE read_loop;
END IF;
CASE cur_action
WHEN 'I', 'U' THEN
-- 从旧表中取出该行，然后拆分插入到两个新表
-- 这里注意，如果新表中已经存在，则更新，否则插入
-- 这里简化处理：先删除新表中的旧数据（如果存在），然后插入新数据？或者使用REPLACE（要求有主键）
-- 但是新表拆分后，主键可能不同（比如usercore表的主键可能是user_id，userinfo的主键也是user_id，但都是从旧表id来的）
-- 所以，对于usercore表：
REPLACE INTO usercore (...)
SELECT ... FROM old_table WHERE id = cur_pk;
REPLACE INTO userinfo (...)
SELECT ... FROM old_table WHERE id = cur_pk;
WHEN 'D' THEN
DELETE FROM usercore WHERE id = cur_pk;
DELETE FROM userinfo WHERE user_id = cur_pk;
END CASE;
END LOOP;
CLOSE cur;
-- 标记这些日志为已处理
UPDATE change_log SET processed=1 WHERE id BETWEEN @start_log_id AND @end_log_id;
DROP TEMPORARY TABLE tmp_logs;
UNTIL (batch_size行数 < @batch_size) END REPEAT;
5. 此时历史数据迁移完成，并且日志也处理到当前时刻。
6. 暂停应用（停止写入旧表）。
7. 处理最后剩余的日志（如果有）：
执行和处理日志时一样的操作，把剩下的processed=0的日志处理掉。
8. 将应用切换到新表（修改应用的数据库访问配置，指向新表）。
9. 删除旧表、触发器、日志表。
注意：这个方案需要在一个低峰期执行，以避免日志量过大。
但是，这个方法在最后需要停写，虽然时间很短（只有步骤6和7的时间，即处理最后一点日志的时间），但并不是完全不停机。
如果要求完全不停机，我们可以在步骤6和7时，不停止应用，而是将应用同时写两份（双写）：同时写旧表和新表。但这需要修改应用代码，而且可能增加延迟。
所以，对于单机单库，如果允许短暂停写（几秒钟），那么上述方案是可行的。如果要求完全不停写，则需要双写方案，在迁移完成后，将读切换到新表，然后停止写旧表，但这样也需要修改应用。
综上所述，根据用户的需求，我们给出两种方案：
方案一（有短暂停写）：
使用触发器+日志表+分批迁移，最后切换时停写几秒钟。
方案二（完全不停写）：
1. 创建新表，并修改应用代码，使其同时写旧表和新表（双写）。同时，迁移历史数据到新表。
2. 迁移完成后，将读切换到新表。
3. 验证新表数据无误后，移除旧表的写入和旧表。
但是，方案二需要修改应用代码，可能更复杂。
因此，我们根据用户是否允许修改应用来选择。
由于用户之前提到的是“单机单库中进行不停机的本地操作”，所以可能更倾向于方案一（停机时间极短）。
最后，推荐方案一，并给出具体步骤。
注意：以上方案适用于MySQL。其他数据库可能类似，但触发器和具体语法会有所不同。
</think>
针对单机单库环境的不停机数据迁移，推荐采用**应用层双写+增量同步**方案，兼顾安全性与实时性。以下是具体操作流程：
---
### 一、迁移方案流程（零停机）
```mermaid
graph TD
A[初始状态] --> B{双写阶段}
B -->|1. 开启双写| C[新表 usercore/userinfo]
B -->|同时写| D[旧表 user]
C --> E[2. 异步历史迁移]
D --> E
E --> F[3. 增量追平]
F --> G[4. 读流量切新表]
G --> H[5. 停写旧表]
```
---
### 二、核心操作步骤
#### 1. 应用层双写改造
```java
// 应用层写入逻辑改造示例
public void saveUser(User user) {
// 同步写入新表
userCoreMapper.insert(user.toCore());
userInfoMapper.insert(user.toInfo());
// 同步写入旧表（兼容未迁移流量）
legacyUserMapper.insert(user);
}
```
**关键配置**：
- 事务保证双写原子性：`@Transactional(rollbackFor=Exception.class)`
- 启用Spring Retry重试机制：`@Retryable(maxAttempts=3)`
#### 2. 历史数据迁移
```sql
/* 分批迁移（每批5万行） */
INSERT IGNORE INTO usercore
SELECT id, username, password FROM user
WHERE id BETWEEN 1 AND 50000;
INSERT IGNORE INTO userinfo
SELECT id, reg_date, address FROM user
WHERE id BETWEEN 1 AND 50000;
```
**优化技巧**：
- 使用`WHERE id > last_id ORDER BY id LIMIT 50000` 增量分批
- 避开高峰时段执行，每批间隔10秒
#### 3. 增量数据追平
```sql
/* 校验缺失数据 */
SELECT a.id
FROM user a
LEFT JOIN usercore b ON a.id = b.user_id
WHERE b.user_id IS NULL;
```
追平工具选择：
```mermaid
graph LR
A[增量工具] --> B(MySQL触发器)
A --> C(Binlog解析)
A --> D(时间戳扫描)
B -->|简单但性能损耗| E[创建 AFTER INSERT 触发器]
C -->|高效推荐| F[mysqlbinlog | binlog2sql]
D -->|字段需索引| G[WHERE update_time > last_migrate_time]
```
---
### 三、一致性保障措施
#### 1. 数据校验策略
```bash
# 行级校验脚本
mysql -e "SELECT
COUNT(*) AS cnt,
MD5(GROUP_CONCAT(id,username)) AS hash
FROM user" > old.md5
mysql -e "SELECT
(SELECT COUNT(*) FROM usercore) AS core_cnt,
(SELECT MD5(...) FROM usercore) AS core_hash,
(SELECT ... FROM userinfo)" > new.md5
diff {old,new}.md5
```
#### 2. 灰度切换流程
```mermaid
sequenceDiagram
运维平台->>应用集群: 1. 10%读切新表
监控系统-->>运维平台: 2. 监控错误率
Note right of 监控系统: 持续5分钟错误率<0.1%
运维平台->>应用集群: 3. 100%读切新表
运维平台->>应用集群: 4. 关闭旧表写入
```
---
### 四、注意事项
1. **索引优化**
- 新表提前创建索引：
```sql
ALTER TABLE usercore ADD INDEX idx_username(username);
```
- 禁用迁移过程的auto-commit：
`SET autocommit=0;`
2. **锁冲突规避**
- 使用`SELECT ... FOR UPDATE SKIP LOCKED` (MySQL 8.0+)
- 迁移批次大小动态调整：
$$batch\_size = \frac{max\_connections \times 0.8}{2}$$
3. **回滚方案**
| 故障场景 | 回滚动作 |
|---------------|-----------------------------|
| 新表数据异常 | 流量切回旧表 + 暂停双写 |
| 迁移进程中断 | 记录断点ID + `FLUSH TABLES` |
| 主键冲突 | `REPLACE INTO`代替`INSERT` |
> ⚠️ 生产环境建议在低峰期操作，优先使用**binlog追增量**方案[^1]
---
扫一扫
到【灌水乐园】发言
6万+
