# MCP MySQL Server

A Model Context Protocol (MCP) server for MySQL databases, including support for AWS RDS and other cloud MySQL instances. This server provides comprehensive database management capabilities through a standardized MCP interface.

> 📋 **Quick Start for VS Code Users:** See [VSCODE_SETUP.md](VSCODE_SETUP.md) for step-by-step VS Code and GitHub Copilot integration instructions.

## Features

- ✅ **Database Connection Management**: Connect to local MySQL or cloud instances (AWS RDS, Google Cloud SQL, etc.)
- ✅ **Query Execution**: Execute SQL queries with prepared statement support
- ✅ **Schema Inspection**: List databases, tables, and describe table structures
- ✅ **Index Management**: View table indexes and statistics
- ✅ **Security**: Secure connection handling with SSL support
- ✅ **Error Handling**: Comprehensive error reporting and connection management

## Installation

### From NPM (Recommended)

```bash
npm install -g @sajithrw/mcp-mysql@1.0.0
```

Or run ad‑hoc without global install using `npx` (shown later in config).

### From Source

```bash
git clone https://github.com/sajithrw/mcp-mysql.git
cd mcp-mysql
npm install
npm run build
```

## Configuration

### VS Code (mcp.json) Setup (Recommended)

Create (or update) `.vscode/mcp.json` in your project or in your global VS Code user settings folder. Use the published package via `npx` so you always invoke the correct version.

```json
{
  "servers": {
    "mcp-mysql": {
      "type": "stdio",
      "command": "npx",
      "args": [
        "@sajithrw/mcp-mysql@1.0.0"
      ],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}
```

Then:
1. Reload VS Code window (Command Palette: "Developer: Reload Window").
2. Open Command Palette and run: "MCP: Start Server" (pick `mcp-mysql`).
3. Use Copilot / MCP clients to call tools (e.g., ask to list tables).

> Previous `settings.json` based `github.copilot.advanced.mcp` configuration is deprecated in favor of `mcp.json` discovery.

### Alternative: Local Build Path

If working from a local clone/build instead of the published package:

```json
{
  "servers": {
    "mcp-mysql": {
      "type": "stdio",
      "command": "node",
      "args": ["/absolute/path/to/mcp-mysql/build/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password"
      }
    }
  }
}
```

### Using with Claude Desktop

Add the server to your Claude Desktop configuration:

```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["@sajithrw/mcp-mysql@1.0.0"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password"
      }
    }
  }
}
```

### Using with MCP Inspector

For testing and development:

```bash
npx @modelcontextprotocol/inspector npx @sajithrw/mcp-mysql@1.0.0
```

## Using with VS Code and GitHub Copilot

Once configured, you can use the MySQL MCP server with GitHub Copilot in VS Code to interact with your databases using natural language.

### Example Interactions

1. **Database Exploration:**
   - "Show me all tables in the database"
   - "Describe the structure of the users table"
   - "What indexes are on the products table?"

2. **Data Analysis:**
   - "Show me the top 10 customers by order count"
   - "Find all users created in the last 30 days"
   - "Get statistics about the orders table"

3. **Schema Management:**
   - "List all databases on this server"
   - "Show me table sizes and row counts"
   - "Find tables with specific column names"

### Step-by-Step (Recap)

1. Create `.vscode/mcp.json` with config above.
2. Reload VS Code window.
3. Run "MCP: Start Server".
4. Use Copilot Chat / MCP-aware client to issue requests.

### Troubleshooting Quick Tips

- Ensure the package version (`1.0.0`) matches what you intend to use.
- If the server won't start, run it manually: `npx @sajithrw/mcp-mysql@1.0.0` to see logs.
- Verify environment variables are present (you can also place them in a `.env` if your shell loads them before launching VS Code).

## Available Tools

### 1. `mysql_connect`

Connect to a MySQL database.

**Parameters:**
- `host` (required): MySQL server hostname or IP address
- `port` (optional): MySQL server port (default: 3306)
- `user` (required): Database username
- `password` (required): Database password
- `database` (optional): Database name to connect to
- `ssl` (optional): Use SSL connection (default: false)

