生産環境mysql主主同步主鍵沖突處理

收到短信報警，兩台資料庫都報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

<code>mysql&gt; show slave status \g</code>

<code>*************************** 1. row ***************************</code>

<code>slave_io_state:</code>

<code>master_host: 192.168.0.223</code>

<code>master_user: slave</code>

<code>master_port: 13204</code>

<code>connect_retry: 60</code>

<code>master_log_file: mysql-bin.000009</code>

<code>read_master_log_pos: 50419</code>

<code>relay_log_file: mysqld-relay-bin.000014</code>

<code>relay_log_pos: 34626</code>

<code>relay_master_log_file: mysql-bin.000009</code>

<code>slave_io_running: no</code>

<code>slave_sql_running: no</code>

<code>replicate_do_db:</code>

<code>replicate_ignore_db: mysql,information_schema,performance_schema,</code><code>test</code><code>,mysql,information_schema,performance_schema,</code><code>test</code>

<code>replicate_do_table:</code>

<code>replicate_ignore_table:</code>

<code>replicate_wild_do_table:</code>

<code>replicate_wild_ignore_table:</code>

<code>last_errno: 1062</code>

<code>last_error: error </code><code>'duplicate entry '</code><code>1329544</code><code>' for key '</code><code>primary</code><code>''</code> <code>on query. default database: </code><code>'data'</code><code>. query: 'insert into kn_chongzhi(orderid,aa,buynum,state,</code><code>type</code><code>,create_time,fac，cc,flag)</code>

<code>values(20130702173025036581,15935779926,1,0,</code><code>'sj'</code><code>,1372757425,</code><code>'30.27'</code><code>,</code><code>'30'</code><code>,100)'</code>

<code>skip_counter: 0</code>

<code>exec_master_log_pos: 34480</code>

<code>relay_log_space: 51171</code>

<code>until_condition: none</code>

<code>until_log_file:</code>

<code>until_log_pos: 0</code>

<code>master_ssl_allowed: no</code>

<code>master_ssl_ca_file:</code>

<code>master_ssl_ca_path:</code>

<code>master_ssl_cert:</code>

<code>master_ssl_cipher:</code>

<code>master_ssl_key:</code>

<code>seconds_behind_master: null</code>

<code>master_ssl_verify_server_cert: no</code>

<code>last_io_errno: 0</code>

<code>last_io_error:</code>

<code>last_sql_errno: 1062</code>

<code>last_sql_error: error </code><code>'duplicate entry '</code><code>1329544</code><code>' for key '</code><code>primary</code><code>''</code> <code>on query. default database: </code><code>'data'</code><code>. query: 'insert into kn_chongzhi(orderid,aa,buynum,state,</code><code>type</code><code>,create_time,fac,cc,flag)</code>

<code>replicate_ignore_server_ids:</code>

<code>master_server_id: 2</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>

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

<code>mysql&gt; desc  kn_chongzhi;</code>

<code>+-------------+-----------------+------+-----+---------+----------------+</code>

<code>| field       | type            | null | key | default | extra          |</code>

<code>| </code><code>id</code>          <code>| int(10)         | no   | pri | null    | auto_increment |</code>

<code>| aa    | varchar(32)     | no   | mul | null    |                |</code>

<code>| bizofferid  | varchar(32)     | no   |     | null    |                |</code>

<code>| number      | varchar(20)     | no   | mul | null    |                |</code>

<code>| cc       | float(10,2)     | no   |     | null    |                |</code>

<code>| fac   | float(10,2)     | yes  |     | 0.00    |                |</code>

<code>| buynum      | int(10)         | no   |     | null    |                |</code>

<code>| state       | tinyint(4)      | no   |     | 0       |                |</code>

<code>| </code><code>type</code>        <code>| enum(</code><code>'sj'</code><code>,</code><code>'qb'</code><code>) | no   |     | sj      |                |</code>

<code>| create_time | int(11)         | no   |     | null    |                |</code>

<code>| update_time | int(11)         | no   |     | null    |                |</code>

<code>| flag        | int(10)         | no   |     | 0       |                |</code>

<code>12 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>

想必大家已經知道問題是這麼産生的了，這裡我再大體的說一下，可能有些人還不明白哈，回頭看前面的架構，引起 這個問題的原因是主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&gt;=1329544\g （檢視在主2上寫了幾條資料）

<code>mysql&gt; </code><code>select</code> <code>* from kn_chongzhi where </code><code>id</code><code>&gt;=1329544\g</code>

<code>*************************** 3661. row ***************************</code>

<code>id</code><code>: 1329545</code>

<code>aa: 20130702213504529562</code>

<code>bizofferid: dk201307021139565210</code>

<code>number: 13991056094</code>

<code>cc: 30.00</code>

<code>fac: 30.22</code>

<code>buynum: 1</code>

<code>state: 2</code>

<code>type</code><code>: sj</code>

<code>create_time: 1372772104</code>

<code>update_time: 1372772474</code>

<code>flag: 100</code>

<code>*************************** 3662. row ***************************</code>

<code>id</code><code>: 1329546</code>

<code>aa: 20130702213506629648</code>

<code>bizofferid: dk201307021139588209</code>

<code>number: 15511391791</code>

<code>fac: 30.17</code>

<code>state: 0</code>

<code>create_time: 1372772106</code>

<code>update_time: 0</code>

<code>*************************** 3663. row ***************************</code>

<code>id</code><code>: 1329547</code>

<code>aa: 20130702213516595293</code>

<code>bizofferid: dk201307021139758209</code>

<code>number: 13615611693</code>

<code>cc: 100.00</code>

<code>fac: 99.85</code>

<code>create_time: 1372772116</code>

<code>update_time: 1372772315</code>

<code>flag: 101</code>

3、在主2上delete from kn_chongzhi where id&gt;=1329544;  并設定自曾id從1329545開始

<code>mysql&gt; delete from kn_chongzhi where </code><code>id</code><code>&gt;=1329544;</code>

<code>query ok, 0 rows affected (0.00 sec)</code>

<code>mysql&gt; alter table kn_chongzhi auto_increment=1329545;</code>

<code>query ok, 0 rows affected (0.15 sec)</code>

<code>records: 0  duplicates: 0  warnings: 0</code>

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必須連續，那就不能設定這兩個參數了：

<code>主1：</code>

<code>auto-increment-increment=2</code>

<code>auto-increment-offset=1</code>

<code>主2：</code>

<code>auto-increment-offset=2</code>