解决MySQL(1032、1062、1864) 主从错误

一、解决MySQL 1032 主从错误

Part1:写在最前 

1032错误—-现在生产库中好多数据,在从库误删了,生产库更新后找不到了,现在主从不同步了,再跳过错误也没用,因为没这条,再更新还会报错

解决方案

Part1:临时方案

1
2
3
4
5
6
7
8
9
10
11
mysql> stop slave;
Query OK, 0 rows
affected (0.00 sec)
 
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows
affected (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows
affected (0.00 sec)

Part2:永久方案

end_log_pos 有了它,根据pos值,直接就能找到,找到delete那条数据,反做(变成insert

故障模拟

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
mysql> delete from helei where id=3;
Query OK, 1 row
affected (0.29 sec)
 
mysql> select from helei;
+----+------+
| id | text |
+----+------+
|  1 | aa  
|
|  2 | bb  
|
|  4 | ee  
|
|  5 | ff  
|
|  6 | gg  
|
|  7 | hh  
|
+----+------+
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: 3711
               Relay_Log_File:
HE1-relay-bin.000007
                Relay_Log_Pos: 484
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

此时从库状态是正常的,但一旦主库对该条记录进行操作

HE3主库更新从库刚刚误删的数据

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
mysql> update helei set text='ccc' where id=3;
Query OK, 1 row
affected (0.01 sec)
Rows matched: 1  Changed: 1 
Warnings: 0
 
mysql> select from helei;
+----+------+
| id | text |
+----+------+
|  1 | aa  
|
|  2 | bb  
|
|  3 | ccc 
|
|  4 | ee  
|
|  5 | ff  
|
|  6 | gg  
|
|  7 | hh  
|
+----+------+
rows in set (0.00
sec)

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
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: 3918
               Relay_Log_File:
HE1-relay-bin.000007
                Relay_Log_Pos: 484
        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: 1032
                   Last_Error: Could not
execute Update_rows event on table test.helei; Can't find record in 'helei',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log
mysql-bin.000005, end_log_pos 3887
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3711
              Relay_Log_Space: 1626
              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: 1032
              
Last_SQL_Error: Could not execute Update_rows event on table test.helei;
Can't find record in 'helei', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 3887(这个mysql-bin.000005,end_log_pos
3887是主库的)
  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:25:02
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00
sec)

