请教下各位大佬，关于 mysql 的双主复制问题

做双主复制时按照网上的教程，为了防止自增主键冲突
设置了
auto-increment-increment = 2 //每次增长 2
auto-increment-offset = 1 //设置自动增长的字段的偏移量，即初始值为 1

B 设置了
auto-increment-increment = 2 //每次增长 2
auto-increment-offset = 2 //设置自动增长的字段的偏移量，即初始值为 2

这样 A 数据的自增主键就变成了 1、3、5、7 ....
B 数据的自增主键就变成了 2、4、6、8 ...

现在有个问题的是，这个主键同时也是外键，被别的表给引用，例如用户表的主键 id，代表用户 ID
在记录用户登录记录时 insert loginlog(userid, time, ip) values(1，xxx,xxx)
这条语句被同步到 B 的时候，userid 也是 1，但实际上，用户在 B 上的 ID 是 2。

这样 B 表的数据就会错乱，请问这种问题怎么解决，因为之前的业务没考虑这种复制问题，想在想到的解决办法是，用个新的 userid 字段代表用户 ID，但是这种改动太大了，请问各位大佬还有没有其它办法，或者是我的双主复制做的有问题呢，谢谢。

主键

字段

userid

自增主键

24 条回复 • 2019-07-03 18:14:36 +08:00

demos

2019-07-02 20:43:32 +08:00

消灭 0 回复，一个回的大佬都没有吗 -_-

moodasmood

2019-07-02 23:23:19 +08:00 via Android

我理解的复制，不应该是 id 一样吗？

littlewing

2019-07-03 02:19:03 +08:00 via Android

没懂你在说啥

littlewing

2019-07-03 02:20:26 +08:00 via Android

另外，双主一般是只开放一个用于 write 的

medivh

2019-07-03 06:00:54 +08:00

"但实际上，用户在 B 上的 ID 是 2。"
你这个设计真...那你读怎么处理呢？双读然后服务去合并数据吗？

AngryPanda

2019-07-03 07:11:10 +08:00 via Android

没看懂你要干嘛

2019-07-03 07:50:38 +08:00 via iPhone

双主一般同时只会有一个在写，那么为什么还需要考虑 id 冲突的问题呢？因为切换哪个可写的时候，可能会有同时写的情况（这个看你切换的逻辑）所以会预防性的把 id 这么设计。那么解法就是，要么保证切换的时候确认旧的已经停止写入再切，要不就是不要用外键之类的数据库约束，你程序自己保证。mysql 双主带来的脏数据问题多了，你要保证服务在线，那就很难保证数据完整性。

paranoiagu

2019-07-03 08:16:35 +08:00 via Android

同时写入死锁肯定会很多。读写分离吧。

cubecube

2019-07-03 08:28:28 +08:00 via Android

你这么做就很反直觉了。非要这么做，为啥用户在 b 上还有一个？就是 1 不就是唯一的了么

coolwind1981

2019-07-03 08:45:02 +08:00

同步设定好之前，A 和 B 的数据是要一样的（比如把 A 的数据导入 B，或把 B 的数据导入 A ），然后再开始同步
这样用户的 ID 在 A，B 中都是一样的，不会出现在一个中 ID 是 1，B 中 ID 是 2，就不会有您说的这个问题

coolwind1981

2019-07-03 08:47:05 +08:00

A 新增一個用戶，ID 是 1，同步到 B 中，ID 也是 1，不會變成 2 的；
B 新增一個用戶，ID 是 2，同步到 A 中，ID 也是 2，不會變成 3 的；

smartychaos

2019-07-03 09:28:39 +08:00

技术上讲 binlog 使用 row 模式，而不是在从库上执行一遍 statement 就好了吧。

cyssxt

2019-07-03 09:36:19 +08:00 via iPhone

分库不像分库复制不像复制

sujin190

2019-07-03 10:00:25 +08:00

@littlewing #4
@wd #7 人家这样，估计要的就是双写入吧

isbarton

2019-07-03 10:11:55 +08:00

不是 DBA，但是了解一些 msyql。
这个是比较野的多主方案吧。看样子是两个节点互为主从。这么用的比较少。

mysql 多主现在生产环境用的一般有 PXC/MGC，或者 MGR 方案。
pxc 和 MGC 其实同样的东西，都是 Percona 主导的技术。
MGR 是比较新的方案，没怎么了解。
应用层都不用重新设计。

多主一般强调一致性，同时在所有的节点做写入操作。保证所有的节点数据一致。
缺点明显，集群性能估计是单节点的 60%左右。如果有个节点性能差，会直接拖后腿。

dot2017

2019-07-03 10:17:18 +08:00

为什么要用双主呢，不安全啊

demos

2019-07-03 12:53:30 +08:00

感谢各位大佬的回答，看很多人的回复说，同步后不会出现 ID 不一样的情况
我理解 mysql 的同步是
A: insert into softuser (name) values ('张三')
ID 不填，使用自增，新插入的用户,ID 是 1
同步时 A 会把这条 SQL 发给 B，B 去执行，因为 B 的 auto-increment-offset = 2，所以 B 执行这条语句后新插入的用户 ID 是 2
我看了下 binlog，记录的语句也是这个，我的理解没问题吧。

lannoooW

2019-07-03 14:31:40 +08:00

@demos MySQL 复制分三种，row 模式是同步具体的数据，不是发 sql 语句到 B 上执行； statement 模式是发 sql 执行；还有一种 mixed 模式，两者都有，主要是在一条 sql 会涉及大量数据变化的时候（如 truncate, create table as select...）会发 sql 到 B 执行。题主双主复制应该是 row 模式吧=。=

demos

2019-07-03 15:17:28 +08:00

@lannoooW 用的 mixed，看了下说明，mixed 模式默认是发 statement，只有特殊情况才会用 row
也就是默认还发的 sql。
请问双主是要改成的是 row 模式吗，搜了几编双主的配置文章，大部分都没提到这点。

2019-07-03 16:38:00 +08:00 via iPhone

@sujin190 #14 双写还互相同步的话估计数据很快就一团糟了

sujin190

2019-07-03 17:20:38 +08:00

@lannoooW #18
@demos #19 mysql 主从还有发 sql 的，难道不应该主库提交之后把数据发给从库么？否则像 update a set updated_at=now() 这样的语句岂不是主从得到的时间不一致了，怎么可能啊

lannoooW

2019-07-03 17:48:45 +08:00

@demos 看了下确实 mixed 模式下默认是 statement 模式。官方的解释是这样的：
“ With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.

You can avoid these issues by using MySQL's row-based replication instead.”
也就是说用 MIXED 的模式或者 statement 模式不能保证两边数据是没问题的，像你这种场景还是 row 模式复制比较合适

lazyfighter

2019-07-03 17:52:20 +08:00

看的我一脸懵逼，不是复制吗咋 id 还变了呢

lazyfighter

2019-07-03 18:14:36 +08:00

@lazyfighter 我孤陋寡闻了