Database Binary Logging Tutorial: Complete MySQL and PostgreSQL WAL Configuration for VPS Backup and Recovery in 2026

Understanding Database Binary Logging

Database binary logging captures every data modification in a sequential log file. This enables precise point-in-time recovery and replication. For VPS administrators, this logging mechanism represents the difference between losing hours of data and recovering to the exact moment before a failure.

MySQL's binary log records INSERT, UPDATE, DELETE, and DDL statements. PostgreSQL's Write-Ahead Log (WAL) provides similar functionality with additional crash recovery benefits. Both systems write changes to disk before applying them to data files.

Your VPS database requires binary logging for several critical operations: point-in-time recovery, master-slave replication, and incremental backups. Without these logs, you're limited to full backups and face potential data loss between backup intervals.

MySQL Binary Log Configuration

Start by editing MySQL's configuration file to enable binary logging. Open /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu systems:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these configuration lines under the [mysqld] section:

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
binlog_format=ROW
expire_logs_days=7
max_binlog_size=100M
sync_binlog=1

The log-bin directive specifies the log file location and prefix. Set binlog_format=ROW for complete data capture - this format logs the actual row changes rather than SQL statements.

Create the log directory with proper ownership:

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

Restart MySQL to activate binary logging:

sudo systemctl restart mysql

Verify binary logging is active:

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -u root -p -e "SHOW BINARY LOGS;"

PostgreSQL WAL Configuration

PostgreSQL's WAL system requires configuration in postgresql.conf. Ubuntu typically uses /etc/postgresql/16/main/postgresql.conf.

Edit the configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Configure these WAL-related settings:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/16/archive/%f && cp %p /var/lib/postgresql/16/archive/%f'
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_completion_target = 0.9

Create the WAL archive directory:

sudo mkdir -p /var/lib/postgresql/16/archive
sudo chown postgres:postgres /var/lib/postgresql/16/archive
sudo chmod 700 /var/lib/postgresql/16/archive

Restart PostgreSQL:

sudo systemctl restart postgresql

Verify WAL archiving is working:

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('wal_level', 'archive_mode', 'archive_command');"
sudo ls -la /var/lib/postgresql/16/archive/

Point-in-Time Recovery Setup

Binary logs enable recovery to any specific moment, not just backup times. This capability requires proper log retention and backup coordination.

For MySQL, create a complete backup that coordinates with binary log positions:

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

The --master-data=2 option records the binary log file name and position as a comment in the dump file. This information is crucial for point-in-time recovery.

For PostgreSQL, use pg_basebackup to create a consistent base backup:

sudo -u postgres pg_basebackup -D /backup/postgresql-base-$(date +%Y%m%d_%H%M%S) -Ft -z -P

This backup includes the current WAL position. You can recover to any point after the backup time using archived WAL files.

HostMyCode database hosting includes automated backup coordination with binary logging. Your VPS databases maintain consistent recovery points without manual intervention.

Log Rotation and Retention

Binary logs consume significant disk space over time. Implement proper rotation to prevent storage exhaustion while maintaining recovery capabilities.

MySQL automatically rotates binary logs based on your max_binlog_size setting. Manual rotation is possible:

mysql -u root -p -e "FLUSH LOGS;"

Purge old binary logs safely after confirming backups are complete:

mysql -u root -p -e "PURGE BINARY LOGS TO 'mysql-bin.000010';"

Never purge logs needed for active replication or recovery scenarios.

PostgreSQL WAL segments rotate automatically. Configure log retention in your archive script:

#!/bin/bash
# Enhanced archive script with retention
WAL_FILE="$1"
WAL_PATH="$2"
ARCHIVE_DIR="/var/lib/postgresql/16/archive"

# Archive the WAL file
cp "$WAL_PATH" "$ARCHIVE_DIR/$WAL_FILE"

# Remove archives older than 7 days
find "$ARCHIVE_DIR" -name "*.wal" -mtime +7 -delete

exit 0

Make this script executable and update your archive_command to use it:

sudo chmod +x /usr/local/bin/postgres-archive.sh

Monitoring Binary Log Health

Regular monitoring prevents log-related issues from affecting your VPS database performance. Track log generation rates, disk usage, and archive success rates.

Monitor MySQL binary log space usage:

mysql -u root -p -e "SELECT ROUND(SUM(File_size)/1024/1024,2) AS 'Binlog Size (MB)' FROM INFORMATION_SCHEMA.BINARY_LOG_FILES;"

Check PostgreSQL WAL directory size:

sudo du -sh /var/lib/postgresql/16/main/pg_wal/
sudo du -sh /var/lib/postgresql/16/archive/

Set up alerts for unusual log generation patterns. Sudden increases often indicate runaway transactions or bulk operations that require attention.

Create a simple monitoring script for MySQL binary log health:

