MySQL Database Backup | Germain Documentation

⚙️ Overview

This procedure uses a Linux shell script and GermainUX automation to create scheduled MySQL database backups.

The script maintains:

Backup Type	Details
Daily	Seven rotating daily backups
Weekly	Weekly backups created every Sunday
Monthly	Monthly backups created on the first day of each month
Yearly	Yearly backups created on January 1

📋 Requirements

Requirement	Details
Operating system	Linux
MySQL client	The `mysqldump` utility must be installed.
File permissions	The execution account must be able to create and update files in the backup directory.
Database permissions	The MySQL account must have sufficient privileges to back up the selected database.
GermainUX Engine	Required to execute the script through a Local Program action.

🔑 Configure the MySQL Credentials

Store the database credentials in a protected MySQL configuration file rather than placing the password directly in the script.

Create /etc/germain/mysql-backup.cnf:

[client]
user=BACKUP_USERNAME
password=BACKUP_PASSWORD
host=MYSQL_SERVER

Restrict access to the file:

sudo chmod 600 /etc/germain/mysql-backup.cnf

Ensure that the account executing the backup owns the file or has permission to read it.

📁 Backup Script

Update DATABASE, DUMP_ROOT_DIR, and MYSQL_CONFIG for your environment.

Bash

#!/bin/bash

set -euo pipefail
umask 077

DUMP_ROOT_DIR="/DB_DUMPS/mysql"
MYSQL_CONFIG="/etc/germain/mysql-backup.cnf"
DATABASE="DATABASE_NAME"

DATE="$(date +%Y-%m-%d)"
WEEK_DAY="$(date +%u)"       # Monday=1, Sunday=7
DAY="$(date +%d)"
MONTH_DAY="$(date +%d-%m)"

DAILY_DIR="${DUMP_ROOT_DIR}/daily"
WEEKLY_DIR="${DUMP_ROOT_DIR}/weekly"
MONTHLY_DIR="${DUMP_ROOT_DIR}/monthly"
YEARLY_DIR="${DUMP_ROOT_DIR}/yearly"

DAILY_BACKUP="${DAILY_DIR}/database_dump_${WEEK_DAY}.sql.gz"

# Create the backup directory structure.
install -d -m 700 \
    "$DAILY_DIR" \
    "$WEEKLY_DIR" \
    "$MONTHLY_DIR" \
    "$YEARLY_DIR"

echo "Creating MySQL backup for database: ${DATABASE}"

/usr/bin/mysqldump \
    --defaults-extra-file="$MYSQL_CONFIG" \
    --single-transaction \
    --quick \
    --no-tablespaces \
    "$DATABASE" | gzip > "$DAILY_BACKUP"

# Confirm that the backup file was created and is not empty.
if [[ ! -s "$DAILY_BACKUP" ]]; then
    echo "ERROR: The backup file is missing or empty."
    exit 1
fi

echo "Daily backup created: ${DAILY_BACKUP}"

# Create a weekly backup every Sunday.
if [[ "$WEEK_DAY" == "7" ]]; then
    WEEKLY_BACKUP="${WEEKLY_DIR}/database_dump_${DATE}.sql.gz"
    cp "$DAILY_BACKUP" "$WEEKLY_BACKUP"
    echo "Weekly backup created: ${WEEKLY_BACKUP}"
fi

# Create a monthly backup on the first day of each month.
if [[ "$DAY" == "01" ]]; then
    MONTHLY_BACKUP="${MONTHLY_DIR}/database_dump_${DATE}.sql.gz"
    cp "$DAILY_BACKUP" "$MONTHLY_BACKUP"
    echo "Monthly backup created: ${MONTHLY_BACKUP}"
fi

# Create a yearly backup on January 1.
if [[ "$MONTH_DAY" == "01-01" ]]; then
    YEARLY_BACKUP="${YEARLY_DIR}/database_dump_${DATE}.sql.gz"
    cp "$DAILY_BACKUP" "$YEARLY_BACKUP"
    echo "Yearly backup created: ${YEARLY_BACKUP}"
fi

echo "MySQL backup completed successfully."

🛠️ Prepare the Script

Save the script on the server—for example:

/opt/germain/scripts/mysql-backup.sh

Make it executable:

chmod 700 /opt/germain/scripts/mysql-backup.sh

Run it manually to verify that it completes successfully:

/opt/germain/scripts/mysql-backup.sh

Confirm that a compressed backup was created under:

/DB_DUMPS/mysql/daily

📅 Schedule the Backup with GermainUX

Go to Germain Workspace > Left Menu > Automation > Local Program and create a Local Program action.

Configure the action as follows:

Field	Value
Name	MySQL Database Backup
Program	`/bin/bash`
Arguments	`/opt/germain/scripts/mysql-backup.sh`
Expected Exit Value	`0`
Logging Enabled	Enabled
Notify on Failure	Enabled
Run on Schedule	Enabled

Schedule the action to run once per day. The script automatically determines whether it must also create a weekly, monthly, or yearly copy.

🛡️ Validate and Protect the Backups

Regularly verify that:

Check	Details
Scheduled executions	Scheduled executions complete successfully.
Backup files	Backup files are created and are not empty.
Retention	Older weekly, monthly, and yearly backups are removed according to your retention policy.
Off-site copies	Backups are copied to a separate server or storage location.
Restoration testing	Database restoration is tested periodically.

A backup should not be considered reliable until its restoration process has been successfully tested.