mysql中GTID报错怎么办

55次阅读
没有评论

共计 14493 个字符,预计需要花费 37 分钟才能阅读完成。

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

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 ,然后重新做同步,这样就可以有新的中继日值了。

感谢你能够认真阅读完这篇文章,希望丸趣 TV 小编分享的“mysql 中 GTID 报错怎么办”这篇文章对大家有帮助,同时也希望大家多多支持丸趣 TV,关注丸趣 TV 行业资讯频道,更多相关知识等着你来学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-26发表,共计14493字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)