**Example:**
```json
{
  "host": "localhost",
  "port": 3306,
  "user": "myuser",
  "password": "mypassword",
  "database": "mydb",
  "ssl": false
}
```

### 2. `mysql_query`

Execute a SQL query on the connected database.

**Parameters:**
- `query` (required): SQL query to execute
- `parameters` (optional): Array of parameters for prepared statements

**Examples:**

Simple query:
```json
{
  "query": "SELECT * FROM users LIMIT 10"
}
```

Prepared statement:
```json
{
  "query": "SELECT * FROM users WHERE age > ? AND city = ?",
  "parameters": ["25", "New York"]
}
```

### 3. `mysql_list_databases`

List all databases on the MySQL server.

**Parameters:** None

### 4. `mysql_list_tables`

List all tables in the current or specified database.

**Parameters:**
- `database` (optional): Database name (uses current database if not specified)

### 5. `mysql_describe_table`

Get the structure/schema of a specific table.

**Parameters:**
- `table` (required): Table name to describe
- `database` (optional): Database name (uses current database if not specified)

### 6. `mysql_show_indexes`

Show indexes for a specific table.

**Parameters:**
- `table` (required): Table name to show indexes for
- `database` (optional): Database name (uses current database if not specified)

### 7. `mysql_get_table_stats`

Get statistics about a table (row count, size, etc.).

**Parameters:**
- `table` (required): Table name to get statistics for
- `database` (optional): Database name (uses current database if not specified)

### 8. `mysql_disconnect`

Disconnect from the MySQL database.

**Parameters:** None

## Common Use Cases

### 1. Connecting to AWS RDS

```json
{
  "host": "mydb.abc123.us-west-2.rds.amazonaws.com",
  "port": 3306,
  "user": "admin",
  "password": "mypassword",
  "database": "production",
  "ssl": true
}
```

### 2. Exploring Database Schema

1. Connect to database using `mysql_connect`
2. List all databases with `mysql_list_databases`
3. List tables with `mysql_list_tables`
4. Describe specific tables with `mysql_describe_table`
5. Check indexes with `mysql_show_indexes`

### 3. Data Analysis

1. Connect to database
2. Execute analytical queries with `mysql_query`
3. Get table statistics with `mysql_get_table_stats`

## Security Considerations

- **Credentials**: Never hardcode database credentials. Use environment variables or secure configuration management.
- **SSL/TLS**: Always use SSL when connecting to production databases or cloud instances.
- **Permissions**: Use database users with minimal required permissions.
- **Query Validation**: The server uses prepared statements to prevent SQL injection.

## Environment Variables

You can set default connection parameters using environment variables:

```bash
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=myuser
export MYSQL_PASSWORD=mypassword
export MYSQL_DATABASE=mydb
export MYSQL_SSL=true
```

## Development

### Building

```bash
npm run build
```

### Testing

```bash
npm run test
```

### Development Mode

```bash
npm run dev
```

## Troubleshooting

### Connection Issues

1. **Connection Refused**: Check that MySQL server is running and accessible
2. **Authentication Failed**: Verify username and password
3. **SSL Errors**: Ensure SSL is properly configured on both client and server
4. **Timeout**: Check network connectivity and firewall settings

### Common Error Messages

- `"Not connected to MySQL"`: Use `mysql_connect` before executing other commands
- `"Query execution failed"`: Check SQL syntax and table/column names
- `"Connection failed"`: Verify connection parameters and network connectivity

## Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests
5. Submit a pull request

## License

MIT License - see LICENSE file for details.

## Support

- [GitHub Issues](https://github.com/sajithrw/mcp-mysql/issues)
- [MCP Documentation](https://modelcontextprotocol.io/)
- [MySQL Documentation](https://dev.mysql.com/doc/)

## Related Projects

- [Model Context Protocol](https://github.com/modelcontextprotocol)
- [MCP Python SDK](https://github.com/modelcontextprotocol/python-sdk)
- [MCP TypeScript SDK](https://github.com/modelcontextprotocol/typescript-sdk)