Back to tutorials
Tutorial

MySQL Binary Logging Configuration Tutorial: Complete Point-in-Time Recovery and Replication Setup for Production VPS in 2026

Master MySQL binary logging configuration for VPS. Complete tutorial covering binlog setup, point-in-time recovery, and replication configuration.

By Anurag Singh
Updated on May 21, 2026
Category: Tutorial
Share article
MySQL Binary Logging Configuration Tutorial: Complete Point-in-Time Recovery and Replication Setup for Production VPS in 2026

Understanding MySQL Binary Logging Fundamentals

MySQL binary logging records every data-changing operation on your database server. These binary logs (binlogs) serve two critical functions: enabling point-in-time recovery after disasters and powering MySQL replication between servers.

Your VPS database needs proper binlog configuration to handle production workloads safely. Binary logs differ from error logs or slow query logs. They contain actual SQL statements and row changes in binary format, making them essential for data recovery scenarios.

Prerequisites for Binary Log Setup

Before configuring MySQL binary logging, verify your server meets these requirements:

  • MySQL 8.0 or MariaDB 10.6+ installed on Ubuntu 22.04/24.04 or AlmaLinux 9
  • Root or sudo access to your VPS
  • At least 2GB available disk space for log files
  • Database downtime window for configuration changes

Check your current MySQL version and binlog status:

sudo mysql -u root -p
SELECT VERSION();
SHOW VARIABLES LIKE 'log_bin%';
SHOW BINARY LOGS;

MySQL Binary Logging Configuration Steps

Edit your MySQL configuration file. The location varies by distribution:

# Ubuntu/Debian
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# AlmaLinux/RHEL/CentOS
sudo nano /etc/my.cnf

Add these settings under the [mysqld] section:

[mysqld]
# Binary logging configuration
log-bin = /var/log/mysql/mysql-bin
server-id = 1
binlog_format = ROW
binlog_row_image = FULL
max_binlog_size = 100M
expire_logs_days = 7
binlog_cache_size = 32K
sync_binlog = 1

Create the binary log directory with proper permissions:

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

Binary Log Format Selection and Impact

MySQL offers three binary log formats. Each has distinct characteristics.

ROW format records actual row changes. This format provides the most accurate replication but generates larger log files. Use ROW for production systems requiring data consistency.

STATEMENT format logs the actual SQL statements. While compact, it can cause replication inconsistencies with non-deterministic functions like NOW() or RAND().

MIXED format automatically switches between ROW and STATEMENT based on the query type. This balances file size with consistency but adds complexity.

For most VPS deployments, ROW format offers the best reliability despite larger file sizes.

Server ID Configuration for Replication

Every MySQL server requires a unique server-id when binary logging is enabled. This identifier distinguishes servers in replication topologies. It also prevents circular loops.

Generate unique server IDs using IP addresses or systematic numbering:

# Master server
server-id = 1

# First replica
server-id = 2

# Second replica  
server-id = 3

Avoid server-id conflicts by maintaining a central registry for production environments.

Binary Log Security and Access Control

Binary logs contain sensitive data requiring proper security measures. Configure file permissions and access controls:

# Secure binary log files
sudo find /var/log/mysql -name "mysql-bin.*" -exec chmod 640 {} \;
sudo find /var/log/mysql -name "mysql-bin.*" -exec chown mysql:mysql {} \;

Create a dedicated replication user with minimal privileges:

CREATE USER 'replication'@'%' IDENTIFIED BY 'SecurePassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Need a production-ready VPS for your MySQL setup? HostMyCode's managed VPS hosting includes pre-configured MySQL instances with optimized binlog settings and automated backup management.

Point-in-Time Recovery Implementation

Binary logs enable precise recovery to any specific moment. This process requires both full backups and binary log archives.

Create a baseline backup using mysqldump with consistent positions:

mysqldump --single-transaction --routines --triggers \
  --master-data=2 --all-databases \
  > full_backup_$(date +%Y%m%d_%H%M%S).sql

The --master-data=2 option records the binary log position in the dump file as comments.

Recovery example restoring to a specific timestamp:

# Restore full backup
mysql -u root -p < full_backup_20260315_143022.sql

# Apply binary logs up to target time
mysqlbinlog --stop-datetime="2026-03-15 15:30:00" \
  /var/log/mysql/mysql-bin.000045 | mysql -u root -p

Binary Log Rotation and Retention Management

