How to Automate MySQL Backups with Shell Scripts and Cron

Backing up databases is the foundation of disaster recovery; it prevents data loss caused by operational errors or system failures. For many websites and systems, the database is everything, so reliable backup procedures are critical.

Why Backups Matter

Backups protect against accidental deletions, hardware faults, and other disruptions. The article outlines common storage media (optical disc, tape, hard disk, RAID, DAS, NAS, SAN, cloud storage) and focuses on using a local disk for demonstration.

Preparing the Environment

Check available disk space with # df -h and choose a disk with sufficient free space.

Create a dedicated backup directory, e.g., mkdir /home/backup.

Writing the Backup Shell Script

Create a script file (replace DatabaseName with your actual database name):

#!/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

Remember to replace username, password, and DatabaseName with real values.

Making the Script Executable

chmod u+x bkDatabaseName.sh

Run the script once to verify it works:

./bkDatabaseName.sh

Scheduling with Cron

Ensure crontab is installed (install via yum install crontabs on CentOS if missing). Then edit the crontab: crontab -e Add the following line to execute the backup script every minute: */1 * * * * /home/backup/bkDatabaseName.sh This schedule means the script runs once per minute.

Testing and Monitoring

After a few minutes, list the backup directory to confirm new files appear. If the job fails, view the cron log: # tail -f /var/log/cron The log will show entries for each cron execution, helping diagnose issues.

Additional Notes

Store backups on a separate physical disk or network storage for better safety.

Adjust the cron schedule to a more realistic interval (hourly, daily) for production use.

Secure the script by restricting file permissions and avoiding plain‑text passwords.