Daily MySQL Backup Script with Bash: A Complete Guide

Why Backup MySQL Databases?

Backing up your MySQL databases is critical for disaster recovery, data integrity, and ensuring you never lose important information. Automating this task saves time and reduces the risk of human error.

What You'll Need

• A Linux server with MySQL installed
• Bash shell (default in most distributions)
• Access to mysqldump command
• Basic knowledge of using the terminal

Step-by-Step Bash Script

Here’s a script to backup a MySQL database and store it with a timestamped filename. Make sure to replace the placeholder values with your actual MySQL credentials and database name.

#!/bin/bash

# MySQL credentials
USER="your_username"
PASSWORD="your_password"
DATABASE="your_database"

# Backup destination
BACKUP_DIR="/path/to/backup"
DATE=$(date +"%F")
FILENAME="$DATABASE-$DATE.sql"

# Create backup
mysqldump -u $USER -p$PASSWORD $DATABASE > "$BACKUP_DIR/$FILENAME"

# Optional: Delete backups older than 7 days
find "$BACKUP_DIR" -type f -name "*.sql" -mtime +7 -exec rm {} \;

# Print completion message
echo "Backup for $DATABASE completed on $DATE"

Automate with Cron

To run this script every day automatically, add it to your crontab.

1. Open the crontab: crontab -e
2. Add the following line to run the script daily at 2 AM:

0 2 * * * /bin/bash /path/to/your_script.sh

Make sure the script is executable: chmod +x /path/to/your_script.sh

Best Practices

• Do not hard-code passwords in production scripts; use ~/.my.cnf instead.
• Store backups in a secure and separate location.
• Use compression to save disk space: mysqldump ... | gzip > file.sql.gz
• Test your backups regularly by restoring them on a test server.