mcp-mysql

du-mozzie/mcp-mysql

3.2

If you are the rightful owner of mcp-mysql and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to dayong@mcphub.com.

A MySQL database server implementation for the Model Context Protocol (MCP), built with Spring Boot, providing AI models with secure access to MySQL databases through a standardized protocol.

Tools
4
Resources
0
Prompts
0

MCP MySQL Server

A MySQL database server implementation for the Model Context Protocol (MCP), built with Spring Boot. This server provides AI models with secure access to MySQL databases through a standardized protocol.

Features

  • MCP Protocol Compliance: Implements MCP protocol over stdin/stdout
  • Dynamic Tool Registration: Configurable database operations (SELECT, INSERT, UPDATE, DELETE)
  • Security First:
    • PreparedStatement for SQL injection protection
    • Table/column name validation
    • Required WHERE clauses for UPDATE/DELETE
    • Query result limitations
  • Connection Pooling: HikariCP for efficient database connections
  • Comprehensive Logging: All logs output to stderr for clean MCP communication

Prerequisites

  • Java 17 or higher
  • Maven 3.6 or higher
  • MySQL 8.0 or higher
  • Access to a MySQL database

Building

Build the project using Maven:

mvn clean package

This will create an executable JAR file in the target directory: mcp-mysql-server-1.0.0.jar

Usage

Command-Line Parameters

Required Parameters
  • --host=<hostname>: MySQL server hostname or IP address
  • --username=<user>: MySQL username
  • --password=<password>: MySQL password
  • --database=<dbname>: Database name to connect to
Optional Parameters
  • --port=<port>: MySQL port (default: 3306)
  • --allowed-operations=<ops>: Comma-separated list of allowed operations (default: select,insert,update,delete)
    • Valid values: select, insert, update, delete
  • --max-connections=<num>: Maximum database connection pool size (default: 10)
  • --query-timeout=<seconds>: SQL query timeout in seconds (default: 30)
  • --max-rows=<num>: Maximum rows returned for SELECT queries (default: 1000)

Running the Server

Basic example:

java -jar target/mcp-mysql-server-1.0.0.jar \
  --host=localhost \
  --port=3306 \
  --username=myuser \
  --password=mypassword \
  --database=mydb

With custom configuration:

java -jar target/mcp-mysql-server-1.0.0.jar \
  --host=db.example.com \
  --username=admin \
  --password=secret123 \
  --database=production \
  --allowed-operations=select,insert \
  --max-connections=20 \
  --query-timeout=60 \
  --max-rows=500

Available Tools

Depending on the --allowed-operations configuration, the following tools are available:

mysql_query

Execute SELECT queries on the database.

Parameters:

  • query (string, required): SQL SELECT query to execute

Returns:

{
  "rows": [...],
  "rowCount": 10
}

Example:

{
  "name": "mysql_query",
  "arguments": {
    "query": "SELECT * FROM users WHERE status = 'active' LIMIT 10"
  }
}

mysql_insert

Insert data into a table.

Parameters:

  • table (string, required): Table name
  • data (object, required): Key-value pairs of column names and values

Returns:

{
  "rowsAffected": 1,
  "insertId": 42
}

Example:

{
  "name": "mysql_insert",
  "arguments": {
    "table": "users",
    "data": {
      "username": "john_doe",
      "email": "john@example.com",
      "status": "active"
    }
  }
}

mysql_update

Update data in a table.

Parameters:

  • table (string, required): Table name
  • data (object, required): Key-value pairs of columns to update
  • where (string, required): WHERE clause condition (without 'WHERE' keyword)

Returns:

{
  "rowsAffected": 3
}

Example:

{
  "name": "mysql_update",
  "arguments": {
    "table": "users",
    "data": {
      "status": "inactive"
    },
    "where": "last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"
  }
}

mysql_delete

Delete data from a table.