此时主从又不同步了,如果还去执行跳过错误操作,主从恢复同步,而且状态均为yes,但!这并不能解决该问题,如果主库又更新该条记录,那么还是会报相同错误,而且pos号还会变,这就导致了恢复时你不知道前一条的pos号,导致丢失数据。

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
mysql> stop slave;
Query OK, 0 rows
affected (0.00 sec)
 
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows
affected (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows
affected (0.00 sec)
 
mysql> select from helei;
+----+--------+
| id | text   |
+----+--------+
|  1 | aa    
|
|  2 | bb    
|
|  4 | ee    
|
|  5 | ff    
|
|  6 | gg    
|
|  7 | hh    
|
|  8 | helei1 |
+----+--------+
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: 4119
               Relay_Log_File:
HE1-relay-bin.000008
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

这里虽然通过跳过错误达到恢复主从同步,但如果主库又对该条记录更新

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
mysql> update helei set text='cccc' where id=3;
Query OK, 1 row
affected (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: 4328
               Relay_Log_File:
HE1-relay-bin.000008
                Relay_Log_Pos: 283
        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: 1032
                  
Last_Error: Could not execute Update_rows event on table test.helei;
Can't find record in 'helei', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 4297
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4119
              Relay_Log_Space: 1435
              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: 1032
              
Last_SQL_Error: Could not execute Update_rows event on table test.helei;
Can't find record in 'helei', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 4297
  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:33:34
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0

二、解决MySQL 1062 主从错误 // <![CDATA[
var myid = “”;
function add_flink(){
if(myid==””){

var refurlk = “http://suifu.blog.51cto.com/9167728/1845114”;

//commentSubmit(“”,refurlk);
location.href=”http://home.51cto.com/index.php?reback=”+encodeURIComponent(encodeURIComponent(refurlk));

return false;
}else{
var mtk = “d3ead2ff828d795e0bd59c6542f7d9cc”;
var url=’/mod/edit_flink.php?type=addflink&uid=9167728&flink=http://suifu.blog.51cto.com&mtk=’+mtk;
var ajax = InitAjax1();
ajax.open(“GET”, url, true);
ajax.onreadystatechange = function() {
if (ajax.readyState == 4 && ajax.status == 200) {
/*if(myid == 5290427){
alert(ajax.responseText);
}*/
if(ajax.responseText==””){
alert(“添加成功。”);
}
if(ajax.responseText==”1″){
alert(“链接指向自己。”);
}
if(ajax.responseText==”2″){
alert(“友情链接已存在。”)
}
if(ajax.responseText==”4″){
alert(“验证已过期。”)
}
}
}
ajax.send(null);
}

}
function sendmessage(){

var refurlk = “http://suifu.blog.51cto.com/9167728/1845114”;

if(myid){
return true;
}else{
commentSubmit(“”,refurlk);
return false;
}
}
function copylink(ourl){
if(!ourl){
var clipBoardContent = “http://suifu.blog.51cto.com”;
}else{
var clipBoardContent = ourl;
}
window.clipboardData.setData(“Text”,clipBoardContent);
alert(“复制成功!”);
return false;
}
function correctPNG() {
if (document.getElementById(‘blog_touxian’))
{
var img = document.getElementById(‘blog_touxian’);
var imgName = img.src.toUpperCase()
var imgID = (img.id) ? “id='” + img.id + “‘ ” : “”
var imgClass = (img.className) ? “class='” + img.className + “‘ ” : “”
var imgTitle = (img.title) ? “title='” + img.title + “‘ ” : “title='” + img.alt + “‘ ”
var imgStyle = “display:inline-block;” + img.style.cssText
if (img.align == “left”) imgStyle = “float:left;” + imgStyle
if (img.align == “right”) imgStyle = “float:right;” + imgStyle
if (img.parentElement.href) imgStyle = “cursor:hand;” + imgStyle
var strNewHTML = “” ;
img.outerHTML = strNewHTML;
}
}
//window.attachEvent(“onload”, correctPNG);
window.onload=correctPNG;

function copy(){
var text=document.getElementById(“txtUser”).value;
if(copy2Clipboard(text)!=false){
alert(“复制成功了! “);
}
}
function copy2Clipboard(txt){
if(window.clipboardData){
window.clipboardData.clearData();
window.clipboardData.setData(“Text”,txt);
}else if(navigator.userAgent.indexOf(“Opera”)!=-1){
window.location=txt;
}else if(window.netscape){
try{
netscape.security.PrivilegeManager.enablePrivilege(“UniversalXPConnect”);
}
catch(e){
alert(“您使用的浏览器不支持此复制功能,请使用Ctrl+C或鼠标右键。”);
return false;
}
var clip=Components.classes[‘@mozilla.org/widget/clipboard;1’].createInstance(Components.interfaces.nsIClipboard);
if(!clip)return;
var trans=Components.classes[‘@mozilla.org/widget/transferable;1’].createInstance(Components.interfaces.nsITransferable);
if(!trans)return;
trans.addDataFlavor(‘text/unicode’);
var str=new Object();
var len=new Object();
var str=Components.classes[“@mozilla.org/supports-string;1”].createInstance(Components.interfaces.nsISupportsString);
var copytext=txt;str.data=copytext;
trans.setTransferData(“text/unicode”,str,copytext.length*2);
var clipid=Components.interfaces.nsIClipboard;
if(!clip)return false;
clip.setData(trans,null,clipid.kGlobalClipboard);
return true;
}
}

function mod_close(){
document.getElementById(‘mod_tg’).style.display=”none”;
}

function match_invite(uid) {
var url=’/mod/match_invite.php’;
var ajax = InitAjax();
var re = ‘uid=’ + uid;
ajax.open(“POST”, url, true);
ajax.setRequestHeader(“Content-Type”,”application/x-www-form-urlencoded”);
ajax.send(re);
ajax.onreadystatechange = function() {
if (ajax.readyState == 4 && ajax.status == 200) {
if(ajax.responseText == 1){
alert(“邀请信息已经发送成功。”);
} else if(ajax.responseText==”-1″){
alert(“邀请失败,请稍候再试。”);
} else if(ajax.responseText==”2″){
alert(“该用户已经报名了。”)
}
}
}
}
// ]]>

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 
|
+----+------+
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  
|
+----+------+
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)
三、解决MySQL 1864 主从错误 // <![CDATA[
var myid = “”;
function add_flink(){
if(myid==””){var refurlk = “http://suifu.blog.51cto.com/9167728/1841764”;

//commentSubmit(“”,refurlk);
location.href=”http://home.51cto.com/index.php?reback=”+encodeURIComponent(encodeURIComponent(refurlk));

return false;
}else{
var mtk = “d3ead2ff828d795e0bd59c6542f7d9cc”;
var url=’/mod/edit_flink.php?type=addflink&uid=9167728&flink=http://suifu.blog.51cto.com&mtk=’+mtk;
var ajax = InitAjax1();
ajax.open(“GET”, url, true);
ajax.onreadystatechange = function() {
if (ajax.readyState == 4 && ajax.status == 200) {
/*if(myid == 5290427){
alert(ajax.responseText);
}*/
if(ajax.responseText==””){
alert(“添加成功。”);
}
if(ajax.responseText==”1″){
alert(“链接指向自己。”);
}
if(ajax.responseText==”2″){
alert(“友情链接已存在。”)
}
if(ajax.responseText==”4″){
alert(“验证已过期。”)
}
}
}
ajax.send(null);
}

}
function sendmessage(){

var refurlk = “http://suifu.blog.51cto.com/9167728/1841764”;

if(myid){
return true;
}else{
commentSubmit(“”,refurlk);
return false;
}
}
function copylink(ourl){
if(!ourl){
var clipBoardContent = “http://suifu.blog.51cto.com”;
}else{
var clipBoardContent = ourl;
}
window.clipboardData.setData(“Text”,clipBoardContent);
alert(“复制成功!”);
return false;
}
function correctPNG() {
if (document.getElementById(‘blog_touxian’))
{
var img = document.getElementById(‘blog_touxian’);
var imgName = img.src.toUpperCase()
var imgID = (img.id) ? “id='” + img.id + “‘ ” : “”
var imgClass = (img.className) ? “class='” + img.className + “‘ ” : “”
var imgTitle = (img.title) ? “title='” + img.title + “‘ ” : “title='” + img.alt + “‘ ”
var imgStyle = “display:inline-block;” + img.style.cssText
if (img.align == “left”) imgStyle = “float:left;” + imgStyle
if (img.align == “right”) imgStyle = “float:right;” + imgStyle
if (img.parentElement.href) imgStyle = “cursor:hand;” + imgStyle
var strNewHTML = “” ;
img.outerHTML = strNewHTML;
}
}
//window.attachEvent(“onload”, correctPNG);
window.onload=correctPNG;

function copy(){
var text=document.getElementById(“txtUser”).value;
if(copy2Clipboard(text)!=false){
alert(“复制成功了! “);
}
}
function copy2Clipboard(txt){
if(window.clipboardData){
window.clipboardData.clearData();
window.clipboardData.setData(“Text”,txt);
}else if(navigator.userAgent.indexOf(“Opera”)!=-1){
window.location=txt;
}else if(window.netscape){
try{
netscape.security.PrivilegeManager.enablePrivilege(“UniversalXPConnect”);
}
catch(e){
alert(“您使用的浏览器不支持此复制功能,请使用Ctrl+C或鼠标右键。”);
return false;
}
var clip=Components.classes[‘@mozilla.org/widget/clipboard;1’].createInstance(Components.interfaces.nsIClipboard);
if(!clip)return;
var trans=Components.classes[‘@mozilla.org/widget/transferable;1’].createInstance(Components.interfaces.nsITransferable);
if(!trans)return;
trans.addDataFlavor(‘text/unicode’);
var str=new Object();
var len=new Object();
var str=Components.classes[“@mozilla.org/supports-string;1”].createInstance(Components.interfaces.nsISupportsString);
var copytext=txt;str.data=copytext;
trans.setTransferData(“text/unicode”,str,copytext.length*2);
var clipid=Components.interfaces.nsIClipboard;
if(!clip)return false;
clip.setData(trans,null,clipid.kGlobalClipboard);
return true;
}
}

function mod_close(){
document.getElementById(‘mod_tg’).style.display=”none”;
}

function match_invite(uid) {
var url=’/mod/match_invite.php’;
var ajax = InitAjax();
var re = ‘uid=’ + uid;
ajax.open(“POST”, url, true);
ajax.setRequestHeader(“Content-Type”,”application/x-www-form-urlencoded”);
ajax.send(re);
ajax.onreadystatechange = function() {
if (ajax.readyState == 4 && ajax.status == 200) {
if(ajax.responseText == 1){
alert(“邀请信息已经发送成功。”);
} else if(ajax.responseText==”-1″){
alert(“邀请失败,请稍候再试。”);
} else if(ajax.responseText==”2″){
alert(“该用户已经报名了。”)
}
}
}
}
// ]]>

wKiom1e87-aQ0QXXAAAPkNxB-7Q330.png

    从字面意思看了一下是因为slave_pending_jobs_size_max默认值为1677721616MB),但是slave接收到的slave_pending_jobs_size_max1708545317M);

