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的工具路径。
基于开始/结束时间：
1 /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
#######################################
igoodful@qq.com
