mysql中GTID报错如何做
发布时间：2021-12-23 12:45:56 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了mysql中GTID报错怎么办，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 1. 在master 上删除一条记录，而slave 上找不到。 2：第二种：主键重复。在slave已经有该记录
|
这篇文章主要介绍了mysql中GTID报错怎么办，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 1. 在master 上删除一条记录，而slave 上找不到。 2：第二种：主键重复。在slave已经有该记录，又在master上插入了同一条记录 3：在master上更新一条记录，而slave上找不到，丢失了数据。 4：slave的中继日志relay-bin损坏。 mysql> show slave statusG; *************************** 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 statusG; *************************** 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 statusG; *************************** 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 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 statusG; *************************** 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) 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 ，然后重新做同步，这样就可以有新的中继日值了。 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
