Linux VPS MySQL Backup and Recovery Tutorial: Complete Automation with mysqldump, Binary Logs, and Point-in-Time Restore in 2026

Why MySQL Backup and Recovery Matters on Your VPS

Database loss happens without warning. Hardware fails, human error deletes critical data, or corruption strikes during peak traffic. A proper MySQL backup and recovery strategy transforms these disasters from business-ending events into minor inconveniences.

Most VPS owners run basic mysqldump commands and hope for the best. That approach works until you need to restore data from three hours ago, not three days.

Effective MySQL backup and recovery requires multiple layers. You need full backups, incremental captures, binary log shipping, and tested restore procedures.

This tutorial covers the complete workflow for production VPS environments running Ubuntu 22.04, Rocky Linux 9, or AlmaLinux 9. You'll implement automated daily backups, enable point-in-time recovery, and verify your restore process actually works.

Prerequisites and Environment Setup

You need a Linux VPS with MySQL 8.0 or MariaDB 10.6+ installed and running. This tutorial assumes you have root access and basic familiarity with MySQL administration.

Check your MySQL version and ensure binary logging is enabled:

mysql -u root -p -e "SELECT VERSION();"
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'binlog_format';"

If log_bin shows OFF, add these lines to your configuration file:

For Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnf
For Rocky/AlmaLinux: /etc/my.cnf

[mysqld]
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 500M

Restart MySQL and verify binary logging is active:

systemctl restart mysql
mysql -u root -p -e "SHOW MASTER STATUS;"

Create a dedicated backup user with minimal privileges:

mysql -u root -p
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_backup_password';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Full Database Backup with mysqldump

Start with consistent full backups using mysqldump. This method captures complete database state including structure, data, triggers, and stored procedures.

Create the backup script /opt/mysql-backup/full_backup.sh:

#!/bin/bash

# MySQL backup configuration
BACKUP_USER="backup_user"
BACKUP_PASS="secure_backup_password"
BACKUP_DIR="/opt/mysql-backup/full"
LOG_FILE="/var/log/mysql-backup.log"
RETENTION_DAYS=30

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Generate timestamp
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="$BACKUP_DIR/mysql_full_$TIMESTAMP.sql"

# Log backup start
echo "$(date): Starting full MySQL backup" >> "$LOG_FILE"

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

# Check backup success
if [ $? -eq 0 ]; then
  # Compress backup
  gzip "$BACKUP_FILE"
  echo "$(date): Backup completed successfully: ${BACKUP_FILE}.gz" >> "$LOG_FILE"
  
  # Remove old backups
  find "$BACKUP_DIR" -name "mysql_full_*.sql.gz" -mtime +$RETENTION_DAYS -delete
  echo "$(date): Cleaned backups older than $RETENTION_DAYS days" >> "$LOG_FILE"
else
  echo "$(date): Backup failed with exit code $?" >> "$LOG_FILE"
  exit 1
fi

Make the script executable and test it:

chmod +x /opt/mysql-backup/full_backup.sh
/opt/mysql-backup/full_backup.sh

The --single-transaction flag ensures consistent backups for InnoDB tables without locking. The --master-data=2 option records binary log position for point-in-time recovery.

Binary Log Backup and Management

Binary logs contain every database change since your last full backup. Without them, you can only restore to backup time, not to the exact moment before data loss occurred.

Create a binary log backup script /opt/mysql-backup/binlog_backup.sh:

#!/bin/bash

BINLOG_DIR="/var/lib/mysql"
BACKUP_DIR="/opt/mysql-backup/binlogs"
LOG_FILE="/var/log/mysql-backup.log"
RETENTION_DAYS=7

# Create binlog backup directory
mkdir -p "$BACKUP_DIR"

# Flush logs to close current binlog file
mysql -u backup_user -psecure_backup_password -e "FLUSH LOGS;"

# Copy completed binlog files
for binlog in $(mysql -u backup_user -psecure_backup_password -e "SHOW BINARY LOGS;" | awk 'NR>1 {print $1}' | head -n -1); do
  if [ ! -f "$BACKUP_DIR/$binlog" ]; then
    cp "$BINLOG_DIR/$binlog" "$BACKUP_DIR/"
    echo "$(date): Copied binlog $binlog" >> "$LOG_FILE"
  fi
done

# Clean old binlog backups
find "$BACKUP_DIR" -name "mysql-bin.*" -mtime +$RETENTION_DAYS -delete
echo "$(date): Cleaned old binlog backups" >> "$LOG_FILE"

