• 04 May, 2026
• 0 Comments
• 3 Mins Read

MySQL Binary Logs

MySQL Binary Logs: Formats, Row Images & Point-in-Time Recovery

Introduction

MySQL Binary Logs (binlogs) are one of the most critical components in MySQL for data recovery, replication, and auditing changes. They record all changes made to the database, allowing administrators to replay events and restore databases to a specific point in time.

Whether you’re managing production databases or preparing for disaster recovery, understanding binary logs is essential.

What Are MySQL Binary Logs?

Binary logs are log files that record all data-modifying operations such as:

• INSERT, UPDATE, DELETE
• CREATE, ALTER, DROP
• Transaction commits

They do not store SELECT queries, as those don’t modify data.

Key Uses:

• Replication (Master → Replica)
• Point-in-Time Recovery (PITR)
• Auditing database changes

Binary Log File Structure

Binary logs are stored as:

• mysql-bin.000001
• mysql-bin.000002

And indexed in:

• mysql-bin.index

Each file contains events, which represent database changes.

Binary Log Formats

MySQL supports three binary logging formats:

1. Statement-Based Logging (SBL)

• Logs the actual SQL statements

• Example:

  UPDATE employees SET salary = salary + 1000 WHERE dept = 'IT';

Advantages:

• Smaller log size
• Easy to read/debug

Disadvantages:

• Non-deterministic queries may cause inconsistency
• Functions like NOW() or RAND() can behave differently

2. Row-Based Logging (RBL)

• Logs actual row changes

• Example:

  Before: salary = 5000
  After:  salary = 6000

Advantages:

• Accurate and reliable
• Safe for replication

Disadvantages:

• Larger log size
• Harder to read manually

3. Mixed Logging

• Combines both Statement + Row
• MySQL automatically decides the format

Use Case:

• Default in many setups
• Balances performance and accuracy

Controlling Binary Log Format

You can configure the format using:

SET GLOBAL binlog_format = 'ROW';   -- or STATEMENT / MIXED

Or in my.cnf:

binlog_format=ROW

Row-Based Logging: binlog_row_image

When using ROW format, MySQL provides control over how much row data is logged using:

binlog_row_image=FULL | MINIMAL | NOBLOB

1. FULL (Default)

• Logs entire row (before and after image)

Example:

• All columns are stored regardless of change

Pros:

• Complete data for recovery
• Safe and reliable

Cons:

• Larger binary logs

2. MINIMAL

• Logs only changed columns

Example:

• Only salary column stored if updated

Pros:

• Reduced log size
• Better performance

Cons:

• Less detailed for auditing

3. NOBLOB

• Logs all columns except BLOB/TEXT fields unless changed

Pros:

• Saves space for large data types

Cons:

• Slightly less complete data

Binary Log Size & Storage Implications

Binary logs can grow quickly depending on:

• Transaction volume
• Logging format (ROW > STATEMENT)
• Row image setting (FULL > MINIMAL)

Example Impact:

Managing Binary Log Storage

1. Enable Binary Logging

log_bin = mysql-bin

2. Set Expiration Policy

binlog_expire_logs_seconds = 604800  # 7 days

Or:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

3. Monitor Disk Usage

SHOW BINARY LOGS;

4. Delete Old Logs

PURGE BINARY LOGS TO 'mysql-bin.000010';

Importance of Binary Log Retention

Proper retention ensures:

• Recovery from failures
• Data consistency
• Compliance and auditing

Best Practice:

• Retain logs based on backup frequency
• Example:
  • Full backup daily → Keep binlogs at least 2–3 days

Point-in-Time Recovery (PITR)

PITR allows restoring the database to a specific moment, such as before a failure or accidental deletion.

Steps for Point-in-Time Recovery

Step 1: Take Full Backup

mysqldump -u root -p --all-databases > full_backup.sql

Step 2: Restore Backup

mysql -u root -p < full_backup.sql

Step 3: Identify Recovery Time or Position

Check binary logs:

mysqlbinlog mysql-bin.000001

Find:

• Timestamp
• Log position

Step 4: Apply Binary Logs

Recover up to specific time:

mysqlbinlog --stop-datetime="2026-05-01 10:30:00" mysql-bin.000001 | mysql -u root -p

Recover up to specific position:

mysqlbinlog --stop-position=1200 mysql-bin.000001 | mysql -u root -p

Step 5: Verify Data

Ensure:

• Data consistency
• No missing transactions

Practical Example

Scenario:

• Data deleted at 10:30 AM
• Backup taken at 9:00 AM

Solution:

1. Restore 9:00 AM backup
2. Replay binary logs until 10:29 AM
3. Stop before DELETE operation

Best Practices

• Use ROW format for production systems
• Set binlog_row_image=MINIMAL for performance
• Always maintain regular backups
• Monitor disk usage regularly
• Test PITR periodically
• Store binlogs on separate storage if possible

Conclusion

MySQL binary logs are essential for:

• Replication reliability
• Data recovery
• Audit tracking

Understanding formats like Statement, Row, and Mixed, along with row image controls, helps optimize performance and storage. Combined with proper retention policies and recovery strategies, binary logs become a powerful tool for maintaining database integrity.