Create production-grade MySQL backup automation with gzip compression, logrotate management, and systemd timer scheduling. Includes integrity checking and email notifications for reliable database protection.

Prerequisites

MySQL server installed and running
Root or sudo access
Basic understanding of Linux file permissions

What this solves

Automated MySQL backups protect your database from data loss while managing storage space efficiently. This tutorial sets up compressed backups with automated rotation, integrity verification, and failure notifications to ensure your MySQL data is consistently protected without manual intervention.

Step-by-step configuration

Install required packages

Install MySQL client tools and email utilities for backup operations and notifications.

sudo apt update
sudo apt install -y mysql-client gzip mailutils logrotate

sudo dnf install -y mysql mailx gzip logrotate

Create backup user and directory structure

Create a dedicated backup user and secure directory structure for storing backup files.

sudo useradd -r -s /bin/false backup-user
sudo mkdir -p /opt/mysql-backups/{daily,logs}
sudo chown -R backup-user:backup-user /opt/mysql-backups
sudo chmod 750 /opt/mysql-backups
sudo chmod 755 /opt/mysql-backups/daily /opt/mysql-backups/logs

Configure MySQL authentication

Create a MySQL user with backup privileges and configure secure authentication.

sudo mysql -u root -p

Run these commands in the MySQL prompt:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupP@ssw0rd2024!';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON . TO 'backup_user'@'localhost';
GRANT LOCK TABLES ON . TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Create MySQL credentials file

Store MySQL credentials securely in a configuration file with restricted permissions.

sudo -u backup-user touch /home/backup-user/.my.cnf

[mysqldump]
user=backup_user
password=BackupP@ssw0rd2024!
host=localhost
single-transaction=true
routines=true
triggers=true
set-gtid-purged=OFF

sudo chown backup-user:backup-user /home/backup-user/.my.cnf
sudo chmod 600 /home/backup-user/.my.cnf

Create the backup script

Create a comprehensive backup script with compression, logging, and integrity checking.

#!/bin/bash

MySQL Backup Script with Compression and Logging
Author: System Administrator
Date: $(date +%Y-%m-%d)

set -euo pipefail

Configuration
BACKUP_DIR="/opt/mysql-backups/daily"
LOG_DIR="/opt/mysql-backups/logs"
LOG_FILE="$LOG_DIR/backup-$(date +%Y-%m-%d).log"
RETENTION_DAYS=7
EMAIL_RECIPIENT="admin@example.com"
HOSTNAME=$(hostname)

Function to log messages
log_message() {
    local level=$1
    shift
    local message="$*"
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    echo "[$timestamp] [$level] $message" | tee -a "$LOG_FILE"
}

Function to send email notification
send_notification() {
    local subject="$1"
    local body="$2"
    echo "$body" | mail -s "$subject" "$EMAIL_RECIPIENT" 2>/dev/null || true
}

Start logging
log_message "INFO" "Starting MySQL backup on $HOSTNAME"

Get list of databases (excluding system databases)
DATABASES=$(mysql --defaults-file=/home/backup-user/.my.cnf -e "SHOW DATABASES;" | grep -Ev '^(Database|information_schema|performance_schema|mysql|sys)$')

if [ -z "$DATABASES" ]; then
    log_message "WARNING" "No user databases found to backup"
    exit 0
fi

Create timestamped backup directory
BACKUP_TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_SESSION_DIR="$BACKUP_DIR/$BACKUP_TIMESTAMP"
mkdir -p "$BACKUP_SESSION_DIR"

Backup each database
BACKUP_SUCCESS=true
for DB in $DATABASES; do
    log_message "INFO" "Starting backup of database: $DB"
    
    BACKUP_FILE="$BACKUP_SESSION_DIR/${DB}_${BACKUP_TIMESTAMP}.sql"
    COMPRESSED_FILE="${BACKUP_FILE}.gz"
    
    # Perform backup with compression
    if mysqldump --defaults-file=/home/backup-user/.my.cnf \
        --single-transaction \
        --routines \
        --triggers \
        --set-gtid-purged=OFF \
        --verbose \
        "$DB" | gzip > "$COMPRESSED_FILE"; then
        
        # Verify compressed file
        if gzip -t "$COMPRESSED_FILE" 2>/dev/null; then
            BACKUP_SIZE=$(du -h "$COMPRESSED_FILE" | cut -f1)
            log_message "INFO" "Successfully backed up $DB (Size: $BACKUP_SIZE)"
        else
            log_message "ERROR" "Backup verification failed for $DB - corrupted gzip file"
            rm -f "$COMPRESSED_FILE"
            BACKUP_SUCCESS=false
        fi
    else
        log_message "ERROR" "Failed to backup database: $DB"
        BACKUP_SUCCESS=false
    fi
