Set up MySQL point-in-time recovery with binary logs for database backup and restore

Install MySQL server

Install MySQL server with the necessary tools for backup and binary log management.

sudo apt update
sudo apt install -y mysql-server mysql-client

sudo dnf install -y mysql-server mysql
sudo systemctl enable --now mysqld

Configure MySQL binary logging

Enable binary logging in MySQL configuration to capture all database changes for point-in-time recovery.

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

# Enable GTID for better replication consistency
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# InnoDB settings for durability
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1

Create binary log directory

Create the binary log directory with proper permissions for the mysql user.

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

Restart MySQL service

Restart MySQL to apply the binary logging configuration.

sudo systemctl restart mysql
sudo systemctl status mysql

Create backup user

Create a dedicated MySQL user with necessary privileges for backup operations.

sudo mysql -e "CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongBackupPass123!';"
sudo mysql -e "GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';"
sudo mysql -e "FLUSH PRIVILEGES;"

Create backup directory structure

Set up directories for storing full backups and binary log backups with proper permissions.

sudo mkdir -p /backup/mysql/{full,binlogs}
sudo chown -R mysql:mysql /backup/mysql
sudo chmod 750 /backup/mysql

Create full backup script

Create a script to perform consistent full backups using mysqldump with binary log position recording.

#!/bin/bash
set -euo pipefail

# Configuration
BACKUP_DIR="/backup/mysql/full"
BACKUP_USER="backup"
BACKUP_PASS="StrongBackupPass123!"
RETENTION_DAYS=7

# Create backup filename with timestamp
BACKUP_FILE="$BACKUP_DIR/mysql-full-$(date +%Y%m%d-%H%M%S).sql"
LOG_FILE="/var/log/mysql-backup.log"

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

log "Starting full MySQL backup"

# Create backup with consistent snapshot
mysqldump --user="$BACKUP_USER" --password="$BACKUP_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    --master-data=2 \
    --all-databases \
    --flush-logs > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    log "Full backup completed: $BACKUP_FILE"
    
    # Compress backup
    gzip "$BACKUP_FILE"
    log "Backup compressed: ${BACKUP_FILE}.gz"
    
    # Extract binary log position from backup
    BINLOG_INFO=$(zcat "${BACKUP_FILE}.gz" | grep "CHANGE MASTER TO" | head -1)
    log "Binary log position: $BINLOG_INFO"
else
    log "ERROR: Full backup failed"
    exit 1
fi

# Clean up old backups
find "$BACKUP_DIR" -name "mysql-full-*.sql.gz" -mtime +$RETENTION_DAYS -delete
log "Cleanup completed - removed backups older than $RETENTION_DAYS days"

log "Full backup process completed successfully"

Create binary log backup script

Create a script to safely backup and archive binary logs for point-in-time recovery.

#!/bin/bash
set -euo pipefail

# Configuration
BINLOG_BACKUP_DIR="/backup/mysql/binlogs"
MYSQL_BINLOG_DIR="/var/log/mysql"
BACKUP_USER="backup"
BACKUP_PASS="StrongBackupPass123!"
RETENTION_DAYS=30
LOG_FILE="/var/log/mysql-binlog-backup.log"

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

log "Starting binary log backup"

# Get list of binary logs
BINLOGS=$(mysql --user="$BACKUP_USER" --password="$BACKUP_PASS" -e "SHOW BINARY LOGS" --batch --skip-column-names)

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

# Get current binary log file
CURRENT_BINLOG=$(mysql --user="$BACKUP_USER" --password="$BACKUP_PASS" -e "SHOW MASTER STATUS" --batch --skip-column-names | cut -f1)

