Automate MySQL Database Backups on Linux with Bash and Cron

Backing up databases is a fundamental part of disaster recovery; losing data due to human error or system failure can be catastrophic, so regular automated backups are critical.

Choose a storage medium

The article lists common media such as CD, tape, hard disk, disk arrays, DAS, NAS, SAN, and cloud storage, but focuses on using a local disk for the tutorial.

1. Check available disk space

Use commands like df -h to ensure the target partition has enough free space; storing backups on a separate disk is recommended.

2. Create a backup directory

cd /home
mkdir backup
cd backup

3. Write the backup shell script

Replace DatabaseName, username, and password with actual values. Two variants are provided: one that creates a plain .sql file and another that pipes the dump through gzip to produce a compressed .sql.gz file.

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

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

4. Make the script executable

chmod u+x bkDatabaseName.sh

Run the script once ( ./bkDatabaseName.sh) to verify it works.

5. Add a cron job

First ensure crontab is installed (e.g., yum install crontabs on CentOS). Then edit the crontab: crontab -e Add a line such as: */1 * * * * /home/backup/bkDatabaseName.sh This runs the backup script every minute; adjust the schedule as needed (e.g., 0 3 * * * /home/backup/bkDatabaseName.sh for daily 3 AM backups).

6. Test the scheduled task

Run ls repeatedly and check that new backup files appear after the expected interval. If the job fails, view the cron log with tail -f /var/log/cron.

Crontab syntax reference

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

Example entries: 30 21 * * * /usr/local/apache/bin/apachectl restart – restart Apache nightly at 21:30. 45 4 1,10,22 * * /usr/local/apache/bin/apachectl restart – restart on the 1st, 10th, and 22nd of each month at 04:45. 0 */1 * * * /usr/local/apache/bin/apachectl restart – restart every hour.

These examples illustrate how to craft schedules for any required backup frequency.