done

Clean up old backups
log_message "INFO" "Cleaning up backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -type d -name "[0-9]_[0-9]" -mtime +$RETENTION_DAYS -exec rm -rf {} + 2>/dev/null || true

Calculate total backup size
TOTAL_SIZE=$(du -sh "$BACKUP_SESSION_DIR" 2>/dev/null | cut -f1 || echo "Unknown")

Send notification based on backup status
if [ "$BACKUP_SUCCESS" = true ]; then
    log_message "INFO" "MySQL backup completed successfully (Total size: $TOTAL_SIZE)"
    send_notification "[SUCCESS] MySQL Backup - $HOSTNAME" "MySQL backup completed successfully on $HOSTNAME\n\nBackup location: $BACKUP_SESSION_DIR\nTotal size: $TOTAL_SIZE\nTimestamp: $BACKUP_TIMESTAMP"
else
    log_message "ERROR" "MySQL backup completed with errors"
    send_notification "[ERROR] MySQL Backup Failed - $HOSTNAME" "MySQL backup completed with errors on $HOSTNAME\n\nPlease check the log file: $LOG_FILE\nTimestamp: $BACKUP_TIMESTAMP"
    exit 1
fi

log_message "INFO" "Backup process finished"

Set script permissions and ownership

Configure secure permissions for the backup script to ensure only the backup user can execute it.

sudo chown backup-user:backup-user /opt/mysql-backups/backup-script.sh
sudo chmod 750 /opt/mysql-backups/backup-script.sh

Configure log rotation

Set up logrotate to manage backup log files and prevent disk space issues.

