Ticket #311 & 326 : Implement Automated MySQL Backups Using mysqldump - SupaHotBall/OE2-Group-D GitHub Wiki

Task

Action Required:

  • Implement automated MySQL logical backups with these specifications.

  • Your solution should be able to handle regular logical backups of MySQL databases, and integrated with our existing rsync backup infrastructure.

  • Backup Configuration

  • Tool: mysqldump with transactional consistency
  • Schedule: Daily at 0200 + Weekly full backups
  • Retention:
  • 7 daily backups
  • 3 weekly backups
  • Security Requirements
  • Create dedicated backup user with minimum privileges
  • You may consider encrypting the backups before transfer using gpg or any other suitable encrytion tool
  • Testing and Integration with Our Existing Systems
  • Create Puppet backup module with cron job to schedule this process
  • Test restoration procedures
  • Validate ownCloud data integrity post-restore
  • Acceptance Criteria:
  • βœ” Daily logical backups of your team database(s)
  • βœ” Verified restoration procedure
  • βœ” Backup validation tests successful
  • βœ” Schedule Daily/Weekly restoration drills
  • Documentation & Deliverables:
  • Backup module/script with inline comments
  • Restoration runbook
  • Integration notes for existing backup system
  • Troubleshooting /error documentation where applicable

Steps Taken

Create the MariaDB Backup User

sudo mysql

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'changeme';
GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

image

Exit the MariaDB shell and run

sudo nano /root/.my.cnf

Then paste the following code into the file

[client]
user=backup
password=changeme

image

Ensure that proper permissions are set. This is to ensure that only root can read the file which is important for security

sudo chmod 600 /root/.my.cnf

image

Update the backup script

#!/bin/bash

# Encrypted MariaDB Backup Script with rsync integration

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +"%Y-%m-%d_%H-%M")
HOSTNAME=$(hostname -s)
RECIPIENT="backup@group-d"
KEY="/etc/backup/.ssh/id_rsa"
DEST="[email protected]:/home/group-d/db/"

mkdir -p "$BACKUP_DIR/daily" "$BACKUP_DIR/weekly"

# Determine backup type based on the day (Sunday = weekly)
if [ "$(date +%u)" -eq 7 ]; then
  TYPE="weekly"
  RETENTION=3
else
  TYPE="daily"
  RETENTION=7
fi

DUMP_FILE="$BACKUP_DIR/$TYPE/${HOSTNAME}_mysqldump_${DATE}.sql"
ENCRYPTED_FILE="$DUMP_FILE.gpg"

# Step 1: Dump the MariaDB database
mysqldump --defaults-file=/root/.my.cnf --all-databases --single-transaction --add-drop-table > "$DUMP_FILE"

# Step 2: Encrypt the SQL dump with GPG
gpg --yes --batch --output "$ENCRYPTED_FILE" --encrypt --recipient "$RECIPIENT" "$DUMP_FILE"

# Step 3: Delete unencrypted file for security
rm -f "$DUMP_FILE"

# Step 4: Retention cleanup
find "$BACKUP_DIR/$TYPE" -name "*.sql.gpg" -type f -mtime +$RETENTION -delete

# Step 5: Sync encrypted backup to offsite server
rsync -avz -e "ssh -i $KEY" "$ENCRYPTED_FILE" "$DEST"

# Step 6: Logging
echo "[$(date)] Encrypted MariaDB $TYPE backup completed and transferred." >> /var/log/backup-db.log

Generate a GPG key

gpg --full-generate-key

Follow the steps below for the prompt

Prompt What to Choose
Please select kind of key 1 β†’ RSA and RSA (default)
RSA keysize 4096
Expiration 0 (key does not expire)
Real name Backup User
Email address backup@group-d
Passphrase Leave blank (for automated use)
Proceed confirmation Press O to confirm

image

Check that the gpg key shows up on the list

gpg --list-keys

image

Run the backup script once finished to test that it works

After verifying that it works, integrate the new script with the cron job in the backup module so that it runs daily at 0200. Edit the init.pp file in the manifests directory of the backup module

