Metadata-Version: 2.4
Name: mcp-server-mysql-py
Version: 1.0.0
Summary: Enterprise-grade MySQL MCP Server with security controls and multi-connection support
Project-URL: Homepage, https://github.com/your-org/mcp-server-mysql-python
Project-URL: Repository, https://github.com/your-org/mcp-server-mysql-python
Project-URL: Issues, https://github.com/your-org/mcp-server-mysql-python/issues
License: MIT
Keywords: ai,claude,database,mcp,mysql
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Python: >=3.10
Requires-Dist: mcp>=1.0.0
Requires-Dist: pydantic>=2.0.0
Requires-Dist: pymysql>=1.1.0
Provides-Extra: dev
Requires-Dist: pytest-asyncio>=0.21.0; extra == 'dev'
Requires-Dist: pytest-cov>=4.0.0; extra == 'dev'
Requires-Dist: pytest>=7.0.0; extra == 'dev'
Description-Content-Type: text/markdown

# mcp-server-mysql-py

Enterprise-grade MySQL MCP Server with security controls, multi-connection support, and AI-powered database operations.

## Features

- **Security First**: Read-only mode by default, DDL/write restrictions, confirmation required for write operations
- **Multi-Connection**: Manage multiple database environments (dev, test, prod) with easy switching
- **SQL Analysis**: Static SQL analysis for risk assessment before execution
- **Query Controls**: Automatic result truncation, query timeout, execution plan analysis
- **Schema Exploration**: Search tables/columns, view indexes, show CREATE TABLE DDL
- **MCP Resources**: Access database metadata via resource URIs
- **Backward Compatible**: Works with existing single-connection configurations

## Installation

```bash
pip install mcp-server-mysql-py
```

## Quick Start

### Basic Configuration (Single Connection)

```json
{
  "mcpServers": {
    "mysql": {
      "command": "mcp-server-mysql-py",
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "password",
        "MYSQL_DB": "mydb"
      }
    }
  }
}
```

### Multi-Connection Configuration

Create a `config.json` file:

```json
{
  "connections": {
    "dev": {
      "host": "dev-db.local",
      "port": 3306,
      "user": "dev",
      "password": "devpass",
      "database": "devdb",
      "description": "Development Environment"
    },
    "prod": {
      "host": "prod-db.local",
      "port": 3306,
      "user": "readonly",
      "password": "prodpass",
      "database": "proddb",
      "description": "Production Environment (Read-Only)"
    }
  },
  "default_connection": "dev"
}
```

Then configure MCP:

```json
{
  "mcpServers": {
    "mysql": {
      "command": "mcp-server-mysql-py",
      "env": {
        "MYSQL_CONFIG_FILE": "/path/to/config.json"
      }
    }
  }
}
```

## Configuration

### Environment Variables

| Variable | Description | Default |
|----------|-------------|---------|
| `MYSQL_HOST` | Database host | `127.0.0.1` |
| `MYSQL_PORT` | Database port | `3306` |
| `MYSQL_USER` | Database user | `root` |
| `MYSQL_PASS` | Database password | (required) |
| `MYSQL_DB` | Database name | (optional) |
| `MYSQL_CONFIG_FILE` | Path to multi-connection config file | (optional) |
| `MYSQL_DEFAULT_CONNECTION` | Default connection name | `default` |
| `ALLOW_WRITE` | Allow INSERT/UPDATE/DELETE | `false` |
| `ALLOW_DDL` | Allow CREATE/ALTER/DROP/TRUNCATE | `false` |
| `REQUIRE_CONFIRM_FOR_WRITE` | Require confirmation for writes | `true` |
| `MAX_ROWS` | Maximum rows to return | `1000` |
| `QUERY_TIMEOUT` | Query timeout in seconds | `30` |
| `LOG_LEVEL` | Logging level (DEBUG, INFO, WARNING, ERROR) | `INFO` |

### Security Settings

**Default Security Posture:**
- `ALLOW_WRITE=false` - Write operations blocked
- `ALLOW_DDL=false` - DDL operations blocked
- `REQUIRE_CONFIRM_FOR_WRITE=true` - Write confirmation required

**Enabling Write Operations:**

```json
{
  "env": {
    "ALLOW_WRITE": "true",
    "REQUIRE_CONFIRM_FOR_WRITE": "true"
  }
}
```

When `REQUIRE_CONFIRM_FOR_WRITE=true`, write operations require `confirmed=true` parameter:

```json
{
  "sql": "UPDATE users SET status = 'active' WHERE id = 1",
  "confirmed": true
}
```

## Tools

### Core Tools

#### `mysql_query`
Execute SQL queries with security controls.

**Parameters:**
- `sql` (required): SQL statement to execute
- `connection` (optional): Connection name to use
- `confirmed` (optional): Set to `true` to confirm write operations

**Example:**
```json
{
  "sql": "SELECT * FROM users LIMIT 10",
  "connection": "dev"
}
```

#### `mysql_tables`
List all tables in the current database.

**Parameters:**
- `connection` (optional): Connection name to use

#### `mysql_describe`
Show columns of a specific table.

**Parameters:**
- `table` (required): Table name
- `connection` (optional): Connection name to use

