mysql last errno 126_MySQL Last_SQL_Errno: 1062-阿里云开发者社区

一、环境描述

1. centos 6.6

2.mysql 5.6.25

3.基于gtid的复制

二、异常描述

误把从节点当成主节点插入一条数据，同一条数据在主、从节点插入都进行了一次插入操作，导致主键冲突，slave线程异常。

三、处理过程

1.查看报错信息

(root@localhost) [(none)]> show slave status\G;

*************************** 1. row ***************************

...............略.............................

Last_Errno: 1062

Last_Error: Could not execute Write_rows event on table user-center.t_dea_info; Duplicate entry '89465d5d792541459e38cabaf677c95e' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000005, end_log_pos 469856269

Skip_Counter: 0

Exec_Master_Log_Pos: 469855892

Relay_Log_Space: 470236370

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 user-center.t_dea_info; Duplicate entry '89465d5d792541459e38cabaf677c95e' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000005, end_log_pos 469856269

Replicate_Ignore_Server_Ids:

Master_Server_Id: 157

Master_UUID: 77220b08-41ab-11e5-b9be-000ec4cac97a

Master_Info_File: mysql.slave_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: 170410 16:10:01

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 77220b08-41ab-11e5-b9be-000ec4cac97a:33417992-37560729

Executed_Gtid_Set: 1d7e2d03-084b-11e6-88f0-000ec4cac979:1-3236,

716feeae-41b4-11e5-82c2-000ec4cac979:1-2,

77220b08-41ab-11e5-b9be-000ec4cac97a:1-37559950,

99bcf840-22f5-11e6-b6d2-000ec4cac981:1-10

Auto_Position: 1

1 row in set (0.00 sec)

2.确认需要跳过的gtid是关键

找到这个已经执行的gtid，加1

77220b08-41ab-11e5-b9be-000ec4cac97a:1-37559950,

3.跳gtid

(root@localhost) [(none)]> stop slave;

(root@localhost) [(none)]> SET @@SESSION.GTID_NEXT= '77220b08-41ab-11e5-b9be-000ec4cac97a:37559951';

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> BEGIN; COMMIT;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> SET SESSION GTID_NEXT = AUTOMATIC;

Query OK, 0 rows affected (0.00 sec)

4.重启线程，查看状态，确认OK

(root@localhost) [(none)]> START SLAVE;

Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost) [(none)]> show slave status\G;

*************************** 1. row ***************************

.........................略............................

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 470459548

Relay_Log_File: relay-bin.000016

Relay_Log_Pos: 70709

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: 470459548

Relay_Log_Space: 114127

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: 157

Master_UUID: 77220b08-41ab-11e5-b9be-000ec4cac97a

Master_Info_File: mysql.slave_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: 77220b08-41ab-11e5-b9be-000ec4cac97a:33417992-37561196

Executed_Gtid_Set: 1d7e2d03-084b-11e6-88f0-000ec4cac979:1-3236,

716feeae-41b4-11e5-82c2-000ec4cac979:1-2,

77220b08-41ab-11e5-b9be-000ec4cac97a:1-37561196,

99bcf840-22f5-11e6-b6d2-000ec4cac981:1-10

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

四、关键点

1.找到需要跳过的GTID,知道为什么要加1，理解了原理，问题就迎刃而解了。