MySQL Binary Logs for Replication and Backup

By Khimananda Oli | Last reviewed: August 2026

MySQL Binary Logs for Replication and Backup are the single most critical component for data durability and high availability in any production MySQL environment. Without properly configured binary logs, you cannot perform point-in-time recovery (PITR), set up asynchronous or semi-synchronous replication, or audit write operations for compliance. This guide provides the exact configuration patterns I use when installing MySQL on Ubuntu servers to ensure audit-ready, recoverable database infrastructure.

How do you configure MySQL Binary Logs for Replication and Backup correctly?

The difference between a fragile database and a resilient one often comes down to five lines of configuration. In practice, most out-of-the-box MySQL installations have binary logging disabled or misconfigured for modern workloads. You must explicitly enable and tune these settings in your /etc/mysql/mysql.conf.d/mysqld.cnf file.

Essential my.cnf parameters

[mysqld]
# Core binary log settings
server-id                = 1
log_bin                  = /var/log/mysql/mysql-bin
binlog_format            = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON

# Retention and rotation
expire_logs_days         = 7
max_binlog_size          = 1073741824
binlog_expire_logs_seconds = 604800

# Safety and performance
sync_binlog              = 1
innodb_flush_log_at_trx_commit = 1
binlog_row_image         = FULL
log_slave_updates        = ON

• server-id: Must be unique across all servers in a replication topology. Use a deterministic scheme like IP-based or rack-based IDs rather than random numbers.
• binlog_format=ROW: Always prefer ROW format over STATEMENT or MIXED. Row-based logging captures actual data changes, making replication deterministic and safe for triggers, stored procedures, and non-deterministic functions.
• gtid_mode=ON: Global Transaction Identifiers simplify failover, allow easy replica promotion, and eliminate the fragility of file-position-based replication. There is no reason to run without GTIDs in 2026.
• sync_binlog=1: Forces the binary log to flush to disk on every commit. Combined with innodb_flush_log_at_trx_commit=1, this guarantees zero committed transactions are lost on crash. The performance cost is real but acceptable for any system where data loss is unacceptable.
• binlog_row_image=FULL: Captures both before and after images of every row change. This is essential for tools like Debezium, Maxwell, or custom CDC pipelines that need to understand what changed, not just the new state.

Validating your configuration

After restarting MySQL, verify the active settings:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'gtid_mode';
SHOW BINARY LOGS;

If SHOW BINARY LOGS returns an error, binary logging is not enabled. Check your error log at /var/log/mysql/error.log for permission issues on the binlog directory. The MySQL process owner must have write access to the configured path.

How does point-in-time recovery work with MySQL binary logs?

Point-in-time recovery combines a full logical or physical backup with binary log replay to restore a database to any exact moment. This is the primary defense against accidental DELETE statements, ransomware, or application bugs that corrupt data gradually. If you follow Ubuntu server backup strategies, you already know that backups without tested restores are worthless.

The PITR workflow

1. Restore the base backup: Load your most recent full backup taken before the target recovery point. For mysqldump backups, use mysql < backup.sql. For Percona XtraBackup, use xtrabackup --prepare followed by --copy-back.
2. Identify the target position: Determine the exact timestamp or GTID where you want to stop recovery. Use mysqlbinlog --start-datetime="2026-08-17 10:00:00" --stop-datetime="2026-08-17 10:15:30" /var/log/mysql/mysql-bin.000142 to inspect events.
3. Replay binary logs: Apply all binlog events from the backup's position up to your target stop point using mysqlbinlog | mysql.
4. Verify integrity: Run application-level checks, row counts, and checksums to confirm the restored state matches expectations.

Practical PITR command sequence

# Step 1: Restore base backup
mysql -u root -p < /backups/full-2026-08-17-0200.sql

# Step 2: Find the GTID executed in the backup
grep "GTID" /backups/full-2026-08-17-0200.sql | head -1

# Step 3: Replay binlogs from backup GTID to incident time
mysqlbinlog \
  --include-gtids='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-45892' \
  --exclude-gtids='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:45893' \
  /var/log/mysql/mysql-bin.000142 \
  /var/log/mysql/mysql-bin.000143 \
  | mysql -u root -p

