Tag Archive .my.cnf

#!/bin/bash

# --- Set Variables ---
# MySQL user with backup privileges
DB_USER="<username>"

# Date for the directory name in YYYYMMDD format
DATE=$(date +"%Y%m%d")

# Directory to store backup files
BACKUP_DIR="/var/backups/mysql/$DATE"

# --- Check and Create Backup Directory ---
if [ ! -d "$BACKUP_DIR" ]; then
    mkdir -p "$BACKUP_DIR"
    echo "Creating directory: $BACKUP_DIR"
fi

# --- Start Database Backup Process ---
echo "--- Starting Database Backup ---"

# Get a list of all non-system databases
DATABASES=$(mysql -u$DB_USER -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

# Loop through each database and back it up
for DB in $DATABASES; do
    echo "  > Backing up database: $DB"
    mysqldump --user=$DB_USER \
        --single-transaction \
        --events \
        --routines \
        --triggers \
        --add-drop-database "$DB" | gzip > "$BACKUP_DIR/$DB.sql.gz"

    if [ $? -eq 0 ]; then
        echo "    - Success: $BACKUP_DIR/$DB.sql.gz"
    else
        echo "    - Error backing up database: $DB"
    fi
done

echo "--- Database Backup Complete ---"

---

### **Backup Users and Permissions**

echo "--- Starting User and Permissions Backup ---"

# Backup user accounts and their associated permissions from the `mysql` system database
echo "  > Backing up user accounts, tables, and permissions..."
mysqldump -u$DB_USER --single-transaction --skip-triggers --compact mysql user db tables_priv columns_priv procs_priv > "$BACKUP_DIR/users_and_grants.sql"
if [ $? -eq 0 ]; then
    echo "    - Success: $BACKUP_DIR/users_and_grants.sql"
else
    echo "    - Error backing up user accounts and grants"
fi

# Backup the "SHOW GRANTS" statements which are crucial for recovering permissions
echo "  > Backing up SHOW GRANTS statements..."
mysql -u$DB_USER -NBe "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user" | mysql -u$DB_USER -N | sed 's/$/;/' > "$BACKUP_DIR/grants.sql"
if [ $? -eq 0 ]; then
    echo "    - Success: $BACKUP_DIR/grants.sql"
else
    echo "    - Error backing up grants"
fi

echo "--- User and Permissions Backup Complete ---"

---

### **Delete Old Backup Files**

echo "--- Deleting backup directories older than 90 days ---"
find /var/backups/mysql/ -maxdepth 1 -type d -name "????????*" -mtime +90 -exec rm -rf {} \;

echo "--- All backup processes finished successfully ---"