Secure, Efficient MySQL Automated Backup Script – Zero Maintenance

Why Automated MySQL Backups Matter

Accidental table deletions, server failures, or malicious attacks can jeopardize critical business data, making regular, reliable backups the last line of defense. Manual backups are error‑prone and easy to forget, so an automated, hands‑free solution is essential.

Key Features of the Script

Secure Backup: Excludes default system databases, backing up only business data.

Compressed Storage: Uses gzip to shrink backup files, saving over 70% disk space.

Log Tracing: Detailed logs record success or failure for each database.

Automatic Cleanup: Deletes files older than a configurable retention period.

Flexible Parameters: User, password, port, backup path, and retention days are all customizable.

Robust Error Handling: Checks paths, permissions, and cleans temporary files before execution.

Preparation Before Use

1. Create a Dedicated MySQL Backup Account

-- Create backup account (localhost only)
CREATE USER 'back'@'localhost' IDENTIFIED BY 'Xlsys2025';
-- Grant minimal required privileges
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON *.* TO 'back'@'localhost';
FLUSH PRIVILEGES;

2. Verify System Dependencies

Ensure the host runs Linux (CentOS, Ubuntu, etc.), MySQL 5.6+ or 8.0+, and that gzip is installed ( yum install gzip -y or apt install gzip -y).

Script Configuration

#!/bin/bash
# ===========================================================================
# Script: mysql_backup.sh
# Function: Automated full‑backup of MySQL databases
# ===========================================================================
# -------------------------- Backup Parameters -----------------------------
back_user_name="root"          # MySQL backup user (prefer non‑root)
back_user_pass="xlsys2025"      # Password
mysql_port=3066                 # Custom MySQL port (default 3306)
mysql_ip="localhost"           # MySQL host
back_path="/data/database/mysql/mysql_backup"  # Backup directory
back_day=10                     # Retention days
# -------------------------- System Paths -------------------------------
mysql_bin="/data/database/mysql/mysqldb/bin"   # Path to mysqldump
back_date=$(date +%F-%H)                         # Timestamp
temp_db_list="/tmp/DBname"                     # Temp DB list file
# ===========================================================================
# (The rest of the script follows the same structure as in the source.)

Adjust the variables at the top to match your environment, especially mysql_bin, back_path, and credentials.

Usage Steps

Upload and Grant Execution Rights

chmod +x /usr/local/sys/mysql_backup.sh
/usr/local/sys/mysql_backup.sh   # Run once manually to verify

Schedule with Cron

# Edit crontab
crontab -e
# Add daily run at 1 AM
0 1 * * * /usr/local/sys/mysql_backup.sh > /dev/null 2>&1

Verify Backup Results

# List generated .sql.gz files
ls -l /data/mysql_backup/
# Inspect the log for errors
cat /data/mysql_backup/logs/mysql_backup_$(date +%F-%H).log

Common Troubleshooting

1. "mysqldump: command not found"

Cause: mysql_bin points to a wrong directory. Fix by locating mysqldump with find / -name mysqldump and updating the script.

2. Permission Denied in Logs

Cause: Backup account lacks required privileges. Re‑run the SQL in the preparation section, ensuring LOCK TABLES and SELECT are granted.

3. Empty Backup Files

Cause: No business databases were detected (system databases were filtered out). Verify that your MySQL instance contains user databases or modify the filter list to include the desired ones.

Optimization Suggestions

Off‑site Storage: Sync backup files to remote servers or cloud storage (OSS, S3) to protect against local disk loss.

Regular Restore Tests: Perform monthly restore drills to confirm backup integrity.

Credential Security: Avoid plain‑text passwords; store them in .my.cnf with restricted permissions.

Monitoring & Alerts: Integrate DingTalk, WeChat Work, or other alerting mechanisms into the script to notify on failures.