Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
The MySQL binary log records every change made to the database — inserts, updates, deletes, schema changes. It’s the foundation for replication and point-in-time recovery. Most production MySQL servers run with binary logging enabled.

​
Enabling Binary Logging

Binary logging is enabled by default in MySQL 8.0. To verify: 
SHOW VARIABLES LIKE 'log_bin';
-- log_bin  ON
Configure it in my.cnf / my.ini: 
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
server_id = 1          -- required for replication; good practice to set always
binlog_expire_logs_seconds = 604800  -- auto-purge logs older than 7 days
max_binlog_size = 100M -- rotate to a new file when this size is reached
After log_bin is set, MySQL names binlog files as mysql-bin.000001, mysql-bin.000002, etc., with an index file (mysql-bin.index) listing them all.

​
Binary Log Formats

MySQL supports three binlog formats:
FormatWhat’s loggedReplication safetyLog sizePerformance impactBest for
ROWActual before/after row data for each affected rowSafest — deterministic regardless of replica stateLargest (grows with data volume, especially bulk updates)Highest write overhead on sourceProduction replication; PITR; CDC/change data capture
STATEMENTThe SQL statement that caused the changeUnsafe for non-deterministic functions (RAND(), UUID(), NOW())SmallestLowest overheadRead-heavy workloads where log size matters and all statements are deterministic
MIXEDSTATEMENT by default; switches to ROW for unsafe statementsSafe (ROW used when needed)MediumMediumLegacy setups migrating from STATEMENT; not recommended for new deployments
ROW format is the recommended default. It replicates correctly even when non-deterministic functions like RAND() or UUID() are involved. The downside is larger log files — a single UPDATE touching millions of rows produces one statement in STATEMENT format but one row event per affected row in ROW format.

​
When to enable binary logging and which format to choose

Binary logging is enabled by default in MySQL 8.0+. The question is usually which format to use and whether to leave it on at all.
ScenarioRecommendation
Running any replicaEnable binary logging, use ROW format — it’s required for Group Replication and recommended for standard replication
Point-in-time recovery neededEnable binary logging; format doesn’t affect PITR capability, but ROW gives the most precise replay
Change data capture (CDC) with Debezium, Kafka Connect, etc.Enable binary logging, ROW format required — CDC tools read row-level events directly
Standalone dev/test instance, no replication, no PITRDisabling binary logging (--skip-log-bin) reduces write overhead and disk usage
Very high write throughput, log size is a concernConsider binlog_row_image = MINIMAL (logs only changed columns) rather than switching to STATEMENT
Auditing or complianceROW format with binlog_row_image = FULL (the default) gives a complete before/after record
Check and change the format: 
SHOW VARIABLES LIKE 'binlog_format';
SET SESSION binlog_format = 'ROW';  -- change for current session

​
Listing and Viewing Binary Logs

List all binary log files: 
SHOW BINARY LOGS;
Check the current binlog file and position: 
SHOW BINARY LOG STATUS;
View the events in a binlog from SQL: 
SHOW BINLOG EVENTS IN 'mysql-bin.000003' LIMIT 20;
SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM 154 LIMIT 10;

​
mysqlbinlog

The mysqlbinlog tool decodes binary log files into human-readable SQL. It’s used for point-in-time recovery and auditing: 
# Read a local binlog file
mysqlbinlog /var/log/mysql/mysql-bin.000003

# Filter by time range
mysqlbinlog --start-datetime="2024-06-15 10:00:00" \
            --stop-datetime="2024-06-15 12:00:00" \
            /var/log/mysql/mysql-bin.000003

# Decode ROW format events (shows actual data changes)
mysqlbinlog --verbose /var/log/mysql/mysql-bin.000003

# Read directly from a running server
mysqlbinlog --read-from-remote-server \
            --host=127.0.0.1 --user=root --password \
            mysql-bin.000003

​
Point-in-Time Recovery

Combining a full backup with binary logs lets you restore to any point in time after the backup was taken. Typical workflow:
  1. Take a full backup with mysqldump --single-transaction --source-data:
mysqldump --single-transaction --source-data=2 --all-databases \
  -u root -p > backup.sql
--source-data=2 writes the binlog filename and position as a comment in the dump. This is your recovery starting point.
  1. Restore the full backup:
mysql -u root -p < backup.sql
  1. Find the binlog file and position from the dump:
head -50 backup.sql | grep "SOURCE_LOG_FILE"
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=154;
  1. Replay binlogs from that position up to just before the event you want to undo:
mysqlbinlog --start-position=154 \
            --stop-datetime="2024-06-15 11:59:00" \
            mysql-bin.000003 mysql-bin.000004 | mysql -u root -p

​
Managing Binary Logs

Manually purge old binary logs: 
-- Purge all logs before a specific log file
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- Purge all logs older than a date
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';
Auto-purge with binlog_expire_logs_seconds (MySQL 8.0+) or expire_logs_days: 
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 days in seconds
Never delete binlog files directly with rm — use PURGE BINARY LOGS so the index file stays consistent.

​
Frequently Asked Questions

​
Does binary logging affect write performance?

Yes, modestly. ROW format logging adds overhead proportional to the amount of data changed. For write-heavy workloads, the overhead is typically 5–15%. It’s almost always worth it for the recovery and replication capabilities it enables.

​
Can I skip binary logging for a specific session?

SET SESSION sql_log_bin = 0;
-- ... operations that won't be logged ...
SET SESSION sql_log_bin = 1;
This is useful for loading data on a replica that doesn’t need to propagate further, or for bulk operations you want to exclude from point-in-time recovery scope. Requires the BINLOG_ADMIN or SUPER privilege.

​
How do I know how much disk space my binary logs are using?

SHOW BINARY LOGS;
-- Sum the File_size column
Or on disk: du -sh /var/log/mysql/mysql-bin.*

​
Troubleshooting

ProblemSolution
Binary log files filling up diskSet binlog_expire_logs_seconds or run PURGE BINARY LOGS BEFORE — never rm the files directly
mysqlbinlog output is unreadable for ROW formatAdd --verbose (or -v) to decode row events into pseudo-SQL
Point-in-time recovery missed some changesCheck that all binlog files in the range were included — gaps in the file list leave holes in the replay
ERROR: Binary logging not possible in replicationReplica may need log_bin enabled and a unique server_id — both are required
High replication lagROW format produces more data — check replica I/O and SQL thread lag with SHOW REPLICA STATUS\G