MySQL Automated Backup

This script automatically backs up, compresses, and cleans old backups of your MySQL/MariaDB databases. It is a critical tool that protects against data loss and is essential, especially in production environments.

What Does This Script Do?

This script ensures safe backup of your MySQL databases. It creates a separate backup file for each database and:

• Automatically backs up all databases
• Compresses backups with gzip to save disk space
• Adds timestamp to each backup
• Automatically deletes backups older than specified period
• Backs up all database objects including stored procedures, triggers, and events

Why Should You Use It?

Database backup is critical for recovering your system in case of any data loss. With this script:

• You can perform regular and automated backups
• You use disk space efficiently (with compression)
• Old backups are automatically cleaned
• A separate backup file is created for each database
• Full automation is achieved with cron job

Security Features

• Consistent backup for InnoDB tables (--single-transaction)
• System databases are automatically excluded
• Backup files are protected by compression
• Detailed reporting in case of errors

Step-by-Step Usage Guide

1. Create Script File

Save the script code to a file:

nano mysql_backup.sh

Paste the script code and save.

2. Configuration Settings

Edit variables at the beginning of the script:

BACKUP_DIR="/backup/mysql"          # Backup directory
MYSQL_USER="root"                    # MySQL username
MYSQL_PASSWORD="your_password"       # MySQL password
RETENTION_DAYS=7                     # How many days to keep backups

3. Secure Password Storage (Recommended)

Instead of storing password in script, use .my.cnf file:

# Create ~/.my.cnf file
[client]
user=root
password=your_password

# Restrict permissions
chmod 600 ~/.my.cnf

# Remove password from script, use only username
mysqldump -u"$MYSQL_USER" ...

4. Create Backup Directory

sudo mkdir -p /backup/mysql
sudo chown $USER:$USER /backup/mysql

5. Make Script Executable

chmod +x mysql_backup.sh

6. Test Run

./mysql_backup.sh

7. Automate with Cron Job

# Edit crontab
crontab -e

# Run every day at 02:00
0 2 * * * /path/to/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

Restore Backup

To restore a backup:

# Decompress backup
gunzip database_name_20240201_020000.sql.gz

# Restore database
mysql -u root -p database_name < database_name_20240201_020000.sql

Requirements

• MySQL/MariaDB: Database server must be installed and running
• mysqldump: MySQL backup tool must be installed
• gzip: Required for compression (usually installed by default)
• Write Permission: Write permission for backup directory
• Sufficient Disk Space: Enough space for backups
• MySQL Access Rights: Read permission for databases

Installation Check

Check if required tools are installed:

# MySQL/MariaDB check
mysql --version

# mysqldump check
mysqldump --version

# gzip check
gzip --version

MySQL User Permissions

Required permissions for backup:

# Connect to MySQL
mysql -u root -p

# Create backup user (recommended)
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Use Cases

1. Daily Automated Backup

Automatically back up your production databases every day. Achieve full automation with cron job.

2. Disaster Recovery Preparation

Take regular backups to quickly restore in case of server crash, hardware failure, or data corruption.

3. Data Migration

You can back up and restore when moving the database to another server.

4. Test Environments

You can use backups to create copies of production data in test environments.

5. Data Analysis

You can use backups to analyze production data without putting load on the production server.

6. Compliance and Auditing

Regular backup is mandatory in some industries. You can ensure compliance with this script.

Usage Examples

Example 1: Basic Usage

# Run script
./mysql_backup.sh

# Output:
# ======================================
#    MYSQL BACKUP PROCESS
# ======================================
# Backup directory: /backup/mysql
# Date: 20240201_020000
# Retention: 7 days
# 
# 📦 Backing up database: myapp
#   ✓ Backup completed: /backup/mysql/myapp_20240201_020000.sql.gz
#   📊 Size: 15M

Example 2: Automation with Cron Job

# Edit crontab
crontab -e

# Run every day at 02:00
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

# Run every 6 hours
0 */6 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

Example 3: Backup to Remote Server

# Add rsync to script
rsync -avz /backup/mysql/ user@remote-server:/remote/backup/mysql/

Example 4: Email Notification

# Add to end of script
if [ $? -eq 0 ]; then
    echo "Backup completed successfully" | mail -s "MySQL Backup Success" [email protected]
else
    echo "Backup failed!" | mail -s "MySQL Backup Failed" [email protected]
fi

Troubleshooting

Problem: "Access denied for user" Error

Solution: Check MySQL username and password. If using .my.cnf file, check permissions:

chmod 600 ~/.my.cnf

Problem: "mysqldump: command not found"

Solution: mysqldump is not installed. Install it:

# Ubuntu/Debian
sudo apt-get install mysql-client

# CentOS/RHEL
sudo yum install mysql

Problem: "No space left on device"

Solution: Check disk space and clean old backups:

df -h
du -sh /backup/mysql
# Manually delete old backups or reduce RETENTION_DAYS value

Problem: Backup Very Slow

Solution: For large databases:

• Use --lock-tables instead of --single-transaction (for MyISAM)
• Back up large tables separately
• Schedule backup during low traffic hours

Problem: Backup File Corrupted

Solution: Check backup file:

# Test compressed file
gunzip -t backup_file.sql.gz

# Check SQL file
head -n 20 backup_file.sql