Automate MySQL Backups with Bash Scripts and Cron Jobs

Why Backups Matter

Backing up data is the foundation of disaster recovery; it protects against accidental deletions or system failures by copying databases to separate storage media.

Choosing a Storage Medium

Optical disc Magnetic tape Hard disk Disk array DAS (Direct‑Attached Storage) NAS (Network‑Attached Storage) SAN (Storage Area Network) Cloud storage

The tutorial focuses on using a local hard‑disk as the backup target.

Step‑by‑Step Backup Setup

1. Check Available Disk Space

Ensure the destination disk has enough free space to avoid backup failures.

# df -h

2. Create a Backup Directory

Choose a directory on a disk with sufficient space, e.g., /home/backup.

cd /home
mkdir backup
cd backup

3. Write the Backup Shell Script

Replace DatabaseName with the actual MySQL database name, and adjust the username/password.

#!/bin/bash
mysqldump -uusername -ppassword DatabaseName > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql

To compress the dump directly:

#!/bin/bash
mysqldump -uusername -ppassword DatabaseName | gzip > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz

Note: Replace username, password, and DatabaseName with real values.

4. Make the Script Executable

chmod u+x bkDatabaseName.sh

Run it once to verify it works:

./bkDatabaseName.sh

5. Schedule the Backup with Cron

First, ensure crontab is installed (install via yum install crontabs on CentOS if missing).

Edit the crontab file: crontab -e Add the following line to run the script every minute (adjust the schedule as needed): */1 * * * * /home/backup/bkDatabaseName.sh This entry means the script executes once per minute.

6. Verify the Cron Job

After a few minutes, list the backup directory to see generated files, or monitor the cron log: # tail -f /var/log/cron Typical log entries show the cron daemon starting and finishing the scheduled task.