Configure automatic log rotation to prevent disk space exhaustion. MySQL provides several rotation mechanisms.

Size-based rotation triggers when logs exceed max_binlog_size:

max_binlog_size = 100M

Time-based retention automatically purges old logs:

# MySQL 8.0+ syntax
binlog_expire_logs_seconds = 604800  # 7 days

# Legacy syntax (still supported)
expire_logs_days = 7

Manual purge commands for immediate cleanup:

# Purge logs older than specific date
PURGE BINARY LOGS BEFORE '2026-03-08 00:00:00';

# Purge logs up to specific file
PURGE BINARY LOGS TO 'mysql-bin.000045';

Monitoring Binary Log Performance Impact

Binary logging adds overhead to write operations. Monitor these key metrics to assess impact:

# Check binary log status
SHOW VARIABLES LIKE 'log_bin%';
SHOW MASTER STATUS;

# Monitor binlog cache usage
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';

Key performance indicators include:

  • Binlog_cache_disk_use - Cache spills to disk (should be low)
  • Binlog_cache_use - Total cache utilization
  • Binlog_stmt_cache_disk_use - Statement cache disk usage

Tune binlog_cache_size if disk usage exceeds 1% of total cache usage.

Troubleshooting Common Binary Log Issues

Binary log disk space problems require immediate attention. Monitor disk usage and implement alerts:

# Check binary log disk usage
du -sh /var/log/mysql/
ls -lah /var/log/mysql/mysql-bin.*

Permission errors often occur after manual file operations:

# Fix ownership recursively
sudo chown -R mysql:mysql /var/log/mysql/
sudo chmod 750 /var/log/mysql/
sudo chmod 640 /var/log/mysql/mysql-bin.*

Corruption detection using mysqlbinlog verification:

# Test binary log integrity
mysqlbinlog --verify-binlog-checksum /var/log/mysql/mysql-bin.000045

Replication Setup Using Binary Logs

Binary logs power MySQL's master-slave replication. Configure the replica server connection:

# On replica server
CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='replication',
  MASTER_PASSWORD='SecurePassword123!',
  MASTER_LOG_FILE='mysql-bin.000045',
  MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

Monitor replication lag and connection status regularly. The database failover configuration tutorial covers advanced replication scenarios.

Advanced Binary Log Configuration Options

Production environments benefit from these advanced settings:

[mysqld]
# Enhanced binary logging
binlog_checksum = CRC32
binlog_rows_query_log_events = ON
binlog_transaction_compression = ON
binlog_transaction_compression_level_zstd = 3
log_slave_updates = ON
gtid_mode = ON
enforce_gtid_consistency = ON

GTID (Global Transaction Identifier) simplifies replication management. It provides unique identifiers for every transaction across your database cluster.

Binary Log Backup Integration

Include binary logs in your backup strategy using dedicated tools. The MySQL index optimization tutorial explains complementary database maintenance practices.

Automated backup script example:

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Flush logs to create clean break
mysql -u root -p -e "FLUSH LOGS;"

# Copy binary logs
cp /var/log/mysql/mysql-bin.* "$BACKUP_DIR/binlogs_$DATE/"

# Create full backup
mysqldump --single-transaction --master-data=2 \
  --all-databases > "$BACKUP_DIR/full_$DATE.sql"

Frequently Asked Questions

How much disk space do MySQL binary logs consume?

Binary log size depends on your write volume and format. ROW format typically uses 2-5x more space than STATEMENT format.

A busy e-commerce site might generate 1-2GB of binary logs daily. A read-heavy application produces much less.

Can I enable binary logging on a running production server?

Yes, but it requires a MySQL restart. Plan a maintenance window since enabling binary logging changes server behavior.

Performance may briefly drop as caches warm up after the restart.

What happens if binary logs fill up my disk?

MySQL stops accepting writes when binary log disk space is exhausted. Implement monitoring alerts and automated purging to prevent this scenario.

Emergency recovery requires manual log purging or disk expansion.

Should I use sync_binlog=1 for all workloads?

sync_binlog=1 ensures durability by forcing disk writes after each transaction but reduces performance. Use it for financial or critical data.

Consider sync_binlog=0 for development or non-critical workloads where performance matters more than perfect durability.

How do I migrate binary logs to a new server?

Copy binary logs and relay logs to the new server, ensuring identical MySQL versions. Update server-id values to avoid conflicts.

The database migration strategies guide covers comprehensive migration procedures.