August 15, 2026
9 min read
Table of Contents
By Kokil Thapa | Last reviewed: August 2026
If you run a production database behind a Laravel or PHP application, relying solely on nightly logical dumps is a data-loss risk. MySQL binary logs for replication and backup provide the transaction-level history required for point-in-time recovery (PITR) and asynchronous replication, bridging the gap between your last full backup and the moment of failure. Properly configuring these logs is not optional for business-critical systems; it is the foundation of any resilient database-driven website development in Nepal or global infrastructure where downtime directly impacts revenue.
Quick answer: MySQL binary logs for replication and backup are sequential event files recording every data-modifying transaction. Enable them via
log_bin, set binlog_format=ROW for safety, configure retention with binlog_expire_logs_seconds, and use mysqlbinlog combined with full backups to achieve point-in-time recovery and reliable replica synchronization.How do you correctly configure MySQL binary logs for replication and backup?
Enabling binary logging requires specific parameters in your my.cnf or mysqld.cnf file. The default configuration on many Ubuntu 24.04 servers is either disabled or tuned for minimal disk usage rather than durability. For production workloads running MySQL 8.0 or 8.4 LTS, you must explicitly define the format, expiration, and synchronization behavior.
In my experience maintaining legal-tech portals and eCommerce platforms, the most common failure mode is not the binary log itself but the mismatch between the server ID and the application's expectation of consistency. Every server in a replication topology must have a unique server-id. If you clone a VM or restore a snapshot without changing this ID, replication will silently fail or create data corruption loops.
[mysqld]
# Unique ID for this server (1-4294967295)
server-id = 1
# Enable binary logging with a descriptive prefix
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
# Retention: 7 days (604800 seconds)
binlog_expire_logs_seconds = 604800
# Durability: Sync to disk on every commit (safest)
sync_binlog = 1
# GTID mode for modern replication (recommended for MySQL 8.x)
gtid_mode = ON
enforce_gtid_consistency = ON
# Performance: Use CRC32 checksums to detect corruption
binlog_checksum = CRC32
# Limit individual file size to ease rotation/purging
max_binlog_size = 1073741824The binlog_format=ROW setting is non-negotiable for modern applications. Statement-based logging (SBR) records SQL queries verbatim, which breaks when functions like NOW(), UUID(), or user-defined variables produce different results on replicas. Row-based logging records the actual changed rows, ensuring deterministic replay. While RBR generates larger log files, the trade-off for data integrity is always worth it in production.
Setting sync_binlog=1 forces the operating system to flush the binary log to disk after every transaction commit. This is the only safe setting for primary databases handling payments or legal records. Setting it to 0 or higher values improves write throughput significantly but risks losing transactions during a power failure. On systems where IOPS are constrained, ensure your storage subsystem has a battery-backed write cache or uses enterprise NVMe; otherwise, the synchronous writes will become your primary bottleneck.
What is the difference between statement, row, and mixed binlog formats?
Choosing the right binary log format determines whether your replicas stay consistent and whether your point-in-time recovery actually works. MySQL supports three modes, but in 2026, only one should be your default.
| Format | Consistency | Log Size | Replication Safety | Best For |
|---|---|---|---|---|
| ROW | Deterministic | Larger | Highest | All production apps, especially Laravel/PHP |
| STATEMENT | Non-deterministic risk | Smallest | Low | Legacy read-only analytics only |
| MIXED | Variable | Medium | Medium | Specific ETL workloads with known safe queries |
Row-based replication logs the before-and-after image of each modified row. This eliminates ambiguity caused by non-deterministic functions. When building Laravel APIs that rely on timestamps, UUID generation, or JSON column updates, SBR will inevitably cause replica drift. RBR also enables features like parallel replication and change data capture (CDC) tools that parse binary logs for search indexing or audit trails.
The downside of RBR is volume. A bulk UPDATE affecting 100,000 rows generates significantly more binary log data in ROW format than in STATEMENT format. To mitigate this, ensure binlog_row_image=FULL (the default) unless you have a specific reason to reduce it. Also, verify that all tables have explicit primary keys; without them, RBR must log all columns for identification, exploding log size and slowing replica apply threads.
How do you perform point-in-time recovery using mysqlbinlog?
Point-in-time recovery combines your last full physical or logical backup with the binary logs generated since that backup. This is the core value proposition of MySQL binary logs for replication and backup. Without them, you can only restore to the exact moment of your last dump, potentially losing hours of transactions.
- Restore the base backup: Load your most recent full backup taken before the target recovery time. Ensure the server is started with
skip_replica_startif restoring to a replica, or in single-user mode if restoring a primary. - Identify the binary log range: Check your backup metadata for the binlog file and position at the time of backup. Use
SHOW BINARY LOGS;to list available files on the source. - Extract relevant events: Use
mysqlbinlogwith start/stop datetime or position arguments to generate a SQL replay script. - Apply the events: Pipe the output into the MySQL client to replay transactions up to the exact second before the incident.
# Example: Recover transactions between two timestamps
mysqlbinlog \
--start-datetime="2026-08-14 02:00:00" \
--stop-datetime="2026-08-14 02:45:30" \
/var/log/mysql/mysql-bin.000142 \
/var/log/mysql/mysql-bin.000143 \
| mysql -u root -p recovery_db
# Using GTIDs (preferred for MySQL 8.x)
mysqlbinlog \
--include-gtids="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-5482" \
/var/log/mysql/mysql-bin.000142 \
| mysql -u root -p recovery_dbA critical operational detail: always decode binary logs to a file first before piping to MySQL when recovering a production system. This allows you to inspect the SQL statements for accidental destructive operations. I have seen recoveries go wrong because an automated script replayed a DROP TABLE that was captured in the binlog window. Manual inspection of the intermediate SQL file prevents compounding disasters.
For Laravel applications using migrations, remember that schema changes are also recorded in the binary log. If you are recovering to a point mid-migration, the database state may be inconsistent with your application code version. Always align your application deployment rollback with your database recovery point. This coordination is often overlooked in MySQL optimization strategies but is vital for clean recovery.
How do you manage binary log retention and disk space safely?
Binary logs consume disk space proportional to write volume. On a busy eCommerce site during peak seasons like Dashain or Black Friday, log generation can exceed 50GB per day. Unmanaged growth will fill your partition and crash MySQL. Automatic expiration is mandatory.
The binlog_expire_logs_seconds variable replaced the deprecated expire_logs_days in MySQL 8.0. Set it based on your backup frequency and recovery objectives. If you take daily full backups at 02:00 and need 7 days of PITR capability, set expiration to at least 8 days (691200 seconds) to account for backup duration variance. Never set expiration shorter than your backup interval.
-- Check current binary log status
SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
-- Manually purge old logs (safe, respects active replicas)
PURGE BINARY LOGS BEFORE '2026-08-07 00:00:00';
-- Or purge by filename
PURGE BINARY LOGS TO 'mysql-bin.000130';
-- Monitor disk usage in real-time
SELECT
ROUND(SUM(File_size) / 1024 / 1024, 2) AS total_mb,
COUNT(*) AS file_count
FROM performance_schema.log_status;A dangerous anti-pattern is manually deleting binary log files from the filesystem using rm. This corrupts MySQL's internal index and can prevent the server from starting. Always use PURGE BINARY LOGS or let automatic expiration handle cleanup. If you must reclaim space urgently during an outage, use PURGE aggressively; it is replica-aware and will not delete logs still needed by connected replicas.
On servers with limited SSD capacity, consider mounting binary logs on a separate volume. This isolates log I/O from data directory I/O and prevents log growth from impacting query performance. Ensure the separate volume is included in your backup strategy; losing binary logs means losing PITR capability even if your data files are intact.
What are the best practices for monitoring binary log health in production?
Monitoring binary logs goes beyond checking disk space. You need visibility into replication lag, log rotation frequency, and error states. Silent failures are the enemy of disaster recovery preparedness.
- Replication Lag: Monitor
Seconds_Behind_Sourceon replicas. Sustained lag indicates the replica cannot keep up with binary log generation, meaning your PITR window is effectively reduced. - Error Detection: Watch
Last_IO_ErrorandLast_SQL_ErrorinSHOW REPLICA STATUS. Binary log corruption or format mismatches surface here first. - Rotation Rate: Frequent rotation (multiple times per hour) suggests
max_binlog_sizeis too small, increasing overhead. Infrequent rotation (days) makes individual file management harder. Target 1-4 rotations per day for most workloads. - GTID Consistency: Verify
Gtid_Executedsets are contiguous across replicas. Gaps indicate missed transactions or improper failover procedures.
Integrate these checks into your existing monitoring stack. Prometheus exporters for MySQL expose binary log metrics natively. Alert on replica lag exceeding 300 seconds and on any non-empty error fields. Test your recovery procedure quarterly; untested backups are merely hopes. For teams managing multiple client projects, documenting these runbooks prevents panic during 3 AM incidents.
Implementing Resilient MySQL Binary Logs for Replication and Backup
Configuring MySQL binary logs for replication and backup correctly transforms your database from a fragile single point of failure into a recoverable, replicable system. Use ROW format, enforce synchronous binlog writes on primaries, automate expiration aligned with your backup schedule, and validate recovery procedures regularly. These practices apply equally to a solo Laravel project and a multi-node eCommerce cluster.
If your current setup lacks binary logging or uses statement-based replication, prioritize migration during your next maintenance window. The cost of disk space is trivial compared to the cost of unrecoverable data loss. For architecture review, migration planning, or production database hardening, contact me to discuss your specific infrastructure requirements.
Frequently Asked Questions
Binary logs record all data-modifying statements in chronological order. Replicas read these events to replay changes exactly as they occurred on the source, ensuring data consistency across nodes.
Consumption depends entirely on write volume and retention settings. A busy eCommerce site might generate 50GB daily, while a low-traffic legal portal uses under 1GB. Always monitor with du -sh /var/lib/mysql before setting max_binlog_size.
Enable it immediately if you need point-in-time recovery or plan future replication. The performance overhead is negligible compared to the safety benefit of restoring to any specific second after a failure.
Add log_bin=mysql-bin, binlog_format=ROW, server_id=1, and expire_logs_days=7 under [mysqld]. ROW format is mandatory for reliable replication in modern MySQL versions. Restart the service and verify with SHOW VARIABLES LIKE 'log_bin'. In my experience deploying Laravel applications on Ubuntu servers, forgetting to set a unique server_id causes silent replication failures that only surface during disaster recovery testing.
ROW format logs actual row changes rather than SQL statements, preventing non-deterministic query issues on replicas. STATEMENT format saves space but breaks when queries use NOW() or UUID(). MIXED attempts automatic switching but adds complexity. For production Laravel or WooCommerce systems handling payments and orders, I always enforce ROW format because data integrity matters more than marginal storage savings. The slight increase in log size prevents subtle replication drift that could corrupt financial records or customer data.
No. Binary logs only contain incremental changes since the last full backup. You need a base backup from mysqldump or Percona XtraBackup plus all subsequent binary logs to restore to a specific timestamp. On client projects, I schedule nightly XtraBackup runs and retain seven days of binary logs, allowing restoration to any second within that window. Relying solely on binary logs without a verified base backup is a common mistake that makes recovery impossible after corruption or accidental deletion.
Never delete files manually from the filesystem. Use PURGE BINARY LOGS BEFORE '2026-08-01' or SET GLOBAL expire_logs_days=7. Before purging, confirm all replicas have processed those positions with SHOW REPLICA STATUS. On shared EC2 infrastructure running multiple sister sites, I automate this via cron after verifying replica lag is zero. Manual deletion corrupts the binary log index file and crashes replication threads, requiring expensive rebuilds from fresh backups.
Disk failures, abrupt shutdowns, or full partitions cause corruption. Monitor with mysqlbinlog --verify-binlog-checksum and check error logs for "Binlog checksum mismatch" warnings. Implement disk space alerts at 80% capacity because full partitions truncate active logs mid-write. In production environments, I run weekly verification scripts and maintain redundant monitoring through both application-level health checks and OS-level disk watchers. Corruption often goes unnoticed until a replica fails or recovery is attempted, making proactive detection essential for business-critical databases.
Expect 5-15% write overhead depending on sync settings. sync_binlog=1 guarantees durability but forces disk flushes every transaction. Setting it to 0 improves throughput significantly but risks losing recent transactions on crash. For payment processing systems like Nepal Gift Card, I keep sync_binlog=1 despite the cost. For content-heavy directories where occasional data loss is acceptable, relaxing this setting provides meaningful performance gains. Benchmark your specific workload with sysbench before changing defaults in production.
Restrict file permissions to mysql:mysql with 640 mode. Encrypt logs using binlog_encryption=ON in MySQL 8.0+. Never store logs on publicly accessible volumes or include them in unencrypted backups. Binary logs contain raw customer data, payment details, and authentication tokens in recoverable form. On legal-tech portals handling case documents, I treat binary logs with the same security controls as the primary database. Audit access regularly and ensure backup encryption keys are stored separately from the encrypted log files themselves.
Check Seconds_Behind_Source in SHOW REPLICA STATUS first. High values indicate the replica cannot apply events fast enough. Common causes include missing indexes on replica tables, long-running transactions on source, or insufficient replica hardware. Enable replica_parallel_workers=4 or higher for multi-threaded applying. On WooCommerce stores during sale events, I temporarily scale replica resources and monitor lag metrics. Persistent lag often reveals schema mismatches or inefficient queries that also degrade application performance independently of replication.
Binary logs exist on the source and record all changes. Relay logs exist on replicas and store fetched binary log events before local execution. The replica IO thread writes to relay logs; the SQL thread reads and applies them. Understanding this separation helps diagnose whether network transfer or local application is the bottleneck. When troubleshooting replication issues on client deployments, checking relay log position versus binary log position quickly identifies whether the problem lies in fetching events or executing them locally.
Execute FLUSH BINARY LOGS to close the current file and open a new one instantly. This is safe during production and requires no downtime. Schedule rotations during low-traffic periods to minimize impact on concurrent writes. After flushing, verify the new file appears in SHOW BINARY LOGS. On Deployer-managed deployments where database maintenance windows are coordinated with application releases, I incorporate log rotation into deployment hooks. This ensures clean log boundaries align with release timestamps, simplifying post-deployment debugging if issues arise.
Yes, but with significant caveats. Binary logs capture row-level changes without application context like user IDs or request metadata. Correlating database events to specific users requires joining with application logs using timestamps and transaction IDs. For proper auditing in legal-tech platforms, I implement dedicated audit tables at the application layer instead. Binary logs serve as a forensic fallback when application logging fails, not as a primary audit mechanism. They lack the semantic meaning needed for compliance reporting or user activity tracking.
MySQL stops accepting writes immediately when the partition reaches capacity. The server remains running but all INSERT, UPDATE, and DELETE operations fail with "disk full" errors. Replicas continue reading existing logs but cannot fetch new events. Recovery requires freeing space by purging old logs or expanding the partition, then restarting affected services. On Ubuntu servers hosting multiple applications, I configure separate partitions for /var/lib/mysql and implement aggressive monitoring alerts at 70% usage. Preventing this scenario is far cheaper than emergency recovery during business hours.
