求完善CentOS7下带邮件通知的MySQL自动备份脚本

Hey there, let's fix up your backup script step by step. I'll address all three issues you mentioned: the incomplete database list in emails, missing database size data, and replacing hardcoded credentials with your myuser/mypass variables.

Here's the fully revised script with all fixes and improvements:

#!/bin/bash
# Custom script to backup all MySQL dbs on localhost
# and output db list, sizes, and backup status to email

# Configuration Variables
myuser="root"
mypass="******"
backup_dir="/backups/WEB02DBs"
recipient_email="myemail@email.com"
date_stamp=$(date +"%d-%m-%y_%T")

# Create backup directory if it doesn't exist
mkdir -p $backup_dir

# Get clean list of databases (excluding system databases)
dblist=$(mysql -u $myuser -p$mypass -sN -e 'SHOW DATABASES' | grep -v -E 'information_schema|mysql|performance_schema')

# Initialize email content with timestamp
email_content="WEB02 Database Backup Report - $date_stamp\n\n"
email_content+="Successfully backed up databases:\n"

# Backup each database and collect size info
for db in $dblist; do
    # Create compressed, timestamped backup
    mysqldump -u $myuser -p$mypass $db | gzip -c > "$backup_dir/${db}_${date_stamp}.sql.gz"
    
    # Calculate database size (data + indexes) in human-readable MB
    db_size=$(mysql -u $myuser -p$mypass -sN -e "SELECT CONCAT(ROUND(SUM(data_length + index_length) / 1024 / 1024, 2), ' MB') FROM information_schema.TABLES WHERE table_schema = '$db'")
    
    # Add entry to email content
    email_content+="- $db (Size: $db_size)\n"
done

# Add total backup directory size to email
total_backup_size=$(du -sh $backup_dir | awk '{print $1}')
email_content+="\nTotal backup directory size: $total_backup_size\n"

# Send formatted email report
echo -e "$email_content" | mail -s "WEB02 DBs Backup Report - $date_stamp" $recipient_email

Key Fixes & Improvements #

• Unified Credentials: All mysql and mysqldump commands now use your myuser and mypass variables, so you only need to update credentials in one place.
• Correct Database List: Used mysql -sN to get a clean, header-free list of databases, then filtered out system dbs in one grep command (no messy multiple sed steps). The original script was echoing the filename dblist instead of its content—this fixes the "only performance schema showing" issue.
• Database Size Calculation: Added a query to information_schema.TABLES to calculate each database's total size (data + indexes) in human-readable MB format, which was missing entirely in your original script.
• Structured Email: Built a clear, readable email body with timestamp, individual database details, and total backup size, instead of sending a useless filename.
• Timestamped Backups: Added a timestamp to backup files to prevent overwriting old backups and track when each backup was created.
• Safety Checks: Ensured the backup directory exists before writing files, avoiding errors if the path is new.

内容的提问来源于stack exchange，提问作者Fishy