加入收藏 | 设为首页 | 会员中心 | 我要投稿 银川站长网 (https://www.0951zz.com/)- 云通信、基础存储、云上网络、机器学习、视觉智能!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

mysql中GTID报错如何办

发布时间:2023-09-07 13:10:50 所属栏目:MySql教程 来源:
导读:这篇文章主要介绍了mysql中GTID报错怎么办,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。1. 在master 上删除一条记录,而slave 上找不到。2:

这篇文章主要介绍了mysql中GTID报错怎么办,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。

1. 在master 上删除一条记录,而slave 上找不到。

2:第二种:主键重复。在slave已经有该记录,又在master上插入了同一条记录

3:在master上更新一条记录,而slave上找不到,丢失了数据。

4:slave的中继日志relay-bin损坏。

mysql> show slave status\G;

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

        Slave_IO_State: Waiting for master to send event

         Master_Host: **********

         Master_User: repl_user

         Master_Port: ****

        Connect_Retry: 60

       Master_Log_File: mysql-bin.000006

     Read_Master_Log_Pos: 1140

        Relay_Log_File: relay-bin.000003

        Relay_Log_Pos: 882

    Relay_Master_Log_File: mysql-bin.000006

       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: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffa-141877405c37:20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

         Skip_Counter: 0

     Exec_Master_Log_Pos: 835

       Relay_Log_Space: 1559

       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: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffa-141877405c37:20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

 Replicate_Ignore_Server_Ids: 

       Master_Server_Id: 21

         Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

       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: 160410 20:37:35

        Master_SSL_Crl: 

      Master_SSL_Crlpath: 

      Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

      Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-19,

dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1

        Auto_Position: 1

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> stop

  -> slave;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from hjl;

+----+------+

| id | name |

+----+------+

| 1 | hjl |

+----+------+

1 row in set (0.00 sec)

mysql> delete table hjl where id = 1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table hjl where id = 1' at line 1

mysql> delete from hjl where id = 1;

Query OK, 1 row affected (0.00 sec)

mysql> start slave;

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

mysql> show slave status\G;

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

        Slave_IO_State: Waiting for master to send event

         Master_Host: 10.0.10.70

         Master_User: repl_user

         Master_Port: 5370

        Connect_Retry: 60

       Master_Log_File: mysql-bin.000006

     Read_Master_Log_Pos: 1140

        Relay_Log_File: relay-bin.000004

        Relay_Log_Pos: 448

    Relay_Master_Log_File: mysql-bin.000006

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

       Relay_Log_Space: 1682

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

         Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

       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: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

      Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-20,

dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1-2

        Auto_Position: 1

1 row in set (0.00 sec)

继日志损坏(这种情况SLAVE在宕机,或者非法关机,例如电源故障、主板烧了等,造成中继日志损坏,同步停掉)

以下是对上述四种情况做的示例:

(1)在master 上删除一条记录,而slave 上找不到

Connect_Retry: 60

       Master_Log_File: mysql-bin.000006

     Read_Master_Log_Pos: 530

        Relay_Log_File: relay-bin.000003

        Relay_Log_Pos: 361

    Relay_Master_Log_File: mysql-bin.000006

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

          Last_Error: Worker 3 failed executing transaction '' at master log mysql-bin.000006, end_log_pos 361; Error 'Unknown table 'rentcar.hjl'' on query. Default database: 'rentcar'. Query: 'DROP TABLE `hjl` /* generated by server */'

         Skip_Counter: 0

     Exec_Master_Log_Pos: 191

       Relay_Log_Space: 1072

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

        Last_SQL_Error: Worker 3 failed executing transaction '' at master log mysql-bin.000006, end_log_pos 361; Error 'Unknown table 'rentcar.hjl'' on query. Default database: 'rentcar'. Query: 'DROP TABLE `hjl` /* generated by server */'

 Replicate_Ignore_Server_Ids: 

       Master_Server_Id: 21

         Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

       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: 160410 20:30:13

        Master_SSL_Crl: 

      Master_SSL_Crlpath: 

      Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:17-18

      Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-16

        Auto_Position: 1

1 row in set (0.00 sec)

会出现以上的情况,

解决方法:

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset master;

Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;

Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_purged='6ee9b003-f1a7-11e1-9ffa-141877405c37:1-17';

Query OK, 0 rows affected (0.00 sec)

跳过错误地方

mysql> start slave;

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

mysql> show slave status\G;

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

        Slave_IO_State: Waiting for master to send event

         Master_Host: 10.0.10.70

         Master_User: repl_user

         Master_Port: 5370

        Connect_Retry: 60

       Master_Log_File: mysql-bin.000006

     Read_Master_Log_Pos: 530

        Relay_Log_File: relay-bin.000003

        Relay_Log_Pos: 577

    Relay_Master_Log_File: mysql-bin.000006

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

       Relay_Log_Space: 949

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

         Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

       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: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18

      Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-18

        Auto_Position: 1

1 row in set (0.00 sec)

ERROR: 

No query specified

(2)主键重复

mysql> show slave status\G;

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

        Slave_IO_State: Waiting for master to send event

         Master_Host: **********

         Master_User: repl_user

         Master_Port: ****

        Connect_Retry: 60

       Master_Log_File: mysql-bin.000006

     Read_Master_Log_Pos: 1140

        Relay_Log_File: relay-bin.000003

        Relay_Log_Pos: 882

    Relay_Master_Log_File: mysql-bin.000006

       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: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffa-141877405c37:20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

         Skip_Counter: 0

     Exec_Master_Log_Pos: 835

       Relay_Log_Space: 1559

       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: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffa-141877405c37:20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

 Replicate_Ignore_Server_Ids: 

       Master_Server_Id: 21

         Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

       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: 160410 20:37:35

        Master_SSL_Crl: 

      Master_SSL_Crlpath: 

      Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

      Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-19,

dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1

        Auto_Position: 1

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> stop

  -> slave;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from hjl;

+----+------+

| id | name |

+----+------+

| 1 | hjl |

+----+------+

1 row in set (0.00 sec)

mysql> delete from hjl where id = 1;

Query OK, 1 row affected (0.00 sec)

mysql> start slave;

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

mysql> show slave status\G;

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

        Slave_IO_State: Waiting for master to send event

         Master_Host: 10.0.10.70

         Master_User: repl_user

         Master_Port: 5370

        Connect_Retry: 60

       Master_Log_File: mysql-bin.000006

     Read_Master_Log_Pos: 1140

        Relay_Log_File: relay-bin.000004

        Relay_Log_Pos: 448

    Relay_Master_Log_File: mysql-bin.000006

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

       Relay_Log_Space: 1682

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

         Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

       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: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

      Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-20,

dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1-2

        Auto_Position: 1

1 row in set (0.00 sec)

3 在master 上更新一条数据。在slave 上面无法更新。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1; 

Can't find record in 't1', 

Error_code: 1032; 

handler error HA_ERR_KEY_NOT_FOUND; 

the event's master log mysql-bin.000010, end_log_pos 794

解决方法:

在master上,用mysqlbinlog 分析下出错的binlog日志在干什么。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | grep -A '10' 794

#120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F

### UPDATE hcy.t1

### WHERE

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='bbc' /* STRING(4) meta=65028 nullable=1 is_null=0 */

### SET

### @1=2 /* INT meta=0 nullable=0 is_null=0 */

### @2='BTV' /* STRING(4) meta=65028 nullable=1 is_null=0 */

# at 794

#120302 12:08:36 server id 22 end_log_pos 821 Xid = 60

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

在slave上,查找下更新后的那条记录,应该是不存在的。

mysql> select * from t1 where id=2;

Empty set (0.00 sec)

然后再到master查看

mysql> select * from t1 where id=2;

+----+------+

| id | name |

+----+------+

| 2 | BTV |

+----+------+

1 row in set (0.00 sec)

把丢失的数据在slave上填补,然后跳过报错即可。

4:slave的中继日志relay-bin损坏。

解决方法:找到同步的GTID 复制事务ID ,然后重新做同步,这样就可以有新的中继日值了。

(编辑:银川站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章