Skip to main content

Binary logs

Learning Focus

Use this lesson to understand Binary logs with practical syntax and examples.

Here is a complete MySQL learning module tailored to the topic of Backup and Recovery: Binary Logs for beginner to intermediate learners.

Backup and Recovery: Binary Logs in MySQL

Concept Overview​

What Are Binary Logs?​

In MySQL, binary logs are a set of log files that record all changes made to the database, such as data modifications (INSERT, UPDATE, DELETE) and data definition changes (ALTER TABLE, etc.). They do not log SELECT queries.

Why Are Binary Logs Important?​

  • Recovery: Used to restore data after a crash by replaying events from a backup point.
  • Replication: Crucial for master-slave replication setups.
  • Auditing: Useful for tracking changes in sensitive environments.

Where It Fits in the Database Workflow​

Binary logs are generated by the MySQL server when binary logging is enabled. They are part of a broader backup and recovery strategy, often paired with physical or logical backups like mysqldump or Percona XtraBackup.

Basic Syntax & Rules​

Enabling Binary Logs​

Edit my.cnf or my.ini file:

[mysqld]
log-bin=mysql-bin
server-id=1

Restart the server after editing the config.

Checking Binary Log Status​

SHOW VARIABLES LIKE 'log_bin';

Viewing Binary Log Files​

SHOW BINARY LOGS;

Viewing Events in a Binary Log​

SHOW BINLOG EVENTS IN 'mysql-bin.000001';

Restoring from Binary Logs​

mysqlbinlog --start-datetime="2025-07-01 10:00:00" \
            --stop-datetime="2025-07-01 12:00:00" \
            /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

Important Considerations​

  • Binary logs must be enabled before a crash to be useful for recovery.
  • They can consume disk space rapidly; configure log rotation (expire_logs_days).
  • Replication integrity depends on them.

Step-by-Step Examples​

Example 1: Enable and View Binary Logs​

Step 1: Edit config file

[mysqld]
log-bin=mysql-bin

Step 2: Restart MySQL

Step 3: Verify

SHOW VARIABLES LIKE 'log_bin';

Expected Output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

Example 2: Simulate Changes and View Logs​

Step 1: Create a table and insert data

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    role VARCHAR(50)
);

INSERT INTO employees (name, role)
VALUES ('Alice', 'Engineer'), ('Bob', 'Manager');

Step 2: Check current binary logs

SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

Example 3: Replay Binary Log Events​

mysqlbinlog /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

Use --start-position and --stop-position for partial recovery.

Practical Use Cases​

1. Crash Recovery​

Problem: Server crashes at 3 PM

Solution: Restore last full backup (e.g., from midnight) and replay binary logs from midnight to 3 PM.

2. Replication Setup​

Binary logs are the foundation of asynchronous replication between MySQL servers.

3. Audit Trail​

DBAs or compliance teams can use them to track changes made to critical tables.

4. Point-In-Time Recovery (PITR)​

Use binary logs to recover the database to a specific time - useful after accidental DROP or DELETE.

5. Migrating Servers​

Binary logs can be exported and applied to a new server to replicate recent changes after a snapshot.

Common Mistakes & Troubleshooting​

MistakeExplanationSolution
Not enabling binary logsDefault in some versions is OFFSet log-bin in config
Logs fill up diskNo rotation policyUse expire_logs_days = 7
Logs missing after crashNot using sync_binlog=1Set sync_binlog=1 for durability
Replaying bad logsApplying logs without filteringUse --start-datetime, --stop-datetime or --database carefully

Best Practices​

  • Enable binary logs in production always.
  • Set expire_logs_days to avoid space issues.
  • Use sync_binlog=1 for safer writes.
  • Use timestamps or positions when restoring logs.
  • Pair binary logs with full backups for PITR.
  • Automate binary log archiving and pruning.

Hands-On Practice​

Exercise 1: Enable and Verify Binary Logs​

Task: Modify config to enable logs, restart MySQL, and verify status.

[OK] Expected Output:

| log_bin       | ON    |

Exercise 2: Simulate Events​

Task: Create table products, insert records, and list binary events.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(8,2)
);

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00), ('Mouse', 25.00);

Then run:

SHOW BINLOG EVENTS;

Exercise 3: Partial Restore​

Use mysqlbinlog with --start-datetime and --stop-datetime to simulate recovery.

mysqlbinlog --start-datetime="2025-07-01 10:00:00" \
            --stop-datetime="2025-07-01 10:05:00" \
            /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

Connection to Other Concepts​

Related ConceptsDescription
ReplicationBinary logs are used to send changes from master to slave.
mysqldump / xtrabackupUse these tools to create base backup before applying logs.
PITRRequires full backup + binary logs.
Event SchedulerCan be used to rotate or back up binary logs.

Prerequisites​

  • Understanding of mysqldump
  • Basic SQL operations (CREATE, INSERT, etc.)

What to Learn Next​

  • GTID-based replication
  • Logical backup with mysqldump
  • Percona XtraBackup
  • Incremental backups

Visual Learning Diagram​

flowchart TD
  A[Basic SQL Concepts]:::allNodes --> B[MySQL Server Architecture]:::allNodes
  B --> C[MySQL Configuration Files]:::allNodes
  C --> D[Binary Logs (Current Topic)]:::allNodes
  D --> E[Crash Recovery with Binary Logs]:::allNodes
  D --> F[Replication Setup Using Binary Logs]:::allNodes
  D --> G[PITR with Binary Logs + Backups]:::allNodes
  G --> H[Percona XtraBackup]:::allNodes
  G --> I[Incremental Backup Strategy]:::allNodes

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  class D stroke-width:4px

Common Pitfalls​

PitfallConsequencePrevention
Running destructive commands without contextDatabases or tables are dropped unexpectedlyConfirm target with SELECT DATABASE(); and run in a controlled environment first
Skipping backups before schema/data changesRecovery window becomes long or impossibleCreate a backup snapshot and test restore before production changes
Not validating privileges and locksOperations fail midway or block trafficCheck grants, active sessions, and maintenance window constraints

Quick Reference​

SHOW BINARY LOGS;
SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN "binlog.000001" LIMIT 20;

What's Next​