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  -t  [-d ]"
    echo "Example: $0 -f /backup/mysql/full/mysql-full-20241201-120000.sql.gz -t '2024-12-01 14:30:00'"
    exit 1
}

Parse command line arguments
FULL_BACKUP=""
TARGET_TIME=""
TARGET_DB=""

while getopts "f:t:d:h" opt; do
    case $opt in
        f) FULL_BACKUP="$OPTARG" ;;
        t) TARGET_TIME="$OPTARG" ;;
        d) TARGET_DB="$OPTARG" ;;
        h) usage ;;
        *) usage ;;
    esac
done

if [ -z "$FULL_BACKUP" ] || [ -z "$TARGET_TIME" ]; then
    usage
fi

Configuration
BINLOG_BACKUP_DIR="/backup/mysql/binlogs"
TEMP_DIR="/tmp/mysql-pitr-$$"
LOG_FILE="/var/log/mysql-pitr-restore.log"

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

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

Create temporary directory
mkdir -p "$TEMP_DIR"

Extract binary log position from full backup
if [[ "$FULL_BACKUP" == *.gz ]]; then
    BINLOG_INFO=$(zcat "$FULL_BACKUP" | grep "CHANGE MASTER TO" | head -1)
else
    BINLOG_INFO=$(cat "$FULL_BACKUP" | grep "CHANGE MASTER TO" | head -1)
fi

BINLOG_FILE=$(echo "$BINLOG_INFO" | sed -n "s/.MASTER_LOG_FILE='\([^']\)'.*/\1/p")
BINLOG_POS=$(echo "$BINLOG_INFO" | sed -n "s/.MASTER_LOG_POS=\([0-9]\).*/\1/p")

log "Starting from binary log: $BINLOG_FILE at position: $BINLOG_POS"

Restore full backup
log "Restoring full backup..."
if [[ "$FULL_BACKUP" == *.gz ]]; then
    zcat "$FULL_BACKUP" | mysql
else
    mysql < "$FULL_BACKUP"
fi
log "Full backup restored successfully"

Apply binary logs up to target time
log "Applying binary logs up to $TARGET_TIME..."

Find all binary logs after the backup
BINLOG_FILES=$(find "$BINLOG_BACKUP_DIR" -name "mysql-bin.*" | sort)

for BINLOG_BACKUP in $BINLOG_FILES; do
    CURRENT_BINLOG=$(basename "$BINLOG_BACKUP" .gz)
    
    # Skip logs before our starting position
    if [[ "$CURRENT_BINLOG" < "$BINLOG_FILE" ]]; then
        continue
    fi
    
    log "Processing binary log: $CURRENT_BINLOG"
    
    # Decompress if needed
    if [[ "$BINLOG_BACKUP" == *.gz ]]; then
        zcat "$BINLOG_BACKUP" > "$TEMP_DIR/$CURRENT_BINLOG"
        BINLOG_PATH="$TEMP_DIR/$CURRENT_BINLOG"
    else
        BINLOG_PATH="$BINLOG_BACKUP"
    fi
    
    # Apply binary log with time limit
    if [ "$CURRENT_BINLOG" = "$BINLOG_FILE" ]; then
        # First log - start from position
        mysqlbinlog --start-position="$BINLOG_POS" --stop-datetime="$TARGET_TIME" "$BINLOG_PATH" | mysql
    else
        # Subsequent logs - apply from beginning
        mysqlbinlog --stop-datetime="$TARGET_TIME" "$BINLOG_PATH" | mysql
    fi
    
    log "Applied binary log: $CURRENT_BINLOG"
done

Cleanup
rm -rf "$TEMP_DIR"

log "Point-in-time recovery completed successfully to $TARGET_TIME"
log "Please verify your data and restart applications as needed"

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%';"