Automate MySQL Database Backups with Bash Scripts and Cron on Linux

Overview

Backup is the foundation of disaster recovery; it copies all or part of data from the application host to other storage media to prevent loss from mistakes or failures. For many systems, the database is everything, so proper backup is critical.

Why Backup?

Ensuring a reliable copy of your data protects against accidental deletion, hardware failure, and other disruptions.

Storage Media

Optical disc

Tape

Hard disk

Disk array

DAS (Direct‑Attached Storage)

NAS (Network‑Attached Storage)

SAN (Storage Area Network)

Cloud storage

The following steps focus on using a local disk as the storage medium.

1. Check Disk Space

Choose a disk with sufficient free space to avoid backup failures caused by insufficient storage.

2. Create Backup Directory

cd /home
mkdir backup
cd backup

3. Create Backup Shell Script

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

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

To compress the backup:

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

4. Add Execute Permission

chmod u+x bkDatabaseName.sh

Run the script once to verify it works:

./bkDatabaseName.sh

5. Schedule the Job with Crontab

First, ensure crontab is installed. If the command is not found, install it via your package manager (e.g., yum install crontabs on CentOS).

Open the crontab editor: crontab -e Add an entry such as: */1 * * * * /home/backup/bkDatabaseName.sh This runs the backup script every minute. Other examples:

# Daily at 3 AM
0 3 * * * /home/backup/bkDatabaseName.sh
# Every Monday at noon
0 12 * * 1 /home/backup/bkDatabaseName.sh

6. Test the Scheduled Task

Run ls a few times and verify that backup files appear after the scheduled interval. If the job fails, view the cron log:

tail -f /var/log/cron

Crontab Format

Crontab entries consist of six fields:

Minute (0‑59)

Hour (0‑23)

Day of month (1‑31)

Month (1‑12)

Day of week (0‑6, where 0 is Sunday)

Command to execute

Examples:

30 21 * * * /usr/local/apache/bin/apachectl restart   # Every day at 21:30
45 4 1,10,22 * * /usr/local/apache/bin/apachectl restart   # 1st, 10th, 22nd of each month at 04:45
0 23 * * 6 /usr/local/apache/bin/apachectl restart   # Every Saturday at 23:00