Production Environment mysql master/Master synchronization primary key conflict handling

When you receive an SMS alert, both databases report slave synchronization failure. First, describe the environment, architecture: lvs + keepalived + amoeba + mysql, master-master replication, and single write,

Master 1: 192.168.0.223 write) Master 2: 192.168.0.230 OK, first show slave status \ G to see the specific error of synchronization failure. log on to the master 2 database to view:

mysql> show slave status \G*************************** 1. row ***************************Slave_IO_State:Master_Host: 192.168.0.223Master_User: slaveMaster_Port: 13204Connect_Retry: 60Master_Log_File: mysql-bin.000009Read_Master_Log_Pos: 50419Relay_Log_File: mysqld-relay-bin.000014Relay_Log_Pos: 34626Relay_Master_Log_File: mysql-bin.000009Slave_IO_Running: NoSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB: mysql,information_schema,performance_schema,test,mysql,information_schema,performance_schema,testReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1062Last_Error: Error 'Duplicate entry '1329544' for key 'PRIMARY'' on query. Default database: 'data'. Query: 'insert into kn_chongzhi(orderid,aa,buyNum,state,type,create_time,fac，cc,flag)values(20130702173025036581,15935779926,1,0,'SJ',1372757425,'30.27','30',100)'Skip_Counter: 0Exec_Master_Log_Pos: 34480Relay_Log_Space: 51171Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1062Last_SQL_Error: Error 'Duplicate entry '1329544' for key 'PRIMARY'' on query. Default database: 'data'. Query: 'insert into kn_chongzhi(orderid,aa,buyNum,state,type,create_time,fac,cc,flag)values(20130702173025036581,15935779926,1,0,'SJ',1372757425,'30.27','30',100)'Replicate_Ignore_Server_Ids:Master_Server_Id: 21 row in set (0.00 sec)

Nima: The primary key conflict. First, check the structure of the table:

mysql> desc  kn_chongzhi;+-------------+-----------------+------+-----+---------+----------------+| Field       | Type            | Null | Key | Default | Extra          |+-------------+-----------------+------+-----+---------+----------------+| id          | int(10)         | NO   | PRI | NULL    | auto_increment || aa    | varchar(32)     | NO   | MUL | NULL    |                || bizOfferId  | varchar(32)     | NO   |     | NULL    |                || number      | varchar(20)     | NO   | MUL | NULL    |                || cc       | float(10,2)     | NO   |     | NULL    |                || fac   | float(10,2)     | YES  |     | 0.00    |                || buyNum      | int(10)         | NO   |     | NULL    |                || state       | tinyint(4)      | NO   |     | 0       |                || type        | enum('SJ','QB') | NO   |     | SJ      |                || create_time | int(11)         | NO   |     | NULL    |                || update_time | int(11)         | NO   |     | NULL    |                || flag        | int(10)         | NO   |     | 0       |                |+-------------+-----------------+------+-----+---------+----------------+12 rows in set (0.00 sec)

I think you already know that the problem has occurred. Here I will give you a general explanation. Some people may not understand it yet. Let's look back at the previous architecture, the cause of this problem is that the network jitter of Master 1 Causes amoeba to switch the write to master 2. The network of Master 1 is ready, and the write is switched back to master 1, because the primary key ID is from the past, this problem occurs. For example, the primary key ID is written to master 1, 6 data IDs = 1, 2, 3, 4, 5, and 6 have been written. Suddenly, the primary 1 network jitters, three IDs = 7, 8, and 9 are written in master 2, and the network of Master 1 is restored, write again on Master 1 with id = 7, 8, 9, 10 ,....), In this case, id = 7, 8, 9, 10 ..... To master 2, Master 2 needs to copy id = 7, 8, and 9 to master 1. Isn't that silly? Processing Process: 1. stop slave on two databases; 2. Run select * from kn_chongzhi where id> = 1329544 \ G on Master 2 to check the number of data records written on Master 2)

mysql> select * from kn_chongzhi where id>=1329544\G*************************** 3661. row ***************************id: 1329545aa: 20130702213504529562bizOfferId: DK201307021139565210number: 13991056094cc: 30.00fac: 30.22buyNum: 1state: 2type: SJcreate_time: 1372772104update_time: 1372772474flag: 100*************************** 3662. row ***************************id: 1329546aa: 20130702213506629648bizOfferId: DK201307021139588209number: 15511391791cc: 30.00fac: 30.17buyNum: 1state: 0type: SJcreate_time: 1372772106update_time: 0flag: 100*************************** 3663. row ***************************id: 1329547aa: 20130702213516595293bizOfferId: DK201307021139758209number: 13615611693cc: 100.00fac: 99.85buyNum: 1state: 2type: SJcreate_time: 1372772116update_time: 1372772315flag: 101

 

3. delete from kn_chongzhi where id> = 1329544 on Master 2 and set the ID to start from 1329545.

mysql> delete from kn_chongzhi where id>=1329544;Query OK, 0 rows affected (0.00 sec)mysql> alter table kn_chongzhi auto_increment=1329545;Query OK, 0 rows affected (0.15 sec)Records: 0  Duplicates: 0  Warnings: 0

4. slave start and show slave status \ G on master 2, and it is found that master 2 synchronizes master 1 has been OK; 5. show master status \ G on master 2, get the binlog file name and Position, change master6 again on Master 1, save the above three data, and send it to the programmer to master 1, PS: of course, if I click set, this problem will not occur. If the business requires that the ID be continuous, then these two parameters cannot be set:

Master 1: auto-increment = 2auto-increment-offset = 1 master 2: auto-increment = 2auto-increment-offset = 2

 

This article from the "diaosi O & M male" blog, please be sure to keep this source http://navyaijm.blog.51cto.com/4647068/1241728