Step by step

Login to mysql, and create a user

Description: A dedicated backup user is created in MySQL to allow mysqldump to safely extract data from the owncloud database. The user has minimal privileges, enhancing security by avoiding use of the root account during automated backups.

Command: sudo mysql -u root -p

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, LOCK TABLES, SHOW VIEW ON owncloud.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

---

Update backup-db.sh

Description: The backup script is updated to support both daily and weekly backups, with file naming based on the current day. It stores backups in /opt/backups, uses rsync to securely transfer files to a remote server, and deletes older backups based on a retention policy (7 days for daily, 21 days for weekly).

Command:

#!/bin/bash

TIMESTAMP=$(date +%F_%H-%M)
DAY_OF_WEEK=$(date +%u)  # 1 = Monday, 7 = Sunday

# Local backup directory
BACKUP_DIR="/opt/backups"
mkdir -p "$BACKUP_DIR"

# Determine backup type
if [ "$DAY_OF_WEEK" -eq 7 ]; then
    TYPE="weekly"
else
    TYPE="daily"
fi

# Backup file naming
FILENAME="db-owncloudsql-$TYPE-$TIMESTAMP.sql"
BACKUP_PATH="$BACKUP_DIR/$FILENAME"

# Perform the MySQL dump
mysqldump -u backup_user -p'StrongPassword123!' --single-transaction owncloud > "$BACKUP_PATH"

# Optional: Encrypt the backup
#gpg --symmetric --cipher-algo AES256 "$BACKUP_PATH"
#BACKUP_PATH="$BACKUP_PATH.gpg"

# Transfer backup to remote server via rsync + SSH
rsync -avz -e "ssh -i ~/.ssh/id_rsa" "$BACKUP_PATH" [email protected]:/home/group-c/storage/db/

# Retention policy: Delete old backups
find "$BACKUP_DIR" -name "db-owncloudsql-daily-*.sql" -mtime +7 -delete
find "$BACKUP_DIR" -name "db-owncloudsql-weekly-*.sql" -mtime +21 -delete

Code Explination

Line / Section	What It Does
#!/bin/bash	Specifies that the script should run using the Bash shell.
TIMESTAMP=$(date +%F_%H-%M)	Generates a timestamp in the format YYYY-MM-DD_HH-MM for naming the backup file.
DAY_OF_WEEK=$(date +%u)	Gets the day of the week (1 = Monday, ..., 7 = Sunday) to determine if the backup is daily/weekly.
BACKUP_DIR="/opt/backups"	Sets the local directory where backups will be stored.
mkdir -p "$BACKUP_DIR"	Creates the backup directory if it doesn't already exist.
if [ "$DAY_OF_WEEK" -eq 7 ]; then TYPE="weekly"; else TYPE="daily"; fi	Determines if the backup is weekly (Sunday) or daily (other days).
FILENAME="db-owncloudsql-$TYPE-$TIMESTAMP.sql"	Creates a filename that includes the type and timestamp.
BACKUP_PATH="$BACKUP_DIR/$FILENAME"	Combines the backup directory and filename into a full path.
mysqldump -u backup_user -p'StrongPassword123!' --single-transaction owncloud > "$BACKUP_PATH"	Dumps the owncloud MySQL database to the backup file using consistent snapshot mode.
#gpg --symmetric --cipher-algo AES256 "$BACKUP_PATH"	(Optional) Encrypts the backup using GPG with AES-256 (commented out by default).
#BACKUP_PATH="$BACKUP_PATH.gpg"	(Optional) Updates the backup path to point to the encrypted .gpg file.
rsync -avz -e "ssh -i ~/.ssh/id_rsa" "$BACKUP_PATH" [email protected]:/home/group-c/storage/db/	Transfers the backup securely to a remote server using rsync over SSH.
find "$BACKUP_DIR" -name "db-owncloudsql-daily-*.sql" -mtime +7 -delete	Deletes daily backups older than 7 days.
find "$BACKUP_DIR" -name "db-owncloudsql-weekly-*.sql" -mtime +21 -delete	Deletes weekly backups older than 21 days.

---

Edit the init file

Description: The Puppet init.pp file is edited to schedule the backup script to run at 2 AM, but only for the DB server. This ensures backups are automated without affecting other server roles (app/mgmt).

