xaviram/mysql-mariadb-mcp-server

MariaDB/MySQL MCP Server

A Model Context Protocol (MCP) server that provides seamless integration with MariaDB and MySQL databases. This server enables AI assistants and other MCP clients to interact with your database through a standardized interface.

✨ Features

• 🔐 Secure Database Connections: Support for SSL/TLS encryption and connection pooling
• 🔧 Comprehensive Database Tools: Execute queries, browse tables, and inspect database structure
• 📊 Smart Data Sampling: Preview table contents with configurable limits
• 🌐 Multiple Database Support: Works with both MariaDB and MySQL
• ⚡ Async Performance: Built with asyncio for high performance
• 🔍 Error Handling: Robust error handling with detailed feedback
• 📝 Extensive Logging: Configurable logging for debugging and monitoring
• 🛡️ Security Best Practices: Environment-based configuration and SQL injection protection

🚀 Quick Start

Prerequisites

• Python 3.8 or higher
• MariaDB or MySQL server
• Required Python packages (see requirements.txt)

Installation

1. Clone the repository:

   git clone https://github.com/xaviram/mysql-mariadb-mcp-server.git
   cd mysql-mariadb-mcp-server
   

2. Install dependencies:

   pip install -r requirements.txt
   

3. Configure your database connection:

   cp mcp.example.json mcp.json
   # Edit mcp.json with your database credentials
   

4. Test the server:

   python mysql_mcp_server.py
   

⚙️ Configuration

Environment Variables

Configure the server using environment variables or the mcp.json file:

Configuration File

Create a mcp.json file:

{
  "DB_HOST": "localhost",
  "DB_PORT": "3306",
  "DB_USER": "your_username",
  "DB_PASSWORD": "your_password",
  "DB_NAME": "your_database",
  "DB_CHARSET": "utf8mb4",
  "CONNECTION_TIMEOUT": "10",
  "MAX_CONNECTIONS": "5",
  "USE_SSL": "false"
}

�️ Available Tools

execute_sql

Execute SQL queries on the database.

Parameters:

• query (string): SQL query to execute

Example:

SELECT * FROM users WHERE active = 1 LIMIT 10;

show_tables

List all tables in the current database.

Returns: List of table names with row counts and descriptions.

describe_table

Show the structure of a specific table.

Parameters:

• table_name (string): Name of the table to describe

Returns: Column definitions with types, constraints, and properties.

table_data_sample

Preview data from a table.

Parameters:

• table_name (string): Name of the table
• limit (integer, optional): Number of rows to return (default: 10)

Returns: Sample rows from the specified table.

📚 Resources

The server provides the following resources:

• mysql://{database}/tables: Overview of all tables in the database
• mysql://{database}/table/{table_name}: Detailed information about a specific table

🔧 Usage with MCP Clients

Claude Desktop

Add to your Claude Desktop configuration:

{
  "servers": {
    "mysql": {
      "command": "python",
      "args": ["path/to/mysql_mcp_server.py"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database"
      }
    }
  }
}

Continue VS Code Extension

Add to your Continue configuration:

{
  "contextProviders": [
    {
      "name": "mcp",
      "params": {
        "command": "python",
        "args": ["mysql_mcp_server.py"],
        "env": {
          "DB_HOST": "localhost",
          "DB_USER": "your_username",
          "DB_PASSWORD": "your_password",
          "DB_NAME": "your_database"
        }
      }
    }
  ]
}

🧪 Testing

Run the comprehensive test suite:

python test_mcp_server.py

The test script will:

• Verify database connectivity
• Test all available tools
• Validate resource access
• Check error handling
• Provide troubleshooting guidance

🛡️ Security Considerations

Best Practices

1. Use Environment Variables: Never hardcode database credentials
2. Limit Database Permissions: Create a dedicated database user with minimal required permissions
3. Enable SSL/TLS: Use encrypted connections in production
4. Network Security: Restrict database access to trusted networks
5. Query Validation: The server includes protection against SQL injection

Recommended Database User Permissions