/opt/mysql-backups/logs/*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    copytruncate
    su backup-user backup-user
    postrotate
        /bin/systemctl reload rsyslog > /dev/null 2>&1 || true
    endscript
}

Create systemd service unit

Create a systemd service to run the backup script with proper user context and security restrictions.

[Unit]
Description=MySQL Database Backup Service
Wants=network-online.target mysql.service
After=network-online.target mysql.service

[Service]
Type=oneshot
User=backup-user
Group=backup-user
ExecStart=/opt/mysql-backups/backup-script.sh
WorkingDirectory=/opt/mysql-backups
PrivateTmp=yes
NoNewPrivileges=yes
ProtectSystem=strict
ProtectHome=yes
ReadWritePaths=/opt/mysql-backups
StandardOutput=journal
StandardError=journal

Create systemd timer unit

Configure a systemd timer to run backups daily at 2 AM with randomized delay to prevent resource conflicts.

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

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

[Install]
WantedBy=timers.target

Enable and start the backup timer

Reload systemd configuration and enable the backup timer to start automatically on boot.

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

Test the backup system

Run a test backup to verify the configuration and check for any issues.

sudo systemctl start mysql-backup.service
sudo systemctl status mysql-backup.service

Verify your setup

Check the timer status and verify backup files are created correctly.

sudo systemctl status mysql-backup.timer
sudo systemctl list-timers mysql-backup.timer
ls -la /opt/mysql-backups/daily/
ls -la /opt/mysql-backups/logs/

Verify backup file integrity:

find /opt/mysql-backups/daily -name "*.gz" -type f -exec gzip -t {} \;
find /opt/mysql-backups/daily -name "*.gz" -type f -exec du -h {} \;

Check journal logs for service execution:

sudo journalctl -u mysql-backup.service --since today
sudo journalctl -u mysql-backup.timer --since "1 week ago"

Common issues

Symptom	Cause	Fix
Permission denied accessing backup directory	Incorrect ownership or permissions	`sudo chown -R backup-user:backup-user /opt/mysql-backups && sudo chmod 750 /opt/mysql-backups`
MySQL authentication failed	Wrong credentials or missing privileges	Verify `/home/backup-user/.my.cnf` and MySQL user permissions
Email notifications not working	Mail system not configured	Install and configure postfix: `sudo dpkg-reconfigure postfix`
Timer not triggering backups	Timer not enabled or systemd issue	`sudo systemctl enable mysql-backup.timer && sudo systemctl start mysql-backup.timer`
Backup files growing too large	No cleanup or long retention period	Adjust `RETENTION_DAYS` in backup script or add size-based cleanup
Corrupted backup files	Insufficient disk space or I/O errors	Check disk space with `df -h` and test with `gzip -t filename.gz`

Next steps

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash

set -euo pipefail

# MySQL Backup Installation Script
# Author: Infrastructure Engineering Team
# Description: Sets up automated MySQL database backups with compression and rotation

# Color codes for output
readonly RED='\033[0;31m'
readonly GREEN='\033[0;32m'
readonly YELLOW='\033[1;33m'
readonly NC='\033[0m' # No Color

# Configuration
readonly BACKUP_USER="backup-user"
readonly MYSQL_BACKUP_USER="backup_user"
readonly BACKUP_DIR="/opt/mysql-backups"
readonly SCRIPT_NAME="mysql-backup.sh"
readonly MYSQL_PASSWORD="${MYSQL_ROOT_PASSWORD:-}"
readonly EMAIL="${BACKUP_EMAIL:-admin@example.com}"

# Progress tracking
STEP=1
TOTAL_STEPS=8

# Functions
log_info() {
    echo -e "${GREEN}[${STEP}/${TOTAL_STEPS}]${NC} $1"
    ((STEP++))
}

log_warn() {
    echo -e "${YELLOW}WARNING:${NC} $1" >&2
}

log_error() {
    echo -e "${RED}ERROR:${NC} $1" >&2
}

usage() {
    cat << EOF
Usage: $0 [OPTIONS]

Options:
    -e EMAIL    Email address for backup notifications (default: admin@example.com)
    -p PASSWORD MySQL root password (can also set MYSQL_ROOT_PASSWORD env var)
    -h          Show this help message

Environment Variables:
    MYSQL_ROOT_PASSWORD    MySQL root password
    BACKUP_EMAIL          Email for notifications

Example:
    $0 -e admin@company.com -p mysqlrootpass
    MYSQL_ROOT_PASSWORD=secret ./install.sh -e admin@company.com
EOF
    exit 1
}

cleanup() {
    if [[ $? -ne 0 ]]; then
        log_error "Installation failed. Cleaning up..."
        if id "$BACKUP_USER" &>/dev/null; then
            userdel -r "$BACKUP_USER" 2>/dev/null || true
        fi
        rm -rf "$BACKUP_DIR" 2>/dev/null || true
        rm -f "/etc/cron.d/mysql-backup" 2>/dev/null || true
    fi
}

trap cleanup ERR

check_prerequisites() {
    if [[ $EUID -ne 0 ]]; then
        log_error "This script must be run as root"
        exit 1
    fi

    if ! command -v mysql &>/dev/null && ! command -v mariadb &>/dev/null; then
        log_error "MySQL/MariaDB server not found. Please install MySQL/MariaDB first."
        exit 1
    fi

    if [[ -z "$MYSQL_PASSWORD" ]]; then
        log_error "MySQL root password required. Use -p option or set MYSQL_ROOT_PASSWORD"
        exit 1
    fi
}

detect_distro() {
    if [[ ! -f /etc/os-release ]]; then
        log_error "Cannot detect distribution. /etc/os-release not found."
        exit 1
    fi

    . /etc/os-release
    case "$ID" in
        ubuntu|debian)
            PKG_INSTALL="apt update && apt install -y"
            MAIL_PKG="mailutils"
            ;;
        almalinux|rocky|centos|rhel|ol)
            PKG_INSTALL="dnf install -y"
            MAIL_PKG="mailx"
            ;;
        fedora)
            PKG_INSTALL="dnf install -y"
            MAIL_PKG="mailx"
            ;;
        amzn)
            PKG_INSTALL="yum install -y"
            MAIL_PKG="mailx"
            ;;
        *)
            log_error "Unsupported distribution: $ID"
            exit 1
            ;;
    esac
}

install_packages() {
    log_info "Installing required packages..."
    
    case "$ID" in
        ubuntu|debian)
            eval "$PKG_INSTALL mysql-client gzip $MAIL_PKG logrotate"
            ;;
        *)
            eval "$PKG_INSTALL mysql gzip $MAIL_PKG logrotate"
            ;;
    esac
}

create_backup_user() {
    log_info "Creating backup user and directory structure..."
    
    if ! id "$BACKUP_USER" &>/dev/null; then
        useradd -r -s /bin/false -d /home/$BACKUP_USER -m "$BACKUP_USER"
    fi

    mkdir -p "$BACKUP_DIR"/{daily,logs}
    chown -R "$BACKUP_USER:$BACKUP_USER" "$BACKUP_DIR"
    chmod 750 "$BACKUP_DIR"
    chmod 755 "$BACKUP_DIR"/daily "$BACKUP_DIR"/logs
}

configure_mysql_user() {
    log_info "Creating MySQL backup user..."
    
    mysql -u root -p"$MYSQL_PASSWORD" << EOF
CREATE USER IF NOT EXISTS '${MYSQL_BACKUP_USER}'@'localhost' IDENTIFIED BY 'BackupP@ssw0rd2024!';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO '${MYSQL_BACKUP_USER}'@'localhost';
GRANT LOCK TABLES ON *.* TO '${MYSQL_BACKUP_USER}'@'localhost';
FLUSH PRIVILEGES;
EOF
}

create_mysql_config() {
    log_info "Creating MySQL credentials file..."
    
    cat > "/home/$BACKUP_USER/.my.cnf" << 'EOF'
[mysqldump]
user=backup_user
password=BackupP@ssw0rd2024!
host=localhost
single-transaction=true
routines=true
triggers=true
set-gtid-purged=OFF
EOF
    
    chown "$BACKUP_USER:$BACKUP_USER" "/home/$BACKUP_USER/.my.cnf"
    chmod 600 "/home/$BACKUP_USER/.my.cnf"
}

create_backup_script() {
    log_info "Creating backup script..."
    
    cat > "/usr/local/bin/$SCRIPT_NAME" << EOF
#!/usr/bin/env bash

set -euo pipefail

# Configuration
BACKUP_DIR="$BACKUP_DIR/daily"
LOG_DIR="$BACKUP_DIR/logs"
LOG_FILE="\$LOG_DIR/backup-\$(date +%Y-%m-%d).log"
RETENTION_DAYS=7
EMAIL_RECIPIENT="$EMAIL"
HOSTNAME=\$(hostname)

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

# Function to send email notification
send_notification() {
    local subject="\$1"
    local body="\$2"
    echo "\$body" | mail -s "\$subject" "\$EMAIL_RECIPIENT" 2>/dev/null || true
}

# Start logging
log_message "INFO" "Starting MySQL backup on \$HOSTNAME"

# Get list of databases (excluding system databases)
DATABASES=\$(mysql --defaults-file=/home/$BACKUP_USER/.my.cnf -e "SHOW DATABASES;" | grep -Ev '^(Database|information_schema|performance_schema|mysql|sys)\$')

if [[ -z "\$DATABASES" ]]; then
    log_message "WARNING" "No user databases found to backup"
    exit 0
fi

# Create timestamped backup directory
BACKUP_TIMESTAMP=\$(date +%Y%m%d_%H%M%S)
BACKUP_SESSION_DIR="\$BACKUP_DIR/\$BACKUP_TIMESTAMP"
mkdir -p "\$BACKUP_SESSION_DIR"

# Backup each database
BACKUP_SUCCESS=true
for DB in \$DATABASES; do
    log_message "INFO" "Starting backup of database: \$DB"
    
    BACKUP_FILE="\$BACKUP_SESSION_DIR/\${DB}_\${BACKUP_TIMESTAMP}.sql"
    COMPRESSED_FILE="\${BACKUP_FILE}.gz"
    
    if mysqldump --defaults-file=/home/$BACKUP_USER/.my.cnf "\$DB" | gzip > "\$COMPRESSED_FILE"; then
        if gzip -t "\$COMPRESSED_FILE" 2>/dev/null; then
            BACKUP_SIZE=\$(du -h "\$COMPRESSED_FILE" | cut -f1)
            log_message "INFO" "Successfully backed up \$DB (Size: \$BACKUP_SIZE)"
        else
            log_message "ERROR" "Backup verification failed for \$DB"
            BACKUP_SUCCESS=false
        fi
    else
        log_message "ERROR" "Backup failed for \$DB"
        BACKUP_SUCCESS=false
    fi
done

# Cleanup old backups
find "\$BACKUP_DIR" -maxdepth 1 -type d -mtime +\$RETENTION_DAYS -exec rm -rf {} \; 2>/dev/null || true

# Send notification
if [[ "\$BACKUP_SUCCESS" == "true" ]]; then
    log_message "INFO" "All backups completed successfully"
    send_notification "MySQL Backup Success - \$HOSTNAME" "MySQL backup completed successfully on \$HOSTNAME at \$(date)"
else
    log_message "ERROR" "Some backups failed"
    send_notification "MySQL Backup Failed - \$HOSTNAME" "MySQL backup encountered errors on \$HOSTNAME. Check logs at \$LOG_FILE"
    exit 1
fi
EOF
    
    chmod 755 "/usr/local/bin/$SCRIPT_NAME"
    chown root:root "/usr/local/bin/$SCRIPT_NAME"
}

setup_cron() {
    log_info "Setting up daily cron job..."
    
    cat > "/etc/cron.d/mysql-backup" << EOF
# MySQL Daily Backup
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# Run daily at 2:00 AM
0 2 * * * $BACKUP_USER /usr/local/bin/$SCRIPT_NAME
EOF
    
    chmod 644 "/etc/cron.d/mysql-backup"
}

setup_logrotate() {
    log_info "Configuring log rotation..."
    
    cat > "/etc/logrotate.d/mysql-backup" << EOF
$BACKUP_DIR/logs/*.log {
    daily
    missingok
    rotate 30
    compress
    delaycompress
    notifempty
    su $BACKUP_USER $BACKUP_USER
}
EOF
}

verify_installation() {
    log_info "Verifying installation..."
    
    # Check backup user
    if ! id "$BACKUP_USER" &>/dev/null; then
        log_error "Backup user creation failed"
        return 1
    fi
    
    # Check directories
    if [[ ! -d "$BACKUP_DIR" ]]; then
        log_error "Backup directory creation failed"
        return 1
    fi
    
    # Check script
    if [[ ! -f "/usr/local/bin/$SCRIPT_NAME" ]]; then
        log_error "Backup script creation failed"
        return 1
    fi
    
    # Check MySQL user
    if ! mysql -u "$MYSQL_BACKUP_USER" -p'BackupP@ssw0rd2024!' -e "SELECT 1;" &>/dev/null; then
        log_error "MySQL backup user verification failed"
        return 1
    fi
    
    # Test backup script (dry run)
    sudo -u "$BACKUP_USER" "/usr/local/bin/$SCRIPT_NAME" || {
        log_warn "Backup script test run failed. Check MySQL configuration."
    }
    
    echo -e "${GREEN}Installation completed successfully!${NC}"
    echo "- Backup directory: $BACKUP_DIR"
    echo "- Daily backups scheduled at 2:00 AM"
    echo "- Notifications sent to: $EMAIL"
    echo "- Log files: $BACKUP_DIR/logs/"
}

# Parse command line arguments
while getopts "e:p:h" opt; do
    case $opt in
        e) EMAIL="$OPTARG" ;;
        p) MYSQL_PASSWORD="$OPTARG" ;;
        h) usage ;;
        *) usage ;;
    esac
done

# Main execution
main() {
    echo -e "${GREEN}MySQL Backup Setup Script${NC}"
    echo "================================"
    
    check_prerequisites
    detect_distro
    install_packages
    create_backup_user
    configure_mysql_user
    create_mysql_config
    create_backup_script
    setup_cron
    setup_logrotate
    verify_installation
}

main "$@"

Review the script before running. Execute with: bash install.sh

#MySQL #backup automation #database compression #mysqldump #systemd timers

Set up automated MySQL database backups with compression and rotation

Prerequisites

What this solves

Step-by-step configuration

Install required packages

Create backup user and directory structure

Configure MySQL authentication

Create MySQL credentials file

Create the backup script

MySQL Backup Script with Compression and Logging

Author: System Administrator

Date: $(date +%Y-%m-%d)

Configuration

Function to log messages

Function to send email notification

Start logging

Get list of databases (excluding system databases)

Create timestamped backup directory

Backup each database

Clean up old backups

Calculate total backup size

Send notification based on backup status

Set script permissions and ownership

Configure log rotation

Create systemd service unit

Create systemd timer unit

Enable and start the backup timer

Test the backup system

Verify your setup

Common issues

Next steps

Related tutorials

Set up ClickHouse and Kafka real-time data pipeline with streaming analytics

Install and configure DuckDB for analytical workloads with Python integration

Install and configure MongoDB 8.0 with authentication and SSL encryption

Don't want to manage this yourself?