数据库之间表的迁移

什么时候用？

相信大家在开发中，会先在本地和测试环境的数据库去建立表，没问题了再去正式环境建立相同的表。或者我们有旧系统和新系统，要把旧系统里面的数据库某个功能的相关的表给导出来，这时候就会发生表的数据迁移。

目前我处于的公司正在维护一个新系统，是对原来旧系统的升级和维护，就需要进行测试，需要迁移表。

Nvaicate 完成快速迁移

之后可以点击开始后即可完成不同连接下不同数据库里面表的快速迁移。

数据备份工具

这里我先来介绍一下 MySQL 上常用的两款数据备份工具：mysqldump和XtraBackup。

mysqldump：一个用于备份和恢复 MySQL 数据库的命令行工具。它允许用户导出 MySQL 数据库的结构、数据以及表之间的关系，以便在数据库发生问题时进行恢复。它是 一个逻辑备份工具，导出的内容是一条条 SQL。

XtraBackup：它使用了 InnoDB 存储引擎的数据备份技术，支持增量备份和恢复，并且支 持多主机备份和恢复。它是一个物理备份工具，相当于直接复制 InnoDB 的底层存储文 件。

通过程序完成数据迁移

1. 创建目标表。

2. 用源表的数据初始化目标表。

3. 执行一次校验，并且修复数据，此时用源表数据修复目标表数据。

4. 业务代码开启双写，此时读源表，并且先写源表，数据以源表为准。

5. 开启增量校验和数据修复，保持一段时间。

6. 切换双写顺序，此时读目标表，并且先写目标表，数据以目标表为准。

7. 继续保持增量校验和数据修复。

8. 切换为目标表单写，读写都只操作目标表。

如果是程序，你的项目面临的场面需要保持数据一致性问题，那么有两个办法：

update_time字段校验

在初始化数据之后，你可以先尝试立刻校验和修复一下数据，因为如果你前面用的是备份数 据，那么备份数据已经落后生产数据了。比如说你用的是昨天的备份，那么今天的修改目标 表就没有。还有如果你是导出的数据，那么导出数据到你导入数据这段时间，数据发生了变 化，目标表依旧是没有的。

如果你们公司有明确的数据库规范的话，比如说所有的表都需要有 update_time 这个字段， 那么你在校验和修复的时候就可以采用增量的方案。因为只有 update_time 晚于你导出数据 的那个时间点，才说明这一行的数据已经发生了变更。在修复的时候就直接用源表的数据覆 盖掉目标表的数据。这个方案有两个条件：所有的表都是有更新时间戳的，并且删除是软删除的。

监听binlog

binlog 只用于触 发校验和修复这个动作，当我收到 binlog 之后，我会用 binlog 中的主键，去 查询源表和目标表，再比较两者的数据。如果不一致，就用源表的数据去修复 目标表。

拿到 binlog 之后，我用主键去目标表查询数据，然后把 binlog 里面的内容和 目标表的数据进行比较。如果数据不一致，再用 binlog 的主键去源表里面查询 到数据，直接覆盖目标表的数据。

主键问题

如果在源表中使用的是自增主键，那么在双写的时候写入目标表要写入主键。你需要在写入源表的时候拿到自增主键，然后写入目标表的时候设置好主 键。因为你其实并不能确保目标表自增的主键，和你源表自增的主键是同一个值。比如说在 并发场景下，两次插入。

在双写的时候比较难以处理的问题是自增主键问题。为了保持源表和目标表的 数据完全一致，需要在源表插入的时候拿到自增主键的值，然后用这个值作为 目标表插入的主键。

在处理批量插入的时候要更加小心一些。正常来说，批量插入如果用的是 VALUES 语法，那么生成的主键是连续的，就可以从返回的最后一个主键推测 出前面其他行的主键。即便 innodb_autoinc_lock_mode 取值是 2 也能保证 这一点。但是如果用的是多个 INSERT INTO VALUE 语句，或者 INSERT SELECT语句，这些语句生成的主键就可能不连续。在双写之前，就要先改造这 一类的业务。

innodb_autoinc_lock_mode

innodb_autoinc_lock_mode 是 InnoDB 引擎里面控制自增主键生成策略的参数，它有三个 取值。

• 0：使用表自增锁，但是锁在 INSERT 语句结束之后就释放了。

• 1：使用表自增锁，如果是普通的 INSERT INTO VALUE 或者 INSERT INTO VALUES 语 句，申请了主键就释放锁，而不是整个 INSERT 语句执行完毕才释放。如果是 INSERT SELECT 等语句，因为无法确定究竟要插入多少行，所以都是整个 INSERT 语句执行完毕才释放。

• 2：使用表自增锁，所有的语句都是申请了主键就立刻释放。