解决方

从库执行如下SQL

1
2
3
mysql>stop slave;
mysql>set global slave_pending_jobs_size_max=20000000;
mysql> start slave;

#在多线程复制时,在队列中Pending的事件所占用的最大内存,默认为16M,如果内存富余,或者延迟较大时,可以适当调大;注意这个值要比主库的max_allowed_packet大

故障模拟:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
从库设置
mysql> set global slave_parallel_workers=4;
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
|slave_parallel_workers | 4     |
+------------------------+-------+
1 row in set (0.00sec)
 
mysql>set global slave_pending_jobs_size_max=1024;
mysql> show variables like 'slave_pend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
|slave_pending_jobs_size_max | 1024  |
+-----------------------------+-------+
1 row in set (0.00sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
主库操作:
mysql> update erp_mkpf set usnam='测试1864错误';
ERROR 1197 (HY000):Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage; increase this mysqld variable and try again
mysql> set global max_binlog_cache_size=8388608000000;
Query OK, 0 rowsaffected (0.00 sec)
mysql> begin;
Query OK, 0 rowsaffected (0.00 sec)
 
mysql> update erp_mkpf set usnam='测试1864错误';
Query OK, 70466 rowsaffected (0.38 sec)
Rows matched:70466  Changed: 70466  Warnings: 0
 
mysql> commit;
Query OK, 0 rowsaffected (0.08 sec)
1
2
3
4
5
6
 
从库查看状态:
mysql> show slavestatus\G
 
               Last_SQL_Errno: 1864
               Last_SQL_Error: Cannot scheduleevent Update_rows, relay-log name ./HE1-relay-bin.000005, position 494 toWorker thread because its size 8200 exceeds 1024 ofslave_pending_jobs_size_max.

未经允许不得转载:JX BLOG » 解决MySQL(1032、1062、1864) 主从错误

赞 (0)

评论 0

评论前必须登录!

登陆 注册