Set Up MySQL Database Backup on RHEL 9: Complete Automation Tutorial

Installing and Configuring MySQL on RHEL 9

Before setting up automated backups, you need MySQL properly installed on your RHEL 9 system. Most Hostperl VPS customers start with a clean RHEL 9 installation and need database services configured from scratch.

Install MySQL server and client tools:

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

Secure your MySQL installation with the built-in security script:

sudo mysql_secure_installation

This script removes test databases, disables remote root access, and sets a strong root password. Choose 'Y' for all security questions unless you have specific requirements otherwise.

Creating a Dedicated Backup User

Never use your root MySQL account for automated backups. Create a dedicated backup user with minimal privileges needed for the job.

Log into MySQL as root:

mysql -u root -p

Create the backup user and grant necessary permissions:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_backup_password123';
GRANT SELECT, LOCK TABLES, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup_user'@'localhost';
GRANT RELOAD ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

These permissions allow the backup user to read all databases, lock tables during backup, and reload the privilege tables. This follows the principle of least privilege for security.

Setting Up MySQL Database Backup Directory Structure

Organization matters when managing database backups across multiple clients or projects. Create a logical directory structure that scales with your hosting needs.

Create the main backup directory:

sudo mkdir -p /var/backups/mysql/daily
sudo mkdir -p /var/backups/mysql/weekly
sudo mkdir -p /var/backups/mysql/monthly
sudo mkdir -p /var/backups/mysql/logs

Set proper ownership and permissions:

sudo chown -R mysql:mysql /var/backups/mysql
sudo chmod 750 /var/backups/mysql
sudo chmod 755 /var/backups/mysql/daily /var/backups/mysql/weekly /var/backups/mysql/monthly

This structure separates backups by retention period and includes a dedicated logs directory for troubleshooting backup issues.

Creating the MySQL Backup Script

Your backup script needs to handle multiple databases, compression, error logging, and cleanup of old backups. Here's a production-ready script that covers all these requirements.

Create the backup script:

sudo nano /usr/local/bin/mysql-backup.sh

Add this complete backup script:

#!/bin/bash

# MySQL Database Backup Script for RHEL 9
# Configuration
BACKUP_DIR="/var/backups/mysql"
MYSQL_USER="backup_user"
MYSQL_PASS="secure_backup_password123"
MYSQL_HOST="localhost"
DATE=$(date +"%Y%m%d_%H%M%S")
LOG_FILE="$BACKUP_DIR/logs/backup_$DATE.log"
RETENTION_DAYS=7

# Function to log messages
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Start backup process
log_message "Starting MySQL backup process"

# Get list of all databases except system databases
DATABASES=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" | grep -Ev "^(Database|information_schema|mysql|performance_schema|sys)$")

if [ -z "$DATABASES" ]; then
    log_message "ERROR: No databases found or connection failed"
    exit 1
fi

# Backup each database
for DATABASE in $DATABASES; do
    log_message "Backing up database: $DATABASE"
    
    BACKUP_FILE="$BACKUP_DIR/daily/${DATABASE}_${DATE}.sql"
    
    mysqldump -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --hex-blob \
        "$DATABASE" > "$BACKUP_FILE"
    
    if [ $? -eq 0 ]; then
        # Compress the backup
        gzip "$BACKUP_FILE"
        log_message "SUCCESS: Database $DATABASE backed up and compressed"
    else
        log_message "ERROR: Failed to backup database $DATABASE"
        rm -f "$BACKUP_FILE"
    fi
done

# Clean up old backups
log_message "Cleaning up backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR/daily" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

log_message "MySQL backup process completed"

Make the script executable:

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

This script uses --single-transaction for InnoDB tables to ensure consistent backups without locking, and includes stored procedures, triggers, and events in the backup.

Automating MySQL Database Backup with Cron

Schedule your backups to run during low-traffic periods. For most hosting scenarios, early morning hours work best to minimize impact on website performance.

Open the root crontab:

sudo crontab -e

Add these backup schedules:

# Daily MySQL backups at 2:30 AM
30 2 * * * /usr/local/bin/mysql-backup.sh

# Weekly backup to separate directory (Sundays at 3:00 AM)
0 3 * * 0 /usr/local/bin/mysql-backup.sh weekly

# Monthly backup (1st day of month at 4:00 AM)
0 4 1 * * /usr/local/bin/mysql-backup.sh monthly

You can modify the script to accept parameters for different backup directories based on the schedule type. This gives you multiple restoration points with different retention policies.

Testing MySQL Backup and Restore Process

Your backup system is only as good as your ability to restore from it. Regular testing prevents backup corruption from going unnoticed until you desperately need that data.

Create a test database to practice restoration:

mysql -u root -p -e "CREATE DATABASE backup_test;"
mysql -u root -p backup_test -e "CREATE TABLE test_data (id INT PRIMARY KEY, content TEXT);"
mysql -u root -p backup_test -e "INSERT INTO test_data VALUES (1, 'Test backup data');"

Run your backup script manually to create a backup of this test database:

sudo /usr/local/bin/mysql-backup.sh

Now test the restoration process:

