Detailed Explanation of Mysql Binlog Data Viewing Method

Introduction to binlog

binlog, or binary log, records all changes on the database.

When the SQL statement to change the database is executed, a record will be written at the end of binlog, and the statement parser will be notified that the statement is finished.

binlog format

Based on statements, there is no guarantee that all statements are successfully executed from the library, such as update... limit 1;

Row-based, count every change as one line in binlog. Row-based formats have advantages when performing a particularly complex update or delete operation.

Log in to mysql to view binlog

View only the contents of the first binlog file

show binlog events;

View the contents of the specified binlog file

show binlog events in 'mysql-bin.000002';

View the binlog file currently being written

show master status\G

Get the list of binlog files

show binary logs;

View with mysqlbinlog tool

Note:

Do not view the binlog file currently being written

Do not add--force parameter to force access

If the binlog format is row mode, add the-vv parameter

Local view

Based on start/end time

mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d  Library name  2 Binary document 

Based on pos value

mysqlbinlog --start-postion=107 --stop-position=1000 -d  Library name  2 Binary document 

Remote viewing

Specify the start/end time and redirect the results to the local t. binlog file.

mysqlbinlog -u username -p password -hl-db1.dba.beta.cn6.qunar.com -P3306 \
--read-from-remote-server --start-datetime='2013-09-10 23:00:00' --stop-datetime='2013-09-10 23

Summarize