Configure MySQL binary log backup and point-in-time recovery

Enable MySQL binary logging

Binary logging must be enabled in MySQL configuration to record all data-modifying operations for point-in-time recovery.

# Binary logging configuration
log-bin = /var/log/mysql/mysql-bin
server-id = 1
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
max_binlog_size = 1G
sync_binlog = 1
binlog_cache_size = 1M

Create binary log directory

Set up the directory structure for binary logs with proper permissions and ownership for MySQL access.

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

Restart MySQL service

Apply the binary logging configuration by restarting the MySQL service.

sudo systemctl restart mysql
sudo systemctl status mysql

Verify binary logging is active

Confirm binary logging is working and check the current log file status.

sudo mysql -e "SHOW VARIABLES LIKE 'log_bin';"
sudo mysql -e "SHOW MASTER STATUS;"
sudo mysql -e "SHOW BINARY LOGS;"

Create binary log backup directory

Set up dedicated storage for binary log backups with appropriate structure and permissions.

sudo mkdir -p /backup/mysql/binlogs
sudo mkdir -p /backup/mysql/binlogs/archive
sudo chown mysql:mysql /backup/mysql/binlogs
sudo chmod 750 /backup/mysql/binlogs

Create binary log backup script

Implement automated backup script that safely copies and compresses binary logs while maintaining recovery capability.

#!/bin/bash

MySQL Binary Log Backup Script
Safely backs up and rotates binary logs

set -euo pipefail

Configuration
MYSQL_USER="backup_user"
MYSQL_PASSWORD="secure_backup_password"
BINLOG_DIR="/var/log/mysql"
BACKUP_DIR="/backup/mysql/binlogs"
ARCHIVE_DIR="/backup/mysql/binlogs/archive"
RETENTION_DAYS=30
LOG_FILE="/var/log/mysql-binlog-backup.log"

Logging function
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

log "Starting binary log backup"

Get list of binary logs except the current one
BINLOGS=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW BINARY LOGS;" | tail -n +2 | head -n -1 | awk '{print $1}')

if [ -z "$BINLOGS" ]; then
    log "No binary logs to backup"
    exit 0
fi

Create dated backup directory
BACKUP_DATE=$(date '+%Y%m%d')
DATE_DIR="$BACKUP_DIR/$BACKUP_DATE"
mkdir -p "$DATE_DIR"

Backup and compress each binary log
for binlog in $BINLOGS; do
    if [ -f "$BINLOG_DIR/$binlog" ]; then
        log "Backing up $binlog"
        cp "$BINLOG_DIR/$binlog" "$DATE_DIR/"
        gzip "$DATE_DIR/$binlog"
        log "Compressed $binlog to $DATE_DIR/$binlog.gz"
    fi
done

Flush logs to create new binary log
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "FLUSH BINARY LOGS;"
log "Flushed binary logs"

Purge old binary logs (keep last 3 days worth)
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);"
log "Purged old binary logs"

Archive old backups
find "$BACKUP_DIR" -type d -name "[0-9]*" -mtime +7 -exec mv {} "$ARCHIVE_DIR/" \;

Clean up very old archives
find "$ARCHIVE_DIR" -type d -name "[0-9]*" -mtime +$RETENTION_DAYS -exec rm -rf {} \;

log "Binary log backup completed successfully"

Generate backup report
BACKUP_SIZE=$(du -sh "$DATE_DIR" | cut -f1)
BINLOG_COUNT=$(ls -1 "$DATE_DIR" | wc -l)
log "Backup summary: $BINLOG_COUNT files, $BACKUP_SIZE total size"

Make backup script executable

Set proper permissions on the backup script for execution by the system.

sudo chmod +x /usr/local/bin/mysql-binlog-backup.sh
sudo chown mysql:mysql /usr/local/bin/mysql-binlog-backup.sh

Create MySQL backup user

Create a dedicated user account with minimal privileges required for binary log operations.

sudo mysql -e "CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_backup_password';"
sudo mysql -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO 'backup_user'@'localhost';"
sudo mysql -e "FLUSH PRIVILEGES;"

Set up automated backup schedule

Configure systemd timer for regular binary log backup execution.

[Unit]
Description=MySQL Binary Log Backup
After=mysql.service
Requires=mysql.service

[Service]
Type=oneshot
User=mysql
Group=mysql
ExecStart=/usr/local/bin/mysql-binlog-backup.sh
StandardOutput=journal
StandardError=journal

[Unit]
Description=MySQL Binary Log Backup Timer
Requires=mysql-binlog-backup.service

[Timer]
OnCalendar=--* 02:00:00
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target

Enable backup automation

Start and enable the systemd timer for automated binary log backups.

