This bash script automates the process of daily backups for all MySQL/MariaDB databases on a Linux server. It stores each database backup in a dedicated directory under /var/backups/mysql/ and names the backup files using the format <database_name>_<date_time>.sql. Additionally, the script includes a cleanup function that deletes backups older t…

backup_mysql_db.sh

#!/bin/bash

# backup_mysql_db.sh
# This script backs up each MySQL/MariaDB database into its own file daily.
# The backups are stored in /var/backups/mysql/<database_name>/<database_name>_<date_time>.sql
# Backups older than 30 days are automatically deleted.
#
# Author: Ph33nx
# GitHub: https://github.com/ph33nx
#
# Usage:
# 1. Save this script as backup_mysql_db.sh
# 2. Make the script executable: chmod +x backup_mysql_db.sh
# 3. Schedule it to run daily using crontab:
#    sudo crontab -e
#    Add the following line to run the script at 2 AM daily:
#    0 2 * * * /path/to/backup_mysql_db.sh

# Define variables
BACKUP_ROOT_DIR="/var/backups/mysql"
DATE=$(date +%F_%H-%M-%S)

# Ensure the script is run as root
if [[ $EUID -ne 0 ]]; then
   echo "This script must be run as root" 
   exit 1
fi

# Create backup root directory if it does not exist
if [ ! -d "$BACKUP_ROOT_DIR" ]; then
    mkdir -p $BACKUP_ROOT_DIR
    echo "Created backup root directory: $BACKUP_ROOT_DIR"
fi

# Get a list of databases
databases=$(mysql -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")

# Loop through each database and back it up
for db in $databases; do
  BACKUP_DIR="$BACKUP_ROOT_DIR/$db"
  
  # Create a directory for each database if it doesn't exist
  if [ ! -d "$BACKUP_DIR" ]; then
    mkdir -p $BACKUP_DIR
    echo "Created backup directory for $db: $BACKUP_DIR"
  fi

  BACKUP_FILE="${BACKUP_DIR}/${db}_${DATE}.sql"
  echo "Backing up $db to $BACKUP_FILE..."
  
  # Perform the backup
  mysqldump --databases $db > $BACKUP_FILE

  # Find and delete backups older than 30 days
  find $BACKUP_DIR -type f -name "${db}_*.sql" -mtime +30 -exec rm {} \;
done

echo "Backup completed."