# Process each binary log except the current one
echo "$BINLOGS" | while read -r binlog_line; do
    BINLOG_FILE=$(echo "$binlog_line" | awk '{print $1}')
    
    # Skip current active binary log
    if [ "$BINLOG_FILE" = "$CURRENT_BINLOG" ]; then
        log "Skipping active binary log: $BINLOG_FILE"
        continue
    fi
    
    # Check if already backed up
    if [ -f "$BINLOG_BACKUP_DIR/$BINLOG_FILE" ]; then
        log "Binary log already backed up: $BINLOG_FILE"
        continue
    fi
    
    # Copy binary log
    if cp "$MYSQL_BINLOG_DIR/$BINLOG_FILE" "$BINLOG_BACKUP_DIR/"; then
        log "Backed up binary log: $BINLOG_FILE"
        
        # Compress the backup
        gzip "$BINLOG_BACKUP_DIR/$BINLOG_FILE"
        log "Compressed binary log: $BINLOG_FILE.gz"
    else
        log "ERROR: Failed to backup binary log: $BINLOG_FILE"
    fi
done

# Flush logs to create a new binary log
mysql --user="$BACKUP_USER" --password="$BACKUP_PASS" -e "FLUSH LOGS"
log "Flushed binary logs"

# Clean up old binary log backups
find "$BINLOG_BACKUP_DIR" -name "mysql-bin.*" -mtime +$RETENTION_DAYS -delete
log "Cleanup completed - removed binary log backups older than $RETENTION_DAYS days"

log "Binary log backup completed successfully"

Make backup scripts executable

Set proper permissions for the backup scripts to ensure they can be executed securely.

sudo chmod 750 /usr/local/bin/mysql-full-backup.sh
sudo chmod 750 /usr/local/bin/mysql-binlog-backup.sh
sudo chown mysql:mysql /usr/local/bin/mysql-*-backup.sh

Create point-in-time recovery script

Create a script to perform point-in-time recovery from full backup and binary logs.

#!/bin/bash
set -euo pipefail