sudo systemctl daemon-reload
sudo systemctl enable mysql-binlog-backup.timer
sudo systemctl start mysql-binlog-backup.timer
sudo systemctl status mysql-binlog-backup.timer

Create point-in-time recovery script

Implement recovery script that can restore database to any specific point in time using full backup and binary logs.

#!/bin/bash

MySQL Point-in-Time Recovery Script
Restores database to specific point in time

set -euo pipefail

Check arguments
if [ $# -ne 2 ]; then
    echo "Usage: $0  "
    echo "Example: $0 /backup/mysql/full/backup.sql '2024-01-15 14:30:00'"
    exit 1
fi

FULL_BACKUP="$1"
TARGET_TIME="$2"
BINLOG_DIR="/backup/mysql/binlogs"
RECOVERY_LOG="/var/log/mysql-pitr-recovery.log"
TEMP_DIR="/tmp/mysql-pitr-$$"

Logging function
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$RECOVERY_LOG"
}

log "Starting point-in-time recovery to $TARGET_TIME"
log "Using full backup: $FULL_BACKUP"

Validate inputs
if [ ! -f "$FULL_BACKUP" ]; then
    log "ERROR: Full backup file not found: $FULL_BACKUP"
    exit 1
fi

Create temporary directory
mkdir -p "$TEMP_DIR"
trap "rm -rf $TEMP_DIR" EXIT

Stop MySQL for recovery
log "Stopping MySQL service"
sudo systemctl stop mysql

Backup current data directory
log "Backing up current data directory"
sudo mv /var/lib/mysql /var/lib/mysql.backup.$(date +%s)

Initialize new data directory
log "Initializing new data directory"
sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
sudo -u mysql mysqld --initialize-insecure --datadir=/var/lib/mysql

Start MySQL in safe mode
log "Starting MySQL"
sudo systemctl start mysql

Restore full backup
log "Restoring full backup"
mysql < "$FULL_BACKUP"

Find and decompress relevant binary logs
log "Processing binary logs up to $TARGET_TIME"

Get backup date from filename or directory
BACKUP_DATE=$(echo "$FULL_BACKUP" | grep -oE '[0-9]{8}' | head -1)
if [ -z "$BACKUP_DATE" ]; then
    log "WARNING: Cannot determine backup date, searching all binary logs"
    SEARCH_DIRS=$(find "$BINLOG_DIR" -type d -name "[0-9]*" | sort)
else
    SEARCH_DIRS=$(find "$BINLOG_DIR" -type d -name "[0-9]*" | sort | awk -v date="$BACKUP_DATE" '$1 >= date')
fi

Decompress and apply binary logs
for dir in $SEARCH_DIRS; do
    log "Processing directory: $dir"
    for binlog_gz in $(find "$dir" -name "*.gz" | sort); do
        binlog_name=$(basename "$binlog_gz" .gz)
        log "Decompressing $binlog_name"
        gunzip -c "$binlog_gz" > "$TEMP_DIR/$binlog_name"
        
        # Apply binary log up to target time
        log "Applying $binlog_name up to $TARGET_TIME"
        mysqlbinlog --stop-datetime="$TARGET_TIME" "$TEMP_DIR/$binlog_name" | mysql
        
        rm "$TEMP_DIR/$binlog_name"
    done
done

log "Point-in-time recovery completed successfully"
log "Database restored to $TARGET_TIME"

Verify recovery
log "Verifying recovery"
mysql -e "SELECT NOW() as recovery_time;"
mysql -e "SHOW DATABASES;"

Make recovery script executable

Set execution permissions on the point-in-time recovery script.

sudo chmod +x /usr/local/bin/mysql-pitr-recovery.sh
sudo chown root:mysql /usr/local/bin/mysql-pitr-recovery.sh
sudo chmod 750 /usr/local/bin/mysql-pitr-recovery.sh

Create monitoring script

Set up monitoring for binary log health and backup status to ensure recovery capability.

#!/bin/bash

MySQL Binary Log Monitoring Script
Monitors binlog health and backup status

set -euo pipefail

MYSQL_USER="backup_user"
MYSQL_PASSWORD="secure_backup_password"
BINLOG_DIR="/var/log/mysql"
BACKUP_DIR="/backup/mysql/binlogs"
ALERT_LOG="/var/log/mysql-binlog-alerts.log"
MAX_BINLOG_AGE_HOURS=24
MAX_BACKUP_AGE_HOURS=48

log_alert() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - ALERT: $1" | tee -a "$ALERT_LOG"
}

log_info() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - INFO: $1"
}

Check if binary logging is enabled
BINLOG_STATUS=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW VARIABLES LIKE 'log_bin';" | tail -1 | awk '{print $2}')

if [ "$BINLOG_STATUS" != "ON" ]; then
    log_alert "Binary logging is disabled"
    exit 1
fi