# Drop the test database to simulate data loss
mysql -u root -p -e "DROP DATABASE backup_test;"

# Find the most recent backup file
ls -la /var/backups/mysql/daily/ | grep backup_test

# Restore from backup (replace with actual filename)
zcat /var/backups/mysql/daily/backup_test_YYYYMMDD_HHMMSS.sql.gz | mysql -u root -p

# Verify restoration
mysql -u root -p backup_test -e "SELECT * FROM test_data;"

Document this restoration process. During actual data loss incidents, having clear restoration steps reduces recovery time and stress.

Monitoring Backup Success and Failure

Automated backups fail silently unless you build in monitoring. Your backup system should alert you when something goes wrong, not leave you discovering backup failures during a recovery attempt.

Enhance your backup script with notification capabilities. Add this function to your backup script after the configuration section:

# Email notification function
send_notification() {
    local subject="$1"
    local message="$2"
    
    echo "$message" | mail -s "$subject" admin@yourdomain.com
}

# Check if backup completed successfully
if [ $BACKUP_SUCCESS -eq 0 ]; then
    send_notification "MySQL Backup Success" "All databases backed up successfully on $(hostname) at $(date)"
else
    send_notification "MySQL Backup FAILED" "Backup process failed on $(hostname). Check logs at $LOG_FILE"
fi

Install mail utilities if not already present:

sudo dnf install mailx -y

Configure your system's mail settings to work with your hosting provider's SMTP service. Many VPS hosting customers use external SMTP services for reliable email delivery.

Optimizing MySQL Database Backup Performance

Large databases require optimization to complete backups within reasonable time windows. Several mysqldump options can significantly improve backup performance and reduce server load.

For large databases, modify your mysqldump command with these performance options:

mysqldump -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
    --single-transaction \
    --quick \
    --lock-tables=false \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --compress \
    --extended-insert \
    "$DATABASE" | gzip > "${BACKUP_FILE}.gz"

The --quick option retrieves rows one at a time rather than buffering the entire result set. --extended-insert uses multiple-row INSERT syntax to reduce backup file size and improve restore speed.

For extremely large databases, consider using mysqldump with parallel processing:

# Backup specific tables in parallel
mysqldump --where="id % 4 = 0" database_name large_table > part1.sql &
mysqldump --where="id % 4 = 1" database_name large_table > part2.sql &
mysqldump --where="id % 4 = 2" database_name large_table > part3.sql &
mysqldump --where="id % 4 = 3" database_name large_table > part4.sql &
wait

This approach works when you have tables with numeric primary keys and need to minimize backup windows.

Implementing Backup Rotation and Storage Management

Storage costs accumulate quickly with database backups, especially for hosting providers managing multiple client databases. Implement intelligent retention policies that balance recovery needs with storage efficiency.

Create an enhanced cleanup function that maintains different retention periods:

# Enhanced cleanup with multiple retention periods
cleanup_backups() {
    local backup_dir="$1"
    local daily_retention=7
    local weekly_retention=30
    local monthly_retention=365
    
    # Keep daily backups for 7 days
    find "$backup_dir/daily" -name "*.sql.gz" -mtime +$daily_retention -delete
    
    # Move weekly backups older than 30 days to archive or delete
    find "$backup_dir/weekly" -name "*.sql.gz" -mtime +$weekly_retention -delete
    
    # Keep monthly backups for 1 year
    find "$backup_dir/monthly" -name "*.sql.gz" -mtime +$monthly_retention -delete
    
    log_message "Backup cleanup completed"
}

For remote storage, consider uploading backups to object storage services. Add this to your backup script:

# Upload to remote storage (example with rsync)
rsync -avz --delete /var/backups/mysql/ backup-server:/remote/mysql-backups/$(hostname)/

if [ $? -eq 0 ]; then
    log_message "Backups successfully uploaded to remote storage"
else
    log_message "ERROR: Failed to upload backups to remote storage"
fi

Frequently Asked Questions

How often should I test MySQL database backup restoration?

Test your backup restoration process monthly at minimum. For production systems, weekly testing ensures you can recover quickly when needed. Set up automated restoration tests to a separate test server if possible.

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

mysqldump creates logical backups (SQL statements) that are portable across MySQL versions and platforms. Physical backups copy actual database files and are faster for large databases but less flexible. For most hosting scenarios, mysqldump provides better compatibility and easier restoration.

Can I backup MySQL databases while they're in use?

Yes, using the --single-transaction option ensures consistent backups of InnoDB tables without locking. MyISAM tables require brief locks during backup. For busy production systems, schedule backups during low-traffic periods.

How much disk space should I allocate for MySQL backups?

Plan for backups to use 20-30% of your total database size when compressed. Factor in your retention policy - keeping 7 daily, 4 weekly, and 12 monthly backups requires planning for about 23 backup copies total.

Should I encrypt MySQL database backups?

Yes, especially for production data containing sensitive information. Use GPG encryption after compression: gzip backup.sql | gpg --cipher-algo AES256 --compress-algo 1 --symmetric --output backup.sql.gz.gpg. Store encryption keys securely and separate from backup files.