Parameters:

  • table (string, required): Table name
  • where (string, required): WHERE clause condition (without 'WHERE' keyword)

Returns:

{
  "rowsAffected": 5
}

Example:

{
  "name": "mysql_delete",
  "arguments": {
    "table": "users",
    "where": "id = 123"
  }
}

MCP Protocol Communication

The server communicates via stdin/stdout using JSON-RPC 2.0 format.

Initialize

{
  "jsonrpc": "2.0",
  "method": "initialize",
  "params": {},
  "id": 1
}

List Tools

{
  "jsonrpc": "2.0",
  "method": "tools/list",
  "params": {},
  "id": 2
}

Call Tool

{
  "jsonrpc": "2.0",
  "method": "tools/call",
  "params": {
    "name": "mysql_query",
    "arguments": {
      "query": "SELECT * FROM users LIMIT 5"
    }
  },
  "id": 3
}

Security Considerations

SQL Injection Protection

  • All INSERT and UPDATE operations use PreparedStatements
  • Table and column names are validated against a whitelist pattern
  • Only alphanumeric characters and underscores allowed in identifiers
  • Maximum identifier length: 64 characters

Query Restrictions

  • mysql_query only accepts SELECT statements
  • Dangerous keywords (DROP, DELETE, INSERT, UPDATE, etc.) blocked in SELECT queries
  • UPDATE and DELETE require WHERE clauses to prevent mass operations

Connection Security

  • Use strong passwords
  • Consider using SSL/TLS for database connections (configure in JDBC URL)
  • Limit allowed-operations to minimum required permissions
  • Set appropriate max-rows to prevent excessive data retrieval

Logging

All application logs are output to stderr to avoid interfering with MCP protocol communication on stdout.

You can control log levels using the LOG_LEVEL environment variable:

LOG_LEVEL=DEBUG java -jar target/mcp-mysql-server-1.0.0.jar --host=... --username=... --password=... --database=...

Troubleshooting

Connection Refused

Issue: Cannot connect to MySQL server

Solutions:

  • Verify MySQL server is running
  • Check hostname and port
  • Verify firewall rules allow connection
  • Ensure user has permission to connect from your host

Authentication Failed

Issue: Access denied for user

Solutions:

  • Verify username and password
  • Check user privileges: GRANT ALL PRIVILEGES ON database.* TO 'user'@'host';
  • Ensure user can connect from your host

Query Timeout

Issue: Queries are timing out

Solutions:

  • Increase --query-timeout parameter
  • Optimize slow queries
  • Check database server performance
  • Review --max-connections setting

Tool Not Found

Issue: Tool is not available

Solutions:

  • Check --allowed-operations parameter includes the required operation
  • Verify tool name spelling (mysql_query, mysql_insert, mysql_update, mysql_delete)

Project Structure

src/main/java/com/example/mcpmysql/
├── McpMysqlServerApplication.java       # Main application entry point
├── config/
│   ├── CommandLineArgsParser.java       # CLI arguments parser
│   ├── DatabaseConfig.java              # Database & connection pool config
│   └── McpConfig.java                   # Application configuration
├── exception/
│   └── MySqlToolException.java          # Custom exception
├── model/
│   ├── McpError.java                    # MCP error model
│   ├── McpRequest.java                  # MCP request model
│   ├── McpResponse.java                 # MCP response model
│   ├── ToolCallResult.java              # Tool execution result
│   └── ToolDefinition.java              # Tool definition model
└── service/
    ├── McpProtocolHandler.java          # MCP protocol handler
    ├── MySqlToolService.java            # MySQL operations implementation
    └── ToolRegistryService.java         # Tool registration service

License

This project is provided as-is for educational and development purposes.

Contributing

Contributions are welcome! Please ensure:

  • Code follows existing style
  • Security measures are maintained
  • All tests pass
  • Documentation is updated

Support

For issues, questions, or feature requests, please open an issue in the project repository.