MySQL Binlog Configuration and Usage Guide

Section 1 introduces MySQL binary log (binlog) configuration, noting that MySQL does not enable binary logging by default.

Basic parameters can be inspected with commands such as select @@log_bin;, select @@log_bin_basename;, select @@server_id;, select @@binlog_format;, and select @@sync_binlog;.

The binlog serves as a log for backup and recovery and is a prerequisite for master‑slave replication.

Key configuration settings include: server_id=8 (or server_id=3306) log_bin=/data/binlog/mysql-bin (the directory and filename prefix) binlog_format=row to record changes in row‑based format

Enabling the binary log in MySQL 5.7 or later requires setting log_bin even on a single‑node server.

Each binlog event is the smallest unit of logging, identified by a start position and an end position. For example, an event may start at 194 and end at 254, which helps in extracting specific portions of the log.

MySQL records different types of statements:

DDL and DCL statements are logged verbatim.

DML statements are logged in ROW mode, capturing the exact row changes.

The statement format offers higher readability and smaller logs but is less precise, while the row format is more accurate but generates larger logs.

Log viewing commands include: show binary logs; – list all binlog files. show master status; – display the current binlog file and position. show binlog events in 'mysql-bin.000003'; – list events in a specific binlog.

Using mysql -e "show binlog events in 'xxx' limit ..." for quick filtering.

The mysqlbinlog utility can extract logs with options such as --start-position, --stop-position, --start-datetime, --stop-datetime, and --base64-output=decode-rows -vvv.

Example output of show binlog events displays columns like Log_name, Pos, Event_type, Server_id, End_log_pos, and Info, which describe the file name, start position, event type, server identifier, end position, and event details respectively.

Additional syntax for SHOW BINLOG EVENTS allows optional clauses such as IN 'log_name', FROM pos, and LIMIT [offset,] row_count.

To extract logs based on GTID, enable GTID mode by editing /etc/my.cnf with gtid-mode=on and enforce-gtid-consistency=true, then restart MySQL. Use mysqlbinlog --include-gtids='uuid:1-6' or --exclude-gtids='uuid:4' to include or exclude specific transactions.