The --exclude-gtids parameter is critical. It prevents replaying the destructive transaction itself while applying everything before it. Always test PITR procedures quarterly in a staging environment that mirrors production. Untested recovery plans fail during actual incidents.

What are the trade-offs between binary log formats and retention strategies?

Choosing the right binary log format and retention policy involves balancing storage costs, replication safety, and operational complexity. Teams running MySQL master-slave replication often discover these trade-offs only after hitting production issues.

For retention, align binlog_expire_logs_seconds with your backup frequency and RPO. If you take daily full backups at 2 AM and your RPO is 24 hours, retain at least 8 days of binlogs to handle backup failures gracefully. For SOC 2 or ISO 27001 compliance, many organizations retain 30–90 days. Store older binlogs in object storage (S3, R2) using automated scripts rather than keeping them on expensive primary storage.

Automated binlog archival pattern

#!/bin/bash
# Archive rotated binlogs to S3-compatible storage
BINLOG_DIR="/var/log/mysql"
BUCKET="s3://company-db-binlogs/production"

for f in $(ls ${BINLOG_DIR}/mysql-bin.0* | grep -v '\.index$' | tail -n +2); do
  BASENAME=$(basename "$f")
  if ! aws s3 ls "${BUCKET}/${BASENAME}" >/dev/null 2>&1; then
    aws s3 cp "$f" "${BUCKET}/${BASENAME}" --storage-class GLACIER_IR
    echo "$(date -Iseconds) Archived ${BASENAME}"
  fi
done

Run this script via cron immediately after each full backup completes. Never delete binlogs from primary storage until they are confirmed archived and verified. I have seen teams lose PITR capability because their archival script silently failed for weeks.

How do you monitor and troubleshoot binary log issues in production?

Binary logs introduce operational overhead that demands monitoring. Disk exhaustion from unrotated binlogs is the #1 cause of unplanned MySQL outages in environments I have audited. Integrate binlog metrics into your Prometheus and Grafana monitoring stack before going live.

Critical metrics to track

• Binlog disk usage: Alert when total binlog size exceeds 80% of allocated partition. Use du -sb /var/log/mysql/mysql-bin.* | awk '{sum+=$1} END {print sum}' in a Prometheus exporter.
• Replication lag: Monitor Seconds_Behind_Master and GTID gaps. Lag exceeding your RTO threshold indicates replica inability to keep pace with binlog generation rate.
• Binlog rotation frequency: Frequent rotations (more than hourly) suggest max_binlog_size is too small or write volume has spiked unexpectedly.
• Sync latency: With sync_binlog=1, monitor commit latency. Sustained increases indicate storage subsystem degradation.

Common failure modes and fixes

Disk full errors: Immediately increase expire_logs_days reduction or manually purge old logs with PURGE BINARY LOGS BEFORE '2026-08-10 00:00:00'. Then investigate why retention exceeded capacity. Add disk space alerts at 70%, 80%, and 90% thresholds.

Replica falling behind: Check if the replica is single-threaded. Enable parallel replication with replica_parallel_workers=4 and replica_parallel_type=LOGICAL_CLOCK. Verify network bandwidth between primary and replica. Consider semi-synchronous replication if async lag is consistently problematic.

Corrupt binlog files: Run mysqlbinlog --verify-binlog-checksum /var/log/mysql/mysql-bin.000XXX to validate integrity. Corruption typically indicates storage hardware failure. Replace the affected disk and restore from replica or backup. Enable binlog_checksum=CRC32 (default since MySQL 5.6) to detect corruption early.

Implementing MySQL Binary Logs for Replication and Backup in Production

MySQL Binary Logs for Replication and Backup are non-negotiable for any system where data loss is unacceptable or compliance requires audit trails. Configure ROW format with GTIDs enabled, set retention to match your RPO plus buffer, automate archival to cheap object storage, and integrate monitoring before your first production deployment. Test your PITR procedure quarterly—documented but untested recovery is a liability, not an asset. If your current setup lacks any of these elements, prioritize fixing them this sprint. Reach out via the contact page if you need help auditing your MySQL replication topology or designing a compliant backup architecture.