1062错误----主键冲突，出现这种情况就是从库出现插入操作，主库又重新来了一遍，iothread没问题，sqlthread出错
解决方案：
|
1
2
3
4
5
|
mysql> stop slave;
mysql>
set
global
sql_slave_skip_counter=1;
mysql> start slave;
|
故障模拟：
从库如果误插入
|
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
mysql>
insert
into
helei(text)
values
(
'iii'
);
Query OK, 1 row affected (0.28 sec)
mysql>
select
*
from
helei;
+
----+------+
| id | text |
+
----+------+
| 1 | aa
|
| 2 | bb
|
| 3 | cc
|
| 4 | ee
|
| 5 | ff
|
| 6 | gg
|
| 7 | hh
|
| 8 | iii
|
+
----+------+
8
rows
in
set
(0.00
sec)
mysql> show slave
status\G
***************************
1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 192.168.1.250
Master_User: mysync
Master_Port: 2503306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 2509
Relay_Log_File:
HE1-relay-bin.000005
Relay_Log_Pos: 480
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这时候从库是正常的，但由于从库私自插入数据，已经和主库不一致，如果这时主库也插入相同的数据
主库此时没有8,iii这条记录
mysql>
select
*
from
helei;
+
----+------+
| id | text |
+
----+------+
| 1 | aa
|
| 2 | bb
|
| 3 | cc
|
| 4 | ee
|
| 5 | ff
|
| 6 | gg
|
| 7 | hh
|
+
----+------+
7
rows
in
set
(0.00
sec)
mysql>show slave status\G
***************************
1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 192.168.1.250
Master_User: mysync
Master_Port: 2503306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 2709
Relay_Log_File:
HE1-relay-bin.000005
Relay_Log_Pos: 480
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running:
No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Could
not
execute
Write_rows event
on
table
test.helei;
Duplicate entry
'8'
for
key
'PRIMARY'
, Error_code: 1062; handler error
HA_ERR_FOUND_DUPP_KEY; the event
's master log mysql-bin.000005, end_log_pos
2678
Skip_Counter: 0
Exec_Master_Log_Pos: 2509
Relay_Log_Space: 851
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: Could not
execute Write_rows event on table test.helei; Duplicate entry '
8
' for key
'
PRIMARY
', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event'
s
master log mysql-bin.000005, end_log_pos 2678
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2503306
Master_UUID:
f7c96432-f665-11e5-943f-000c2967a454
Master_Info_File:
/data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 160331 09:12:02
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row
in
set
(0.00 sec)
|
1062错误----主键冲突，出现这种情况就是从库出现插入操作，主库又重新来了一遍，iothread没问题，sqlthread出错
出现这样的问题，先停从库，set跳过这条事务
HE1从库操作
|
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
mysql> stop slave;
Query OK, 0
rows
affected (0.26 sec)
mysql>
set
global
sql_slave_skip_counter=1;（这里写10也是一样的,最好不用这种方法，用pt-slave-restart，无需手动stop/start slave）
Query OK, 0
rows
affected (0.30 sec)
mysql> start slave;
Query OK, 0
rows
affected (0.04 sec)
mysql> show slave status\G
***************************
1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 192.168.1.250
Master_User: mysync
Master_Port: 2503306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 2909
Relay_Log_File:
HE1-relay-bin.000006
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2909
Relay_Log_Space: 1214
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: 0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2503306
Master_UUID:
f7c96432-f665-11e5-943f-000c2967a454
Master_Info_File:
/data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State: Slave has
read
all
relay log; waiting
for
the slave I/O thread
to
update
it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row
in
set
(0.00
sec)
|
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1845114，如需转载请自行联系原作者
