mysql中GTID報錯怎么辦
這篇文章主要介紹了MySQL中GTID報錯怎么辦，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
站在用戶的角度思考問題，與客戶深入溝通，找到漳縣網(wǎng)站設(shè)計與漳縣網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都做網(wǎng)站、網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、域名注冊、雅安服務(wù)器托管、企業(yè)郵箱。業(yè)務(wù)覆蓋漳縣地區(qū)。
1. 在master 上刪除一條記錄，而slave 上找不到。
2：第二種：主鍵重復(fù)。在slave已經(jīng)有該記錄，又在master上插入了同一條記錄
3：在master上更新一條記錄，而slave上找不到，丟失了數(shù)據(jù)。
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在宕機，或者非法關(guān)機，例如電源故障、主板燒了等，造成中繼日志損壞，同步停掉）
以下是對上述四種情況做的示例：
（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)
會出現(xiàn)以上的情況，
解決方法：
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）主鍵重復(fù)
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 上更新一條數(shù)據(jù)。在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上，查找下更新后的那條記錄，應(yīng)該是不存在的。
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)
把丟失的數(shù)據(jù)在slave上填補，然后跳過報錯即可。
4：slave的中繼日志relay-bin損壞。
解決方法：找到同步的GTID 復(fù)制事務(wù)ID ，然后重新做同步，這樣就可以有新的中繼日值了。
感謝你能夠認真閱讀完這篇文章，希望小編分享的“mysql中GTID報錯怎么辦”這篇文章對大家有幫助，同時也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!
分享題目：mysql中GTID報錯怎么辦
文章來源：http://muchs.cn/article20/ijccjo.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、網(wǎng)頁設(shè)計公司、網(wǎng)站收錄、品牌網(wǎng)站設(shè)計、品牌網(wǎng)站建設(shè)、用戶體驗
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 品牌網(wǎng)站設(shè)計如何做好用戶體驗 2021-10-03
- 品牌網(wǎng)站設(shè)計要遵守哪些原則 2023-03-11
- 用戶體驗其實就這么簡單！ 2022-06-07
- 品牌網(wǎng)站設(shè)計制作的簡單步驟 2022-12-03
- 企業(yè)品牌網(wǎng)站設(shè)計思路介紹 2023-01-29
- 品牌網(wǎng)站設(shè)計四大核心制作要素 2022-08-15
- 品牌網(wǎng)站設(shè)計的四大原則 2013-09-05
- 品牌網(wǎng)站設(shè)計如何達到展示目的 2013-09-04
- 優(yōu)秀品牌網(wǎng)站設(shè)計欣賞 2014-06-25
- 深圳品牌網(wǎng)站設(shè)計公司 2014-05-13
- 品牌網(wǎng)站設(shè)計優(yōu)越性提升企業(yè)建站水平 2021-04-18
- 4個消費電子產(chǎn)品的品牌網(wǎng)站設(shè)計 2021-09-28