mysql-mcp

MySQL MCP Server

A Model Context Protocol (MCP) server that provides MySQL database query capabilities. This server allows AI assistants to interact with MySQL databases through a standardized interface.

Features

• Execute SQL queries with prepared statement support
• List databases and tables
• Describe table structures
• Secure connection management with environment variables
• Error handling and connection recovery
• Support for all MySQL data types

Installation

1. Clone or download this project
2. Install dependencies:

   npm install

1. Copy the environment configuration:

   cp .env.example .env

1. Configure your MySQL connection in .env:

   MYSQL_HOST=localhost
   MYSQL_PORT=3306
   MYSQL_USER=your_username
   MYSQL_PASSWORD=your_password
   MYSQL_DATABASE=your_database

Usage

Running the Server

# Start the server
npm start

# Development mode with auto-restart
npm run dev

Available Tools

1. mysql_query

Execute SQL queries with optional prepared statement parameters.

Parameters:

• query (required): The SQL query to execute
• parameters (optional): Array of parameters for prepared statements

Examples: ```sql -- Simple SELECT SELECT * FROM users LIMIT 10

-- Prepared statement with parameters SELECT * FROM users WHERE age > ? AND city = ? -- Parameters: ["25", "New York"]

-- INSERT with prepared statement INSERT INTO users (name, email, age) VALUES (?, ?, ?) -- Parameters: ["John Doe", "john@example.com", "30"] ```

2. mysql_describe_table

Get the structure of a specific table.

Parameters:

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

3. mysql_list_tables

List all tables in the current database.

Parameters: None

4. mysql_list_databases

List all databases accessible to the current user.

Parameters: None

Configuration with MCP Clients

Claude Desktop

Add this server to your Claude Desktop configuration file:

Windows: %APPDATA%\Claude\claude_desktop_config.json macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["path/to/mysql-mcp/server.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "your_database"
      }
    }
  }
}

Other MCP Clients

This server follows the standard MCP protocol and should work with any compatible client. Refer to your client's documentation for configuration details.

Security Considerations

1. Environment Variables: Never commit your .env file with actual credentials
2. Database Permissions: Use a database user with minimal required permissions
3. Network Security: Ensure your MySQL server is properly secured
4. Query Validation: The server executes queries as provided - ensure your AI assistant is configured appropriately

Database Permissions

For security, create a dedicated MySQL user for the MCP server:

-- Create a dedicated user
CREATE USER 'mcp_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant necessary permissions (adjust as needed)
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'mcp_user'@'localhost';

-- For read-only access, use only:
-- GRANT SELECT ON your_database.* TO 'mcp_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Troubleshooting

Connection Issues

1. Check MySQL server status:

   # Linux/macOS
   sudo systemctl status mysql
   
   # Windows (if using MySQL service)
   net start mysql

1. Verify connection parameters in .env

1. Check MySQL user permissions:

   SHOW GRANTS FOR 'your_username'@'localhost';

Common Errors

• "Access denied": Check username/password in .env
• "Unknown database": Verify database name exists
• "Connection refused": Check if MySQL server is running and host/port are correct

Development

Project Structure

mysql-mcp/
├── server.js          # Main MCP server implementation
├── package.json       # Node.js dependencies and scripts
├── .env.example       # Environment configuration template
├── .env               # Your local configuration (not in git)
├── README.md          # This file
└── .gitignore         # Git ignore rules

Adding New Tools

To add new database tools, extend the setupToolHandlers() method in server.js:

1. Add the tool definition in ListToolsRequestSchema handler
2. Add the case in CallToolRequestSchema handler
3. Implement the handler method

License

ISC License

Contributing

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

Support

For issues and questions:

1. Check the troubleshooting section
2. Review MySQL and MCP documentation
3. Create an issue in the project repository