mysql的show binlog events和show relaylog events

 

二进制日志查看

1、show查看。

mysql> show binary logs;  #获取binlog文件列表

mysql> show master status； #查看当前正在写入的binlog文件

mysql> show binlog events;   #只查看第一个binlog文件的内容

mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容

 

 

获取当前实例正在执行的语句：先获取位点，再根据位点查看binlog解析：

root@xxx ((none)) > show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.032438
         Position: 1000312563
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 04c724a5-700c-11ea-bc3e-e4434bcd18ee:1-31963973038,
0af9b8b6-ad03-11e9-abca-e4434b034bf8:1-9773242904,
7c472392-ad03-11e9-beba-246e96b91198:1-22263563597,
bd3f2d47-ac5c-11e9-a378-e4434b21bc70:1-39550055933
1 row in set (0.00 sec)

ERROR: 
No query specified

Fri Jan 29 10:54:53 2021
root@xxx ((none)) > show binlog events in 'mysql-bin.032438'  from 1000312563  limit 5\G          
*************************** 1. row ***************************
   Log_name: mysql-bin.032438
        Pos: 1000312563
 Event_type: Gtid
  Server_id: 177100296
End_log_pos: 1000312624
       Info: SET @@SESSION.GTID_NEXT= '7c472392-ad03-11e9-beba-246e96b91198:22263563598'
*************************** 2. row ***************************
   Log_name: mysql-bin.032438
        Pos: 1000312624
 Event_type: Query
  Server_id: 177100296
End_log_pos: 1000312683
       Info: BEGIN
*************************** 3. row ***************************
   Log_name: mysql-bin.032438
        Pos: 1000312683
 Event_type: Rows_query
  Server_id: 177100296
End_log_pos: 1000312883
       Info: # INSERT INTO tag_endpoint(tag, endpoint_id, ts, t_create) VALUES ('job=passport-web', 142285, 1615656500, now())  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), ts=VALUES(ts)
*************************** 4. row ***************************
   Log_name: mysql-bin.032438
        Pos: 1000312883
 Event_type: Table_map
  Server_id: 177100296
End_log_pos: 1000312952
       Info: table_id: 108 (falcon_global.tag_endpoint)
*************************** 5. row ***************************
   Log_name: mysql-bin.032438
        Pos: 1000312952
 Event_type: Update_rows
  Server_id: 177100296
End_log_pos: 1000313090
       Info: table_id: 108 flags: STMT_END_F
5 rows in set (0.00 sec)

Fri Jan 29 10:55:13 2021

 

 

 

mysqlbinlog查看

mysqlbinlog是一个查看mysql二进制日志的工具，可以把mysql上面的所有操作记录从日志里导出，这个工具默认的安装路径为：/usr/local/mysql/bin/mysqlbinlog

     可以通过find / -name "mysqlbinlog"命令查找mysqlbinlog的工具路径。

      基于开始/结束时间：

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2013-03-01 00:00:00" --stop-datetime="2014-03-21 23:59:59" /usr/local/mysql/var/mysql-bin.000007 -r  test2.sql

 

 

 

relaylog查看：show slave status;show relaylog events in 'relay-log' from position limit num;

 

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: mysql_sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.032986
          Read_Master_Log_Pos: 7185978
               Relay_Log_File: relay-bin.035917
                Relay_Log_Pos: 130240634
        Relay_Master_Log_File: mysql-bin.032979
             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: 130240509
              Relay_Log_Space: 7523397747
              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: 2197
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: 1771656296
                  Master_UUID: 7c472392-ad03-11e9-beba-246e96b91198
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 7c472392-ad03-11e9-beba-246e96b91198:1222544-22277846498
            Executed_Gtid_Set: 04c724a5-700c-11ea-bc3e-e4434bcd18ee:1-31963973038,
0af9b8b6-ad03-11e9-abca-e4434b034bf8:1-9773242904,
7c472392-ad03-11e9-beba-246e96b91198:1-22265036988,
bd3f2d47-ac5c-11e9-a378-e4434b21bc70:1-39550055933
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Fri Jan 29 10:59:39 2021

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


root@xxx ((none)) > show relaylog events in 'relay-bin.035917' from 130240634  limit 10\G
*************************** 1. row ***************************
   Log_name: relay-bin.035917
        Pos: 130240634
 Event_type: Gtid
  Server_id: 177100296
End_log_pos: 130240570
       Info: SET @@SESSION.GTID_NEXT= '7c472392-ad03-11e9-beba-246e96b91198:22265036989'
*************************** 2. row ***************************
   Log_name: relay-bin.035917
        Pos: 130240695
 Event_type: Query
  Server_id: 177100296
End_log_pos: 130240655
       Info: BEGIN
*************************** 3. row ***************************
   Log_name: relay-bin.035917
        Pos: 130240780
 Event_type: Rows_query
  Server_id: 177100296
End_log_pos: 130240863
       Info: # INSERT INTO tag_endpoint(tag, endpoint_id, ts, t_create) VALUES ('jobgroup=info', 33456248, 1645676920, now())  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), ts=VALUES(ts)
*************************** 4. row ***************************
   Log_name: relay-bin.035917
        Pos: 130240988
 Event_type: Table_map
  Server_id: 177100296
End_log_pos: 130240932
       Info: table_id: 32082 (falcon_global.tag_endpoint)
*************************** 5. row ***************************
   Log_name: relay-bin.035917
        Pos: 130241057
 Event_type: Update_rows
  Server_id: 177100296
End_log_pos: 130241084
       Info: table_id: 32082 flags: STMT_END_F
*************************** 6. row ***************************
   Log_name: relay-bin.035917
        Pos: 130241209
 Event_type: Xid
  Server_id: 177100296
End_log_pos: 130241111
       Info: COMMIT /* xid=269436948832 */
*************************** 7. row ***************************
   Log_name: relay-bin.035917
        Pos: 130241236
 Event_type: Gtid
  Server_id: 177100296
End_log_pos: 130241172
       Info: SET @@SESSION.GTID_NEXT= '7c472392-ad03-11e9-beba-246e96b91198:22265036990'
*************************** 8. row ***************************
   Log_name: relay-bin.035917
        Pos: 130241297
 Event_type: Query
  Server_id: 177100296
End_log_pos: 130241257
       Info: BEGIN
*************************** 9. row ***************************
   Log_name: relay-bin.035917
        Pos: 130241382
 Event_type: Rows_query
  Server_id: 177100296
End_log_pos: 130241442
       Info: # INSERT INTO tag_endpoint(tag, endpoint_id, ts, t_create) VALUES ('ret=req', 1764031, 1231286920, now())  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), ts=VALUES(ts)
*************************** 10. row ***************************
   Log_name: relay-bin.035917
        Pos: 130241567
 Event_type: Table_map
  Server_id: 177100296
End_log_pos: 130241511
       Info: table_id: 32082 (falcon_global.tag_endpoint)
10 rows in set (0.00 sec)

Fri Jan 29 11:00:37 2021

 

 

 

 

 

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