Binary log backups should run more frequently than full backups. Copy new logs every hour to minimize potential data loss.

Point-in-Time Recovery Implementation

Point-in-time recovery combines your last full backup with binary log replay up to a specific timestamp. This technique recovers data right before corruption or accidental deletion.

Create the recovery script /opt/mysql-backup/point_in_time_restore.sh:

#!/bin/bash

# Usage: ./point_in_time_restore.sh BACKUP_FILE RECOVERY_TIME
# Example: ./point_in_time_restore.sh mysql_full_20261215_020000.sql.gz "2026-12-15 14:30:00"

BACKUP_FILE="$1"
RECOVERY_TIME="$2"
BINLOG_DIR="/opt/mysql-backup/binlogs"
TEMP_DIR="/tmp/mysql_recovery"

if [ $# -ne 2 ]; then
  echo "Usage: $0 BACKUP_FILE 'YYYY-MM-DD HH:MM:SS'"
  exit 1
fi

# Create temporary directory
mkdir -p "$TEMP_DIR"

echo "Starting point-in-time recovery to $RECOVERY_TIME"
echo "Using backup: $BACKUP_FILE"

# Step 1: Restore full backup
echo "Restoring full backup..."
if [[ "$BACKUP_FILE" == *.gz ]]; then
  zcat "$BACKUP_FILE" | mysql -u root -p
else
  mysql -u root -p < "$BACKUP_FILE"
fi

# Step 2: Extract binlog position from backup
echo "Extracting binlog position..."
if [[ "$BACKUP_FILE" == *.gz ]]; then
  BINLOG_INFO=$(zcat "$BACKUP_FILE" | grep "CHANGE MASTER TO" | head -1)
else
  BINLOG_INFO=$(grep "CHANGE MASTER TO" "$BACKUP_FILE" | head -1)
fi

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

echo "Starting from binlog: $START_FILE, position: $START_POS"

# Step 3: Apply binary logs up to recovery time
echo "Applying binary logs until $RECOVERY_TIME..."
mysqlbinlog \
  --start-position="$START_POS" \
  --stop-datetime="$RECOVERY_TIME" \
  --database="" \
  "$BINLOG_DIR"/mysql-bin.* > "$TEMP_DIR/recovery.sql"

# Apply the generated SQL
mysql -u root -p < "$TEMP_DIR/recovery.sql"

echo "Point-in-time recovery completed"
echo "Verify your data and remove $TEMP_DIR when satisfied"

Test point-in-time recovery on a development server before using it in production. The recovery process stops the database, so plan for maintenance windows.

Automated Scheduling with Systemd Timers

Cron jobs work fine for backups, but systemd timers provide better logging and dependency management. Create timer units for both full and binlog backups.

Full backup service (/etc/systemd/system/mysql-backup-full.service):

[Unit]
Description=MySQL Full Backup
Wants=mysql-backup-full.timer

[Service]
Type=oneshot
User=root
ExecStart=/opt/mysql-backup/full_backup.sh

[Install]
WantedBy=multi-user.target

Timer configuration (/etc/systemd/system/mysql-backup-full.timer):

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

[Timer]
OnCalendar=daily
RandomizedDelaySec=30min
Persistent=true

[Install]
WantedBy=timers.target

Enable and start the timer:

systemctl daemon-reload
systemctl enable mysql-backup-full.timer
systemctl start mysql-backup-full.timer
systemctl status mysql-backup-full.timer

Create similar units for binlog backups with OnCalendar=hourly to capture changes more frequently.

Backup Verification and Testing

Untested backups are worthless. Your backup strategy needs regular verification to ensure restores work when you need them.

Create a verification script that automatically tests backup integrity:

#!/bin/bash

BACKUP_DIR="/opt/mysql-backup/full"
TEST_DB="backup_test_$(date +%s)"
LOG_FILE="/var/log/mysql-backup-verification.log"

# Find latest backup
LATEST_BACKUP=$(find "$BACKUP_DIR" -name "mysql_full_*.sql.gz" -type f -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2-)

if [ -z "$LATEST_BACKUP" ]; then
  echo "$(date): No backup files found" >> "$LOG_FILE"
  exit 1
fi

echo "$(date): Testing backup: $LATEST_BACKUP" >> "$LOG_FILE"

# Create test database
mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

# Restore backup to test database
zcat "$LATEST_BACKUP" | sed "s/USE \`/USE \`${TEST_DB}_/g" | mysql -u root -p

# Verify table count
ORIGINAL_TABLES=$(mysql -u root -p -e "SELECT COUNT(*) as count FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');" | tail -1)
RESTORED_TABLES=$(mysql -u root -p -e "SELECT COUNT(*) as count FROM information_schema.tables WHERE table_schema LIKE '${TEST_DB}_%';" | tail -1)

if [ "$ORIGINAL_TABLES" -eq "$RESTORED_TABLES" ]; then
  echo "$(date): Backup verification successful ($RESTORED_TABLES tables)" >> "$LOG_FILE"
else
  echo "$(date): Backup verification failed: $ORIGINAL_TABLES vs $RESTORED_TABLES tables" >> "$LOG_FILE"
fi

# Cleanup test databases
mysql -u root -p -e "DROP DATABASE $TEST_DB;"

Run verification weekly to catch backup problems before you need to restore production data.

Monitoring and Alerting Setup

Backup failures often go unnoticed until you need the missing data. Set up monitoring that alerts you immediately when backups fail or when backup age exceeds acceptable limits.

Create a backup monitoring script:

#!/bin/bash

BACKUP_DIR="/opt/mysql-backup/full"
MAX_AGE_HOURS=26  # Alert if backup is older than 26 hours
ALERT_EMAIL="admin@yourdomain.com"

# Find latest backup timestamp
LATEST_BACKUP=$(find "$BACKUP_DIR" -name "mysql_full_*.sql.gz" -type f -printf '%T@ %p\n' | sort -n | tail -1)

if [ -z "$LATEST_BACKUP" ]; then
  echo "CRITICAL: No MySQL backups found in $BACKUP_DIR" | mail -s "MySQL Backup Alert" "$ALERT_EMAIL"
  exit 1
fi

BACKUP_TIME=$(echo "$LATEST_BACKUP" | cut -d' ' -f1)
CURRENT_TIME=$(date +%s)
AGE_HOURS=$(( (CURRENT_TIME - ${BACKUP_TIME%.*}) / 3600 ))

if [ $AGE_HOURS -gt $MAX_AGE_HOURS ]; then
  echo "WARNING: Latest MySQL backup is $AGE_HOURS hours old (max: $MAX_AGE_HOURS)" | mail -s "MySQL Backup Age Alert" "$ALERT_EMAIL"
fi

# Check backup log for recent failures
if grep -q "Backup failed" /var/log/mysql-backup.log | tail -20; then
  echo "ERROR: Recent backup failures detected" | mail -s "MySQL Backup Failure" "$ALERT_EMAIL"
fi

Configure this monitoring script to run every hour. It checks backup age and scans logs for failure patterns.

Recovery Scenarios and Best Practices

Different disaster scenarios require different recovery approaches. Hardware failure needs full restoration, while accidental data deletion might only need specific table recovery.

For complete server loss, your recovery checklist should include:

1. Provision replacement VPS with identical MySQL version
2. Install and configure MySQL with same settings
3. Restore latest full backup
4. Apply binary logs up to failure point
5. Verify application connectivity and data integrity

For table-level recovery, extract specific tables from your backup:

# Extract single table from compressed backup
zcat mysql_full_20261215_020000.sql.gz | sed -n '/^-- Table structure for table `users`/,/^-- Table structure for table/p' > users_restore.sql

# Apply to database
mysql -u root -p database_name < users_restore.sql

Document your specific recovery procedures and test them quarterly. Include database connection strings, file paths, and escalation contacts.

Frequently Asked Questions

How often should I run MySQL backups on my VPS?

Run full backups daily and binary log backups hourly. This provides point-in-time recovery with maximum 1-hour data loss. High-traffic sites should consider more frequent binary log backups every 15-30 minutes.

Can I perform MySQL backups without stopping the database?

Yes, mysqldump with --single-transaction creates consistent backups of InnoDB tables without locking. For MyISAM tables, you'll need brief read locks. Consider migrating MyISAM tables to InnoDB for better backup compatibility.

How much disk space do MySQL backups typically require?

Compressed mysqldump files are typically 20-40% of your original database size. Binary logs vary based on write activity but usually require 1-5GB per day for moderate traffic sites. Plan for 30-60 days of backup retention.

What's the difference between logical and physical MySQL backups?

Logical backups (mysqldump) export data as SQL statements - portable but slower to restore. Physical backups copy actual data files - faster restore but tied to specific MySQL versions and configurations. Use logical backups for most VPS setups.

Should I encrypt MySQL backups stored on the same VPS?

Absolutely. Use gpg encryption for backup files and store encryption keys separately. Consider offsite backup storage like S3 with client-side encryption. Never store unencrypted database dumps on the same server as your live database.