# === Database Server (db-d) ===
if $facts['networking']['hostname'] == 'db-d' {

  file { '/usr/local/bin/backup-db.sh':
    source => 'puppet:///modules/backup/backup-db.sh',
    owner  => 'root',
    group  => 'root',
    mode   => '0755',
  }

  file { '/var/log/backup-db.log':
    ensure => present,
    owner  => 'root',
    group  => 'root',
    mode   => '0644',
  }

  cron { 'db-backup':
    ensure  => present,
    command => '/usr/local/bin/backup-db.sh',
    user    => 'root',
    minute  => 10,
    hour    => 2,
  }

  ['db-2am', 'db-8am', 'db-2pm', 'db-8pm'].each |$name| {
    cron { $name:
      ensure => absent,
    }
  }

}

Ensure that the backup-db.sh script is either copied into the files directory of the backup module or created in there. Once the init.pp file has been edited, go back to the db server and apply the changes with sudo puppet agent --test

image

Testing Restoration Procedures

Copy the gpg file over from the offsite backup server to the db server. Ensure that you are in the db server while entering the following commands

scp [email protected]:/home/group-d/db/db-d_mysqldump_2025-05-19_08-25.sql.gpg ~/

image

Decrypt the gpg file

gpg --output db-restore.sql --decrypt ~/db-d_mysqldump_2025-05-19_08-25.sql.gpg

image

Run sudo mysql -u root < db-restore.sql to restore the database

Check that the database has been restored

sudo mysql -e "SHOW DATABASES;"

image

Verify Owncloud integrity

image

Restoration Runbook

  1. Log in to the DB server

ssh group-d@ipaddress

  1. Copy the encrypted backup from the offsite server

scp [email protected]:/home/group-d/db/db-d_mysqldump_YYYY-MM-DD_HH-MM.sql.gpg ~/

  1. Decrypt the file with GPG

gpg --output db-restore.sql --decrypt ~/db-d_mysqldump_YYYY-MM-DD_HH-MM.sql.gpg

  1. Restore the database

sudo mysql -u root < db-restore.sql

  1. Verify restoration

sudo mysql -e "SHOW DATABASES;"

  1. Check ownCloud UI

πŸ”„ Scheduled Restore Drills

πŸ“… Date πŸ› οΈ Type πŸ“ Description βœ… Status
2025-05-20 Manual Restore Decrypt and restore .sql.gpg to MariaDB βœ… Completed
2025-05-27 Manual Restore Repeat full restore from most recent backup ⏳ Planned

πŸ”— Integration Notes with Existing Backup System

🧩 Component πŸ” Integration Description
πŸ—„οΈ rsync Used to transfer encrypted .sql.gpg dumps from DB server to the offsite backup server
πŸ” GPG Encrypts backup dumps to prevent unauthorized access
🐘 mysqldump Logical database export tool used with transactional flags (--single-transaction)
⏱️ cron (Puppet) Puppet module ensures daily (02:10) scheduled backups via cron
πŸ“ Folder Structure /var/backups/mysql/daily and /var/backups/mysql/weekly maintained with retention policies
πŸ›  Puppet Managed All configurations and scripts (backup, cron, logrotate) are managed using Puppet

Challenges

GPG Encryption + Rsync Integration Issue (MariaDB Backup)

❗Problem Summary

During testing of the new MariaDB backup script using GPG and rsync, the following error occurred:

image

The GPG key for backup@group-d was created under the group-d user account, but the script was run via sudo, meaning it executed under the root user.

The root GPG keyring did not have access to the backup@group-d public key, so encryption failed.

Because .sql.gpg was never created, rsync had no file to transfer β€” resulting in code 23.

βœ… Solution

  1. Export the GPG public key (as group-d user) and import the key into root's GPG keyring:
gpg --export --armor backup@group-d > backup_pubkey.asc
sudo su -
gpg --import /home/group-d/backup_pubkey.asc
exit

image

  1. Set key trust to ultimate as root
sudo su -
gpg --edit-key backup@group-d
  1. Inside the gpg prompt type trust and then select 5

image

  1. Quit and re-run the script

image

The encrypted .sql.gpg backup was successfully created and transferred to the offsite server via rsync.

External Resources

N/A

Ticket Reference

https://rt.dataraster.com/Ticket/Display.html?id=311 https://rt.dataraster.com/Ticket/Display.html?id=326