MySQL Database Backup with Shell Script and Cron

Backup is the foundation of disaster recovery, copying data to other storage media to prevent loss.

What is backup?

Backup copies all or part of data from the primary host to another storage medium.

Why backup?

Database is critical for many systems; regular backups are essential.

Storage media

CD, Tape, Hard Disk, Disk Array, DAS, NAS, SAN, Cloud Storage

The guide focuses on using a local disk and cron jobs to automate MySQL backups.

1. Check disk space

# df -hFilesystem

2. Create backup directory

cd /home
mkdir backup
cd backup

3. Create backup script

vi bkDatabaseName.sh

Insert the following content (replace placeholders with actual values):

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

Compressed version:

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

4. Make script executable

chmod u+x bkDatabaseName.sh

Run to verify:

./bkDatabaseName.sh

5. Add cron job

Check if crontab is installed:

# crontab
-bash: crontab: command not found

Install if necessary, then edit:

crontab -e

Add the line to run every minute:

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

6. Test execution

Monitor logs:

# tail -f /var/log/cron

Sample output shows the job being triggered.