How to Set Up MySQL Database Backup with Shell Script and Cron on Linux

Backup is the foundation of disaster recovery; it copies all or part of data from a host to another storage medium to prevent loss caused by mistakes or failures, especially for database‑centric applications.

Why backup? Images illustrate the importance of having reliable copies of your data.

Disaster‑recovery plan outlines the overall strategy, while storage media include optical disks, tapes, hard drives, DAS, NAS, SAN, and cloud storage.

Below is a step‑by‑step tutorial using a local disk as the storage medium.

1. Check disk space # df -hFilesystem Choose a disk with enough free space; avoid using the same disk that holds the database.

2. Create backup directory

cd /home
mkdir backupcd backup

3. Create backup shell script

Replace DatabaseName with your actual database name.

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

To compress the dump:

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

Note: substitute username, password, and DatabaseName with real values.

4. Add executable permission chmod u+x bkDatabaseName.sh Run the script once to ensure it works: ./bkDatabaseName.sh 5. Add scheduled task (cron)

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

Open the crontab editor: crontab -e Add the following line to execute the backup script every minute (adjust the schedule as needed): * /1 * * * * /home/backup/bkDatabaseName.sh This means the script runs once per minute.

6. Test the scheduled task

Run ls a few times and check whether new backup files appear after a minute.

If the job fails, view the cron log: # tail -f /var/log/cron The log will show entries such as start and finish times for run-parts and any errors.