# Directory Structure
```
├── .github
│ └── workflows
│ ├── publish.yml
│ └── test.yml
├── .gitignore
├── CHANGELOG.md
├── Dockerfile
├── LICENSE
├── pyproject.toml
├── pytest.ini
├── README.md
├── requirements-dev.txt
├── requirements.txt
├── SECURITY.md
├── smithery.yaml
├── src
│ └── mysql_mcp_server
│ ├── __init__.py
│ └── server.py
└── tests
├── conftest.py
└── test_server.py
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | # Python-generated files
2 | __pycache__/
3 | *.py[oc]
4 | build/
5 | dist/
6 | wheels/
7 | *.egg-info
8 |
9 | # Virtual environments
10 | .venv
11 | Lib/
12 | Scripts/
13 |
14 | # Development environment
15 | .python-version
16 | uv.lock
17 |
18 | # IDE settings (optional)
19 | .vscode/
20 | .idea/
21 |
22 | # Distribution directories
23 | *.dist-info/
24 |
25 | pypi_publishing_notes.md
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | 
2 | 
3 | [](https://smithery.ai/server/mysql-mcp-server)
4 | [](https://mseep.ai/app/designcomputer-mysql-mcp-server)
5 | # MySQL MCP Server
6 | A Model Context Protocol (MCP) implementation that enables secure interaction with MySQL databases. This server component facilitates communication between AI applications (hosts/clients) and MySQL databases, making database exploration and analysis safer and more structured through a controlled interface.
7 |
8 | > **Note**: MySQL MCP Server is not designed to be used as a standalone server, but rather as a communication protocol implementation between AI applications and MySQL databases.
9 |
10 | ## Features
11 | - List available MySQL tables as resources
12 | - Read table contents
13 | - Execute SQL queries with proper error handling
14 | - Secure database access through environment variables
15 | - Comprehensive logging
16 |
17 | ## Installation
18 | ### Manual Installation
19 | ```bash
20 | pip install mysql-mcp-server
21 | ```
22 |
23 | ### Installing via Smithery
24 | To install MySQL MCP Server for Claude Desktop automatically via [Smithery](https://smithery.ai/server/mysql-mcp-server):
25 | ```bash
26 | npx -y @smithery/cli install mysql-mcp-server --client claude
27 | ```
28 |
29 | ## Configuration
30 | Set the following environment variables:
31 | ```bash
32 | MYSQL_HOST=localhost # Database host
33 | MYSQL_PORT=3306 # Optional: Database port (defaults to 3306 if not specified)
34 | MYSQL_USER=your_username
35 | MYSQL_PASSWORD=your_password
36 | MYSQL_DATABASE=your_database
37 | ```
38 |
39 | ## Usage
40 | ### With Claude Desktop
41 | Add this to your `claude_desktop_config.json`:
42 | ```json
43 | {
44 | "mcpServers": {
45 | "mysql": {
46 | "command": "uv",
47 | "args": [
48 | "--directory",
49 | "path/to/mysql_mcp_server",
50 | "run",
51 | "mysql_mcp_server"
52 | ],
53 | "env": {
54 | "MYSQL_HOST": "localhost",
55 | "MYSQL_PORT": "3306",
56 | "MYSQL_USER": "your_username",
57 | "MYSQL_PASSWORD": "your_password",
58 | "MYSQL_DATABASE": "your_database"
59 | }
60 | }
61 | }
62 | }
63 | ```
64 |
65 | ### With Visual Studio Code
66 | Add this to your `mcp.json`:
67 | ```json
68 | {
69 | "servers": {
70 | "mysql": {
71 | "type": "stdio",
72 | "command": "uvx",
73 | "args": [
74 | "--from",
75 | "mysql-mcp-server",
76 | "mysql_mcp_server"
77 | ],
78 | "env": {
79 | "MYSQL_HOST": "localhost",
80 | "MYSQL_PORT": "3306",
81 | "MYSQL_USER": "your_username",
82 | "MYSQL_PASSWORD": "your_password",
83 | "MYSQL_DATABASE": "your_database"
84 | }
85 | }
86 | }
87 | }
88 | ```
89 | Note: Will need to install uv for this to work
90 |
91 | ### Debugging with MCP Inspector
92 | While MySQL MCP Server isn't intended to be run standalone or directly from the command line with Python, you can use the MCP Inspector to debug it.
93 |
94 | The MCP Inspector provides a convenient way to test and debug your MCP implementation:
95 |
96 | ```bash
97 | # Install dependencies
98 | pip install -r requirements.txt
99 | # Use the MCP Inspector for debugging (do not run directly with Python)
100 | ```
101 |
102 | The MySQL MCP Server is designed to be integrated with AI applications like Claude Desktop and should not be run directly as a standalone Python program.
103 |
104 | ## Development
105 | ```bash
106 | # Clone the repository
107 | git clone https://github.com/designcomputer/mysql_mcp_server.git
108 | cd mysql_mcp_server
109 | # Create virtual environment
110 | python -m venv venv
111 | source venv/bin/activate # or `venv\Scripts\activate` on Windows
112 | # Install development dependencies
113 | pip install -r requirements-dev.txt
114 | # Run tests
115 | pytest
116 | ```
117 |
118 | ## Security Considerations
119 | - Never commit environment variables or credentials
120 | - Use a database user with minimal required permissions
121 | - Consider implementing query whitelisting for production use
122 | - Monitor and log all database operations
123 |
124 | ## Security Best Practices
125 | This MCP implementation requires database access to function. For security:
126 | 1. **Create a dedicated MySQL user** with minimal permissions
127 | 2. **Never use root credentials** or administrative accounts
128 | 3. **Restrict database access** to only necessary operations
129 | 4. **Enable logging** for audit purposes
130 | 5. **Regular security reviews** of database access
131 |
132 | See [MySQL Security Configuration Guide](https://github.com/designcomputer/mysql_mcp_server/blob/main/SECURITY.md) for detailed instructions on:
133 | - Creating a restricted MySQL user
134 | - Setting appropriate permissions
135 | - Monitoring database access
136 | - Security best practices
137 |
138 | ⚠️ IMPORTANT: Always follow the principle of least privilege when configuring database access.
139 |
140 | ## License
141 | MIT License - see LICENSE file for details.
142 |
143 | ## Contributing
144 | 1. Fork the repository
145 | 2. Create your feature branch (`git checkout -b feature/amazing-feature`)
146 | 3. Commit your changes (`git commit -m 'Add some amazing feature'`)
147 | 4. Push to the branch (`git push origin feature/amazing-feature`)
148 | 5. Open a Pull Request
149 |
```
--------------------------------------------------------------------------------
/SECURITY.md:
--------------------------------------------------------------------------------
```markdown
1 | ## MySQL Security Configuration
2 |
3 | ### Creating a Restricted MySQL User
4 |
5 | It's crucial to create a dedicated MySQL user with minimal permissions for the MCP server. Never use the root account or a user with full administrative privileges.
6 |
7 | #### 1. Create a new MySQL user
8 |
9 | ```sql
10 | -- Connect as root or administrator
11 | CREATE USER 'mcp_user'@'localhost' IDENTIFIED BY 'your_secure_password';
12 | ```
13 |
14 | #### 2. Grant minimal required permissions
15 |
16 | Basic read-only access (recommended for exploration and analysis):
17 | ```sql
18 | -- Grant SELECT permission only
19 | GRANT SELECT ON your_database.* TO 'mcp_user'@'localhost';
20 | ```
21 |
22 | Standard access (allows data modification but not structural changes):
23 | ```sql
24 | -- Grant data manipulation permissions
25 | GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'mcp_user'@'localhost';
26 | ```
27 |
28 | Advanced access (includes ability to create temporary tables for complex queries):
29 | ```sql
30 | -- Grant additional permissions for advanced operations
31 | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES
32 | ON your_database.* TO 'mcp_user'@'localhost';
33 | ```
34 |
35 | #### 3. Apply the permissions
36 | ```sql
37 | FLUSH PRIVILEGES;
38 | ```
39 |
40 | ### Additional Security Measures
41 |
42 | 1. **Network Access**
43 | - Restrict the user to connecting only from localhost if the MCP server runs on the same machine
44 | - If remote access is needed, specify exact IP addresses rather than using wildcards
45 |
46 | 2. **Query Restrictions**
47 | - Consider using VIEWs to further restrict data access
48 | - Set appropriate `max_queries_per_hour`, `max_updates_per_hour` limits:
49 | ```sql
50 | ALTER USER 'mcp_user'@'localhost'
51 | WITH MAX_QUERIES_PER_HOUR 1000
52 | MAX_UPDATES_PER_HOUR 100;
53 | ```
54 |
55 | 3. **Data Access Control**
56 | - Grant access only to specific tables when possible
57 | - Use column-level permissions for sensitive data:
58 | ```sql
59 | GRANT SELECT (public_column1, public_column2)
60 | ON your_database.sensitive_table TO 'mcp_user'@'localhost';
61 | ```
62 |
63 | 4. **Regular Auditing**
64 | - Enable MySQL audit logging for the MCP user
65 | - Regularly review logs for unusual patterns
66 | - Periodically review and adjust permissions
67 |
68 | ### Environment Configuration
69 |
70 | When setting up the MCP server, use these restricted credentials in your environment:
71 |
72 | ```bash
73 | MYSQL_USER=mcp_user
74 | MYSQL_PASSWORD=your_secure_password
75 | MYSQL_DATABASE=your_database
76 | MYSQL_HOST=localhost
77 | ```
78 |
79 | ### Monitoring Usage
80 |
81 | To monitor the MCP user's database usage:
82 |
83 | ```sql
84 | -- Check current connections
85 | SELECT * FROM information_schema.PROCESSLIST
86 | WHERE user = 'mcp_user';
87 |
88 | -- View user privileges
89 | SHOW GRANTS FOR 'mcp_user'@'localhost';
90 |
91 | -- Check resource limits
92 | SELECT * FROM mysql.user
93 | WHERE user = 'mcp_user' AND host = 'localhost';
94 | ```
95 |
96 | ### Best Practices
97 |
98 | 1. **Regular Password Rotation**
99 | - Change the MCP user's password periodically
100 | - Use strong, randomly generated passwords
101 | - Update application configurations after password changes
102 |
103 | 2. **Permission Review**
104 | - Regularly audit granted permissions
105 | - Remove unnecessary privileges
106 | - Keep permissions as restrictive as possible
107 |
108 | 3. **Access Patterns**
109 | - Monitor query patterns for potential issues
110 | - Set up alerts for unusual activity
111 | - Maintain detailed logs of database access
112 |
113 | 4. **Data Protection**
114 | - Consider encrypting sensitive columns
115 | - Use SSL/TLS for database connections
116 | - Implement data masking where appropriate
```
--------------------------------------------------------------------------------
/requirements.txt:
--------------------------------------------------------------------------------
```
1 | mcp>=1.0.0
2 | mysql-connector-python>=9.1.0
3 |
```
--------------------------------------------------------------------------------
/requirements-dev.txt:
--------------------------------------------------------------------------------
```
1 | pytest>=7.0.0
2 | pytest-asyncio>=0.23.0
3 | pytest-cov>=4.1.0
4 | black>=23.0.0
5 | isort>=5.12.0
6 | mypy>=1.0.0
7 |
```
--------------------------------------------------------------------------------
/pytest.ini:
--------------------------------------------------------------------------------
```
1 | [pytest]
2 | asyncio_mode = auto
3 | asyncio_default_fixture_loop_scope = function
4 | testpaths = tests
5 | python_files = test_*.py
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server/__init__.py:
--------------------------------------------------------------------------------
```python
1 | from . import server
2 | import asyncio
3 |
4 | def main():
5 | """Main entry point for the package."""
6 | asyncio.run(server.main())
7 |
8 | # Expose important items at package level
9 | __all__ = ['main', 'server']
```
--------------------------------------------------------------------------------
/.github/workflows/publish.yml:
--------------------------------------------------------------------------------
```yaml
1 | name: Publish to PyPI
2 | on:
3 | release:
4 | types: [published]
5 | jobs:
6 | deploy:
7 | runs-on: ubuntu-latest
8 | steps:
9 | - uses: actions/checkout@v4
10 |
11 | - name: Set up Python
12 | uses: actions/setup-python@v4
13 | with:
14 | python-version: '3.12'
15 |
16 | - name: Install dependencies
17 | run: |
18 | python -m pip install --upgrade pip
19 | pip install build
20 |
21 | - name: Build package
22 | run: python -m build
23 |
24 | - name: Publish to PyPI
25 | uses: pypa/gh-action-pypi-publish@release/v1
26 | with:
27 | password: ${{ secrets.PYPI_API_TOKEN }}
28 | # Remove attestations line if it exists
29 |
```
--------------------------------------------------------------------------------
/pyproject.toml:
--------------------------------------------------------------------------------
```toml
1 | [project]
2 | name = "mysql_mcp_server"
3 | version = "0.2.2"
4 | description = "A Model Context Protocol (MCP) server that enables secure interaction with MySQL databases. This server allows AI assistants to list tables, read data, and execute SQL queries through a controlled interface, making database exploration and analysis safer and more structured."
5 | readme = "README.md"
6 | requires-python = ">=3.11"
7 | dependencies = [
8 | "mcp>=1.0.0",
9 | "mysql-connector-python>=9.1.0",
10 | ]
11 | [[project.authors]]
12 | name = "Dana K. Williams"
13 | email = "[email protected]"
14 |
15 | [build-system]
16 | requires = ["hatchling"]
17 | build-backend = "hatchling.build"
18 |
19 | [project.scripts]
20 | mysql_mcp_server = "mysql_mcp_server:main"
21 |
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
1 | # Generated by https://smithery.ai. See: https://smithery.ai/docs/config#dockerfile
2 | # Use the official Python image from the Docker Hub
3 | FROM python:3.11-slim
4 |
5 | # Set the working directory in the container
6 | WORKDIR /app
7 |
8 | # Copy the requirements file into the container
9 | COPY requirements.txt .
10 |
11 | # Install the dependencies specified in the requirements file
12 | RUN pip install --no-cache-dir -r requirements.txt
13 |
14 | # Copy the current directory contents into the container at /app
15 | COPY src/ /app/src
16 |
17 | # Set environment variables for MySQL (these can be overwritten with `docker run -e`)
18 | ENV MYSQL_HOST=host.docker.internal
19 | ENV MYSQL_PORT=3306
20 | ENV MYSQL_USER=your_username
21 | ENV MYSQL_PASSWORD=your_password
22 | ENV MYSQL_DATABASE=your_database
23 | ENV PYTHONPATH=/app/src
24 |
25 | # Command to run the server
26 | CMD ["python", "-m", "mysql_mcp_server.server"]
```
--------------------------------------------------------------------------------
/CHANGELOG.md:
--------------------------------------------------------------------------------
```markdown
1 | # Changelog
2 |
3 | All notable changes to this project will be documented in this file.
4 |
5 | The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/),
6 | and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).
7 |
8 | ## [0.2.2] - 2025-04-18
9 |
10 | ### Fixed
11 | - Fixed handling of SQL commands that return result sets, including `SHOW INDEX`, `SHOW CREATE TABLE`, and `DESCRIBE`
12 | - Added improved error handling for result fetching operations
13 | - Added additional debug output to aid in troubleshooting
14 |
15 | ## [0.2.1] - 2025-02-15
16 |
17 | ### Added
18 | - Support for MYSQL_PORT configuration through environment variables
19 | - Documentation for PORT configuration in README
20 |
21 | ### Changed
22 | - Updated tests to use handler functions directly
23 | - Refactored database configuration to runtime
24 |
25 | ## [0.2.0] - 2025-01-20
26 |
27 | ### Added
28 | - Initial release with MCP server implementation
29 | - Support for SQL queries through MCP interface
30 | - Ability to list tables and read data
31 |
```
--------------------------------------------------------------------------------
/tests/conftest.py:
--------------------------------------------------------------------------------
```python
1 | # tests/conftest.py
2 | import pytest
3 | import os
4 | import mysql.connector
5 | from mysql.connector import Error
6 |
7 | @pytest.fixture(scope="session")
8 | def mysql_connection():
9 | """Create a test database connection."""
10 | try:
11 | connection = mysql.connector.connect(
12 | host=os.getenv("MYSQL_HOST", "127.0.0.1"),
13 | user=os.getenv("MYSQL_USER", "root"),
14 | password=os.getenv("MYSQL_PASSWORD", "testpassword"),
15 | database=os.getenv("MYSQL_DATABASE", "test_db")
16 | )
17 |
18 | if connection.is_connected():
19 | # Create a test table
20 | cursor = connection.cursor()
21 | cursor.execute("""
22 | CREATE TABLE IF NOT EXISTS test_table (
23 | id INT AUTO_INCREMENT PRIMARY KEY,
24 | name VARCHAR(255),
25 | value INT
26 | )
27 | """)
28 | connection.commit()
29 |
30 | yield connection
31 |
32 | # Cleanup
33 | cursor.execute("DROP TABLE IF EXISTS test_table")
34 | connection.commit()
35 | cursor.close()
36 | connection.close()
37 |
38 | except Error as e:
39 | pytest.fail(f"Failed to connect to MySQL: {e}")
40 |
41 | @pytest.fixture(scope="session")
42 | def mysql_cursor(mysql_connection):
43 | """Create a test cursor."""
44 | cursor = mysql_connection.cursor()
45 | yield cursor
46 | cursor.close()
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
1 | # Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
2 |
3 | startCommand:
4 | type: stdio
5 | configSchema:
6 | # JSON Schema defining the configuration options for the MCP.
7 | type: object
8 | required:
9 | - mysqlUser
10 | - mysqlPassword
11 | - mysqlDatabase
12 | properties:
13 | mysqlHost:
14 | type: string
15 | description: "The hostname of the MySQL server. Use localhost for local connections or a specific address for remote databases. For Docker, host.docker.internal allows accessing the host machine."
16 | default: "host.docker.internal"
17 | mysqlPort:
18 | type: number
19 | description: "The port of the MySQL server (default: 3306)."
20 | default: 3306
21 | mysqlUser:
22 | type: string
23 | description: "The username for MySQL authentication."
24 | mysqlPassword:
25 | type: string
26 | description: "The password for MySQL authentication."
27 | mysqlDatabase:
28 | type: string
29 | description: "The database to connect to."
30 | commandFunction:
31 | # A function that produces the CLI command to start the MCP on stdio.
32 | |-
33 | (config) => ({
34 | command: 'docker',
35 | args: [
36 | 'run',
37 | '-i',
38 | '--rm',
39 | '-e', `MYSQL_HOST=${config.mysqlHost}`,
40 | '-e', `MYSQL_PORT=${config.mysqlPort}`,
41 | '-e', `MYSQL_USER=${config.mysqlUser}`,
42 | '-e', `MYSQL_PASSWORD=${config.mysqlPassword}`,
43 | '-e', `MYSQL_DATABASE=${config.mysqlDatabase}`,
44 | 'smithery/mysql-mcp-server:latest'
45 | ]
46 | })
```
--------------------------------------------------------------------------------
/.github/workflows/test.yml:
--------------------------------------------------------------------------------
```yaml
1 | # .github/workflows/test.yml
2 | name: Tests
3 |
4 | on:
5 | push:
6 | branches: [ main ]
7 | pull_request:
8 | branches: [ main ]
9 |
10 | jobs:
11 | test:
12 | runs-on: ubuntu-latest
13 |
14 | services:
15 | mysql:
16 | image: mysql:8.0
17 | env:
18 | MYSQL_ROOT_PASSWORD: testpassword
19 | MYSQL_DATABASE: test_db
20 | ports:
21 | - 3306:3306
22 | options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
23 |
24 | strategy:
25 | matrix:
26 | python-version: ["3.11", "3.12"]
27 |
28 | steps:
29 | - uses: actions/checkout@v4
30 |
31 | - name: Set up Python ${{ matrix.python-version }}
32 | uses: actions/setup-python@v4
33 | with:
34 | python-version: ${{ matrix.python-version }}
35 |
36 | - name: Install dependencies
37 | run: |
38 | python -m pip install --upgrade pip
39 | pip install -r requirements.txt
40 | pip install -r requirements-dev.txt
41 | pip install -e .
42 |
43 | - name: Verify installation
44 | run: |
45 | # Show Python path
46 | python -c "import sys; print('\n'.join(sys.path))"
47 | # Try importing the module
48 | python -c "import mysql_mcp_server; print('Module found at:', mysql_mcp_server.__file__)"
49 | # List installed packages
50 | pip list | grep mysql
51 | # Show the content of site-packages
52 | ls -R $(python -c "import site; print(site.getsitepackages()[0])")
53 |
54 | - name: Run tests
55 | env:
56 | MYSQL_HOST: 127.0.0.1
57 | MYSQL_USER: root
58 | MYSQL_PASSWORD: testpassword
59 | MYSQL_DATABASE: test_db
60 | run: |
61 | python -m pytest -v
```
--------------------------------------------------------------------------------
/tests/test_server.py:
--------------------------------------------------------------------------------
```python
1 | import pytest
2 | from mysql_mcp_server.server import app, list_tools, list_resources, read_resource, call_tool
3 | from pydantic import AnyUrl
4 |
5 | def test_server_initialization():
6 | """Test that the server initializes correctly."""
7 | assert app.name == "mysql_mcp_server"
8 |
9 | @pytest.mark.asyncio
10 | async def test_list_tools():
11 | """Test that list_tools returns expected tools."""
12 | tools = await list_tools()
13 | assert len(tools) == 1
14 | assert tools[0].name == "execute_sql"
15 | assert "query" in tools[0].inputSchema["properties"]
16 |
17 | @pytest.mark.asyncio
18 | async def test_call_tool_invalid_name():
19 | """Test calling a tool with an invalid name."""
20 | with pytest.raises(ValueError, match="Unknown tool"):
21 | await call_tool("invalid_tool", {})
22 |
23 | @pytest.mark.asyncio
24 | async def test_call_tool_missing_query():
25 | """Test calling execute_sql without a query."""
26 | with pytest.raises(ValueError, match="Query is required"):
27 | await call_tool("execute_sql", {})
28 |
29 | # Skip database-dependent tests if no database connection
30 | @pytest.mark.asyncio
31 | @pytest.mark.skipif(
32 | not all([
33 | pytest.importorskip("mysql.connector"),
34 | pytest.importorskip("mysql_mcp_server")
35 | ]),
36 | reason="MySQL connection not available"
37 | )
38 | async def test_list_resources():
39 | """Test listing resources (requires database connection)."""
40 | try:
41 | resources = await list_resources()
42 | assert isinstance(resources, list)
43 | except ValueError as e:
44 | if "Missing required database configuration" in str(e):
45 | pytest.skip("Database configuration not available")
46 | raise
```
--------------------------------------------------------------------------------
/src/mysql_mcp_server/server.py:
--------------------------------------------------------------------------------
```python
1 | import asyncio
2 | import logging
3 | import os
4 | import sys
5 | from mysql.connector import connect, Error
6 | from mcp.server import Server
7 | from mcp.types import Resource, Tool, TextContent
8 | from pydantic import AnyUrl
9 |
10 | # Configure logging
11 | logging.basicConfig(
12 | level=logging.INFO,
13 | format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
14 | )
15 | logger = logging.getLogger("mysql_mcp_server")
16 |
17 | def get_db_config():
18 | """Get database configuration from environment variables."""
19 | config = {
20 | "host": os.getenv("MYSQL_HOST", "localhost"),
21 | "port": int(os.getenv("MYSQL_PORT", "3306")),
22 | "user": os.getenv("MYSQL_USER"),
23 | "password": os.getenv("MYSQL_PASSWORD"),
24 | "database": os.getenv("MYSQL_DATABASE"),
25 | # Add charset and collation to avoid utf8mb4_0900_ai_ci issues with older MySQL versions
26 | # These can be overridden via environment variables for specific MySQL versions
27 | "charset": os.getenv("MYSQL_CHARSET", "utf8mb4"),
28 | "collation": os.getenv("MYSQL_COLLATION", "utf8mb4_unicode_ci"),
29 | # Disable autocommit for better transaction control
30 | "autocommit": True,
31 | # Set SQL mode for better compatibility - can be overridden
32 | "sql_mode": os.getenv("MYSQL_SQL_MODE", "TRADITIONAL")
33 | }
34 |
35 | # Remove None values to let MySQL connector use defaults if not specified
36 | config = {k: v for k, v in config.items() if v is not None}
37 |
38 | if not all([config.get("user"), config.get("password"), config.get("database")]):
39 | logger.error("Missing required database configuration. Please check environment variables:")
40 | logger.error("MYSQL_USER, MYSQL_PASSWORD, and MYSQL_DATABASE are required")
41 | raise ValueError("Missing required database configuration")
42 |
43 | return config
44 |
45 | # Initialize server
46 | app = Server("mysql_mcp_server")
47 |
48 | @app.list_resources()
49 | async def list_resources() -> list[Resource]:
50 | """List MySQL tables as resources."""
51 | config = get_db_config()
52 | try:
53 | logger.info(f"Connecting to MySQL with charset: {config.get('charset')}, collation: {config.get('collation')}")
54 | with connect(**config) as conn:
55 | logger.info(f"Successfully connected to MySQL server version: {conn.get_server_info()}")
56 | with conn.cursor() as cursor:
57 | cursor.execute("SHOW TABLES")
58 | tables = cursor.fetchall()
59 | logger.info(f"Found tables: {tables}")
60 |
61 | resources = []
62 | for table in tables:
63 | resources.append(
64 | Resource(
65 | uri=f"mysql://{table[0]}/data",
66 | name=f"Table: {table[0]}",
67 | mimeType="text/plain",
68 | description=f"Data in table: {table[0]}"
69 | )
70 | )
71 | return resources
72 | except Error as e:
73 | logger.error(f"Failed to list resources: {str(e)}")
74 | logger.error(f"Error code: {e.errno}, SQL state: {e.sqlstate}")
75 | return []
76 |
77 | @app.read_resource()
78 | async def read_resource(uri: AnyUrl) -> str:
79 | """Read table contents."""
80 | config = get_db_config()
81 | uri_str = str(uri)
82 | logger.info(f"Reading resource: {uri_str}")
83 |
84 | if not uri_str.startswith("mysql://"):
85 | raise ValueError(f"Invalid URI scheme: {uri_str}")
86 |
87 | parts = uri_str[8:].split('/')
88 | table = parts[0]
89 |
90 | try:
91 | logger.info(f"Connecting to MySQL with charset: {config.get('charset')}, collation: {config.get('collation')}")
92 | with connect(**config) as conn:
93 | logger.info(f"Successfully connected to MySQL server version: {conn.get_server_info()}")
94 | with conn.cursor() as cursor:
95 | cursor.execute(f"SELECT * FROM {table} LIMIT 100")
96 | columns = [desc[0] for desc in cursor.description]
97 | rows = cursor.fetchall()
98 | result = [",".join(map(str, row)) for row in rows]
99 | return "\n".join([",".join(columns)] + result)
100 |
101 | except Error as e:
102 | logger.error(f"Database error reading resource {uri}: {str(e)}")
103 | logger.error(f"Error code: {e.errno}, SQL state: {e.sqlstate}")
104 | raise RuntimeError(f"Database error: {str(e)}")
105 |
106 | @app.list_tools()
107 | async def list_tools() -> list[Tool]:
108 | """List available MySQL tools."""
109 | logger.info("Listing tools...")
110 | return [
111 | Tool(
112 | name="execute_sql",
113 | description="Execute an SQL query on the MySQL server",
114 | inputSchema={
115 | "type": "object",
116 | "properties": {
117 | "query": {
118 | "type": "string",
119 | "description": "The SQL query to execute"
120 | }
121 | },
122 | "required": ["query"]
123 | }
124 | )
125 | ]
126 |
127 | @app.call_tool()
128 | async def call_tool(name: str, arguments: dict) -> list[TextContent]:
129 | """Execute SQL commands."""
130 | config = get_db_config()
131 | logger.info(f"Calling tool: {name} with arguments: {arguments}")
132 |
133 | if name != "execute_sql":
134 | raise ValueError(f"Unknown tool: {name}")
135 |
136 | query = arguments.get("query")
137 | if not query:
138 | raise ValueError("Query is required")
139 |
140 | try:
141 | logger.info(f"Connecting to MySQL with charset: {config.get('charset')}, collation: {config.get('collation')}")
142 | with connect(**config) as conn:
143 | logger.info(f"Successfully connected to MySQL server version: {conn.get_server_info()}")
144 | with conn.cursor() as cursor:
145 | cursor.execute(query)
146 |
147 | # Special handling for SHOW TABLES
148 | if query.strip().upper().startswith("SHOW TABLES"):
149 | tables = cursor.fetchall()
150 | result = ["Tables_in_" + config["database"]] # Header
151 | result.extend([table[0] for table in tables])
152 | return [TextContent(type="text", text="\n".join(result))]
153 |
154 | # Handle all other queries that return result sets (SELECT, SHOW, DESCRIBE etc.)
155 | elif cursor.description is not None:
156 | columns = [desc[0] for desc in cursor.description]
157 | try:
158 | rows = cursor.fetchall()
159 | result = [",".join(map(str, row)) for row in rows]
160 | return [TextContent(type="text", text="\n".join([",".join(columns)] + result))]
161 | except Error as e:
162 | logger.warning(f"Error fetching results: {str(e)}")
163 | return [TextContent(type="text", text=f"Query executed but error fetching results: {str(e)}")]
164 |
165 | # Non-SELECT queries
166 | else:
167 | conn.commit()
168 | return [TextContent(type="text", text=f"Query executed successfully. Rows affected: {cursor.rowcount}")]
169 |
170 | except Error as e:
171 | logger.error(f"Error executing SQL '{query}': {e}")
172 | logger.error(f"Error code: {e.errno}, SQL state: {e.sqlstate}")
173 | return [TextContent(type="text", text=f"Error executing query: {str(e)}")]
174 |
175 | async def main():
176 | """Main entry point to run the MCP server."""
177 | from mcp.server.stdio import stdio_server
178 |
179 | # Add additional debug output
180 | print("Starting MySQL MCP server with config:", file=sys.stderr)
181 | config = get_db_config()
182 | print(f"Host: {config['host']}", file=sys.stderr)
183 | print(f"Port: {config['port']}", file=sys.stderr)
184 | print(f"User: {config['user']}", file=sys.stderr)
185 | print(f"Database: {config['database']}", file=sys.stderr)
186 |
187 | logger.info("Starting MySQL MCP server...")
188 | logger.info(f"Database config: {config['host']}/{config['database']} as {config['user']}")
189 |
190 | async with stdio_server() as (read_stream, write_stream):
191 | try:
192 | await app.run(
193 | read_stream,
194 | write_stream,
195 | app.create_initialization_options()
196 | )
197 | except Exception as e:
198 | logger.error(f"Server error: {str(e)}", exc_info=True)
199 | raise
200 |
201 | if __name__ == "__main__":
202 | asyncio.run(main())
```