วัน: 10 สิงหาคม 2019

#!/bin/bash

# ==========================================
# MySQL Backup Script
# Authenticates via ~/.my.cnf automatically
# ==========================================

# Base Directory for backups
BASE_BACKUP_DIR="/srv/common/backups/mysql"

# DateTime format for directory name (e.g., 2026-07-25_00-12-00)
DateTime=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_DIR="$BASE_BACKUP_DIR/$DateTime"

# --- 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 -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 \
		--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 database
echo "	> Backing up user tables..."
mysqldump --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
echo "	> Backing up SHOW GRANTS statements..."
mysql -NBe "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user" | mysql -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 ---"

# Matches directories with YYYY-MM-DD pattern and removes those older than 90 days
find "$BASE_BACKUP_DIR" -maxdepth 1 -type d -name "20[0-9][0-9]-*" -mtime +90 -exec rm -rf {} \;

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