主主复制在相同行ID后失败

Question

你好，我有安装主-母版mysql复制。

Master A = Localsystem
Master B = Server

它很好用。但是当A大师无法从B大师那里到达时

而我复制的表有Id字段，直到- 233。

现在，在同一表中的两个主表上插入了一行。

两个人的下一个身份证都在桌子上。

当主A可以到达主B时，复制在两端都失败了。

**

on Master A;
show master status \G;

**

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 13.235.146.234
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 33310336
               Relay_Log_File: ded18908-1-relay-bin.000003
                Relay_Log_Pos: 4445481
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: school_testing.ai_student_attendance,school_testing.ai_staff_attendance
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '124', 'sasas', 'sasas', 'sasas')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 20766001
              Relay_Log_Space: 22735128
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '124', 'sasas', 'sasas', 'sasas')'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3
1 row in set (0.00 sec)

ERROR: 
No query specified

**

Master - B 
show slave status \G;

**

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.21.11.142
                  Master_User: replica
                  Master_Port: 22331
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 111212
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 43815
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: school_testing.ai_student_attendance,school_testing.ai_staff_attendance
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '125', 'dsdsdsa', 'dsada', 'asdasdadasdadada')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 83284
              Relay_Log_Space: 72201
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '234' for key 'PRIMARY'' on query. Default database: 'school_testing'. Query: 'INSERT INTO `school_testing`.`ai_student_attendance` (`ids`, `student_id`, `atten_list`, `atten_cont`, `month`) VALUES (NULL, '125', 'dsdsdsa', 'dsada', 'asdasdadasdadada')'
1 row in set (0.00 sec)

ERROR: 
No query specified

我不知道如何纠正这个问题。我需要专家的帮助。

这是个罕见的案例。但是当我面对这个问题时，我的复制失败了。

Answer 1

您需要做的是在每个主服务器上使用不同的ID偏移：

在你的my.cnf文件中-

关于师父1：

auto_increment_increment=2 
auto_increment_offset=1 

关于大师2：

auto_increment_increment=2 
auto_increment_offset=2 

所以每个人产生一个不同的id集合。

例如：

主1= 1，3，5

大师2= 2，4，6

通过这种方式，您不应该获得重复的条目错误(只要您使用的是auto_increment作为您的ID)，而不是手动输入。