Check binary log file age
CURRENT_BINLOG=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW MASTER STATUS;" | tail -1 | awk '{print $1}')
if [ -n "$CURRENT_BINLOG" ]; then
    BINLOG_FILE="$BINLOG_DIR/$CURRENT_BINLOG"
    if [ -f "$BINLOG_FILE" ]; then
        BINLOG_AGE=$(stat -c %Y "$BINLOG_FILE")
        CURRENT_TIME=$(date +%s)
        AGE_HOURS=$(( (CURRENT_TIME - BINLOG_AGE) / 3600 ))
        
        if [ $AGE_HOURS -gt $MAX_BINLOG_AGE_HOURS ]; then
            log_alert "Current binary log is $AGE_HOURS hours old (max: $MAX_BINLOG_AGE_HOURS)"
        else
            log_info "Current binary log age: $AGE_HOURS hours"
        fi
    fi
fi

Check backup directory size and age
if [ -d "$BACKUP_DIR" ]; then
    LATEST_BACKUP=$(find "$BACKUP_DIR" -type d -name "[0-9]*" | sort | tail -1)
    if [ -n "$LATEST_BACKUP" ]; then
        BACKUP_AGE=$(stat -c %Y "$LATEST_BACKUP")
        CURRENT_TIME=$(date +%s)
        BACKUP_AGE_HOURS=$(( (CURRENT_TIME - BACKUP_AGE) / 3600 ))
        
        if [ $BACKUP_AGE_HOURS -gt $MAX_BACKUP_AGE_HOURS ]; then
            log_alert "Latest backup is $BACKUP_AGE_HOURS hours old (max: $MAX_BACKUP_AGE_HOURS)"
        else
            log_info "Latest backup age: $BACKUP_AGE_HOURS hours"
        fi
        
        BACKUP_SIZE=$(du -sh "$BACKUP_DIR" 2>/dev/null | cut -f1)
        log_info "Total backup size: $BACKUP_SIZE"
    else
        log_alert "No backups found in $BACKUP_DIR"
    fi
else
    log_alert "Backup directory $BACKUP_DIR does not exist"
fi

Check disk space
DISK_USAGE=$(df "$BACKUP_DIR" | tail -1 | awk '{print $5}' | sed 's/%//')
if [ $DISK_USAGE -gt 90 ]; then
    log_alert "Backup disk usage is $DISK_USAGE% (threshold: 90%)"
else
    log_info "Backup disk usage: $DISK_USAGE%"
fi

log_info "Binary log monitoring completed"

Set up monitoring automation

Configure systemd timer for regular binary log and backup monitoring.

[Unit]
Description=MySQL Binary Log Monitor
After=mysql.service
Requires=mysql.service

[Service]
Type=oneshot
User=mysql
Group=mysql
ExecStart=/usr/local/bin/mysql-binlog-monitor.sh
StandardOutput=journal
StandardError=journal

[Unit]
Description=MySQL Binary Log Monitor Timer
Requires=mysql-binlog-monitor.service

[Timer]
OnCalendar=-- :00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable monitoring automation

Start and enable the monitoring timer for continuous binary log health checks.

sudo chmod +x /usr/local/bin/mysql-binlog-monitor.sh
sudo systemctl daemon-reload
sudo systemctl enable mysql-binlog-monitor.timer
sudo systemctl start mysql-binlog-monitor.timer

Create test data and full backup

Generate test data and create a full backup to test the recovery process.

# Create test database and data
sudo mysql -e "CREATE DATABASE pitr_test;"
sudo mysql -e "USE pitr_test; CREATE TABLE test_data (id INT AUTO_INCREMENT PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, data VARCHAR(255));"
sudo mysql -e "USE pitr_test; INSERT INTO test_data (data) VALUES ('Initial data'), ('More data'), ('Test entry');"

Create full backup
sudo mysqldump --single-transaction --routines --triggers --all-databases > /backup/mysql/full_backup_$(date +%Y%m%d_%H%M%S).sql

Note the backup time
echo "Full backup completed at: $(date)"

Add more test data after backup
sleep 5
sudo mysql -e "USE pitr_test; INSERT INTO test_data (data) VALUES ('After backup 1'), ('After backup 2');"
sleep 5
sudo mysql -e "USE pitr_test; INSERT INTO test_data (data) VALUES ('After backup 3'), ('Final entry');"

RECOVERY_TIME=$(date '+%Y-%m-%d %H:%M:%S')
echo "Recovery target time: $RECOVERY_TIME"

Add data that should not be recovered
sleep 5
sudo mysql -e "USE pitr_test; INSERT INTO test_data (data) VALUES ('Should not appear'), ('After target time');"

Show all data before recovery test
sudo mysql -e "USE pitr_test; SELECT * FROM test_data;"