DEV Community

Cover image for Automating MySQL Backups with Bash Script & Cron
M. K. Tanjin Sarker
M. K. Tanjin Sarker

Posted on • Edited on

Automating MySQL Backups with Bash Script & Cron

#bash #mysql #automation #devops

🚀 Automating MySQL Backups with Bash Script & Cron

Databases are the heart of most applications—and losing them can be a nightmare. Manual backups work for quick fixes, but in production environments, automated MySQL backups are essential.

In this guide, we’ll build a simple Bash script to back up MySQL databases, compress them, keep them for a fixed number of days, and schedule it with cron.

🔑 Why Automate Backups?

  • ✅ Ensures you always have a recent copy of your data
  • ✅ Reduces risk of human error
  • ✅ Saves time compared to manual exports
  • ✅ Easy to restore when disaster strikes

🛠 Prerequisites

  • A Linux server with MySQL/MariaDB installed
  • A user with access to all databases you want to back up
  • Basic knowledge of shell commands

1) Login as Root 

sudo -i
Enter fullscreen mode Exit fullscreen mode

2) Create a Backup Folder & Script File 

mkdir -p /home/backup
touch /home/backup/backup-mysql.sh
nano /home/backup/backup-mysql.sh
Enter fullscreen mode Exit fullscreen mode

3.1) Add the Backup Script for all Databases.

Paste this inside the file:

#!/bin/bash
#----------------------------------------
# MySQL Backup Script
#----------------------------------------

# 🔧 Configuration
HOST='mysql-localhost'      # MySQL server host
USER='mysql-username'       # Username
PASSWORD='mysql-password'   # Password
DAYS_TO_KEEP=5              # Delete backups older than X days
GZIP=1                      # 1 = gzip compression, 0 = plain .sql
BACKUP_PATH='/home/backup/mysql'   # Location to store backups
#TIMESTAMP=$(date +"%d_%m_%Y_%H_%M_%S")   # Date-time for unique file names
TIMESTAMP=$(date -d "yesterday" +"%d_%m_%Y") #Date-time for previous date file names. reminder here crontab use after 12 am

#----------------------------------------

# 📂 Create backup folder if not exists
if [ ! -d "$BACKUP_PATH" ]; then
  mkdir -p "$BACKUP_PATH"
fi

# 📋 Get database list
databases=$(mysql -h "$HOST" -u "$USER" -p"$PASSWORD" -e "SHOW DATABASES;" | tr -d "|" | grep -v Database)

# 🔄 Loop through databases
for DB in $databases; do
  # Skip system databases
  if [ "$DB" = 'information_schema' ] || [ "$DB" = 'performance_schema' ] || [ "$DB" = 'mysql' ] || [ "$DB" = 'sys' ]; then
    echo "Skipping database: $DB"
    continue
  fi

  # Backup (with or without compression)
  if [ "$GZIP" -eq 0 ]; then
    echo "Backing up database: $DB (no compression)"
    mysqldump -h "$HOST" -u "$USER" -p"$PASSWORD" --databases "$DB" --single-transaction --quick --extended-insert --compact --hex-blob --max-allowed-packet=256M --net-buffer-length=2M > "$BACKUP_PATH/${DB}-${TIMESTAMP}.sql"
  else
    echo "Backing up database: $DB (with compression)"
    mysqldump -h "$HOST" -u "$USER" -p"$PASSWORD" --databases "$DB" --single-transaction --quick --extended-insert --compact --hex-blob --max-allowed-packet=256M --net-buffer-length=2M | gzip -9 -c > "$BACKUP_PATH/${DB}-${TIMESTAMP}.sql.gz"
  fi
done

# 🧹 Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ]; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find "$BACKUP_PATH"/* -mtime +"$DAYS_TO_KEEP" -exec rm {} \;
fi
Enter fullscreen mode Exit fullscreen mode

3.2) Add the Backup Script for single Database.

Paste this inside the file:

#!/bin/bash
#----------------------------------------
# MySQL Backup Script
#----------------------------------------

# 🔧 Configuration
HOST='mysql-localhost'      # MySQL server host
USER='mysql-username'       # Username
PASSWORD='mysql-password'   # Password
DB='mysql-database'
DAYS_TO_KEEP=5              # Delete backups older than X days
GZIP=1                      # 1 = gzip compression, 0 = plain .sql
BACKUP_PATH='/home/backup/mysql'   # Location to store backups
#TIMESTAMP=$(date +"%d_%m_%Y_%H_%M_%S")   # Date-time for unique file names
TIMESTAMP=$(date -d "yesterday" +"%d_%m_%Y") #Date-time for previous date file names. reminder here crontab use after 12 am

#----------------------------------------

# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
  mkdir -p $BACKUP_PATH
fi


  # Backup (with or without compression)
  if [ "$GZIP" -eq 0 ]; then
    echo "Backing up database: $DB (no compression)"
    mysqldump -h "$HOST" -u "$USER" -p"$PASSWORD" --databases "$DB" --single-transaction --quick --extended-insert --compact --hex-blob --max-allowed-packet=256M --net-buffer-length=2M > "$BACKUP_PATH/${DB}-${TIMESTAMP}.sql"
  else
    echo "Backing up database: $DB (with compression)"
    mysqldump -h "$HOST" -u "$USER" -p"$PASSWORD" --databases "$DB" --single-transaction --quick --extended-insert --compact --hex-blob --max-allowed-packet=256M --net-buffer-length=2M | gzip -9 -c > "$BACKUP_PATH/${DB}-${TIMESTAMP}.sql.gz"
  fi


# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi
Enter fullscreen mode Exit fullscreen mode

4) Make the Script Executable 

chmod +x /home/backup/backup-mysql.sh
Enter fullscreen mode Exit fullscreen mode

5) Test the Script

Run it manually:

/home/backup/backup-mysql.sh
Enter fullscreen mode Exit fullscreen mode

Backups will be stored inside:

/home/backup/mysql/
Enter fullscreen mode Exit fullscreen mode

6) Automate with Cron

Open crontab:

crontab -e
Enter fullscreen mode Exit fullscreen mode

Add this line to run the backup daily at 2:30 AM:

30 2 * * * /home/backup/backup-mysql.sh >/dev/null 2>&1
Enter fullscreen mode Exit fullscreen mode

Restart cron service:

sudo systemctl restart cron
Enter fullscreen mode Exit fullscreen mode

🧠 How It Works (Script Breakdown)

  • Configuration variables → Define MySQL credentials, backup path, retention days.
  • Timestamp → Ensures unique filenames (dbname-24_08_2025_02_30_00.sql.gz).
  • Timestamp → Ensures previous filenames (dbname-24_08_2025.sql.gz).
  • Database filtering → Skips system databases (information_schema, etc.).
  • mysqldump with --single-transaction → Ensures consistent snapshot for InnoDB without locking tables.
  • gzip compression → Saves space.
  • Retention policy (find -mtime) → Deletes backups older than 5 days.

✅ Conclusion

With just a few lines of Bash and a cron job, you’ve automated MySQL backups.

  • Backups run every day at 2:30 AM
  • Old backups older than 5 days are automatically removed
  • The script is simple to extend for remote storage, logging, or notifications

Automating backups is a small investment that can save you hours—or even your entire project—in case of data loss.

Top comments (0)