show relaylog events

 

 

###########################################################################

有时候从库正在消化relay log的时候，一直延迟，可以查看正在进行的操作，找到耗时操作：

 

 

 

root@xxx((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.10
                  Master_User: mysqlsync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.011496
          Read_Master_Log_Pos: 249691543
               Relay_Log_File: relay-bin.000022
                Relay_Log_Pos: 784939541
        Relay_Master_Log_File: mysql-bin.011406
             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: 784939352
              Relay_Log_Space: 96941716899
              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: 200846
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: 177078818
                  Master_UUID: 09ecc3bf-5c9a-11e8-bfe1-246e988841c0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

Tue Dec 22 15:37:28 2020
user@xxx((none)) > show relaylog events in 'relay-bin.000022' from 784939541  limit 10 \G;         
*************************** 1. row ***************************
   Log_name: relay-bin.000022
        Pos: 784939541
 Event_type: Query
  Server_id: 177078818
End_log_pos: 784939422
       Info: BEGIN
*************************** 2. row ***************************
   Log_name: relay-bin.000022
        Pos: 784939611
 Event_type: Rows_query
  Server_id: 177078818
End_log_pos: 784939501
       Info: # delete  from  history where clock < 1608162601 limit 600000
*************************** 3. row ***************************
   Log_name: relay-bin.000022
        Pos: 784939690
 Event_type: Table_map
  Server_id: 177078818
End_log_pos: 784939553
       Info: table_id: 2889 (zabbix.history)
*************************** 4. row ***************************
   Log_name: relay-bin.000022
        Pos: 784939742
 Event_type: Delete_rows
  Server_id: 177078818
End_log_pos: 784947759
       Info: table_id: 2889
*************************** 5. row ***************************
   Log_name: relay-bin.000022
        Pos: 784947948
 Event_type: Delete_rows
  Server_id: 177078818
End_log_pos: 784955965
       Info: table_id: 2889
*************************** 6. row ***************************
   Log_name: relay-bin.000022
        Pos: 784956154
 Event_type: Delete_rows
  Server_id: 177078818
End_log_pos: 784964171
       Info: table_id: 2889
*************************** 7. row ***************************
   Log_name: relay-bin.000022
        Pos: 784964360
 Event_type: Delete_rows
  Server_id: 177078818
End_log_pos: 784972377
       Info: table_id: 2889
*************************** 8. row ***************************
   Log_name: relay-bin.000022
        Pos: 784972566
 Event_type: Delete_rows
  Server_id: 177078818
End_log_pos: 784980583
       Info: table_id: 2889
*************************** 9. row ***************************
   Log_name: relay-bin.000022
        Pos: 784980772
 Event_type: Delete_rows
  Server_id: 177078818
End_log_pos: 784988789
       Info: table_id: 2889
*************************** 10. row ***************************
   Log_name: relay-bin.000022
        Pos: 784988978
 Event_type: Delete_rows
  Server_id: 177078818
End_log_pos: 784996995
       Info: table_id: 2889
10 rows in set (0.00 sec)

ERROR: 
No query specified

Tue Dec 22 15:37:42 2020

 

 

 

 

 

 

 

 

 

 

 

#######################################