How to Automate MySQL Database Backups with Bash and Crontab

Overview

Backup is the foundation of disaster recovery, copying all or part of data from a host to another storage medium to prevent loss caused by mistakes or failures. For many systems, the database is everything, so proper backup is critical.

Why Backup?

Ensuring data can be restored after accidental deletion, hardware failure, or other incidents.

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 example uses local disks as the storage medium and demonstrates a basic backup script; other media follow similar access methods.

1. Check Disk Space

Choose a disk with sufficient free space to avoid backup failures.

2. Create Backup Directory

Example commands:

cd /home

mkdir backup

cd backup

3. Write Backup Shell Script

Replace DatabaseName with the actual database name.

#!/bin/bash

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

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

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

4. Make Script Executable

chmod u+x bkDatabaseName.sh

Test the script:

./bkDatabaseName.sh

5. Add Scheduled Task (crontab)

Check if crontab is installed; if not, install it via yum or rpm on CentOS.

Open the crontab editor: crontab -e Add an entry to run the backup script every minute: */1 * * * * /home/backup/bkDatabaseName.sh Or schedule daily at 03:00:

0 3 * * * /home/backup/bkDatabaseName.sh

6. Test the Task

Run ls a few times and verify that backup files appear after a minute. If the task fails, view the log:

tail -f /var/log/cron

Crontab Format

Fields: minute (0‑59) hour (0‑23) day‑of‑month (1‑31) month (1‑12) day‑of‑week (0‑6, 0=Sunday) command.

Examples:

30 21 * * * /usr/local/apache/bin/apachectl restart

45 4 1,10,22 * * /usr/local/apache/bin/apachectl restart

10 1 * * 6,0 /usr/local/apache/bin/apachectl restart

0,30 18-23 * * * /usr/local/apache/bin/apachectl restart

0 23 * * 6 /usr/local/apache/bin/apachectl restart

0 */1 * * * /usr/local/apache/bin/apachectl restart

These examples illustrate how to schedule tasks at specific times or intervals.