#!/bin/bash
# Check binary log count and size
LOG_COUNT=$(mysql -u root -p[password] -se "SELECT COUNT(*) FROM INFORMATION_SCHEMA.BINARY_LOG_FILES;")
LOG_SIZE=$(mysql -u root -p[password] -se "SELECT ROUND(SUM(File_size)/1024/1024,2) FROM INFORMATION_SCHEMA.BINARY_LOG_FILES;")

echo "Binary logs: $LOG_COUNT files, ${LOG_SIZE}MB total"

if [ "$LOG_SIZE" -gt 1000 ]; then
    echo "WARNING: Binary logs exceed 1GB"
fi

Performance Optimization

Binary logging impacts write performance, but proper configuration minimizes overhead while maintaining data safety.

MySQL's sync_binlog setting controls write durability. Setting it to 1 provides maximum safety but higher overhead. For VPS environments with good hardware, this setting is recommended:

sync_binlog = 1  # Flush to disk after each commit

For high-performance scenarios with battery-backed RAID controllers:

sync_binlog = 100  # Flush every 100 transactions

PostgreSQL's synchronous_commit setting offers similar control:

synchronous_commit = on      # Full durability
synchronous_commit = local   # Local durability only (for async replication)

Monitor the performance impact using slow query logs and connection monitoring. The PostgreSQL performance monitoring guide provides comprehensive monitoring setup instructions.

Replication Configuration

Binary logs enable master-slave replication for high availability and read scaling. Configure replication carefully to avoid data inconsistencies.

For MySQL replication, create a dedicated replication user on the master:

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

Get the current binary log position:

SHOW MASTER STATUS;

Configure the slave server to connect to this master using the binary log coordinates. The PostgreSQL replication setup guide covers similar configuration for PostgreSQL streaming replication.

HostMyCode VPS deployments include replication monitoring and automated failover capabilities.

Backup Integration Strategies

Coordinate binary logging with your backup strategy for comprehensive data protection. Inconsistent backup and log management creates recovery gaps.

Implement a backup script that captures both data and log positions:

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

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Perform backup with binary log info
mysqldump --single-transaction --routines --triggers --master-data=2 \
    --all-databases > "$BACKUP_DIR/mysql-full-$DATE.sql"

# Flush and record current binary log position
mysql -e "FLUSH LOGS;"
mysql -e "SHOW MASTER STATUS;" > "$BACKUP_DIR/master-status-$DATE.txt"

echo "Backup completed: mysql-full-$DATE.sql"

For PostgreSQL, coordinate WAL archiving with backup scheduling:

#!/bin/bash
# PostgreSQL backup with WAL coordination
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create base backup
sudo -u postgres pg_basebackup -D "$BACKUP_DIR/base-$DATE" -Ft -z -P

# Record WAL position
sudo -u postgres psql -c "SELECT pg_current_wal_lsn();" > "$BACKUP_DIR/wal-position-$DATE.txt"

echo "Backup completed: base-$DATE"

Troubleshooting Common Issues

Binary logging can fail silently, leading to incomplete logs and recovery problems. Regular verification prevents these issues from affecting your VPS database recovery capabilities.

Common MySQL binary log problems include disk space exhaustion and permission issues. Check disk space regularly:

df -h /var/log/mysql/

Verify binary log file permissions:

ls -la /var/log/mysql/mysql-bin.*

If binary logging stops, check the MySQL error log:

sudo tail -f /var/log/mysql/error.log

For PostgreSQL, monitor archive command failures:

sudo -u postgres psql -c "SELECT * FROM pg_stat_archiver;"

Failed archive operations appear in the PostgreSQL log:

sudo tail -f /var/log/postgresql/postgresql-16-main.log

When archive_command fails repeatedly, PostgreSQL accumulates WAL files in pg_wal/. This can fill your disk. Monitor this directory size and fix archive issues promptly.

Frequently Asked Questions

How much disk space do binary logs typically consume?

Binary log space usage depends on transaction volume. A typical web application database generates 100-500MB of binary logs daily. High-traffic sites may produce several gigabytes. Monitor usage patterns and adjust retention periods accordingly.

Can I enable binary logging on an existing production database?

Yes, but it requires a restart for MySQL and PostgreSQL. Plan the restart during maintenance windows. Performance impact is minimal once logging is active, but initial setup requires brief downtime.

What happens if the archive_command fails in PostgreSQL?

PostgreSQL will retry the archive command automatically. If failures persist, WAL files accumulate in pg_wal/ and can fill your disk. Monitor archive success rates and fix command issues promptly to prevent storage problems.

How do I perform point-in-time recovery using binary logs?

Start with a full backup, then apply binary logs up to your desired recovery point. For MySQL, use mysqlbinlog to replay specific log ranges. PostgreSQL uses pg_waldump for WAL analysis and recovery.conf for automated replay during startup.

Should I use ROW or STATEMENT format for MySQL binary logging?

ROW format provides better consistency and is required for certain replication scenarios. STATEMENT format is more compact but can cause replication inconsistencies with non-deterministic functions. ROW format is recommended for VPS production environments.