playbooks

home / skills / sanjay3290 / ai-skills / mysql

mysql skill

/skills/mysql

This skill executes safe read-only MySQL queries across configured databases to explore schemas and analyze data without risking writes.

npx playbooks add skill sanjay3290/ai-skills --skill mysql

Review the files below or copy the command above to add this skill to your agents.

Files (6)
SKILL.md
4.0 KB
---
name: mysql
description: "Execute read-only SQL queries against multiple MySQL databases. Use when: (1) querying MySQL databases, (2) exploring database schemas/tables, (3) running SELECT queries for data analysis, (4) checking database contents. Supports multiple database connections with descriptions for intelligent auto-selection. Blocks all write operations (INSERT, UPDATE, DELETE, DROP, etc.) for safety."
license: Apache-2.0
metadata:
  author: sanjay3290
  version: "1.0"
---

# MySQL Read-Only Query Skill

Execute safe, read-only queries against configured MySQL databases.

## Requirements

- Python 3.8+
- mysql-connector-python: `pip install -r requirements.txt`

## Setup

Create `connections.json` in the skill directory or `~/.config/claude/mysql-connections.json`.

**Security**: Set file permissions to `600` since it contains credentials:
```bash
chmod 600 connections.json
```

```json
{
  "databases": [
    {
      "name": "production",
      "description": "Main app database - users, orders, transactions",
      "host": "db.example.com",
      "port": 3306,
      "database": "app_prod",
      "user": "readonly_user",
      "password": "your-password",
      "ssl_disabled": false
    }
  ]
}
```

### Config Fields

| Field | Required | Description |
|-------|----------|-------------|
| name | Yes | Identifier for the database (case-insensitive) |
| description | Yes | What data this database contains (used for auto-selection) |
| host | Yes | Database hostname |
| port | No | Port number (default: 3306) |
| database | Yes | Database name |
| user | Yes | Username |
| password | Yes | Password |
| ssl_disabled | No | Set to `true` to disable SSL (default: false) |
| ssl_ca | No | Path to CA certificate file |
| ssl_cert | No | Path to client certificate file |
| ssl_key | No | Path to client private key file |

## Usage

### List configured databases
```bash
python3 scripts/query.py --list
```

### Query a database
```bash
python3 scripts/query.py --db production --query "SELECT * FROM users LIMIT 10"
```

### List tables
```bash
python3 scripts/query.py --db production --tables
```

### Show schema
```bash
python3 scripts/query.py --db production --schema
```

### Limit results
```bash
python3 scripts/query.py --db production --query "SELECT * FROM orders" --limit 100
```

## Database Selection

Match user intent to database `description`:

| User asks about | Look for description containing |
|-----------------|--------------------------------|
| users, accounts | users, accounts, customers |
| orders, sales | orders, transactions, sales |
| analytics, metrics | analytics, metrics, reports |
| logs, events | logs, events, audit |

If unclear, run `--list` and ask user which database.

## Safety Features

- **Read-only session**: Connection uses MySQL `SET SESSION TRANSACTION READ ONLY` (primary protection)
- **Query validation**: Only SELECT, SHOW, DESCRIBE, EXPLAIN, WITH queries allowed
- **Single statement**: Multiple statements per query rejected
- **SSL support**: Configurable SSL with CA, client cert, and key support
- **Query timeout**: 30-second max_execution_time enforced (MySQL 5.7.8+)
- **Memory protection**: Max 10,000 rows per query to prevent OOM
- **Column width cap**: 100 char max per column for readable output
- **Credential sanitization**: Error messages don't leak passwords

## Troubleshooting

| Error | Solution |
|-------|----------|
| Config not found | Create `connections.json` in skill directory |
| Authentication failed | Check username/password in config |
| Connection timeout | Verify host/port, check firewall/VPN |
| SSL error | Try `"ssl_disabled": true` for local databases |
| Permission warning | Run `chmod 600 connections.json` |
| max_execution_time not supported | Upgrade to MySQL 5.7.8+ or MariaDB 10.1.1+ |

## Exit Codes

- **0**: Success
- **1**: Error (config missing, auth failed, invalid query, database error)

## Workflow

1. Run `--list` to show available databases
2. Match user intent to database description
3. Run `--tables` or `--schema` to explore structure
4. Execute query with appropriate LIMIT

Overview

This skill executes safe, read-only SQL queries against one or more configured MySQL databases. It helps explore schemas, list tables, and run SELECT-style queries while preventing any write or destructive operations. Connections are chosen intelligently from descriptive entries so the right database is used for a given intent.

How this skill works

The skill reads a connections.json file containing named database entries with host, credentials, and optional SSL settings. It enforces a read-only session, validates that only allowed statements (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH) are executed, and rejects multi-statement or write queries. Result sets are capped (row count, column width) and queries use a timeout to protect memory and execution resources.

When to use it

  • Run ad-hoc SELECT queries against production or staging databases
  • Explore database structure with table lists and schema dumps
  • Validate data, inspect recent records, or sample rows for analysis
  • Check specific tables or fields before making application changes
  • Quickly confirm counts, joins, or aggregated results without risking writes

Best practices

  • Store credentials in connections.json with file permissions set to 600
  • Provide clear, concise descriptions for each connection to enable auto-selection
  • Use --list, --tables, or --schema to inspect structure before composing queries
  • Always include LIMIT for wide scans; the skill enforces a maximum row cap
  • Avoid long-running full-table scans; rely on indexed predicates where possible

Example use cases

  • List configured databases and pick the one containing users or orders
  • Show schema for the orders table to verify available columns before querying
  • Run SELECT queries to sample the latest 100 rows from a transactions table
  • Describe a table to get column types and nullability for data-mapping tasks
  • Run EXPLAIN on a query to review index usage and performance characteristics

FAQ

How do I add or update database connections?

Create or edit connections.json in the skill directory or ~/.config/claude/mysql-connections.json with the required fields (name, description, host, database, user, password). Set file permissions to 600.

What SQL is blocked by the skill?

All write or DDL statements are blocked, including INSERT, UPDATE, DELETE, DROP, ALTER, and multi-statement requests. Only SELECT, SHOW, DESCRIBE, EXPLAIN, and WITH queries are allowed.

How does the skill protect against large results or long queries?

It enforces a 30-second max_execution_time where supported, limits results to 10,000 rows, and truncates columns above a configured width to avoid OOM and unreadable output.