Production environment MySQL Master synchronization primary key conflict handling

Received SMS alarm, two databases are reported slave synchronization failed, first explain the environment, architecture: Lvs+keepalived+amoeba+mysql, master copy, single write,

Main 1:192.168.0.223 (write)

Main 2:192.168.0.230

Okay, let's show slave status \g The exact error of the sync failure.

Login to main 2 library view:

Mysql> Show Slave status \g *************************** 1.
Row *************************** slave_io_state:master_host:192.168.0.223 Master_user:slave master_port:13204 Connect_retry:60 master_log_file:mysql-bin.000009 read_master_log_pos:50419 Relay_log_file: mysqld-relay-bin.000014 relay_log_pos:34626 relay_master_log_file:mysql-bin.000009 Slave_io_running:no Slave_SQL_ Running:no Replicate_do_db:replicate_ignore_db:mysql,information_schema,performance_schema,test,mysql,
Information_schema,performance_schema,test Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table: replicate_wild_ignore_table:last_errno:1062 last_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 ', ', ', ' skip_counter:0 exec_master_log_pos:34480 relay_log_space:51171 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 ' 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 ', ', ', ' Replicate_ignore_server_ids:master_ Server_id:2 1 row in Set (0.00 sec)

Ni-ma, bitter is the primary key conflict, first look at the structure of this 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 |
| +-------------+-----------------+------+-----+---------+----------------+ rows in Set (0.00 sec)

Presumably we already know that the problem is so produced, here I would like to say, some people may not understand ha, looking back at the previous architecture, the cause of this problem is the main 1 network jitter, resulting in the amoeba write cut to the main 2, the main 1 of the network is good, write and cut back to the main 1, because the primary key ID is self, So here's the problem, I'll give you an example:

The beginning is to write the main 1, has written 6 data (Id=1, 2, 3, 4, 5, 6, suddenly main 1 network jitter, began in main 2 wrote three (id=7, 8, 9), the main 1 of the network has been restored, write again in the main 1 (id=7, 8, 9, ten 、。。。。 At this time, the main 1 to id=7, 8, 9, 10 ... Copy of the data to the main 2, the main 2 to the id=7, 8, 93 data Copy to the main 1, this is not stupid force it?