-- Create a dedicated user for the MCP server
CREATE USER 'mcp_user'@'%' IDENTIFIED BY 'secure_password';

-- Grant only necessary permissions
GRANT SELECT, SHOW VIEW ON your_database.* TO 'mcp_user'@'%';
GRANT SELECT ON information_schema.* TO 'mcp_user'@'%';

-- For administrative tasks (optional)
GRANT CREATE, DROP, ALTER ON your_database.* TO 'mcp_user'@'%';

FLUSH PRIVILEGES;

� Troubleshooting

Common Issues

1. Connection Refused

   • Verify database server is running
   • Check host and port configuration
   • Ensure firewall allows connections

2. Authentication Failed

   • Verify username and password
   • Check user permissions
   • Ensure user can connect from the client host

3. SSL Connection Issues

   • Verify SSL is enabled on the database server
   • Check SSL certificate configuration
   • Try disabling SSL for testing

4. Import Errors

   • Install required dependencies: pip install -r requirements.txt
   • Verify Python version compatibility

Debug Mode

Enable detailed logging by setting environment variable:

export LOG_LEVEL=DEBUG
python mysql_mcp_server.py

🤝 Contributing

We welcome contributions! Please see our for details.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

� License

This project is licensed under the MIT License - see the file for details.

🙏 Acknowledgments

• Model Context Protocol team for the excellent MCP framework
• MySQL and MariaDB communities
• All contributors who help improve this project

📞 Support

• 🐛 Bug Reports: Issues
• 💡 Feature Requests: Discussions
• 📖 Documentation: Wiki

Made with ❤️ for the MCP community

Option B: Copy and edit config file

cp config.example.py config.py

