Backups are the foundation of disaster recovery. They protect against accidental operations, system failures, or unexpected data loss by copying data from the primary storage to another medium. For many systems and websites, the database is the heart of everything, making regular database backups absolutely critical.
This guide explains how to schedule MySQL database backups on Linux using shell scripts and cron jobs.
Storage Media Options #
Common backup storage media include:
- Optical disks
- Magnetic tape
- Hard drives
- Disk arrays
- DAS (Direct Attached Storage)
- NAS (Network Attached Storage)
- SAN (Storage Area Network)
- Cloud storage
In this example, we’ll focus on saving backups to a local disk, but the same principles apply to other media.
1. Check Disk Space #
Before creating backups, ensure you have enough disk space to avoid failures. Use the following command:
df -h
It’s best to store backups on a separate disk or external medium rather than the same disk where the database runs.
2. Create a Backup Directory #
Suppose /home has enough space. Create a backup folder:
cd /home
mkdir backup
cd backup
3. Write a Backup Shell Script #
Create a script named bkDatabaseName.sh:
vi bkDatabaseName.sh
Insert the following code, replacing username, password, and DatabaseName with actual values:
#!/bin/bash
mysqldump -uusername -ppassword DatabaseName > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql
To compress the backup:
#!/bin/bash
mysqldump -uusername -ppassword DatabaseName | gzip > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz
4. Make the Script Executable #
Grant execution permission:
chmod u+x bkDatabaseName.sh
Test it manually:
./bkDatabaseName.sh
5. Schedule Backups with Cron #
Check if crontab is installed
#
If the command is missing, install it. For example, on CentOS/RHEL:
yum install cronie
On Debian/Ubuntu:
sudo apt-get install cron
Add a Cron Job #
Edit the cron table:
crontab -e
Add the following line:
*/1 * * * * /home/backup/bkDatabaseName.sh
This runs the backup script every minute (for testing). In practice, you might schedule it daily or weekly, e.g.:
0 2 * * * /home/backup/bkDatabaseName.sh
(This runs the script at 2 AM daily.)
6. Verify the Scheduled Task #
Check if new backup files are created:
ls /home/backup
If the task fails, review cron logs:
tail -f /var/log/cron
Conclusion #
Regular backups are essential to safeguard your MySQL databases. By combining a simple shell script with cron scheduling, you can ensure automated and reliable data protection. For production systems, consider storing backups on separate disks, external storage, or cloud platforms for added resilience.
✅ With this setup, your MySQL backups run automatically, reducing the risk of catastrophic data loss.