MySQL Database Backup - GermainUX Docs

ServiceManagement

Availability2014.1 or later

Requirements

Tools and access needed

Backup Script

Bash dump script template

Prepare the Script

Install and test manually

Validate and Protect the Backups

Test restores regularly

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.

#!/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.

Get Help

Contact GermainUX Support

The GermainUX team helps you get up and running faster, empower your team, turn data into actionable insights, and optimize performance to deliver measurable business outcomes.

Can't find what you need?

Search all GermainUX docs with Ctrl + K, or use the feedback buttons at the bottom of this page to tell us what is missing.

The content of this document is proprietary to GERMAIN SOFTWARE.

Requirements

Backup Script

Prepare the Script

Validate and Protect the Backups

​Overview

​Requirements

​Configure the MySQL Credentials

​Backup Script

​Prepare the Script

​Schedule the Backup with GermainUX

​Validate and Protect the Backups

​Get Help

Contact GermainUX Support

Can't find what you need?

Overview

Requirements

Configure the MySQL Credentials

Backup Script

Prepare the Script

Schedule the Backup with GermainUX

Validate and Protect the Backups

Get Help