### 3. Run the Server
```bash
python mysql_mcp_server.py

4. Connect with MCP Client

Configure your MCP client with the provided mcp_config.example.json template.

📦 Installation

Prerequisites

• Python 3.8 or higher
• MariaDB 10.3+ or MySQL 5.7+
• MCP-compatible client (like Claude Desktop, Continue, or similar)

Install Dependencies

# Basic installation
pip install -r requirements.txt

# Development installation (includes testing tools)
pip install -r requirements-dev.txt

Verify Installation

# Test database connection
python -c "
import mysql.connector, os
conn = mysql.connector.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    port=int(os.getenv('DB_PORT', '3306')),
    user=os.getenv('DB_USER', 'root'),
    password=os.getenv('DB_PASSWORD', ''),
    database=os.getenv('DB_NAME', 'mysql')
)
print('✅ Database connection successful!')
conn.close()
"

⚙️ Configuration

Configuration Methods (Priority Order)

1. Environment Variables (Highest)
2. config.py file (Medium)
3. Default values (Lowest)

Environment Variables

Configuration File

Create a config.py file from the example:

# config.py
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'your_user',
    'password': 'your_password',
    'database': 'your_database',
    'autocommit': True
}

MCP_CONFIG = {
    'server_name': 'mysql-mcp-server',
    'server_version': '1.0.0',
    'description': 'MCP Server for MySQL/MariaDB'
}

MCP Client Configuration

Example configuration for MCP clients:

{
  "servers": {
    "mysql": {
      "command": "python",
      "args": ["mysql_mcp_server.py"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_user",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database"
      }
    }
  }
}

🛠️ Available Tools

1. execute_sql

Execute any SQL query on the database.

Parameters:

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

Example:

{
  "name": "execute_sql",
  "arguments": {
    "query": "SELECT * FROM users WHERE active = 1 LIMIT 5"
  }
}

2. show_tables

List all tables in the database.

Parameters: None

Usage: "Show me all tables in the database"

3. describe_table

Show the structure of a specific table.

Parameters:

• table_name (string, required): Name of the table to describe

Example:

{
  "name": "describe_table",
  "arguments": {
    "table_name": "users"
  }
}

4. table_data_sample

Get a sample of data from a table.

Parameters:

• table_name (string, required): Name of the table
• limit (integer, optional): Number of rows to return (default: 10)

Example:

{
  "name": "table_data_sample",
  "arguments": {
    "table_name": "products",
    "limit": 5
  }
}

📚 MCP Resources

The server provides structured resources accessible via MCP URIs:

1. mysql://{database}/tables

Returns a JSON list of all tables with metadata:

{
  "database": "myapp_db",
  "tables": ["users", "products", "orders"],
  "total_tables": 3
}

2. mysql://{database}/schema

Returns complete schema information:

{
  "database": "myapp_db",
  "tables": {
    "users": [
      {
        "Field": "id",
        "Type": "int(11)",
        "Null": "NO",
        "Key": "PRI",
        "Default": null,
        "Extra": "auto_increment"
      }
    ]
  }
}

💡 Examples

Natural Language Queries

With an MCP client, you can ask:

• "Show me all tables in the database"
• "What's the structure of the users table?"
• "Get me 5 sample records from the products table"
• "How many active users do we have?"
• "Show me recent orders from the last 7 days"

Direct SQL Execution

-- Basic queries
SELECT COUNT(*) FROM users WHERE status = 'active';
SELECT * FROM products WHERE price > 100 ORDER BY price DESC LIMIT 10;

-- Data analysis
SELECT 
    DATE(created_at) as date,
    COUNT(*) as orders_count,
    SUM(total_amount) as revenue
FROM orders 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Schema exploration
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE products;

🔧 Development

Project Structure

mysql-mariadb-mcp-server/
├── mysql_mcp_server.py      # Main server implementation
├── config.example.py        # Configuration template  
├── mcp_config.example.json  # MCP client config template
├── requirements.txt         # Python dependencies
├── requirements-dev.txt     # Development dependencies
├── test_mcp_server.py       # Test script
├── .gitignore              # Git ignore patterns
├── LICENSE                 # MIT license
└── README.md               # This file

Running Tests

# Test the MCP server functionality
python test_mcp_server.py

# Test database connection only
python -c "from mysql_mcp_server import DatabaseConnection; db = DatabaseConnection(); print('✅ Success' if db.connect() else '❌ Failed')"

Code Quality

# Install development dependencies
pip install -r requirements-dev.txt

# Format code
black mysql_mcp_server.py

# Lint code  
flake8 mysql_mcp_server.py

# Type checking
mypy mysql_mcp_server.py --ignore-missing-imports

🐛 Troubleshooting

Connection Issues

Error: Can't connect to MySQL server

• ✅ Verify database server is running
• ✅ Check host and port settings
• ✅ Confirm firewall allows connections

Error: Access denied for user

• ✅ Verify username and password
• ✅ Check user privileges:

  GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'user'@'host';
  FLUSH PRIVILEGES;
  

Error: Unknown database

• ✅ Ensure database exists: CREATE DATABASE your_database;
• ✅ Verify database name spelling

Module Issues

ImportError: No module named 'mysql.connector'

pip install mysql-connector-python

ImportError: No module named 'mcp'

pip install mcp

Server Issues

Server exits immediately

• ✅ Check logs for error messages
• ✅ Verify database connection parameters
• ✅ Ensure Python version 3.8+

Performance Issues

Slow query responses

• ✅ Add database indexes for frequently queried columns
• ✅ Use LIMIT clauses for large result sets
• ✅ Monitor database server performance

🤝 Contributing

Contributions are welcome! Please follow these steps:

1. Fork the repository
2. Create a feature branch: git checkout -b feature/amazing-feature
3. Make your changes with proper tests
4. Follow code style: Run black and flake8
5. Test thoroughly: Ensure all tests pass
6. Commit changes: git commit -m 'Add amazing feature'
7. Push to branch: git push origin feature/amazing-feature
8. Open a Pull Request

Development Guidelines

• Follow PEP 8 style guidelines
• Add docstrings to new functions
• Include tests for new features
• Update documentation as needed

📄 License

This project is licensed under the MIT License - see the file for details.

🆘 Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions
• Documentation: Check this README and code comments

⭐ Acknowledgments

• Model Context Protocol (MCP) team for the excellent framework
• MySQL and MariaDB communities for robust database systems
• Python community for amazing libraries and tools