分享更有价值
被信任是一种快乐
        找回密码
当前位置：云技术 技术 云技术 数据库 正文

mysql中GTID报错怎么办

2024-01-11 分类：数据库 阅读(29)
文章页正文上

这篇文章主要介绍了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

（2）主键重复
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: 16免费主机域名0410 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-141免费主机域名877405c37: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 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)
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，然后重新做同步，这样就可以有新的中继日值了。
感谢你能够认真阅读完这篇文章，希望小编分享的“mysql中GTID报错怎么办”这篇文章对大家有帮助，同时也希望大家多多支持云技术，关注云技术行业资讯频道，更多相关知识等着你来学习!

相关推荐: 怎么解决message客户端无法重新使用SPID为799的会话问题

本篇内容主要讲解“怎么解决message客户端无法重新使用SPID为799的会话问题”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么解决message客户端无法重新使用SPID为799的会话问题”吧!报错日志：m…

文章页内容下
AD：【 前标题-文章页正文结尾文字广告】文章页正文结尾文字广告
赞(0) 打赏
版权声明：本站采用知识共享、学习交流，不允许用于商业用途；文章由发布者自行承担一切责任，与本站无关。
分享到
文章页正文下

相关推荐

文章页评论上

推荐AD:

吐血推荐

云服务器免费，网站空间免费，等你哦

置顶推荐

热门专题

热门标签

数据库(4326)站长(448)服务器安全(21)短视频(13)自媒体(7)

活跃读者

    网站统计

    • 日志总数：27598
    • 标签总数：6
    • 页面总数：1
    • 链接总数：0
    • 最后更新：2024-04-22

    云服务器、web空间可免费试用

    宝塔面板主机、支持php,mysql等，SSL部署；安全高速企业专供99.999%稳定，另有高防主机、不限制内容等类型，具体可咨询QQ:360163164,Tel同微信：18905205712

    主机选购导航云服务器试用

    登录

    找回密码

    注册