# 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:
   ```bash
   npm install
   ```

3. Copy the environment configuration:
   ```bash
   cp .env.example .env
   ```

4. Configure your MySQL connection in `.env`:
   ```env
   MYSQL_HOST=localhost
   MYSQL_PORT=3306
   MYSQL_USER=your_username
   MYSQL_PASSWORD=your_password
   MYSQL_DATABASE=your_database
   ```

## Usage

### Running the Server

```bash
# 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`

```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:

```sql
-- 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:**
   ```bash
   # Linux/macOS
   sudo systemctl status mysql
   
   # Windows (if using MySQL service)
   net start mysql
   ```

2. **Verify connection parameters in `.env`**

3. **Check MySQL user permissions:**
   ```sql
   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