生產環境mysql主主同步主鍵衝突處理

收到簡訊警示，兩台資料庫都報slave同步失敗了，先說明一下環境，架構：lvs+keepalived+amoeba+mysql，主主複製，單台寫入，

主1:192.168.0.223寫）主2:192.168.0.230好吧，先show slave status \G看一下同步失敗的具體報錯吧登入主2庫查看：

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)

尼瑪，苦逼的又是主鍵衝突，先查看一下這張表的結構：

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)

想必大家已經知道問題是這麼產生的了，這裡我再大體的說一下，可能有些人還不明白哈，回頭看前面的架構，引起 這個問題的原因是主1的網路抖動，導致amoeba把寫切到了主2，主1的網路好了，寫又切回了主1，由於主鍵ID是自曾的，所以就出現了這個問題，我舉個例子：開始是寫主1的，已經寫6條資料id=1、2、3、4、5、6），突然主1網路抖動，開始在主2寫了三條id=7、8、9），主1的網路又恢複了，寫又在主1上了id=7、8、9、10、。。。。），這時，主1要把id=7、8、9、10.。。。。的資料複製給主2，主2 要把id=7、8、9三條資料複製給主1，這不就傻逼了嗎？處理的過程：1、在兩個庫上stop slave；2、在主2上執行select * from kn_chongzhi where id>=1329544\G 查看在主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、在主2上delete from kn_chongzhi where id>=1329544;  並設定自曾ID從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、主2上slave start，show slave  status \G，發現主2同步主1已經ok了；5、在主2上show master  status \G，擷取binlog檔案名稱和Position點，在主1上重新change master6、把上面三條資料儲存好，發給程式猿手到錄入主1，PS：當然，如果我按一下設定，肯定不會出現這個問題，如果業務有要求，ID必須連續，那就不能設定這兩個參數了：

主1：auto-increment-increment=2auto-increment-offset=1主2：auto-increment-increment=2auto-increment-offset=2

 

本文出自 “屌絲營運男” 部落格，請務必保留此出處http://navyaijm.blog.51cto.com/4647068/1241728