# Usage function
usage() {
    echo "Usage: $0 -f 

Make recovery script executable

Set proper permissions for the point-in-time recovery script.

sudo chmod 750 /usr/local/bin/mysql-pitr-restore.sh
sudo chown mysql:mysql /usr/local/bin/mysql-pitr-restore.sh

Set up automated backup schedule

Configure systemd timers to automate full backups and binary log backups.

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

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

Create full backup timer

Schedule daily full backups during low-traffic hours.

[Unit]
Description=MySQL Full Backup Timer
Requires=mysql-full-backup.service

[Timer]
OnCalendar=daily
Persistent=true
RandomizedDelaySec=1800

[Install]
WantedBy=timers.target

Create binary log backup service

Configure systemd service for binary log backups.

[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

Create binary log backup timer

Schedule hourly binary log backups for minimal data loss.

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

[Timer]
OnCalendar=hourly
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target

Enable and start backup timers

Enable the systemd timers to start the automated backup schedule.

sudo systemctl daemon-reload
sudo systemctl enable --now mysql-full-backup.timer
sudo systemctl enable --now mysql-binlog-backup.timer

Create backup monitoring script

Set up monitoring to alert when backups fail or lag behind schedule.

#!/bin/bash
set -euo pipefail

# Configuration
FULL_BACKUP_DIR="/backup/mysql/full"
BINLOG_BACKUP_DIR="/backup/mysql/binlogs"
ALERT_EMAIL="admin@example.com"
MAX_FULL_BACKUP_AGE=25  # hours
MAX_BINLOG_BACKUP_AGE=2  # hours

# Function to send alert
send_alert() {
    local subject="$1"
    local message="$2"
    echo "$message" | mail -s "$subject" "$ALERT_EMAIL"
    logger -t mysql-backup-monitor "ALERT: $subject - $message"
}

# Check full backup freshness
LATEST_FULL=$(find "$FULL_BACKUP_DIR" -name "mysql-full-*.sql.gz" -type f -printf '%T@ %p\n' | sort -nr | head -1 | cut -d' ' -f2-)

if [ -z "$LATEST_FULL" ]; then
    send_alert "MySQL Backup Alert" "No full backups found in $FULL_BACKUP_DIR"
else
    FULL_AGE_HOURS=$(( ($(date +%s) - $(stat -c %Y "$LATEST_FULL")) / 3600 ))
    
    if [ "$FULL_AGE_HOURS" -gt "$MAX_FULL_BACKUP_AGE" ]; then
        send_alert "MySQL Backup Alert" "Latest full backup is $FULL_AGE_HOURS hours old (max: $MAX_FULL_BACKUP_AGE)"
    fi
fi

# Check binary log backup freshness
LATEST_BINLOG=$(find "$BINLOG_BACKUP_DIR" -name "mysql-bin.*" -type f -printf '%T@ %p\n' | sort -nr | head -1 | cut -d' ' -f2-)

if [ -z "$LATEST_BINLOG" ]; then
    send_alert "MySQL Backup Alert" "No binary log backups found in $BINLOG_BACKUP_DIR"
else
    BINLOG_AGE_HOURS=$(( ($(date +%s) - $(stat -c %Y "$LATEST_BINLOG")) / 3600 ))
    
    if [ "$BINLOG_AGE_HOURS" -gt "$MAX_BINLOG_BACKUP_AGE" ]; then
        send_alert "MySQL Backup Alert" "Latest binary log backup is $BINLOG_AGE_HOURS hours old (max: $MAX_BINLOG_BACKUP_AGE)"
    fi
fi

# Check disk space
BACKUP_USAGE=$(df "$FULL_BACKUP_DIR" | awk 'NR==2 {print $5}' | sed 's/%//')
if [ "$BACKUP_USAGE" -gt 90 ]; then
    send_alert "MySQL Backup Alert" "Backup disk usage is at ${BACKUP_USAGE}%"
fi

echo "Backup monitoring completed successfully"

Make monitoring script executable and schedule

Configure the monitoring script to run every hour and check backup health.

sudo chmod 755 /usr/local/bin/mysql-backup-monitor.sh
sudo chown mysql:mysql /usr/local/bin/mysql-backup-monitor.sh

# Monitor MySQL backups every hour
0 * * * * mysql /usr/local/bin/mysql-backup-monitor.sh >/dev/null 2>&1

Example recovery scenario

Here's how to perform point-in-time recovery to restore data before an accidental deletion.

# Example: Recover to 2024-12-01 14:30:00
# Stop MySQL to prevent further changes
sudo systemctl stop mysql

# Backup current data (optional but recommended)
sudo mv /var/lib/mysql /var/lib/mysql.backup
sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql

# Start MySQL for restoration
sudo systemctl start mysql

# Run point-in-time recovery
sudo -u mysql /usr/local/bin/mysql-pitr-restore.sh \
    -f /backup/mysql/full/mysql-full-20241201-120000.sql.gz \
    -t '2024-12-01 14:30:00'

# Restart MySQL
sudo systemctl restart mysql

Set up log rotation monitoring

Monitor binary log disk usage and ensure logs rotate properly to prevent disk space issues.

# Check current binary log status
sudo mysql -e "SHOW MASTER STATUS;"
sudo mysql -e "SHOW BINARY LOGS;"

# Check binary log disk usage
sudo du -sh /var/log/mysql/

# Manually rotate logs if needed
sudo mysql -e "FLUSH LOGS;"

# Purge old binary logs (keep last 3 days)
sudo mysql -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY);"

Performance monitoring queries

Monitor binary log performance impact and adjust configuration as needed.

# Check binary log write performance
sudo mysql -e "SHOW STATUS LIKE 'Binlog%';"

# Monitor binary log cache usage
sudo mysql -e "SHOW STATUS LIKE 'Binlog_cache%';"

# Check if sync_binlog is impacting performance
sudo mysql -e "SHOW STATUS LIKE 'Binlog_stmt_cache%';"