How to Automate MySQL Database Backups with Shell Scripts and Cron

What Is a Backup?

A backup is the process of copying all or part of a data set from the application host's disk or array to another storage medium to prevent data loss caused by human error or system failure.

Why Back Up?

For many websites and systems, the database is everything, so ensuring proper database backups is crucial.

Disaster Recovery Plan

Implementing a disaster recovery plan involves regular backups and choosing appropriate storage media.

Storage Media

Optical disc

Tape

Hard disk

Disk array

DAS (Direct Attached Storage)

NAS (Network Attached Storage)

SAN (Storage Area Network)

Cloud storage

1. Check Disk Space

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

df -h

2. Create Backup Directory

Navigate to a suitable location and create a directory for backups.

cd /home
mkdir backup
cd backup

3. Create Backup Shell Script

Edit a new script file (replace DatabaseName with your actual database name): vi bkDatabaseName.sh Insert the following content (replace username , password , and DatabaseName with real values):

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

For compressed backups, use:

#!/bin/bash
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 ensure it works correctly.

5. Install and Configure Cron

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

Edit the crontab file: crontab -e Add a line to execute the backup script every minute (adjust the schedule as needed):

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

6. Test the Scheduled Task

Run a few ls commands and verify that backup files appear after a minute.

7. Verify Execution Logs

Monitor cron logs to confirm the task runs:

tail -f /var/log/cron