# MySQL MCP Server

A [Model Context Protocol (MCP)](https://modelcontextprotocol.io) server that provides secure MySQL database operations for AI assistants and other MCP clients.

## Features

### 🛠️ Tools
- **execute_query** - Execute safe SQL queries (SELECT, INSERT, SHOW, DESCRIBE)
- **get_table_info** - Get detailed table structure information
- **insert_data** - Convenient data insertion with automatic parameterization
- **list_tables** - List all tables in the database

### 📊 Resources
- **Database Info** (`mysql://database/info`) - Database metadata and table list
- **Table Structure** (`mysql://table/{table_name}`) - Detailed table schema information

### 🔒 Security Features
- **Limited Operations**: Only allows safe operations (SELECT, INSERT, SHOW, DESCRIBE)
- **SQL Injection Protection**: Uses parameterized queries
- **Automatic LIMIT**: Adds LIMIT clauses to SELECT queries to prevent large data dumps
- **Connection Pooling**: Efficient database connection management

## Installation

```bash
npm install mysql-mcp-server
```

## Configuration

The server can be configured using environment variables:

| Variable | Default | Description |
|----------|---------|-------------|
| `MYSQL_HOST` | `localhost` | MySQL server hostname |
| `MYSQL_PORT` | `3306` | MySQL server port |
| `MYSQL_USER` | `root` | MySQL username |
| `MYSQL_PASSWORD` | _(empty)_ | MySQL password |
| `MYSQL_DB` | `mysql` | Default database name |
| `MYSQL_TIMEZONE` | `+00:00` | MySQL timezone |

## Usage with MCP Clients

### Claude Desktop

Add to your Claude Desktop configuration file:

**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
**Windows**: `%APPDATA%/Claude/claude_desktop_config.json`

```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["mysql-mcp-server"],
      "env": {
        "MYSQL_HOST": "your-mysql-host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your-username",
        "MYSQL_PASSWORD": "your-password",
        "MYSQL_DB": "your-database"
      }
    }
  }
}
```

### Other MCP Clients

For other MCP clients, use the server with stdio transport:

```bash
npx mysql-mcp-server
```

## Development

### Prerequisites
- Node.js 18+
- TypeScript
- Access to a MySQL database

### Setup

1. Clone the repository:
```bash
git clone https://github.com/your-username/mysql-mcp-server.git
cd mysql-mcp-server
```

2. Install dependencies:
```bash
npm install
```

3. Set up environment variables:
```bash
cp .env.example .env
# Edit .env with your database configuration
```

4. Build the server:
```bash
npm run build
```

### Development Commands

```bash
# Build the server
npm run build

# Watch mode for development
npm run watch

# Debug with MCP Inspector
npm run inspector
```

### Testing

You can test the server using the MCP Inspector:

```bash
npm run inspector
```

This will start the inspector and provide a URL to test the server in your browser.

## Example Usage

Once connected through an MCP client, you can:

1. **List all tables**:
   ```
   Use the list_tables tool
   ```

2. **Get table structure**:
   ```
   Use get_table_info with table_name: "users"
   ```

3. **Query data**:
   ```
   Use execute_query with sql: "SELECT * FROM users LIMIT 10"
   ```

4. **Insert data**:
   ```
   Use insert_data with table_name: "users" and data: {"name": "John", "email": "john@example.com"}
   ```

## Security Considerations

This server is designed with security in mind:

- **Read-mostly operations**: Only SELECT, INSERT, SHOW, and DESCRIBE operations are allowed
- **No destructive operations**: UPDATE and DELETE are explicitly forbidden
- **Parameterized queries**: All user inputs are properly escaped
- **Connection limits**: Uses connection pooling to prevent resource exhaustion
- **Query limits**: Automatic LIMIT clauses prevent large data dumps

## Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

## License

MIT License - see LICENSE file for details.

## Related

- [Model Context Protocol](https://modelcontextprotocol.io)
- [MCP TypeScript SDK](https://github.com/modelcontextprotocol/typescript-sdk)
- [Other MCP Servers](https://github.com/modelcontextprotocol/servers)