Command: 'db' => ['2'],

---

Deploy the Script to Your DB Node

Description: Test the Db server, and run the backup script manaully

Command:

sudo puppet agent -t
/opt/scripts/backup.sh

---

The backup script correctly produces daily backups. Weekly backups have not appeared yet because the script only generates them on Sundays. This behavior is intentional, following the logic built into the script based on the day of the week.

---

Restoration Runbook

Objective

Restore the owncloud MySQL database from a backup created via mysqldump to recover from data loss, corruption, or outage, ensuring minimal downtime and data consistency.

Prerequisites

• You have access to the DB server (where MySQL runs).
• You have access to the offsite storage (via SSH + rsync).
• MySQL is running, and you have root or equivalent privileges.
• A recent backup .sql file exists (daily or weekly).

Restoration Steps

Restore from Local Backup (If Backup Exists Locally)

# List available local backups
ls -lh /opt/backups/

# Stop OwnCloud-related services to prevent DB writes
sudo systemctl stop apache2     # or nginx/php-fpm

# Drop and recreate the database (CAUTION: this deletes existing data!)
sudo mysql -u root -p
> DROP DATABASE IF EXISTS owncloud;
> CREATE DATABASE owncloud;
> EXIT;

# Restore the SQL file
mysql -u root -p owncloud < /opt/backups/db-owncloudsql-daily-YYYY-MM-DD_HH-MM.sql

# Start services
sudo systemctl start apache2

Restore from Offsite Backup (If Backup is Offsite Only)

# Pull backup from remote offsite storage to /opt/backups
rsync -avz -e "ssh -i ~/.ssh/id_rsa" [email protected]:/home/group-c/storage/db/db-owncloudsql-daily-YYYY-MM-DD_HH-MM.sql /opt/backups/

# Proceed with the same restoration steps as above
sudo systemctl stop apache2
sudo mysql -u root -p
> DROP DATABASE IF EXISTS owncloud;
> CREATE DATABASE owncloud;
> EXIT;

mysql -u root -p owncloud < /opt/backups/db-owncloudsql-daily-YYYY-MM-DD_HH-MM.sql

sudo systemctl start apache2

Post-Restore Validation

# Confirm DB is populated
mysql -u root -p -e "USE owncloud; SHOW TABLES;"

Troubleshooting

Problem	Solution
Backup not found	Check the timestamp format, or sync from remote using rsync.
ERROR 1049 (Unknown DB)	You forgot to CREATE DATABASE owncloud; before restore.
Permission Denied	Check that your MySQL user has the correct privileges.
Empty tables	Make sure the correct .sql backup file was used.
Can't login to OwnCloud	Ensure web server, PHP, and MySQL services are all running.

---

🔗 Integration Notes for MySQL Backup System

📦 Overview

This backup system integrates automated MySQL logical backups with the existing rsync-based remote backup infrastructure. It ensures transactionally consistent backups of the owncloud database using mysqldump, scheduled by Puppet with a cron job, and stored both locally and offsite.

---

🧱 System Components

Component	Description
mysqldump	Creates logical .sql backups with --single-transaction for consistency
backup_user	MySQL user with SELECT, LOCK TABLES, and SHOW VIEW privileges only
backup-db.sh	Bash script that handles backup creation, naming, and retention
rsync	Transfers backup files to remote offsite storage
/opt/backups/	Local backup directory on the DB server
Puppet Cron Job	Automates daily (and weekly) backup execution at 02:00

---

📁 File Locations

File / Path	Purpose
/opt/backups/	Stores local .sql backups on the DB server
/opt/scripts/backup.sh	Executable backup script on the DB server, deployed via Puppet
/etc/puppetlabs/code/modules/backup/files/backup-db.sh	Main backup script on the management server (Puppet master)
/home/group-c/storage/db/	Offsite remote backup storage used by rsync
~/.ssh/id_rsa	SSH private key used for secure rsync file transfers

---

🕒 Scheduling with Puppet

The backup script is scheduled using Puppet to run daily at 2:00 AM only on the DB server.

Puppet init.pp Example:

cron { 'mysql-daily-backup':
  ensure  => present,
  user    => 'root',
  hour    => '2',
  minute  => '0',
  command => '/opt/scripts/backup-db.sh',
  onlyif  => 'hostname | grep db',
}