npm package: @flinkhouse/mysql-mcp-server
Description: An MCP server that provides read-only access to MySQL databases.
Author: flinkhouse.com
Latest version: 0.1.3

<a href="https://glama.ai/mcp/servers/@dpflucas/mysql-mcp-server">
  <img width="380" height="200" src="https://glama.ai/mcp/servers/@dpflucas/mysql-mcp-server/badge" alt="mysql-mcp-server MCP server" />
</a>

[![npm version](https://img.shields.io/npm/v/mysql-mcp-server?color=blue)](https://www.npmjs.com/package/mysql-mcp-server) [![smithery badge](https://smithery.ai/badge/@dpflucas/mysql-mcp-server)](https://smithery.ai/server/@dpflucas/mysql-mcp-server)


# MySQL Database Access MCP Server

This MCP server provides access to MySQL databases. By default, it provides read-only access, but can be configured to allow all operations.

**Default (Read-only) mode**:
- List available databases
- List tables in a database
- Describe table schemas
- Execute read-only SQL queries (SELECT, SHOW, DESCRIBE, EXPLAIN)

**All-operations mode** (when `MYSQL_ALLOW_ALL_OPERATIONS` is enabled):
- All read-only operations above
- Execute any SQL queries including INSERT, UPDATE, DELETE, CREATE, DROP, etc.

## Security Features

- **Configurable access control**: Choose between read-only or all-operations mode
- **Query validation**: In read-only mode, prevents SQL injection and blocks any data modification attempts
- **Query timeout**: Prevents long-running queries from consuming resources
- **Row limit**: Prevents excessive data return

## Installation

### 1. Install using one of these methods:

#### Install from NPM

```bash
# Install globally
npm install -g mysql-mcp-server

# Or install locally in your project
npm install mysql-mcp-server
```

#### Build from Source

```bash
# Clone the repository
git clone https://github.com/dpflucas/mysql-mcp-server.git
cd mysql-mcp-server

# Install dependencies and build
npm install
npm run build
```

#### Install via Smithery

To install MySQL Database Access MCP Server for Claude AI automatically via [Smithery](https://smithery.ai/server/@dpflucas/mysql-mcp-server):

```bash
npx -y @smithery/cli install @dpflucas/mysql-mcp-server --client claude
```

### 2. Configure environment variables

The server requires the following environment variables:

- `MYSQL_HOST`: Database server hostname
- `MYSQL_PORT`: Database server port (default: 3306)
- `MYSQL_USER`: Database username
- `MYSQL_PASSWORD`: Database password (optional, but recommended for secure connections)
- `MYSQL_DATABASE`: Default database name (optional)
- `MYSQL_ALLOW_ALL_OPERATIONS`: Set to "true" or "1" to allow all SQL operations (default: false, read-only mode)

### 3. Add to MCP settings

Add the following configuration to your MCP settings file:

If you installed via npm (Option 1):
```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["mysql-mcp-server"],
      "env": {
        "MYSQL_HOST": "your-mysql-host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your-mysql-user",
        "MYSQL_PASSWORD": "your-mysql-password",
        "MYSQL_DATABASE": "your-default-database",
        "MYSQL_ALLOW_ALL_OPERATIONS": "false"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}
```

If you built from source (Option 2):
```json
{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/path/to/mysql-mcp-server/build/index.js"],
      "env": {
        "MYSQL_HOST": "your-mysql-host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your-mysql-user",
        "MYSQL_PASSWORD": "your-mysql-password",
        "MYSQL_DATABASE": "your-default-database",
        "MYSQL_ALLOW_ALL_OPERATIONS": "false"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}
```

## Available Tools

### list_databases

Lists all accessible databases on the MySQL server.

**Parameters**: None

**Example**:
```json
{
  "server_name": "mysql",
  "tool_name": "list_databases",
  "arguments": {}
}
```

### list_tables

Lists all tables in a specified database.

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

**Example**:
```json
{
  "server_name": "mysql",
  "tool_name": "list_tables",
  "arguments": {
    "database": "my_database"
  }
}
```

### describe_table

Shows the schema for a specific table.

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

**Example**:
```json
{
  "server_name": "mysql",
  "tool_name": "describe_table",
  "arguments": {
    "database": "my_database",
    "table": "my_table"
  }
}
```

### execute_query

Executes a SQL query. The allowed operations depend on the server configuration.

**Parameters**:
- `query` (required): SQL query
  - In read-only mode (default): Only SELECT, SHOW, DESCRIBE, and EXPLAIN statements are allowed
  - In all-operations mode: All SQL operations are allowed including INSERT, UPDATE, DELETE, etc.
- `database` (optional): Database name (uses default if not specified)

**Example (read-only mode)**:
```json
{
  "server_name": "mysql",
  "tool_name": "execute_query",
  "arguments": {
    "database": "my_database",
    "query": "SELECT * FROM my_table LIMIT 10"
  }
}
```

**Example (all-operations mode)**:
```json
{
  "server_name": "mysql",
  "tool_name": "execute_query",
  "arguments": {
    "database": "my_database",
    "query": "INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2')"
  }
}
```

## Advanced Connection Pool Configuration

For more control over the MySQL connection pool behavior, you can configure additional parameters:

```json
{
  "mcpServers": {
    "mysql": {
      "command": "npx",
      "args": ["mysql-mcp-server"],
      "env": {
        "MYSQL_HOST": "your-mysql-host",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your-mysql-user",
        "MYSQL_PASSWORD": "your-mysql-password",
        "MYSQL_DATABASE": "your-default-database",
        
        "MYSQL_CONNECTION_LIMIT": "10",
        "MYSQL_QUEUE_LIMIT": "0",
        "MYSQL_CONNECT_TIMEOUT": "10000",
        "MYSQL_IDLE_TIMEOUT": "60000",
        "MYSQL_MAX_IDLE": "10",
        
        "MYSQL_ALLOW_ALL_OPERATIONS": "false"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}
```

These advanced options allow you to:

- `MYSQL_CONNECTION_LIMIT`: Control the maximum number of connections in the pool (default: 10)
- `MYSQL_QUEUE_LIMIT`: Set the maximum number of connection requests to queue (default: 0, unlimited)
- `MYSQL_CONNECT_TIMEOUT`: Adjust the connection timeout in milliseconds (default: 10000)
- `MYSQL_IDLE_TIMEOUT`: Configure how long a connection can be idle before being released (in milliseconds)
- `MYSQL_MAX_IDLE`: Set the maximum number of idle connections to keep in the pool


## Testing

The server includes test scripts to verify functionality with your MySQL setup:

### 1. Setup Test Database

This script creates a test database, table, and sample data:

```bash
# Set your MySQL credentials as environment variables
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=your_username
export MYSQL_PASSWORD=your_password

# Run the setup script
npm run test:setup
```

### 2. Test MCP Tools

This script tests each of the MCP tools against the test database:

```bash
# Set your MySQL credentials as environment variables
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=your_username
export MYSQL_PASSWORD=your_password
export MYSQL_DATABASE=mcp_test_db

# Run the tools test script
npm run test:tools
```

### 3. Run All Tests

To run both setup and tool tests:

```bash
# Set your MySQL credentials as environment variables
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=your_username
export MYSQL_PASSWORD=your_password

# Run all tests
npm test
```

## Troubleshooting

If you encounter issues:

1. Check the server logs for error messages
2. Verify your MySQL credentials and connection details
3. Ensure your MySQL user has appropriate permissions
4. Check that your query is read-only and properly formatted

## License

This project is licensed under the MIT License - see the [LICENSE](../LICENSE) file for details.