### Schema Exploration Tools

#### `show_create_table`
Show complete CREATE TABLE DDL statement.

**Parameters:**
- `table` (required): Table name
- `connection` (optional): Connection name

#### `show_indexes`
Show index information for a table.

**Parameters:**
- `table` (required): Table name
- `connection` (optional): Connection name

#### `search_tables`
Search for tables by name (fuzzy match).

**Parameters:**
- `keyword` (required): Search keyword
- `connection` (optional): Connection name

#### `search_columns`
Search for columns across all tables.

**Parameters:**
- `keyword` (required): Column name or keyword
- `connection` (optional): Connection name

**Example:**
```json
{
  "keyword": "user_id"
}
```
Returns:
```
t_order.user_id
t_user.user_id
t_log.user_id
```

### Analysis Tools

#### `analyze_sql`
Statically analyze SQL without execution.

**Parameters:**
- `sql` (required): SQL statement to analyze

**Returns:**
- SQL type (SELECT, INSERT, UPDATE, DELETE, DDL, etc.)
- Involved tables
- Whether DDL/DML
- Dangerous operation detection
- Full table update risk
- Missing WHERE clause detection

#### `explain_sql`
Execute EXPLAIN to analyze query execution plan.

**Parameters:**
- `sql` (required): SQL query to explain
- `connection` (optional): Connection name

**Returns:**
- type: Join type
- key: Index used
- rows: Estimated rows
- extra: Additional info

### Connection Management

#### `list_connections`
List all configured database connections.

**Returns:**
- Connection names
- Host/port/database info
- Descriptions
- Current active connection indicator

## Resources

Access database metadata via resource URIs:

### `db://config`
Get current configuration (without passwords).

### `db://databases`
List all accessible databases.

### `db://tables/{database}`
List all tables in a specific database.

### `db://table/{database}/{table}`
Show schema for a specific table.

### `db://indexes/{database}/{table}`
Show indexes for a specific table.

## Prompts

### `ask_user_which_connection_prompt`
Interactive prompt for selecting database connection on first access.

**Parameters:**
- `force_ask` (optional): Force asking even if already selected

## Architecture

```
src/mcp_server_mysql/
├── __init__.py          # Package initialization
├── config.py            # Pydantic configuration models
├── log.py               # Logging module
├── sql_analyzer.py      # SQL static analysis
├── db.py                # Connection management
├── tools.py             # MCP tool implementations
├── resources.py         # MCP resource implementations
├── prompts.py           # MCP prompt implementations
└── server.py            # Main entry point
```

### Module Responsibilities

- **config**: Pydantic models for configuration, environment variable parsing
- **log**: Centralized logging with configurable levels
- **sql_analyzer**: Static SQL analysis for security and risk assessment
- **db**: Connection pooling, query execution with timeout/limits, permission checks
- **tools**: MCP tool definitions and handlers
- **resources**: MCP resource URI handlers
- **prompts**: MCP prompt definitions and handlers
- **server**: MCP server initialization and routing

## Development

### Install Dependencies

```bash
pip install -e ".[dev]"
```

### Run Tests

```bash
pytest
```

### Run Tests with Coverage

```bash
pytest --cov=mcp_server_mysql --cov-report=term-missing
```

## Migration from v0.1.x

The v1.0.0 release is **fully backward compatible** with v0.1.x configurations. Your existing environment variables will continue to work:

```json
{
  "env": {
    "MYSQL_HOST": "127.0.0.1",
    "MYSQL_PORT": "3306",
    "MYSQL_USER": "root",
    "MYSQL_PASS": "password",
    "MYSQL_DB": "mydb"
  }
}
```

**New in v1.0.0:**
- Security controls (read-only by default)
- Multi-connection support via config file
- Additional tools (show_create_table, show_indexes, analyze_sql, etc.)
- Resource URIs for metadata access
- Connection switching

## Version Upgrade

### From v0.1.0 to v1.0.0

```bash
pip install --upgrade mcp-server-mysql-py
```

**Breaking Changes:** None. Fully backward compatible.

**Recommended:** Add security settings to your configuration:

```json
{
  "env": {
    "ALLOW_WRITE": "false",
    "ALLOW_DDL": "false",
    "MAX_ROWS": "1000",
    "QUERY_TIMEOUT": "30"
  }
}
```

## License

MIT

## Contributing

Contributions welcome! Please ensure tests pass before submitting PRs.
# mcp-server-mysql-py

A Python MCP server for MySQL database queries.

## Installation

```bash
pip install mcp-server-mysql-py
```

## Configuration

Set environment variables:

- `MYSQL_HOST` - Database host (default: 127.0.0.1)
- `MYSQL_PORT` - Database port (default: 3306)
- `MYSQL_USER` - Database user (default: root)
- `MYSQL_PASS` - Database password
- `MYSQL_DB` - Database name

## Usage

### MCP Client Configuration

```json
{
  "mcpServers": {
    "mysql": {
      "command": "mcp-server-mysql-py",
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASS": "password",
        "MYSQL_DB": "mydb"
      }
    }
  }
}
```

### Tools

- **mysql_query** - Execute SQL queries
- **mysql_tables** - List all tables
- **mysql_describe** - Show table columns
