cloudthinker-ai MySQL Analysis Skill
Analyze and optimize MySQL/MariaDB databases using safe, read-only operations.
MANDATORY: Two-Phase Execution
Always discover schema before querying. Skipping Phase 1 causes hallucinated table/column names.
Phase 1: Discovery (ALWAYS first)
#!/bin/bash
# Run this FIRST — discover actual databases, tables, and columns
mysql_cmd() {
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" \
--silent --skip-column-names "$@"
}
echo "=== Databases ==="
mysql_cmd -e "SHOW DATABASES;" | grep -v -E '^(information_schema|performance_schema|mysql|sys)$'
echo ""
echo "=== Tables in target DB (with row counts) ==="
mysql_cmd -e "
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH/1024/1024 AS data_mb,
INDEX_LENGTH/1024/1024 AS index_mb, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY DATA_LENGTH DESC;" "$TARGET_DB" 2>/dev/null || \
mysql_cmd -e "SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' ORDER BY TABLE_ROWS DESC LIMIT 20;"
echo ""
echo "=== Columns (sample table) ==="
mysql_cmd -e "DESCRIBE your_table;" "$TARGET_DB"
Phase 2: Analysis
Only reference tables and columns confirmed in Phase 1.
Helper Function
#!/bin/bash
mysql_cmd() {
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" \
--silent --skip-column-names -e "$1" "${2:-}"
}
# For multi-line queries from file
mysql_file() {
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" \
--silent --skip-column-names "${1:-}" < "$2"
}
Anti-Hallucination Rules
- NEVER reference a table without confirming it in
information_schema.TABLES - NEVER reference a column without confirming it in
information_schema.COLUMNSorDESCRIBE - NEVER assume index names — always
SHOW INDEX FROM table - NEVER modify data — only SELECT, SHOW, EXPLAIN, DESCRIBE, information_schema queries
Safety Rules
- READ-ONLY: Only
SELECT,SHOW,EXPLAIN,DESCRIBE— no DDL or DML - ALWAYS add
LIMIT: Default cap 1000 rows unless aggregating - Use
EXPLAINbefore heavy queries on large tables - Avoid
SELECT *— always specify needed columns - Use
information_schemafor metadata, notSHOWcommands when possible (more portable)
Common Operations
Server Health Overview
#!/bin/bash
echo "=== MySQL Server Status ==="
{
mysql_cmd "SHOW GLOBAL STATUS LIKE 'Uptime';" &
mysql_cmd "SHOW GLOBAL STATUS LIKE 'Threads_connected';" &
mysql_cmd "SHOW GLOBAL STATUS LIKE 'Questions';" &
mysql_cmd "SHOW GLOBAL STATUS LIKE 'Slow_queries';" &
mysql_cmd "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';" &
mysql_cmd "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';" &
}
wait
echo ""
echo "=== Buffer Pool Hit Rate ==="
mysql_cmd "
SELECT 100 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests * 100) AS hit_rate_pct
FROM (
SELECT
(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests
) t;"
echo ""
echo "=== Current Connections ==="
mysql_cmd "SHOW PROCESSLIST;" | awk '{print $5}' | sort | uniq -c | sort -rn | head -10
echo ""
echo "=== MySQL Version & Config ==="
mysql_cmd "SELECT VERSION(), @@max_connections, @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb, @@query_cache_size/1024/1024 AS qcache_mb;"
Slow Query Analysis
#!/bin/bash
DB_NAME="${1:-}"
echo "=== Slow Query Log Status ==="
mysql_cmd "SHOW VARIABLES LIKE 'slow_query_log%';"
mysql_cmd "SHOW VARIABLES LIKE 'long_query_time';"
echo ""
echo "=== Top Slow Queries from Performance Schema ==="
mysql_cmd "
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_latency_ms,
ROUND(MAX_TIMER_WAIT/1e9, 2) AS max_latency_ms,
ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS avg_rows_examined,
ROUND(SUM_ROWS_SENT/COUNT_STAR) AS avg_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 15;" 2>/dev/null | head -20
echo ""
echo "=== Currently Running Long Queries (>5s) ==="
mysql_cmd "
SELECT ID, USER, HOST, DB, COMMAND, TIME, LEFT(INFO, 100) AS query_preview
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep' AND TIME > 5
ORDER BY TIME DESC;"
Index Analysis
#!/bin/bash
DB_NAME="${1:-}"
echo "=== Tables Without Primary Key ==="
mysql_cmd "
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys')
AND NOT EXISTS (
SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_NAME = t.TABLE_NAME
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
)
${DB_NAME:+AND TABLE_SCHEMA = '$DB_NAME'}
LIMIT 20;"
echo ""
echo "=== Duplicate Indexes ==="
mysql_cmd "
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys')
${DB_NAME:+AND TABLE_SCHEMA = '$DB_NAME'}
ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
LIMIT 50;"
echo ""
echo "=== Index Usage (Performance Schema) ==="
mysql_cmd "
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
COUNT_READ, COUNT_WRITE, COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA NOT IN ('performance_schema','mysql','sys')
AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_READ ASC
LIMIT 20;" 2>/dev/null
Table Statistics
#!/bin/bash
DB_NAME="${1:?Usage: $0 <database>}"
echo "=== Table Sizes in $DB_NAME ==="
mysql_cmd "
SELECT
TABLE_NAME,
TABLE_ROWS,
ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS total_mb,
ENGINE,
TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '$DB_NAME' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC
LIMIT 25;"
echo ""
echo "=== Fragmented Tables (needs OPTIMIZE) ==="
mysql_cmd "
SELECT TABLE_NAME,
ROUND(DATA_FREE/1024/1024, 2) AS free_space_mb,
ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
ROUND(DATA_FREE/DATA_LENGTH*100, 1) AS fragmentation_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '$DB_NAME'
AND DATA_FREE > 0
AND DATA_LENGTH > 0
ORDER BY DATA_FREE DESC
LIMIT 15;"
Replication Health
#!/bin/bash
echo "=== Replication Status ==="
mysql_cmd "SHOW SLAVE STATUS\G" 2>/dev/null || mysql_cmd "SHOW REPLICA STATUS\G" 2>/dev/null || echo "Not a replica"
echo ""
echo "=== Replication Lag ==="
mysql_cmd "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E 'Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running' || \
mysql_cmd "SHOW REPLICA STATUS\G" 2>/dev/null | grep -E 'Seconds_Behind_Source|Replica_IO_Running|Replica_SQL_Running'
echo ""
echo "=== Binary Log Status ==="
mysql_cmd "SHOW MASTER STATUS\G" 2>/dev/null || mysql_cmd "SHOW BINARY LOG STATUS\G" 2>/dev/null
mysql_cmd "SHOW BINARY LOGS;" 2>/dev/null | awk '{sum+=$2} END {print "Total binlog size:", sum/1024/1024, "MB"}'
InnoDB Health
#!/bin/bash
echo "=== InnoDB Buffer Pool ==="
mysql_cmd "
SELECT
variable_name,
ROUND(variable_value/1024/1024, 1) AS value_mb
FROM information_schema.GLOBAL_STATUS
WHERE variable_name IN (
'Innodb_buffer_pool_bytes_data',
'Innodb_buffer_pool_bytes_dirty',
'Innodb_buffer_pool_pages_total',
'Innodb_buffer_pool_pages_free'
);"
echo ""
echo "=== InnoDB Lock Waits ==="
mysql_cmd "
SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
LEFT(r.trx_query, 80) AS waiting_query,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
LIMIT 10;" 2>/dev/null
echo ""
echo "=== Long Running Transactions ==="
mysql_cmd "
SELECT trx_id, trx_started, trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
LEFT(trx_query, 80) AS query_preview
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY trx_started ASC
LIMIT 10;"
Query Explain
#!/bin/bash
DB_NAME="$1"
QUERY="$2"
echo "=== EXPLAIN for query ==="
mysql_cmd "EXPLAIN FORMAT=JSON $QUERY" "$DB_NAME" | jq '.query_block | {
select_type: .select_type,
table: .table.table_name,
access_type: .table.access_type,
key: .table.key,
key_length: .table.key_length,
rows: .table.rows_examined_per_scan,
filtered: .table.filtered,
extra: .table.attached_condition
}'
echo ""
echo "=== EXPLAIN ANALYZE (MySQL 8+) ==="
mysql_cmd "EXPLAIN ANALYZE $QUERY" "$DB_NAME" 2>/dev/null || echo "EXPLAIN ANALYZE requires MySQL 8.0+"
Output Format
Present results as a structured report:
Analyzing Mysql Report
══════════════════════
Resources discovered: [count]
Resource Status Key Metric Issues
──────────────────────────────────────────────
[name] [ok/warn] [value] [findings]
Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]
Target ≤50 lines of output. Use tables for multi-resource comparisons.
Counter-Rationalizations
| Shortcut | Counter | Why |
|---|---|---|
| "I'll skip discovery and check known resources" | Always run Phase 1 discovery first | Resource names change, new resources appear — assumed names cause errors |
| "The user only asked for a quick check" | Follow the full discovery → analysis flow | Quick checks miss critical issues; structured analysis catches silent failures |
| "Default configuration is probably fine" | Audit configuration explicitly | Defaults often leave logging, security, and optimization features disabled |
| "Metrics aren't needed for this" | Always check relevant metrics when available | API/CLI responses show current state; metrics reveal trends and intermittent issues |
| "I don't have access to that" | Try the command and report the actual error | Assumed permission failures prevent useful investigation; actual errors are informative |
Common Pitfalls
SHOW SLAVE STATUSdeprecated: UseSHOW REPLICA STATUSin MySQL 8.0.22+ and MariaDB 10.5+information_schema.TABLESrow counts:TABLE_ROWSis an estimate for InnoDB — useCOUNT(*)for exact counts- Performance Schema disabled: Some setups disable performance_schema — check with
SHOW VARIABLES LIKE 'performance_schema' SELECT * FROM large_table: Always addLIMIT— NEVER run unbounded queries on tables with millions of rows- Character set issues: If seeing
?or garbled text, add--default-character-set=utf8mb4to connection - InnoDB vs MyISAM: Lock behavior differs significantly — check ENGINE before recommending solutions
ANALYZE TABLEside effects: This updates statistics but requires table lock on MyISAM — use cautiously- MySQL 5.7 vs 8.0 syntax: Many performance_schema queries differ — always check version in Phase 1