MySQL’s binary log (binlog) is a critical component for replication and point-in-time recovery. It records all data-modifying operations—DDL (Data Definition Language) and DML (Data Manipulation Language)—as events, excluding SELECT queries. Each event includes metadata such as timestamp, server ID, and position, enabling precise reconstruction of database state changes.

Binlog Structure and File Types

• Binary log files (e.g., mysql-bin.000001): Contain serialized event records in binary format.
• Index file (e.g., mysql-bin.index): Tracks all available binary log files on the server.

Performance Impact

Enabling binary logging typically introduces a 0.5–1.5% performance overhead due to synchronous writes. This cost is often justified by the operational benefits of replication and recovery.

Common Use Cases

• Replication: Master servers stream binlog events to slaves to maintain data consistency.
• Point-in-time recovery: Replaying binlog events to restore data to a specific moment before failure.
• Auditing and debugging: Tracking schema and data changes across environments.

Core mysqlbinlog Commands

Basic Syntax

mysqlbinlog [options] logfile1 [logfile2 ...]

Decoding Row-Based Events

Row-based replication (RBR) encodes changes as row-level modifications. Use --base64-output=decode-rows with -v or -vv to decode these into human-readable SQL-like statements:

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000003

• -v: Decodes row events into pseudo-SQL with column names.
• -vv: Adds data type annotations and includes row query events.

Filtering by Time Range

Extract events within a time window. Useful for broad investigations but not recommended for recovery due to timestamp precision limits.

mysqlbinlog --start-datetime="2023-10-05 14:30:00" \
            --stop-datetime="2023-10-05 14:45:00" \
            mysql-bin.000001 mysql-bin.000002

Filtering by Position

Use exact byte offsets for precise control. Ideal for targeted recovery.

mysqlbinlog --start-position=1245 \
            --stop-position=2890 \
            mysql-bin.000003

GTID-Based Filtering

When GTID mode is enabled, transactions are uniquely identified. Filter by GTID ranges for granular control:

# Include specific GTIDs
mysqlbinlog --include-gtids='3c7e8b4d-1b3a-11ee-8a9b-0242ac120002:1-5' mysql-bin.000004

# Exclude specific GTIDs
mysqlbinlog --exclude-gtids='3c7e8b4d-1b3a-11ee-8a9b-0242ac120002:3-4' mysql-bin.000004

Remote Log Retrieval

Read binlogs directly from a remote MySQL server using the -R option. Requires replication user privileges:

mysqlbinlog -R -h 192.168.1.10 -u repl_user -p \
            --port=3306 mysql-bin.000005

Advanced Options

Database-Specific Filtering

Limit output to a single database (local logs only):

mysqlbinlog --database=production_db mysql-bin.000006

Output Redirection

Save decoded output to a file for later analysis or replay:

mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000007 > /tmp/recovery_script.sql

Skipping GTID Validation

Use --skip-gtids to bypass GTID checks during replay, forcing execution regardless of existing GTIDs:

mysqlbinlog --skip-gtids mysql-bin.000008 | mysql -u root -p

Database Rewriting

Replay binlog events targeting one database into another—a powerful feature for multi-source replication or migration:

mysqlbinlog --rewrite-db='old_db->new_db' mysql-bin.000009

Force Reading Corrupted Logs

Use -f or --force-read to continue parsing even if encountering malformed or unrecognized events:

mysqlbinlog -f mysql-bin.000010

Offset and Server ID Filtering

Skip initial events or filter by originating server:

# Skip first 5000 events
mysqlbinlog --offset=5000 mysql-bin.000011

# Only show events from server ID 101
mysqlbinlog --server-id=101 mysql-bin.000012

Recovering Data with mysqlbinlog

Recovery involves extracting relevant events and piping them into the MySQL client.

Recovery Workflow

1. Identify the problematic transaction range using position or GTID.
2. Extract events before the corruption point.
3. Apply to target instance with --disable-log-bin to avoid logging recovery actions.

mysqlbinlog --start-position=1500 --stop-position=2200 \
            --disable-log-bin mysql-bin.000013 | \
            mysql -u recovery_user -p --socket=/var/lib/mysql/mysql.sock

Important Notes

• Always test recovery on a non-production instance.
• Never use --base64-output=decode-rows during replay—it strips row data essential for execution.
• Use absolute paths for binlog files and MySQL binaries to avoid context errors.

Statistical Analysis of Binlog Activity

DML Event Summary

Count INSERT, UPDATE, DELETE operations per table using awk:

mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000014 | \
awk '/###/{ if($0 ~ /(INSERT|UPDATE|DELETE)/) count[$2" "$NF]++ } \
END { for(i in count) print i "\t" count[i] }' | \
sort -k3 -nr

DDL Event Extraction

Extract CREATE, ALTER, DROP, and other schema changes:

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000015 | \
awk '/^[0-9]{6}/ { ts=$0 } \
/^(CREATE|ALTER|DROP|TRUNCATE|RENAME|GRANT|REVOKE)/ { print ts "\n" $0 ";" }'

Parallel Replication Metrics

For GTID-based replication, analyze commit dependencies using last_committed and sequence_number:

mysqlbinlog mysql-bin.000016 | \
grep -o 'last_committed=[0-9]\+ sequence_number=[0-9]\+' | \
sed 's/last_committed=\([0-9]*\).*sequence_number=\([0-9]*\)/\1\t\2/' | \
awk '{print $1, $2}' | sort -k1,1n | uniq -c

Best Practices

• Regularly rotate binlogs using FLUSH LOGS or RESET MASTER (with caution).
• Enable binlog_checksum and sync_binlog=1 for durability in critical environments.
• Archive binlogs off-server for disaster recovery.
• Use --no-defaults to prevent unintended configuration interference.