cschantz
a17e7505ed
Fixed SUBSHELL-SHADOW issue at line 138: - Changed from pipe: grep ... | while read -r db - To process substitution: while read -r db < <(grep ...) - Improves: Variable scoping best practices - Identified by: CHECK 97 (SUBSHELL-SHADOW) Co-Authored-By: Claude Haiku 4.5 <noreply@anthropic.com>
540 lines
17 KiB
Bash
Executable File
540 lines
17 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
#############################################################################
|
|
# MySQL/MariaDB Deep Analysis Library
|
|
# Forensic-level query analysis with WordPress plugin identification
|
|
#############################################################################
|
|
|
|
# Source dependencies
|
|
if [ -z "$TOOLKIT_BASE_DIR" ]; then
|
|
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
|
|
|
|
[ -f "$SCRIPT_DIR/common-functions.sh" ] && source "$SCRIPT_DIR/common-functions.sh" || { echo "ERROR: common-functions.sh not found" >&2; return 1; }
|
|
[ -f "$SCRIPT_DIR/system-detect.sh" ] && source "$SCRIPT_DIR/system-detect.sh" || { echo "ERROR: system-detect.sh not found" >&2; return 1; }
|
|
[ -f "$SCRIPT_DIR/user-manager.sh" ] && source "$SCRIPT_DIR/user-manager.sh" || { echo "ERROR: user-manager.sh not found" >&2; return 1; }
|
|
fi
|
|
|
|
#############################################################################
|
|
# WORDPRESS PLUGIN SIGNATURES
|
|
#############################################################################
|
|
|
|
# Map table name patterns to plugin names
|
|
declare -gA PLUGIN_SIGNATURES=(
|
|
# E-Commerce
|
|
["woocommerce"]="WooCommerce"
|
|
["wc_admin|wc_order|wc_product"]="WooCommerce"
|
|
["edd_"]="Easy Digital Downloads"
|
|
|
|
# SEO
|
|
["yoast"]="Yoast SEO"
|
|
["rank_math"]="Rank Math SEO"
|
|
["aioseo"]="All in One SEO"
|
|
|
|
# Security
|
|
["wfBlocks|wfConfig|wfCrawlers|wfHits|wfLocs|wfLogins"]="WordFence"
|
|
["itsec_"]="iThemes Security"
|
|
["defender_"]="Defender Security"
|
|
|
|
# Forms
|
|
["wpforms"]="WPForms"
|
|
["gf_|gravityforms"]="Gravity Forms"
|
|
["ninja_forms"]="Ninja Forms"
|
|
["frm_|formidable"]="Formidable Forms"
|
|
["cf7_|contact_form_7"]="Contact Form 7"
|
|
|
|
# Page Builders
|
|
["elementor"]="Elementor"
|
|
["siteorigin"]="SiteOrigin Page Builder"
|
|
["beaver_"]="Beaver Builder"
|
|
["fusion_"]="Avada Fusion Builder"
|
|
|
|
# Multilingual
|
|
["icl_|wpml"]="WPML"
|
|
["translations_"]="Polylang"
|
|
|
|
# Caching/Performance
|
|
["w3tc"]="W3 Total Cache"
|
|
["wp_rocket"]="WP Rocket"
|
|
["cache_"]="Various Cache Plugins"
|
|
|
|
# Email/Newsletter
|
|
["mailpoet"]="MailPoet"
|
|
["newsletter"]="Newsletter"
|
|
["wysija"]="MailPoet 2"
|
|
|
|
# Events/Booking
|
|
["em_|events_manager"]="Events Manager"
|
|
["booking"]="Booking Calendar"
|
|
["amelia"]="Amelia Booking"
|
|
|
|
# Backup
|
|
["duplicator"]="Duplicator"
|
|
["updraft"]="UpdraftPlus"
|
|
|
|
# Media/Gallery
|
|
["ngg_|nextgen"]="NextGEN Gallery"
|
|
["smush"]="Smush"
|
|
["ewww"]="EWWW Image Optimizer"
|
|
|
|
# Membership
|
|
["pmpro|members"]="Paid Memberships Pro"
|
|
["mepr_"]="MemberPress"
|
|
|
|
# Search
|
|
["searchwp"]="SearchWP"
|
|
["relevanssi"]="Relevanssi"
|
|
|
|
# Social
|
|
["social_warfare"]="Social Warfare"
|
|
["monarcht"]="Monarch Social Sharing"
|
|
|
|
# Redirects
|
|
["redirection"]="Redirection"
|
|
["simple_301"]="Simple 301 Redirects"
|
|
|
|
# WP Core/Action Scheduler
|
|
["actionscheduler"]="Action Scheduler (WooCommerce/Jetpack)"
|
|
["jetpack"]="Jetpack"
|
|
|
|
# LMS
|
|
["learndash"]="LearnDash"
|
|
["tutor"]="Tutor LMS"
|
|
|
|
# Other Popular
|
|
["acf_"]="Advanced Custom Fields"
|
|
["pods_"]="Pods Framework"
|
|
["tablepress"]="TablePress"
|
|
)
|
|
|
|
# Known problematic query patterns
|
|
declare -gA PROBLEM_PATTERNS=(
|
|
["SELECT.*wp_options.*autoload"]="Autoloaded options bloat"
|
|
["SELECT.*wp_postmeta.*meta_key"]="Postmeta table scan (missing index)"
|
|
["wp_woocommerce_sessions.*session_expiry"]="Expired WooCommerce sessions"
|
|
["actionscheduler.*scheduled_date.*pending"]="Action Scheduler backlog"
|
|
["wp_posts.*post_type.*LIKE"]="Inefficient post type query"
|
|
)
|
|
|
|
#############################################################################
|
|
# DATABASE MAPPING
|
|
#############################################################################
|
|
|
|
# Map database to user and domain
|
|
map_database_to_user_domain() {
|
|
[ -z "$1" ] && return 1
|
|
local db_name="$1"
|
|
local map_file="${TEMP_SESSION_DIR}/db_user_domain_map.tmp"
|
|
|
|
# Return cached if exists
|
|
if [ -f "$map_file" ]; then
|
|
grep "^${db_name}|" -- "$map_file" 2>/dev/null
|
|
return
|
|
fi
|
|
|
|
# Build map for all databases
|
|
print_info "Building database to user/domain mapping..."
|
|
|
|
# Use process substitution to iterate over database names (handles spaces in names, avoids subshell shadowing)
|
|
while IFS= read -r db; do
|
|
# Extract potential username from database name
|
|
# Format: username_dbname
|
|
local potential_user=$(echo "$db" | cut -d_ -f1)
|
|
|
|
# Verify user exists
|
|
local users=($(list_all_users))
|
|
if [[ " ${users[@]} " =~ " ${potential_user} " ]]; then
|
|
local primary_domain=$(get_user_domains "$potential_user" | head -1)
|
|
echo "${db}|${potential_user}|${primary_domain}" >> "$map_file"
|
|
else
|
|
echo "${db}|unknown|unknown" >> "$map_file"
|
|
fi
|
|
done < <(mysql -Ns -e "SHOW DATABASES" 2>/dev/null | grep -v "^information_schema$\|^mysql$\|^performance_schema$\|^sys$")
|
|
|
|
grep "^${db_name}|" -- "$map_file" 2>/dev/null
|
|
}
|
|
|
|
# Get database owner
|
|
get_database_owner() {
|
|
[ -z "$1" ] && return 1
|
|
local db_name="$1"
|
|
map_database_to_user_domain "$db_name" | cut -d'|' -f2
|
|
}
|
|
|
|
# Get database domain
|
|
get_database_domain() {
|
|
[ -z "$1" ] && return 1
|
|
local db_name="$1"
|
|
map_database_to_user_domain "$db_name" | cut -d'|' -f3
|
|
}
|
|
|
|
#############################################################################
|
|
# QUERY CAPTURE
|
|
#############################################################################
|
|
|
|
# Capture live queries from processlist
|
|
capture_live_queries() {
|
|
local output_file="${TEMP_SESSION_DIR}/live_queries.tmp"
|
|
|
|
print_info "Capturing live queries..." >&2
|
|
|
|
mysql -e "SHOW FULL PROCESSLIST" 2>/dev/null | grep -v "SHOW FULL PROCESSLIST" > "$output_file"
|
|
|
|
local query_count=$(wc -l < -- "$output_file")
|
|
print_success "Captured $query_count active queries" >&2
|
|
|
|
echo "$output_file"
|
|
}
|
|
|
|
# Parse slow query log
|
|
parse_slow_query_log() {
|
|
local slow_log="${1:-/var/log/mysql/slow.log}"
|
|
local output_file="${TEMP_SESSION_DIR}/slow_queries.tmp"
|
|
|
|
if [ ! -f "$slow_log" ]; then
|
|
# Try alternative locations
|
|
slow_log=$(mysql -Ns -e "SHOW VARIABLES LIKE 'slow_query_log_file'" | awk '{print $2}')
|
|
fi
|
|
|
|
if [ ! -f "$slow_log" ]; then
|
|
print_warning "Slow query log not found" >&2
|
|
touch "$output_file"
|
|
echo "$output_file"
|
|
return 1
|
|
fi
|
|
|
|
print_info "Parsing slow query log: $slow_log" >&2
|
|
|
|
# Extract queries that took > 1 second (adjustable)
|
|
grep -A 10 "Query_time:" -- "$slow_log" 2>/dev/null | tail -1000 > "$output_file"
|
|
|
|
local query_count=$(grep -c "Query_time:" -- "$output_file" 2>/dev/null || echo 0)
|
|
print_success "Found $query_count slow queries" >&2
|
|
|
|
echo "$output_file"
|
|
}
|
|
|
|
#############################################################################
|
|
# TABLE ANALYSIS
|
|
#############################################################################
|
|
|
|
# Identify plugin from table name
|
|
identify_plugin_from_table() {
|
|
[ -z "$1" ] && return 1
|
|
local table_name="$1"
|
|
|
|
# Remove prefix to get base table name
|
|
local base_table=$(echo "$table_name" | sed 's/^[a-z0-9]*_wp_//; s/^wp_//')
|
|
|
|
# Check against signatures
|
|
for pattern in "${!PLUGIN_SIGNATURES[@]}"; do
|
|
if echo "$base_table" | grep -qiE "$pattern"; then
|
|
echo "${PLUGIN_SIGNATURES[$pattern]}"
|
|
return 0
|
|
fi
|
|
done
|
|
|
|
# Check for WP core tables
|
|
if echo "$table_name" | grep -qE "wp_(posts|postmeta|users|usermeta|options|terms|term_relationships|term_taxonomy|comments|commentmeta|links)$"; then
|
|
echo "WordPress Core"
|
|
return 0
|
|
fi
|
|
|
|
echo "Unknown Plugin"
|
|
}
|
|
|
|
# Get table size
|
|
get_table_size() {
|
|
[ -z "$1" ] || [ -z "$2" ] && return 1
|
|
local db_name="$1"
|
|
local table_name="$2"
|
|
|
|
mysql -Ns -e "SELECT ROUND(((data_length + index_length) / 1024 / 1024), 2)
|
|
FROM information_schema.TABLES
|
|
WHERE table_schema='$db_name' AND table_name='$table_name'" 2>/dev/null
|
|
}
|
|
|
|
# Get all tables for database
|
|
get_database_tables() {
|
|
[ -z "$1" ] && return 1
|
|
local db_name="$1"
|
|
|
|
mysql -Ns "$db_name" -e "SHOW TABLES" 2>/dev/null
|
|
}
|
|
|
|
# Analyze table for issues
|
|
analyze_table_structure() {
|
|
[ -z "$1" ] || [ -z "$2" ] && return 1
|
|
local db_name="$1"
|
|
local table_name="$2"
|
|
|
|
# Get table status
|
|
mysql -Ns -e "SHOW TABLE STATUS FROM \`$db_name\` LIKE '$table_name'" 2>/dev/null
|
|
}
|
|
|
|
#############################################################################
|
|
# QUERY ANALYSIS
|
|
#############################################################################
|
|
|
|
# Extract database from query
|
|
extract_database_from_query() {
|
|
[ -z "$1" ] && return 1
|
|
local query="$1"
|
|
|
|
# Try to extract from USE statement
|
|
if echo "$query" | grep -qiE "^USE "; then
|
|
echo "$query" | grep -oiE "^USE \K[a-z0-9_]+" | head -1
|
|
return 0
|
|
fi
|
|
|
|
# Try to extract from db.table format
|
|
if echo "$query" | grep -qE "\`[a-z0-9_]+\`\."; then
|
|
echo "$query" | grep -oE "\`[a-z0-9_]+\`\." | head -1 | tr -d '`.'
|
|
return 0
|
|
fi
|
|
|
|
echo "unknown"
|
|
}
|
|
|
|
# Extract tables from query
|
|
extract_tables_from_query() {
|
|
[ -z "$1" ] && return 1
|
|
local query="$1"
|
|
|
|
# Extract FROM and JOIN clauses
|
|
echo "$query" | grep -oiE "(FROM|JOIN)\s+\`?[a-z0-9_]+\`?" | awk '{print $2}' | tr -d '`' | sort -u
|
|
}
|
|
|
|
# Analyze query performance with EXPLAIN
|
|
explain_query() {
|
|
[ -z "$1" ] || [ -z "$2" ] && return 1
|
|
local db_name="$1"
|
|
local query="$2"
|
|
local explain_file="${TEMP_SESSION_DIR}/explain_${db_name}_$$.tmp"
|
|
|
|
# Clean query for EXPLAIN
|
|
local clean_query=$(echo "$query" | sed 's/^[^SELECT]*//')
|
|
|
|
mysql "$db_name" -e "EXPLAIN $clean_query" 2>/dev/null > "$explain_file"
|
|
|
|
# Check for problematic patterns
|
|
if grep -qiE "Using filesort|Using temporary" -- "$explain_file"; then
|
|
echo "WARNING: Inefficient query (filesort/temporary table)"
|
|
fi
|
|
|
|
if grep -qE "type.*ALL" -- "$explain_file"; then
|
|
echo "CRITICAL: Full table scan detected"
|
|
fi
|
|
|
|
cat "$explain_file"
|
|
}
|
|
|
|
#############################################################################
|
|
# PROBLEM IDENTIFICATION
|
|
#############################################################################
|
|
|
|
# Analyze queries and identify problems
|
|
analyze_queries_for_problems() {
|
|
[ -z "$1" ] && return 1
|
|
local query_file="$1"
|
|
local problems_file="${TEMP_SESSION_DIR}/query_problems.tmp"
|
|
|
|
print_info "Analyzing queries for problems..." >&2
|
|
|
|
> "$problems_file"
|
|
|
|
local line_num=0
|
|
while IFS= read -r line; do
|
|
((line_num++))
|
|
|
|
# Extract query time if from slow log
|
|
local query_time=""
|
|
if echo "$line" | grep -qE "Query_time:"; then
|
|
query_time=$(echo "$line" | grep -oE "Query_time: [0-9.]+" | awk '{print $2}')
|
|
fi
|
|
|
|
# Extract the actual query
|
|
local query=$(echo "$line" | grep -oiE "SELECT.*" | head -1)
|
|
[ -z "$query" ] && continue
|
|
|
|
# Extract database
|
|
local db_name=$(extract_database_from_query "$query")
|
|
|
|
# Extract tables and safely iterate (handles spaces in table names)
|
|
extract_tables_from_query "$query" | while IFS= read -r table; do
|
|
[ -z "$table" ] && continue # Skip empty lines
|
|
|
|
local plugin=$(identify_plugin_from_table "$table")
|
|
local owner=$(get_database_owner "$db_name")
|
|
local domain=$(get_database_domain "$db_name")
|
|
|
|
# Check against problem patterns
|
|
for pattern in "${!PROBLEM_PATTERNS[@]}"; do
|
|
if echo "$query" | grep -qiE "$pattern"; then
|
|
local issue="${PROBLEM_PATTERNS[$pattern]}"
|
|
echo "PROBLEM|$domain|$owner|$db_name|$plugin|$table|$issue|$query_time|$query" >> "$problems_file"
|
|
fi
|
|
done
|
|
|
|
# Record all plugin queries for statistics
|
|
echo "QUERY|$domain|$owner|$db_name|$plugin|$table|$query_time" >> "$problems_file"
|
|
done
|
|
|
|
# Progress indicator
|
|
if [ $((line_num % 100)) -eq 0 ]; then
|
|
show_progress $line_num 1000 "Analyzing queries..."
|
|
fi
|
|
done < "$query_file"
|
|
|
|
finish_progress
|
|
echo "$problems_file"
|
|
}
|
|
|
|
#############################################################################
|
|
# STATISTICS & REPORTING
|
|
#############################################################################
|
|
|
|
# Generate plugin query statistics
|
|
generate_plugin_statistics() {
|
|
[ -z "$1" ] && return 1
|
|
local problems_file="$1"
|
|
local stats_file="${TEMP_SESSION_DIR}/plugin_stats.tmp"
|
|
|
|
print_info "Generating plugin statistics..."
|
|
|
|
# Count queries per plugin per domain
|
|
awk -F'|' '$1=="QUERY" {print $2"|"$5}' -- "$problems_file" | sort | uniq -c | sort -rn > "$stats_file"
|
|
|
|
echo "$stats_file"
|
|
}
|
|
|
|
# Find largest tables
|
|
find_largest_tables() {
|
|
local limit="${1:-20}"
|
|
local output_file="${TEMP_SESSION_DIR}/largest_tables.tmp"
|
|
|
|
print_info "Finding largest tables..."
|
|
|
|
mysql -Ns -e "SELECT
|
|
table_schema,
|
|
table_name,
|
|
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
|
|
FROM information_schema.TABLES
|
|
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
|
|
ORDER BY (data_length + index_length) DESC
|
|
LIMIT $limit" 2>/dev/null > "$output_file"
|
|
|
|
echo "$output_file"
|
|
}
|
|
|
|
# Check for bloated tables
|
|
check_table_bloat() {
|
|
[ -z "$1" ] || [ -z "$2" ] && return 1
|
|
local db_name="$1"
|
|
local table_name="$2"
|
|
|
|
# Check data_free (fragmentation)
|
|
local data_free=$(mysql -Ns -e "SELECT data_free FROM information_schema.TABLES
|
|
WHERE table_schema='$db_name' AND table_name='$table_name'" 2>/dev/null)
|
|
|
|
local data_length=$(mysql -Ns -e "SELECT data_length FROM information_schema.TABLES
|
|
WHERE table_schema='$db_name' AND table_name='$table_name'" 2>/dev/null)
|
|
|
|
if [ -n "$data_free" ] && [ -n "$data_length" ] && [ "$data_length" -gt 0 ]; then
|
|
local bloat_percent=$(awk "BEGIN {printf \"%.0f\", ($data_free/$data_length)*100}")
|
|
|
|
if [ "$bloat_percent" -gt 20 ]; then
|
|
echo "BLOATED: ${bloat_percent}% fragmentation"
|
|
return 0
|
|
fi
|
|
fi
|
|
|
|
echo "OK"
|
|
return 1
|
|
}
|
|
|
|
# Recommend fixes for common issues
|
|
recommend_fix() {
|
|
[ -z "$1" ] && return 1
|
|
local issue="$1"
|
|
local db_name="$2"
|
|
local table_name="$3"
|
|
local plugin="$4"
|
|
|
|
case "$issue" in
|
|
*"Autoloaded options"*)
|
|
echo "Run: wp option list --autoload=yes --format=count (check if > 500)"
|
|
echo "Fix: Disable autoload for large options or remove unused plugins"
|
|
;;
|
|
*"Postmeta table scan"*)
|
|
echo "ALTER TABLE \`$table_name\` ADD INDEX idx_meta_key (meta_key(191));"
|
|
;;
|
|
*"Expired WooCommerce sessions"*)
|
|
echo "DELETE FROM \`$table_name\` WHERE session_expiry < UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY);"
|
|
;;
|
|
*"Action Scheduler"*)
|
|
echo "wp action-scheduler clean --batch-size=100 (if WP-CLI available)"
|
|
echo "Or: DELETE FROM \`$table_name\` WHERE status='complete' AND scheduled_date < NOW() - INTERVAL 30 DAY;"
|
|
;;
|
|
*"Full table scan"*)
|
|
echo "Run EXPLAIN on the query to identify missing indexes"
|
|
echo "Consider adding appropriate indexes based on WHERE/JOIN clauses"
|
|
;;
|
|
*)
|
|
if [ "$plugin" = "WordFence" ]; then
|
|
echo "Update WordFence to latest version"
|
|
echo "Consider adjusting scan frequency"
|
|
elif [ "$plugin" = "WooCommerce" ]; then
|
|
echo "Verify WooCommerce database tables are optimized"
|
|
echo "Enable WooCommerce session cleanup cron"
|
|
elif [ "$plugin" = "Yoast SEO" ]; then
|
|
echo "wp yoast index --reindex (rebuild indexables)"
|
|
fi
|
|
;;
|
|
esac
|
|
}
|
|
|
|
#############################################################################
|
|
# SUMMARY REPORT
|
|
#############################################################################
|
|
|
|
generate_summary_report() {
|
|
[ -z "$1" ] && return 1
|
|
local problems_file="$1"
|
|
|
|
print_banner "MySQL Query Analysis Summary"
|
|
|
|
# Critical issues
|
|
local critical_count=$(grep -c "^PROBLEM" -- "$problems_file" 2>/dev/null || echo 0)
|
|
|
|
if [ "$critical_count" -gt 0 ]; then
|
|
echo -e "${RED}${BOLD} CRITICAL ISSUES FOUND: $critical_count${NC}"
|
|
echo ""
|
|
|
|
grep "^PROBLEM" -- "$problems_file" | head -10 | while IFS='|' read -r type domain owner db plugin table issue query_time query; do
|
|
echo -e "${RED}[!] $plugin - $domain${NC}"
|
|
echo " Database: $db"
|
|
echo " Table: $table"
|
|
echo " Issue: $issue"
|
|
[ -n "$query_time" ] && echo " Query Time: ${query_time}s"
|
|
echo " Fix: $(recommend_fix "$issue" "$db" "$table" "$plugin")"
|
|
echo ""
|
|
done
|
|
else
|
|
echo -e "${GREEN} No critical issues detected${NC}"
|
|
echo ""
|
|
fi
|
|
}
|
|
|
|
#############################################################################
|
|
# EXPORT FUNCTIONS
|
|
#############################################################################
|
|
|
|
# Make functions available to other scripts
|
|
export -f identify_plugin_from_table
|
|
export -f map_database_to_user_domain
|
|
export -f get_database_owner
|
|
export -f get_database_domain
|
|
export -f analyze_queries_for_problems
|
|
export -f generate_plugin_statistics
|
|
export -f recommend_fix
|