chiefgeek
a1a17e81a1
Includes updated JS challenge scripts with Claude-User whitelist, same-site referer bypass, Blackbox-Exporter allowed bot, and all new exporters, cheat sheets, and automation scripts.
433 lines
15 KiB
Bash
433 lines
15 KiB
Bash
#!/bin/bash
|
|
################################################################################
|
|
# Script Name: mysql-exporter.sh
|
|
# Version: 1.0
|
|
# Description: Prometheus textfile exporter for MySQL — server status,
|
|
# connections, QPS, slow queries, buffer pool, replication,
|
|
# thread counts, and table lock waits via mysql CLI
|
|
#
|
|
# Author: Phil Connor
|
|
# Contact: contact@mylinux.work
|
|
# Website: https://mylinux.work
|
|
# License: MIT
|
|
#
|
|
# Prerequisites:
|
|
# - mysql CLI client
|
|
# - Credentials via ~/.my.cnf or --user/--password flags
|
|
# - Network access to MySQL server
|
|
# - netcat (nc) for HTTP mode
|
|
#
|
|
# Usage:
|
|
# # Output to stdout
|
|
# ./mysql-exporter.sh
|
|
#
|
|
# # HTTP server mode
|
|
# ./mysql-exporter.sh --http -p 9207
|
|
#
|
|
# # Textfile collector mode
|
|
# ./mysql-exporter.sh --textfile
|
|
#
|
|
# Metrics Exported:
|
|
# - mysql_overview_up - Exporter status (1=up, 0=down)
|
|
# - mysql_overview_version_info{version} - Server version
|
|
# - mysql_overview_uptime_seconds - Server uptime
|
|
# - mysql_overview_active_connections - Current active connections
|
|
# - mysql_overview_max_connections - Maximum allowed connections
|
|
# - mysql_overview_aborted_connections_total - Aborted connections
|
|
# - mysql_overview_aborted_clients_total - Aborted clients
|
|
# - mysql_overview_queries_total - Total queries executed
|
|
# - mysql_overview_slow_queries_total - Total slow queries
|
|
# - mysql_overview_tmp_disk_tables_total - Temp tables on disk
|
|
# - mysql_overview_threads_running - Running threads
|
|
# - mysql_overview_threads_cached - Cached threads
|
|
# - mysql_overview_threads_connected - Connected threads
|
|
# - mysql_overview_buffer_pool_size_bytes - Buffer pool total size
|
|
# - mysql_overview_buffer_pool_used_bytes - Buffer pool used size
|
|
# - mysql_overview_buffer_pool_hit_rate - Buffer pool hit rate
|
|
# - mysql_overview_table_lock_waits_total - Table lock waits
|
|
# - mysql_overview_slave_running - Replication running status
|
|
# - mysql_overview_seconds_behind_master - Replication lag
|
|
# - mysql_overview_relay_log_space_bytes - Relay log space
|
|
# - mysql_overview_binlog_size_bytes - Binary log size
|
|
# - mysql_overview_exporter_duration_seconds - Script execution time
|
|
# - mysql_overview_exporter_last_run_timestamp - Last successful run
|
|
#
|
|
# Configuration:
|
|
# Default HTTP port: 9207
|
|
# Textfile directory: /var/lib/node_exporter
|
|
#
|
|
################################################################################
|
|
|
|
# ============================================================================
|
|
# CONFIGURATION VARIABLES
|
|
# ============================================================================
|
|
|
|
TEXTFILE_DIR="/var/lib/node_exporter"
|
|
OUTPUT_FILE=""
|
|
HTTP_MODE=false
|
|
HTTP_PORT=9207
|
|
MYSQL_USER=""
|
|
MYSQL_PASSWORD=""
|
|
MYSQL_HOST="127.0.0.1"
|
|
MYSQL_PORT="3306"
|
|
|
|
# ============================================================================
|
|
# HELPER FUNCTIONS
|
|
# ============================================================================
|
|
|
|
show_usage() {
|
|
cat <<EOF
|
|
Usage: $0 [OPTIONS]
|
|
|
|
Export MySQL statistics as Prometheus metrics.
|
|
|
|
MODES:
|
|
--textfile Write to node_exporter textfile collector
|
|
--http Run HTTP server on port $HTTP_PORT
|
|
|
|
OPTIONS:
|
|
-p, --port HTTP port (default: 9207)
|
|
-o, --output Output file path
|
|
--user MySQL username
|
|
--password MySQL password
|
|
--host MySQL host (default: 127.0.0.1)
|
|
--mysql-port MySQL port (default: 3306)
|
|
|
|
EXAMPLES:
|
|
$0 --textfile # Write to textfile collector
|
|
$0 --http --port 9207 # Run HTTP server
|
|
$0 --user monitoring --password secret # Explicit credentials
|
|
$0 -o /tmp/mysql.prom # Write to custom file
|
|
|
|
EOF
|
|
exit 0
|
|
}
|
|
|
|
parse_args() {
|
|
while [[ $# -gt 0 ]]; do
|
|
case $1 in
|
|
-h|--help) show_usage ;;
|
|
--textfile) OUTPUT_FILE="$TEXTFILE_DIR/mysql.prom"; shift ;;
|
|
--http) HTTP_MODE=true; shift ;;
|
|
-p|--port) HTTP_PORT="$2"; shift 2 ;;
|
|
-o|--output) OUTPUT_FILE="$2"; shift 2 ;;
|
|
--user) MYSQL_USER="$2"; shift 2 ;;
|
|
--password) MYSQL_PASSWORD="$2"; shift 2 ;;
|
|
--host) MYSQL_HOST="$2"; shift 2 ;;
|
|
--mysql-port) MYSQL_PORT="$2"; shift 2 ;;
|
|
*) echo "Unknown option: $1" >&2; exit 1 ;;
|
|
esac
|
|
done
|
|
}
|
|
|
|
# Build mysql command with optional credentials
|
|
build_mysql_cmd() {
|
|
local cmd="mysql -h $MYSQL_HOST -P $MYSQL_PORT"
|
|
[ -n "$MYSQL_USER" ] && cmd="$cmd -u $MYSQL_USER"
|
|
[ -n "$MYSQL_PASSWORD" ] && cmd="$cmd -p$MYSQL_PASSWORD"
|
|
cmd="$cmd --batch --skip-column-names"
|
|
echo "$cmd"
|
|
}
|
|
|
|
# Check if mysql client is available and server is reachable
|
|
check_mysql() {
|
|
if ! command -v mysql >/dev/null 2>&1; then
|
|
echo "ERROR: mysql client not found" >&2
|
|
return 1
|
|
fi
|
|
|
|
local mysql_cmd
|
|
mysql_cmd=$(build_mysql_cmd)
|
|
if ! $mysql_cmd -e "SELECT 1" >/dev/null 2>&1; then
|
|
echo "ERROR: Cannot connect to MySQL server" >&2
|
|
return 1
|
|
fi
|
|
|
|
return 0
|
|
}
|
|
|
|
# Execute a MySQL query and return the result
|
|
mysql_query() {
|
|
local query="$1"
|
|
local mysql_cmd
|
|
mysql_cmd=$(build_mysql_cmd)
|
|
$mysql_cmd -e "$query" 2>/dev/null
|
|
}
|
|
|
|
# Get a specific global status variable
|
|
get_status_var() {
|
|
local var_name="$1"
|
|
mysql_query "SHOW GLOBAL STATUS LIKE '$var_name'" | awk '{print $2}'
|
|
}
|
|
|
|
# Get a specific global variable
|
|
get_variable() {
|
|
local var_name="$1"
|
|
mysql_query "SHOW GLOBAL VARIABLES LIKE '$var_name'" | awk '{print $2}'
|
|
}
|
|
|
|
# ============================================================================
|
|
# METRIC GENERATION
|
|
# ============================================================================
|
|
|
|
generate_metrics() {
|
|
local script_start
|
|
script_start=$(date +%s)
|
|
|
|
if ! check_mysql; then
|
|
cat <<EOF
|
|
# HELP mysql_overview_up MySQL exporter status
|
|
# TYPE mysql_overview_up gauge
|
|
mysql_overview_up 0
|
|
EOF
|
|
return
|
|
fi
|
|
|
|
# Gather all metrics
|
|
local version uptime max_conn active_conn
|
|
version=$(mysql_query "SELECT VERSION()" 2>/dev/null)
|
|
uptime=$(get_status_var "Uptime")
|
|
max_conn=$(get_variable "max_connections")
|
|
active_conn=$(get_status_var "Threads_connected")
|
|
|
|
local queries slow_queries tmp_disk_tables
|
|
queries=$(get_status_var "Queries")
|
|
slow_queries=$(get_status_var "Slow_queries")
|
|
tmp_disk_tables=$(get_status_var "Created_tmp_disk_tables")
|
|
|
|
local threads_running threads_cached threads_connected
|
|
threads_running=$(get_status_var "Threads_running")
|
|
threads_cached=$(get_status_var "Threads_cached")
|
|
threads_connected=$(get_status_var "Threads_connected")
|
|
|
|
local bp_size bp_pages_total bp_pages_free bp_read_requests bp_reads
|
|
bp_size=$(get_variable "innodb_buffer_pool_size")
|
|
bp_pages_total=$(get_status_var "Innodb_buffer_pool_pages_total")
|
|
bp_pages_free=$(get_status_var "Innodb_buffer_pool_pages_free")
|
|
bp_read_requests=$(get_status_var "Innodb_buffer_pool_read_requests")
|
|
bp_reads=$(get_status_var "Innodb_buffer_pool_reads")
|
|
|
|
local table_lock_waits aborted_conn aborted_clients
|
|
table_lock_waits=$(get_status_var "Table_locks_waited")
|
|
aborted_conn=$(get_status_var "Aborted_connects")
|
|
aborted_clients=$(get_status_var "Aborted_clients")
|
|
|
|
# Calculate buffer pool metrics
|
|
local bp_used_pages bp_used_bytes bp_hit_rate
|
|
bp_pages_total=${bp_pages_total:-0}
|
|
bp_pages_free=${bp_pages_free:-0}
|
|
bp_used_pages=$((bp_pages_total - bp_pages_free))
|
|
# InnoDB page size is 16384 bytes by default
|
|
bp_used_bytes=$((bp_used_pages * 16384))
|
|
|
|
bp_read_requests=${bp_read_requests:-0}
|
|
bp_reads=${bp_reads:-0}
|
|
if [ "$bp_read_requests" -gt 0 ] 2>/dev/null; then
|
|
bp_hit_rate=$(awk "BEGIN {printf \"%.6f\", 1 - ($bp_reads / $bp_read_requests)}" 2>/dev/null || echo "0")
|
|
else
|
|
bp_hit_rate="1.000000"
|
|
fi
|
|
|
|
# Replication status
|
|
local slave_running seconds_behind relay_log_space
|
|
local repl_output
|
|
repl_output=$(mysql_query "SHOW SLAVE STATUS\G" 2>/dev/null)
|
|
if [ -n "$repl_output" ]; then
|
|
slave_running=$(echo "$repl_output" | grep "Slave_SQL_Running:" | awk '{print $2}')
|
|
[ "$slave_running" = "Yes" ] && slave_running=1 || slave_running=0
|
|
seconds_behind=$(echo "$repl_output" | grep "Seconds_Behind_Master:" | awk '{print $2}')
|
|
[ "$seconds_behind" = "NULL" ] && seconds_behind=-1
|
|
relay_log_space=$(echo "$repl_output" | grep "Relay_Log_Space:" | awk '{print $2}')
|
|
else
|
|
slave_running=0
|
|
seconds_behind=-1
|
|
relay_log_space=0
|
|
fi
|
|
|
|
# Binary log size
|
|
local binlog_size
|
|
binlog_size=$(mysql_query "SHOW BINARY LOGS" 2>/dev/null | awk '{sum += $2} END {print sum+0}')
|
|
|
|
# Output all metrics
|
|
cat <<EOF
|
|
# HELP mysql_overview_up MySQL exporter status
|
|
# TYPE mysql_overview_up gauge
|
|
mysql_overview_up 1
|
|
|
|
# HELP mysql_overview_version_info MySQL server version
|
|
# TYPE mysql_overview_version_info gauge
|
|
mysql_overview_version_info{version="${version:-unknown}"} 1
|
|
|
|
# HELP mysql_overview_uptime_seconds MySQL server uptime in seconds
|
|
# TYPE mysql_overview_uptime_seconds gauge
|
|
mysql_overview_uptime_seconds ${uptime:-0}
|
|
|
|
# HELP mysql_overview_active_connections Current active connections
|
|
# TYPE mysql_overview_active_connections gauge
|
|
mysql_overview_active_connections ${active_conn:-0}
|
|
|
|
# HELP mysql_overview_max_connections Maximum allowed connections
|
|
# TYPE mysql_overview_max_connections gauge
|
|
mysql_overview_max_connections ${max_conn:-0}
|
|
|
|
# HELP mysql_overview_aborted_connections_total Total aborted connections
|
|
# TYPE mysql_overview_aborted_connections_total counter
|
|
mysql_overview_aborted_connections_total ${aborted_conn:-0}
|
|
|
|
# HELP mysql_overview_aborted_clients_total Total aborted clients
|
|
# TYPE mysql_overview_aborted_clients_total counter
|
|
mysql_overview_aborted_clients_total ${aborted_clients:-0}
|
|
|
|
# HELP mysql_overview_queries_total Total queries executed
|
|
# TYPE mysql_overview_queries_total counter
|
|
mysql_overview_queries_total ${queries:-0}
|
|
|
|
# HELP mysql_overview_slow_queries_total Total slow queries
|
|
# TYPE mysql_overview_slow_queries_total counter
|
|
mysql_overview_slow_queries_total ${slow_queries:-0}
|
|
|
|
# HELP mysql_overview_tmp_disk_tables_total Temp tables created on disk
|
|
# TYPE mysql_overview_tmp_disk_tables_total counter
|
|
mysql_overview_tmp_disk_tables_total ${tmp_disk_tables:-0}
|
|
|
|
# HELP mysql_overview_threads_running Currently running threads
|
|
# TYPE mysql_overview_threads_running gauge
|
|
mysql_overview_threads_running ${threads_running:-0}
|
|
|
|
# HELP mysql_overview_threads_cached Cached threads
|
|
# TYPE mysql_overview_threads_cached gauge
|
|
mysql_overview_threads_cached ${threads_cached:-0}
|
|
|
|
# HELP mysql_overview_threads_connected Connected threads
|
|
# TYPE mysql_overview_threads_connected gauge
|
|
mysql_overview_threads_connected ${threads_connected:-0}
|
|
|
|
# HELP mysql_overview_buffer_pool_size_bytes InnoDB buffer pool total size
|
|
# TYPE mysql_overview_buffer_pool_size_bytes gauge
|
|
mysql_overview_buffer_pool_size_bytes ${bp_size:-0}
|
|
|
|
# HELP mysql_overview_buffer_pool_used_bytes InnoDB buffer pool used size
|
|
# TYPE mysql_overview_buffer_pool_used_bytes gauge
|
|
mysql_overview_buffer_pool_used_bytes ${bp_used_bytes:-0}
|
|
|
|
# HELP mysql_overview_buffer_pool_hit_rate InnoDB buffer pool hit rate
|
|
# TYPE mysql_overview_buffer_pool_hit_rate gauge
|
|
mysql_overview_buffer_pool_hit_rate $bp_hit_rate
|
|
|
|
# HELP mysql_overview_table_lock_waits_total Table lock waits
|
|
# TYPE mysql_overview_table_lock_waits_total counter
|
|
mysql_overview_table_lock_waits_total ${table_lock_waits:-0}
|
|
|
|
# HELP mysql_overview_slave_running Replication slave running status
|
|
# TYPE mysql_overview_slave_running gauge
|
|
mysql_overview_slave_running $slave_running
|
|
|
|
# HELP mysql_overview_seconds_behind_master Replication lag in seconds
|
|
# TYPE mysql_overview_seconds_behind_master gauge
|
|
mysql_overview_seconds_behind_master ${seconds_behind:--1}
|
|
|
|
# HELP mysql_overview_relay_log_space_bytes Relay log space usage
|
|
# TYPE mysql_overview_relay_log_space_bytes gauge
|
|
mysql_overview_relay_log_space_bytes ${relay_log_space:-0}
|
|
|
|
# HELP mysql_overview_binlog_size_bytes Total binary log size
|
|
# TYPE mysql_overview_binlog_size_bytes gauge
|
|
mysql_overview_binlog_size_bytes ${binlog_size:-0}
|
|
|
|
EOF
|
|
|
|
# Exporter runtime
|
|
local script_end script_duration
|
|
script_end=$(date +%s)
|
|
script_duration=$((script_end - script_start))
|
|
|
|
cat <<EOF
|
|
# HELP mysql_overview_exporter_duration_seconds Time to generate all metrics
|
|
# TYPE mysql_overview_exporter_duration_seconds gauge
|
|
mysql_overview_exporter_duration_seconds $script_duration
|
|
|
|
# HELP mysql_overview_exporter_last_run_timestamp Unix timestamp of last successful run
|
|
# TYPE mysql_overview_exporter_last_run_timestamp gauge
|
|
mysql_overview_exporter_last_run_timestamp $script_end
|
|
|
|
EOF
|
|
}
|
|
|
|
# ============================================================================
|
|
# HTTP SERVER MODE
|
|
# ============================================================================
|
|
|
|
run_http_server() {
|
|
echo "Starting MySQL exporter on port $HTTP_PORT..." >&2
|
|
|
|
if ! command -v nc >/dev/null 2>&1; then
|
|
echo "ERROR: netcat (nc) required for HTTP mode" >&2
|
|
exit 1
|
|
fi
|
|
|
|
while true; do
|
|
{
|
|
read -r request
|
|
if [[ "$request" =~ ^GET\ /metrics ]]; then
|
|
echo -e "HTTP/1.1 200 OK\r\nContent-Type: text/plain; version=0.0.4\r\n\r"
|
|
generate_metrics
|
|
else
|
|
echo -e "HTTP/1.1 200 OK\r\nContent-Type: text/html\r\n\r"
|
|
cat <<EOF
|
|
<!DOCTYPE html>
|
|
<html>
|
|
<head><title>MySQL Exporter</title></head>
|
|
<body>
|
|
<h1>MySQL Prometheus Exporter</h1>
|
|
<p><a href="/metrics">Metrics</a></p>
|
|
</body>
|
|
</html>
|
|
EOF
|
|
fi
|
|
} | nc -l -p "$HTTP_PORT" -q 1 2>/dev/null
|
|
done
|
|
}
|
|
|
|
# ============================================================================
|
|
# MAIN EXECUTION
|
|
# ============================================================================
|
|
|
|
main() {
|
|
parse_args "$@"
|
|
|
|
if [ "$HTTP_MODE" = true ]; then
|
|
run_http_server
|
|
elif [ -n "$OUTPUT_FILE" ]; then
|
|
local output_dir
|
|
output_dir="$(dirname "$OUTPUT_FILE")"
|
|
mkdir -p "$output_dir"
|
|
|
|
local temp_file
|
|
temp_file=$(mktemp "${output_dir}/.mysql_metrics.XXXXXX")
|
|
|
|
if ! generate_metrics > "$temp_file" 2>/dev/null; then
|
|
rm -f "$temp_file"
|
|
echo "ERROR: Failed to generate metrics" >&2
|
|
exit 1
|
|
fi
|
|
|
|
local file_lines
|
|
file_lines=$(wc -l < "$temp_file" 2>/dev/null || echo 0)
|
|
|
|
if [ "$file_lines" -lt 10 ]; then
|
|
rm -f "$temp_file"
|
|
echo "ERROR: Metrics file too small ($file_lines lines), keeping previous" >&2
|
|
exit 1
|
|
fi
|
|
|
|
chmod 644 "$temp_file"
|
|
mv -f "$temp_file" "$OUTPUT_FILE"
|
|
|
|
echo "Metrics written to $OUTPUT_FILE ($file_lines lines)" >&2
|
|
else
|
|
generate_metrics
|
|
fi
|
|
}
|
|
|
|
main "$@"
|