Automate MySQL Backups with Bash and Cron

Why backup matters

Backup is the foundation of disaster recovery; copying all or part of a database to another storage medium prevents data loss caused by human error or system failure.

Choose a storage medium

The tutorial focuses on using a local disk, but other media such as optical discs, tape, DAS, NAS, SAN, or cloud storage can be used similarly.

Step 1: Check disk space

# df -h

Ensure the target disk has enough free space to store backup files.

Step 2: Create a backup directory

cd /home
mkdir backup
cd backup

In the example, the /home partition has sufficient space, so the backup directory is created there.

Step 3: Write the backup script

Replace DatabaseName, username, and password with your actual values.

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

For compressed backups, pipe the dump to gzip:

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

Step 4: Make the script executable

chmod u+x bkDatabaseName.sh

Run the script once to confirm it works without errors.

./bkDatabaseName.sh

Step 5: Schedule the script with cron

First verify that crontab is installed; if the command is missing, install it via yum on CentOS. # crontab -e Add the following line to run the backup every minute (adjust the schedule as needed):

*/1 * * * * /home/backup/bkDatabaseName.sh

Step 6: Verify execution

Run ls a few times and wait a minute to see if new backup files appear. If the job fails, inspect the cron log: # tail -f /var/log/cron The log output shows when cron starts and finishes the scheduled task.