#!/bin/bash
# =============================================================================
# Script:      pmm_mysql_disk_collector.sh
# Description: Collects MySQL InnoDB table and database disk usage metrics
#              by reading .ibd files from /var/lib/mysql and writing them
#              to a Prometheus textfile collector for PMM/node_exporter.
#              Metrics reported in bytes (du -b) for accuracy.
# Author:      Petar Godinic <petar.godinic@tomsoft.hr>
# Created:     2026
# Version:     1.0
#
# Metrics exported:
#   mysql_table_size_bytes    - Per-table .ibd file size in bytes
#   mysql_database_size_bytes - Total database size in bytes (sum of .ibd files)
#
# Usage:
#   Run manually:  bash pmm_mysql_disk_collector.sh
#   Run via cron:  see cron.d example below
#
# Cron setup (run every 5 minutes):
#   Create file: /etc/cron.d/pmm_mysql_disk_collector
#   With content:
#   -------------------------------------------------------
#   SHELL=/bin/bash
#   PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
#
#   */10 * * * * root /usr/local/percona/pmm/collectors/pmm_mysql_disk_collector.sh >> /var/log/pmm_mysql_disk_collector.log 2>&1
#   -------------------------------------------------------
#
#   Then set permissions:
#   chmod 644 /etc/cron.d/pmm_mysql_disk_collector
# =============================================================================

BASE="/var/lib/mysql"
OUT="/usr/local/percona/pmm/collectors/textfile-collector/low-resolution/mysql_files.prom"
TMP=$(mktemp)

echo "# HELP mysql_table_size_bytes InnoDB table file size in bytes" > "$TMP"
echo "# TYPE mysql_table_size_bytes gauge" >> "$TMP"

echo "# HELP mysql_database_size_bytes Total database size in bytes" >> "$TMP"
echo "# TYPE mysql_database_size_bytes gauge" >> "$TMP"

# Track DB totals
declare -A db_sizes

# Loop databases
for dbpath in "$BASE"/*; do
    [ -d "$dbpath" ] || continue    # Skip if not a directory
    db=$(basename "$dbpath")

    # skip system/internal dirs if needed
    [[ "$db" =~ ^(mysql|performance_schema|sys|information_schema|xtrabackup_.*|undo_00.*|mysql.*|.*.pem)$ ]] && continue

    total=0

    # Loop .ibd files
    while IFS= read -r -d '' file; do
        size=$(du -b "$file" | cut -f1)
        table=$(basename "$file" .ibd)

        echo "mysql_table_size_bytes{database=\"$db\",table=\"$table\"} $size" >> "$TMP"

        total=$((total + size))
    done < <(find "$dbpath" -maxdepth 1 -type f -name "*.ibd" -print0)

    echo "mysql_database_size_bytes{database=\"$db\"} $total" >> "$TMP"
done

mv "$TMP" "$OUT"
chown pmm-agent:pmm-agent "$OUT"