Ticket ID #323 Implement Automated MySQL Backups Using mysqldump - GriffinKat/group-a GitHub Wiki

Backup and Recovery Implementation – OwnCloud MySQL Database

Summary

During the Incident Response Planning and Disaster Recovery drill, a major gap was identified:

The OwnCloud system lacks consistent and restorable MySQL backups
There is no documented restore procedure
Existing file-level backups cannot ensure database-level consistency

This directly violates our SLA commitment for critical infrastructure.

Action Plan

Backup Configuration

Component	Specification
Backup Tool	`mysqldump` with `--single-transaction`
Frequency	Daily at 02:00 AM, Weekly (every Sunday)
Retention	7 Daily backups (`*.sql`)
	3 Weekly archives (`.tar.gz`)
Encryption	(Optional) GPG encryption with public key
Storage	Sent to backup server via `rsync` over SSH

Security Setup

Created a dedicated MySQL user (backup_user@localhost) on db-a server with:

GRANT LOCK TABLES, SELECT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

SSH-based authentication set up between DB server and backup server using a private key (/etc/backup/.ssh/id_rsa)

Integration Notes

This document outlines how the automated database backup system is integrated into the infrastructure using Puppet, cron, rsync and MySQL, as part of the OwnCloud deployment and protection strategy.

Systems Overview

Database Server (db-a)

Hosts MariaDB database (OwnCloud + MySQL)
Runs the encrypted backup script (db-dump.sh)
Manages job scheduling with cron via Puppet

Backup Server (IP: 20.40.64.18)

Stores incoming encrypted database backups
Receives files via rsync over SSH
Maintains daily (db-daily/) and weekly (db-weekly/) backup folders

Tools & Technologies

Tool	Purpose
`mysqldump`	Logical backup of MySQL/MariaDB
`rsync`	Secure file transfer over SSH
`cron`	Automates daily execution of backup script
`Puppet`	Deploys scripts, manages cron jobs
`tar`	Archives weekly encrypted dumps

Backup Folder Structure

On the backup server:

Path	Description
`/home/group-a/storage/db/db-daily/`	Daily `.sql` files (7-day retention)
`/home/group-a/storage/db/db-weekly/`	Weekly `.tar.gz` archives (21-day retention)

Puppet Integration

A db_backup Puppet module was created to:

Deploy the backup script (/usr/local/bin/db-dump.sh)
Ensure log file (/var/log/db-backup.log) exists
Schedule a cron job at 2:00 AM daily

class db_backup {

  # Ensure the backup script is placed in the correct location
    file { '/usr/local/bin/db-dump.sh':
    ensure => file,
    source => 'puppet:///modules/db_backup/db-dump.sh',
    owner  => 'root',
    group  => 'root',
    mode   => '0755',
  }

  # Ensure the backup log file exists with proper permissions
    file { '/var/log/db-backup.log':
    ensure => present,
    owner  => 'root',
    group  => 'root',
    mode   => '0640',
  }

  # Schedule the backup script to run daily at 2:00 AM
    cron { 'daily-db-dump':
    ensure  => present,
    command => '/usr/local/bin/db-dump.sh',
    user    => 'root',
    minute  => 0,
    hour    => 2,
  }
}

Applying db_backup puppet module to the db-a server

Include the module in the site.pp file

Run the following command to pull the configuration from the mgmt-a server to the db-a server

sudo /opt/puppetlabs/puppet/bin/puppet agent --server=mgmt-a --no-daemonize --verbose --onetime

Backup Script Overview

Script Path: /usr/local/bin/db-dump.sh

Key Features:

Dumps owncloud and mysql with transactional safety
Creates daily .sql files in /tmp/db-dumps
Transfers them via rsync to: /home/group-a/storage/db/db-daily/
Weekly .tar.gz archives sent to: /db-weekly/
Automated cleanup:
- 7-day retention for .sql files
- 21-day retention for .tar.gz weekly archives
Logs output to /var/log/db-backup.log

Backup Script:

#!/bin/bash
# Log all output
exec >> /var/log/db-backup.log 2>&1

# Config
DEST="[email protected]:/home/group-a/storage/db"
DUMP_DIR="/tmp/db-dumps"
DATE=$(date +%F)
DAY=$(date +%u)

# Ensure temp directory exists
mkdir -p $DUMP_DIR

# Dump OwnCloud and MySQL with transactional consistency
mysqldump --single-transaction -u backup_user -p'group-amnbc@1721!' owncloud > $DUMP_DIR/owncloud-${DATE}.sql
mysqldump --single-transaction -u backup_user -p'group-amnbc@1721!' mysql > $DUMP_DIR/mysql-${DATE}.sql

# Rsync dumps to the target server
rsync -avz -e "ssh -i /etc/backup/.ssh/id_rsa" $DUMP_DIR/*.sql $DEST/db-daily/

# Delete daily backups older than 7 days
ssh -i /etc/backup/.ssh/id_rsa [email protected] "find /home/group-a/storage/db/db-daily/ -name '*.sql' -mtime +7 -delete"

# WEEKLY FULL BACKUP
if [ "$DAY" -eq 7 ]; then
  ARCHIVE="/tmp/db-weekly-${DATE}.tar.gz"
  tar -czf $ARCHIVE $DUMP_DIR/*.sql
  rsync -avz -e "ssh -i /etc/backup/.ssh/id_rsa" $ARCHIVE $DEST/db-weekly/
  rm -f $ARCHIVE
  ssh -i /etc/backup/.ssh/id_rsa [email protected] "find /home/group-a/storage/db/db-weekly/ -name '*.tar.gz' -mtime +21 -delete"
fi

# Cleanup local
rm -rf $DUMP_DIR

Backup Script Execution and results:

Run the following command on the db-a server after pulling the configuration from the mgmt-a server

sudo /usr/local/bin/db-dump.sh

Result of database files being backed up on the offsite backup server.

Automation Flow

Puppet deploys db-dump.sh and sets up cron
Cron triggers daily at 2:00 AM
Script:
- Dumps databases
- Transfers via rsync
- Retains 7 daily backups
- On Sundays, creates and retains weekly .tar.gz archive

Restoration Runbook – OwnCloud Database

This runbook provides step-by-step instructions to restore a daily backup of the owncloud database from files that were backed up using mysqldump and transferred using rsync.

Prerequisites

Before starting the restoration:

The MySQL/MariaDB service must be running.
You must have SSH access to the backup server and permissions to pull the backup files.
Your current user must have sudo privileges or be the MySQL root user.

Step-by-Step Restoration Procedure

1. Identify the Backup file to Restore

Use SSH or rsync to check available backups:

sudo ssh -i /etc/backup/.ssh/id_rsa [email protected] "ls -lh /home/group-a/storage/db/db-daily/"

Pick a owncloud.sql file for the correct date (e.g. owncloud-2025-05-19.sql).

2. Download the File to Your DB Server

Using `rsync`:

rsync -avz -e "ssh -i /etc/backup/.ssh/id_rsa" [email protected]:/home/group-a/storage/db/db-daily/owncloud-2025-05-19.sql .

Import the SQL Data

Restore OwnCloud:

sudo mysql -u root owncloud < owncloud-2025-05-19.sql

Evidence of restoration

SQL-Level Validation

Application-Level Validation

Troubleshooting

MySQL import fails or access denied

Confirm MySQL is running:

sudo systemctl status mysql

Check user privileges:

SHOW GRANTS FOR 'backup_user'@'localhost';

Error

When restoring a .sql file created by mysqldump, the script often includes: DROP, CREATE AND INSERT COMMANDS. But backup_user is intended to be a read-only user for safe exporting, not restoring.

Fix

Use the root user or another MySQL user with full privileges to restore the database.

Restoration Drill Log (Example)

Date	Archive Used	Status
2025-05-20	owncloud-2025-05-19.sql	✅ Passed: Login + DB Restored