V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
demos
V2EX  ›  MySQL

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

  •  
  •   demos · 2019-07-02 17:24:40 +08:00 · 5823 次点击
    这是一个创建于 1952 天前的主题,其中的信息可能已经有所发展或是发生改变。
    做双主复制时按照网上的教程,为了防止自增主键冲突
    设置了
    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,但是这种改动太大了, 请问各位大佬还有没有其它办法,或者是我的双主复制做的有问题呢,谢谢。
    24 条回复    2019-07-03 18:14:36 +08:00
    demos
        1
    demos  
    OP
       2019-07-02 20:43:32 +08:00
    消灭 0 回复,一个回的大佬都没有吗 -_-
    moodasmood
        2
    moodasmood  
       2019-07-02 23:23:19 +08:00 via Android
    我理解的复制,不应该是 id 一样吗?
    littlewing
        3
    littlewing  
       2019-07-03 02:19:03 +08:00 via Android
    没懂你在说啥
    littlewing
        4
    littlewing  
       2019-07-03 02:20:26 +08:00 via Android
    另外,双主一般是只开放一个用于 write 的
    medivh
        5
    medivh  
       2019-07-03 06:00:54 +08:00
    "但实际上,用户在 B 上的 ID 是 2。"
    你这个设计真...那你读怎么处理呢?双读然后服务去合并数据吗?
    AngryPanda
        6
    AngryPanda  
       2019-07-03 07:11:10 +08:00 via Android
    没看懂你要干嘛
    wd
        7
    wd  
       2019-07-03 07:50:38 +08:00 via iPhone
    双主一般同时只会有一个在写,那么为什么还需要考虑 id 冲突的问题呢?因为切换哪个可写的时候,可能会有同时写的情况(这个看你切换的逻辑)所以会预防性的把 id 这么设计。那么解法就是,要么保证切换的时候确认旧的已经停止写入再切,要不就是不要用外键之类的数据库约束,你程序自己保证。mysql 双主带来的脏数据问题多了,你要保证服务在线,那就很难保证数据完整性。
    paranoiagu
        8
    paranoiagu  
       2019-07-03 08:16:35 +08:00 via Android
    同时写入死锁肯定会很多。读写分离吧。
    cubecube
        9
    cubecube  
       2019-07-03 08:28:28 +08:00 via Android
    你这么做就很反直觉了。非要这么做,为啥用户在 b 上还有一个?就是 1 不就是唯一的了么
    coolwind1981
        10
    coolwind1981  
       2019-07-03 08:45:02 +08:00
    同步设定好之前,A 和 B 的数据是要一样的(比如把 A 的数据导入 B,或把 B 的数据导入 A ),然后再开始同步
    这样用户的 ID 在 A,B 中都是一样的,不会出现在一个中 ID 是 1,B 中 ID 是 2,就不会有您说的这个问题
    coolwind1981
        11
    coolwind1981  
       2019-07-03 08:47:05 +08:00
    A 新增一個用戶,ID 是 1,同步到 B 中,ID 也是 1,不會變成 2 的;
    B 新增一個用戶,ID 是 2,同步到 A 中,ID 也是 2,不會變成 3 的;
    smartychaos
        12
    smartychaos  
       2019-07-03 09:28:39 +08:00
    技术上讲 binlog 使用 row 模式,而不是在从库上执行一遍 statement 就好了吧。
    cyssxt
        13
    cyssxt  
       2019-07-03 09:36:19 +08:00 via iPhone
    分库不像分库 复制不像复制
    sujin190
        14
    sujin190  
       2019-07-03 10:00:25 +08:00
    @littlewing #4
    @wd #7 人家这样,估计要的就是双写入吧
    isbarton
        15
    isbarton  
       2019-07-03 10:11:55 +08:00
    不是 DBA,但是了解一些 msyql。
    这个是比较野的多主方案吧。看样子是两个节点互为主从。这么用的比较少。

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

    多主一般强调一致性,同时在所有的节点做写入操作。保证所有的节点数据一致。
    缺点明显,集群性能估计是单节点的 60%左右。如果有个节点性能差,会直接拖后腿。
    dot2017
        16
    dot2017  
       2019-07-03 10:17:18 +08:00
    为什么要用双主呢,不安全啊
    demos
        17
    demos  
    OP
       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
        18
    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
        19
    demos  
    OP
       2019-07-03 15:17:28 +08:00
    @lannoooW 用的 mixed,看了下说明,mixed 模式默认是发 statement,只有特殊情况才会用 row
    也就是默认还发的 sql。
    请问双主是要改成的是 row 模式吗,搜了几编双主的配置文章,大部分都没提到这点。
    wd
        20
    wd  
       2019-07-03 16:38:00 +08:00 via iPhone
    @sujin190 #14 双写还互相同步的话 估计数据很快就一团糟了
    sujin190
        21
    sujin190  
       2019-07-03 17:20:38 +08:00
    @lannoooW #18
    @demos #19 mysql 主从还有发 sql 的,难道不应该主库提交之后把数据发给从库么?否则像 update a set updated_at=now() 这样的语句岂不是主从得到的时间不一致了,怎么可能啊
    lannoooW
        22
    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
        23
    lazyfighter  
       2019-07-03 17:52:20 +08:00
    看的我一脸懵逼,不是复制吗咋 id 还变了呢
    lazyfighter
        24
    lazyfighter  
       2019-07-03 18:14:36 +08:00
    @lazyfighter 我孤陋寡闻了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5514 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 37ms · UTC 07:51 · PVG 15:51 · LAX 23:51 · JFK 02:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.