Production Environment mysql master/Master synchronization primary key conflict handling

When you receive an SMS alert, both databases report that slave synchronization has failed. First, describe the environment, architecture: lvs + keepalived + amoeba + mysql, master replication, single write, Master 1: 192.168.0.223 (write) Master 2: 192.168

When you receive an SMS alert, both databases report that slave synchronization has failed. First, describe the environment, architecture: lvs + keepalived + amoeba + mysql, master replication, single write, Master 1: 192.168.0.223 (write) Master 2: 192.168

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

Okay. First show slave status \ G to check the synchronization failure error.

Log on to the master database 2 to view the information:

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: Connector: mysqld-relay-bin.000014Relay_Log_Pos: Connector: mysql, information_schema, performance_schema, test, mysql, information_schema, performance_schema, role: Replicate_Ignore_Table: Connector: last_Errno: 1062Last_Error: Error 'duplicate entry '000000' for key 'primary' 'on query. default database: 'data '. query: 'insert into kn_chongzhi (orderid, aa, buyNum, state, type, create_time, fac, cc, flag) values (values, 1372757425, 'sj ', '30. 27 ', '30', 100) 'skip _ Counter: Clerk: 71until_condition: Clerk: Until_Log_Pos: Clerk: Master_SSL_Cert: Clerk: Master_SSL_Key: Seconds_Behind_Master: Clerk: noLast_IO_Errno: 0Last_IO_Error: Last_ SQL _Errno: 1062Last_ SQL _Error: Error 'duplicate entry '000000' for key 'primary' on query. default database: 'data '. query: 'insert into kn_chongzhi (orderid, aa, buyNum, state, type, create_time, fac, cc, flag) values (values, 1372757425, 'sj ', '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; + ------------- + bytes + ------ + ----- + --------- + ---------------- + | 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 (0.00) | YES | 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:

At the beginning, the primary 1 is written, and six data records (id = 1, 2, 3, 4, 5, and 6) have been written. Suddenly, the primary 1 network jitters, three logs (id = 7, 8, and 9) are written in master 2, and the network of Master 1 is restored, write again on Master 1 (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. Execute select * from kn_chongzhi where id> = 1329544 \ G on Master 2 (view the data records written on Master 2)

Mysql> select * from kn_chongzhi where id> = 1329544 \ G *************************** 3661. row ************************* id: 1329545aa: 20130702213504529562 bizOfferId: DK201307021139565210number: 13991056094cc: 30.00fac: 30.22 buyNum: 1 state: 2 type: SJcreate_time: 13727721_update_time: 1372772474 flag: 100 *************************** 3662. row ************************* id: 1329546aa: 20130702213506629648 bizOfferId: DK201307021139588209number: 15347391791cc: 30.00fac: 30.17 buyNum: 1 state: 0 type: SJcreate_time: 13727721_update_time: 0 flag: 100 *************************** 3663. row ************************* id: 1329547aa: 20130702213516595293 bizOfferId: dk20130702114178209number: 13615611693cc: 100.00fac: 99.85 buyNum: 1 state: 2 type: SJcreate_time: 1372772116update_time: 1372772315 flag: 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 has been synchronized to master 1;

5. show master status \ G on master 2, get the binlog file name and Position, and change master again on master 1.

6. Save the above three data and send it to the programmer to master 1,

PS: Of course, if I click set, this problem will certainly not occur. If the business requires that the ID must be continuous, without the record filing space, 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 is from the "diaosi O & M male" blog. Please keep this source

, Hong Kong Space