npm package: @suryaadithya/mysql-mcp-server
Description: A Model Context Protocol server for MySQL database operations
Author: Surya Adithya
Latest version: 2.0.2

# MySQL MCP Server

A Model Context Protocol (MCP) server that provides secure, read-only access to MySQL databases. This server enables Large Language Models to inspect database schemas and execute read-only queries safely.

## Features

- **Read-only Operations**: Only SELECT, SHOW, DESCRIBE, and EXPLAIN queries are allowed
- **Security First**: SQL injection prevention through prepared statements and query validation
- **Connection Pooling**: Efficient database connection management
- **Multi-database Support**: Connect to multiple databases on the same MySQL server
- **Environment Configuration**: Secure configuration through environment variables
- **TypeScript**: Full type safety and modern JavaScript features

## Tools Available

### 1. `execute-query`
Execute read-only SQL queries against the MySQL database.
- **Input**: SQL query string, optional parameters array, optional database name
- **Supported**: SELECT, SHOW, DESCRIBE, EXPLAIN statements
- **Security**: Prepared statements prevent SQL injection

### 2. `list-databases`
List all databases available on the MySQL server.
- **Input**: None
- **Output**: Array of database names

### 3. `list-tables`
List all tables in a specific database.
- **Input**: Optional database name
- **Output**: Table names with types and comments

### 4. `describe-table`
Get detailed information about a table structure.
- **Input**: Table name, optional database name
- **Output**: Column details including data types, constraints, and comments

### 5. `show-indexes`
Show all indexes for a specific table.
- **Input**: Table name, optional database name
- **Output**: Index information including columns and types

## Installation

### Prerequisites
- Node.js 18 or higher
- MySQL server access
- npm or yarn package manager

### From Source
```bash
# Clone the repository
git clone <repository-url>
cd mysql-mcp

# Install dependencies
npm install

# Build the project
npm run build
```

### NPM Installation
```bash
npm install -g @suryaadithya/mysql-mcp-server
```

## Configuration

Create a `.env` file in your project root (copy from `.env.example`):

**Option 1: Individual Environment Variables**
```env
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=your_password_here
MYSQL_DATABASE=your_default_database

# Optional settings
MYSQL_SSL=false
MYSQL_CONNECTION_LIMIT=10
MYSQL_TIMEOUT=60000
```

**Option 2: Connection String (Recommended)**
```env
MYSQL_CONNECTION_STRING=mysql://username:password@localhost:3306/database_name?ssl=true&connectionLimit=10&timeout=60000

# Optional: Override connection limits
MYSQL_CONNECTION_LIMIT=20
MYSQL_TIMEOUT=30000
```

### Environment Variables

| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| `MYSQL_CONNECTION_STRING` | No* | - | Complete MySQL connection string (mysql://user:pass@host:port/db?options) |
| `MYSQL_HOST` | No* | localhost | MySQL server hostname |
| `MYSQL_PORT` | No* | 3306 | MySQL server port |
| `MYSQL_USER` | No* | root | MySQL username |
| `MYSQL_PASSWORD` | Yes* | - | MySQL password |
| `MYSQL_DATABASE` | No* | - | Default database name |
| `MYSQL_SSL` | No* | false | Enable SSL connection |
| `MYSQL_CONNECTION_LIMIT` | No | 10 | Max connections in pool |
| `MYSQL_TIMEOUT` | No | 60000 | Connection timeout (ms) |

**Note**: Either `MYSQL_CONNECTION_STRING` OR the individual connection variables (`MYSQL_HOST`, `MYSQL_USER`, etc.) are required. Connection string takes precedence if both are provided.

## Usage

### Running the Server
```bash
# Development mode
npm run dev

# Production mode
npm start

# Or if installed globally
mysql-mcp
```

### With Claude Desktop

#### Using Claude CLI (Recommended)
Add this server using the Claude CLI:

**Using Connection String:**
```bash
claude mcp add mysql-mcp npx @suryaadithya/mysql-mcp-server \
  -e MYSQL_CONNECTION_STRING="mysql://root:your_password@localhost:3306/your_database"
```

**Using Individual Variables:**
```bash
claude mcp add mysql-mcp npx @suryaadithya/mysql-mcp-server \
  -e MYSQL_HOST=localhost \
  -e MYSQL_PORT=3306 \
  -e MYSQL_USER=root \
  -e MYSQL_PASSWORD=your_password \
  -e MYSQL_DATABASE=your_database
```

**Windows PowerShell:**
```powershell
$env:MYSQL_CONNECTION_STRING="mysql://root:your_password@localhost:3306/your_database"; claude mcp add mysql-mcp cmd /c npx @suryaadithya/mysql-mcp-server
```

#### Manual Configuration
Alternatively, add this server to your Claude Desktop configuration:

**Using Connection String:**
```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@suryaadithya/mysql-mcp-server"],
      "env": {
        "MYSQL_CONNECTION_STRING": "mysql://your_user:your_password@localhost:3306/your_database"
      }
    }
  }
}
```

**Using Individual Variables:**
```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@suryaadithya/mysql-mcp-server"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_user",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}
```

### Example Queries

Once connected, you can ask Claude to:

- "Show me all databases available"
- "List all tables in the 'users' database"
- "Describe the structure of the 'customers' table"
- "Show me the first 10 rows from the 'orders' table"
- "What indexes exist on the 'products' table?"

## Security Features

- **Query Validation**: Only safe, read-only operations are permitted
- **Prepared Statements**: All user inputs are properly escaped
- **Connection Limits**: Configurable connection pooling prevents resource exhaustion
- **Error Handling**: Safe error messages that don't expose sensitive information
- **No Write Operations**: INSERT, UPDATE, DELETE, and DDL statements are blocked

## Development

### Project Structure
```
mysql-mcp/
├── src/
│   ├── index.ts          # Main server entry point
│   ├── database.ts       # Database connection and query methods
│   └── types.ts          # TypeScript type definitions
├── dist/                 # Compiled JavaScript output
├── package.json
├── tsconfig.json
├── .env.example
└── README.md
```

### Building
```bash
npm run build
```

### Development Mode
```bash
npm run dev
```

## Troubleshooting

### Connection Issues
- Verify MySQL server is running and accessible
- Check environment variables are set correctly
- Ensure MySQL user has appropriate permissions
- Test connection manually using mysql client

### Permission Errors
- Ensure MySQL user has SELECT privileges on target databases
- Grant SHOW DATABASES permission if listing databases
- Check firewall settings if connecting remotely

### Query Restrictions
- Only SELECT, SHOW, DESCRIBE, and EXPLAIN queries are allowed
- Multi-statement queries are disabled for security
- Comments (-- and /* */) are not permitted in queries

## License

MIT License - see LICENSE file for details

## Contributing

1. Fork the repository
2. Create a feature branch
3. Add tests for new functionality
4. Ensure all tests pass
5. Submit a pull request

## Support

For issues and questions:
- Create an issue on GitHub
- Check existing documentation
- Verify configuration and permissions