> ## Documentation Index
> Fetch the complete documentation index at: https://docs.germainux.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL Database Backup

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

Service
`Management`

Availability
2014.1 or later

> [card] [Requirements](#requirements)
>
> Tools and access needed

> [card] [Backup Script](#backup-script)
>
> Bash dump script template

> [card] [Prepare the Script](#prepare-the-script)
>
> Install and test manually

> [card] [Validate and Protect the Backups](#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`:

```text theme={null}
[client]
user=BACKUP_USERNAME
password=BACKUP_PASSWORD
host=MYSQL_SERVER
```

Restrict access to the file:

```text theme={null}
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 theme={null}
#!/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:

```text theme={null}
/opt/germain/scripts/mysql-backup.sh
```

Make it executable:

```text theme={null}
chmod 700 /opt/germain/scripts/mysql-backup.sh
```

Run it manually to verify that it completes successfully:

```text theme={null}
/opt/germain/scripts/mysql-backup.sh
```

Confirm that a compressed backup was created under:

```text theme={null}
/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

> [card] [Contact GermainUX Support](https://docs.germainux.com/main/support.md)
>
> 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.

> [card] 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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.