This script, flarum_prune_old_posts.sh, automates the safe removal of old discussions and their associated posts from a Flarum forum database. The script is intended to be run periodically (e.g., via cron) to keep forum content fresh by pruning stale discussions.
#!/bin/sh
# Script: flarum_prune_old_posts.sh
# Purpose: Safely prune Flarum discussions older than 90 days (non-sticky only)
# Authors: ChatGPT and Vic
# Requirements: MySQL/MariaDB client, cron-compatible, tested backups
set -eu
# === CONFIGURATION ===
DB_NAME="flarum"
DB_USER="flarum_user"
DB_PASS="secure_password"
DB_HOST="localhost"
LOG_FILE="/var/log/flarum_prune.log"
TMP_SQL="/tmp/flarum_prune_query.sql"
RETENTION_DAYS=90
# ======================
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
abort() {
log "ERROR: $1"
rm -f "$TMP_SQL"
exit 1
}
log "Starting Flarum pruning process..."
# Step 1: Verify MySQL is reachable
log "Testing database connection..."
if ! mysql -u "$DB_USER" -p"$DB_PASS" -h "$DB_HOST" -e "USE $DB_NAME;" 2>>"$LOG_FILE"; then
abort "Cannot connect to database '$DB_NAME' as user '$DB_USER'."
fi
log "Connected to MySQL database '$DB_NAME'."
# Step 2: Prepare SQL commands
cat > "$TMP_SQL" <<EOF
-- DELETE posts from discussions older than ${RETENTION_DAYS} days and not sticky
DELETE p FROM posts p
JOIN discussions d ON p.discussion_id = d.id
WHERE d.is_sticky = 0 AND d.created_at < (NOW() - INTERVAL ${RETENTION_DAYS} DAY);
-- DELETE the discussions themselves
DELETE FROM discussions
WHERE is_sticky = 0 AND created_at < (NOW() - INTERVAL ${RETENTION_DAYS} DAY);
EOF
log "Prepared SQL query to prune posts and discussions older than ${RETENTION_DAYS} days."
# Step 3: Execute SQL safely
log "Executing deletion query..."
if mysql -u "$DB_USER" -p"$DB_PASS" -h "$DB_HOST" "$DB_NAME" < "$TMP_SQL" 2>>"$LOG_FILE"; then
log "Successfully pruned old discussions and related posts."
else
abort "Failed to execute deletion query. Check SQL syntax or permissions."
fi
# Step 4: Clean up
rm -f "$TMP_SQL"
log "Temporary files cleaned. Pruning process complete."
exit 0
