Automate MySQL Database Backups with Bash and Cron

Overview

Backup is the foundation of disaster recovery; it copies data from the application host to another storage medium to prevent loss due to mistakes or failures. For web services, the database is critical, so regular backups are essential.

Backup Media

Optical disc, Tape, Hard disk, Disk array, DAS, NAS, SAN, Cloud storage

Step 1 – Check Disk Space

Use a command such as df -h to ensure the target disk has enough free space. Prefer storing backups on a separate disk rather than the same one that holds the database.

Step 2 – Create Backup Directory

cd /home
mkdir backup
cd backup

Step 3 – Write Backup Shell Script

Replace DatabaseName, username, and password with actual values.

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

Optional compression:

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

Step 4 – Make Script Executable

chmod u+x bkDatabaseName.sh

Run it once to verify it works.

./bkDatabaseName.sh

Step 5 – Schedule with Crontab

Ensure crontab is installed (install via yum install crontabs on CentOS if missing). Edit the crontab: crontab -e Add a line such as: */1 * * * * /home/backup/bkDatabaseName.sh This runs the script every minute. Other examples show how to run at specific times (e.g., 0 3 * * * /home/backup/bkDatabaseName.sh for daily 3 am).

Step 6 – Verify Execution

After a minute, list the backup directory to see the generated file. If it fails, view the cron log:

tail -f /var/log/cron

Crontab Syntax

The six fields are: minute (0‑59), hour (0‑23), day of month (1‑31), month (1‑12), day of week (0‑6, 0 = Sunday), and the command to run.

Examples:

30 21 * * * /usr/local/apache/bin/apachectl restart – restart Apache at 21:30 daily.

45 4 1,10,22 * * /usr/local/apache/bin/apachectl restart – restart at 04:45 on the 1st, 10th, and 22nd of each month.

0 */1 * * * /usr/local/apache/bin/apachectl restart – restart every hour.