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

shy丶gril發表於2016-05-25
MySql
原創作品，允許轉載，轉載時請務必以超連結形式標明文章 原始出處 、作者資訊和本宣告。否則將追究法律責任。http://navyaijm.blog.51cto.com/4647068/1241728

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

主1:192.168.0.223（寫）
主2:192.168.0.230
好吧，先show slave status G看一下同步失敗的具體報錯吧
登入主2庫檢視：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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`,`30`,100)`
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`,`30`,100)`
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>=1329544G （檢視在主2上寫了幾條資料）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
mysql> select * from kn_chongzhi where id>=1329544G
*************************** 3661. row ***************************
id: 1329545
aa: 20130702213504529562
bizOfferId: DK201307021139565210
number: 13991056094
cc: 30.00
fac: 30.22
buyNum: 1
state: 2
type: SJ
create_time: 1372772104
update_time: 1372772474
flag: 100
*************************** 3662. row ***************************
id: 1329546
aa: 20130702213506629648
bizOfferId: DK201307021139588209
number: 15511391791
cc: 30.00
fac: 30.17
buyNum: 1
state: 0
type: SJ
create_time: 1372772106
update_time: 0
flag: 100
*************************** 3663. row ***************************
id: 1329547
aa: 20130702213516595293
bizOfferId: DK201307021139758209
number: 13615611693
cc: 100.00
fac: 99.85
buyNum: 1
state: 2
type: SJ
create_time: 1372772116
update_time: 1372772315
flag: 101

 

3、在主2上delete from kn_chongzhi where id>=1329544;  並設定自曾ID從1329545開始
1
2
3
4
5
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 master
6、把上面三條資料儲存好，發給程式猿手到錄入主1，
PS：當然，如果我按一下設定，肯定不會出現這個問題，如果業務有要求，ID必須連續，那就不能設定這兩個引數了：
1
2
3
4
5
6
主1：
auto-increment-increment=2
auto-increment-offset=1
主2：
auto-increment-increment=2
auto-increment-offset=2

 

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


相關文章