1. Using Show Binlog Events
A. Getting a list of binlog files mysql> Show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000005 | 1288 | | mysql-bin.000006 | 120 | +------------------+-----------+mysql>b. View the Binlog file currently being written mysql> show Master status\g;********************** 1. Row *************************** file:mysql-bin.000006 position:120c. View the contents of the specified BINLOG file syntax: Show BINLOG EV Ents [in ' Log_name '] [from POS] [LIMIT [offset,] row_count]1. Demo View all transactions in a binary file note: The Binlog file is actually a group of transactions consisting of a sequence of event, the transaction group. Mysql> SHOW BINLOG EVENTS in ' mysql-bin.000005 ' \g*************************** 1. Row *************************** a row represents a transaction group log_name:mysql-bin.000005 pos:4 Event_type:format_desc Server_i d:1end_log_pos:120 info:server Ver:5.6.29-log, Binlog ver:4*************************** 2. Row *************************** log_name:mysql-bin.000005 pos:120 event_type:query server_id:1end_log_pos:1 94 INfo:begin2. Demo to filter query for a binary file transaction mysql> SHOW BINLOG EVENTS in ' mysql-bin.000005 ' from 194 LIMIT 2 \g;******************** 1. Row *************************** log_name:mysql-bin.000005 pos:194 event_type:query server_id:1end_log_pos:3 Info:use ' TPCC '; UPDATE Warehouse SET W_YTD = w_ytd + 3232 WHERE w_id = 1*************************** 2. Row *************************** log_name:mysql-bin.000005 pos:319 event_type:query server_id:1end_log_pos:4 Info:use ' TPCC '; UPDATE District Set D_ytd = D_ytd + 3232 WHERE d_w_id = 1 and d_id = All rows in Set (0.00 sec) mysql>
2. Use Mysqlbinlog to view Binlog(ext: http://blog.csdn.net/leshami/article/details/41962243)
A, extract the specified binlog log # mysqlbinlog/opt/data/app01bin.000001 # mysqlbinlog/opt/data/app01bin.000001|grep Insert/*!40019 SE T @ @session. max_insert_delayed_threads=0*/; INSERT into TB values (2, ' Jack ') b, extract Binlog log for specified position location # mysqlbinlog--start-position= "+"--stop-position= "332"/ opt/data/app01bin.000001 C, extract the Binlog log of the specified position location and output to the compressed file # mysqlbinlog--start-position= "--stop-position=" 332 "/opt/data/app01bin.000001 |gzip >extra_01.sql.gz D, extract position log from specified Binlog location import Database # Mysqlbinlog--start-positio n= "--stop-position=" 332 "/opt/data/app01bin.000001 | Mysql-uroot-p e, extract the Binlog of the specified start time and output to the log file # Mysqlbinlog--start-datetime= "2014-12-15 20:15:23"/opt/data/app01bin.0000 --result-file=extra02.sql F, extract multiple Binlog log files in the specified location # mysqlbinlog--start-position= "--stop-position=" and 332/opt/da Ta/app01bin.000001/opt/data/app01bin.000002|more g, extract the specified database Binlog and convert the character set to UTF8 # Mysqlbinlog--database=test-- set-charset=utf8/opt/data/app01bin.000001/opt/data/app01bin.000002 >test.sql h, remote fetch log, specify end time # mysqlbinlog-urobin-p-h192.168.1.116-p3306--stop-datetime= "2014-12-15 20 : 30:23 "--read-from-remote-server mysql-bin.000033 |more I, remote extraction using row format Binlog log and output to local file # mysqlbinlog-urobin-p-p360 6-h192.168.1.177--READ-FROM-REMOTE-SERVER-VV inst3606bin.000005 >row.sql
2 ways to view Binlog files