Setup MySQL Binary Logging on Ubuntu VPS: Complete Replication Guide

Understanding MySQL Binary Logging for VPS Hosting

MySQL binary logging creates a detailed record of all database changes on your Ubuntu VPS. This log becomes essential for database replication, point-in-time recovery, and detailed audit trails.

Unlike regular error logs, binary logs capture every INSERT, UPDATE, DELETE, and DDL statement that modifies data. For hosting customers running e-commerce sites or multi-user applications, binary logs provide the foundation for reliable data protection.

Your Hostperl VPS comes with MySQL pre-installed, but binary logging requires manual configuration.

Prerequisites and Server Requirements

You'll need MySQL 5.7 or higher running on Ubuntu 20.04+ with at least 2GB RAM. Binary logs consume disk space proportional to your database activity.

Check your current MySQL version:

mysql --version

Verify MySQL service status:

sudo systemctl status mysql

Reserve 5-10GB of disk space for binary logs on actively used databases.

Enable Binary Logging in MySQL Configuration

Open the MySQL configuration file:

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

Add these lines under the [mysqld] section:

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

The server-id must be unique if you plan database replication. Use your VPS IP's last octet for simplicity.

Create the binary log directory:

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

Configure Binary Log Format and Rotation

Binary log format affects replication compatibility and disk usage. ROW format provides the most reliable replication but uses more space than STATEMENT format.

For WordPress, Joomla, or custom applications, ROW format prevents replication issues with non-deterministic queries.

Configure automatic log rotation:

binlog_expire_logs_seconds = 604800  # 7 days
max_binlog_files = 50

This prevents binary logs from consuming excessive disk space on your VPS.

Restart MySQL and Verify Configuration

Test the configuration syntax before restarting:

sudo mysqld --help --verbose | head -20

If no errors appear, restart MySQL:

sudo systemctl restart mysql

Verify binary logging is active:

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

The result should show "ON".

Check binary log files:

mysql -u root -p -e "SHOW BINARY LOGS;"

Monitor Binary Log File Creation

Binary logs create new files when they reach max_binlog_size or MySQL restarts.

Monitor file creation:

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

Each file includes a sequence number. The mysql-bin.index file tracks all binary log files.

Generate some database activity to test logging:

mysql -u root -p -e "CREATE DATABASE test_binlog;"
mysql -u root -p -e "USE test_binlog; CREATE TABLE sample (id INT PRIMARY KEY, name VARCHAR(50));"
mysql -u root -p -e "USE test_binlog; INSERT INTO sample VALUES (1, 'Test Entry');"
mysql -u root -p -e "DROP DATABASE test_binlog;"

View Binary Log Contents

Use mysqlbinlog to read binary log contents:

sudo mysqlbinlog /var/log/mysql/mysql-bin.000001

For human-readable output, add formatting options:

sudo mysqlbinlog --verbose /var/log/mysql/mysql-bin.000001

Filter by time range for troubleshooting:

sudo mysqlbinlog --start-datetime="2026-01-01 00:00:00" --stop-datetime="2026-01-01 23:59:59" /var/log/mysql/mysql-bin.000001

This helps investigate specific database issues or track changes during maintenance windows.

Setup MySQL Binary Logging Purging and Maintenance

Manual purging removes old binary logs when automatic expiration isn't sufficient:

mysql -u root -p -e "PURGE BINARY LOGS BEFORE '2026-01-01 00:00:00';"

Purge logs older than 3 days:

mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);"

Never purge binary logs still needed for replication. Check slave lag first if using replication.

Create a maintenance script for automated purging:

#!/bin/bash
# /home/scripts/purge_binlogs.sh
mysql -u root -p[password] -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

Schedule with cron for weekly execution:

0 2 * * 0 /home/scripts/purge_binlogs.sh

Frequently Asked Questions

How much disk space do binary logs require?

Binary logs use 10-50MB per day for typical web applications. E-commerce sites with heavy transaction volumes may require 200-500MB daily.

Plan for 7-14 days of logs plus 50% buffer space.

Can binary logging affect database performance?

Binary logging adds minimal overhead (1-3%) for most applications. ROW format uses more CPU than STATEMENT format but provides better replication reliability.

The trade-off is worthwhile for production databases.

What happens if binary log disk space fills up?

MySQL stops accepting writes when binary log disk space is exhausted. This protects data integrity but requires immediate attention.

Configure log rotation and monitor disk usage to prevent this situation.

Should I use binary logs for single-server setups?

Yes, binary logs enable point-in-time recovery even without replication. For hosting customers with critical data, binary logs provide recovery options beyond regular backups.

How do I backup binary logs safely?

Use mysqlbinlog to export binary logs to SQL format before backing up the files directly. This ensures you can restore on different MySQL versions if needed.