KatCoder MySQL MCP Server

# KatCoder MySQL MCP Server - Project Summary ## Overview **KatCoder MySQL MCP Server** is a secure and feature-rich MySQL Model Context Protocol (MCP) server that enables AI agents and applications to interact with MySQL databases through a standardized interface. Built with TypeScript and Node.js, this project provides a comprehensive set of database operations while prioritizing security and ease of use. ## Key Features ### 🔒 Security-First Architecture - **SQL Injection Prevention**: Comprehensive input validation and parameterized queries - **Identifier Validation**: Strict validation of table and column names - **Layered Security Model**: Tool-level and database-level permission controls - **Flexible Tool Permissions**: Support for "all" tools or granular tool selection - **Connection Pooling**: Secure connection management with timeout controls - **Error Handling**: Secure error messages that don't expose sensitive information ### 🛠️ Comprehensive Database Operations - **Data Operations**: List, read, create, update, delete records - **Bulk Operations**: Efficient bulk insert for batch data imports - **Schema Management**: Add/drop/modify columns, rename tables/columns - **Index Management**: Create and drop various types of indexes (BTREE, HASH, FULLTEXT, SPATIAL) - **Advanced Operations**: Custom SQL execution, DDL statements, atomic transactions - **Utility Functions**: Database health checks, version info, statistics ### 🔧 Developer Experience - **CLI Interface**: Command-line tool for direct database interaction - **MCP Protocol**: Standardized Model Context Protocol for AI agent integration - **Flexible Configuration**: Environment variables and connection string support - **Comprehensive Logging**: Winston-based logging with multiple levels - **Smart Tool Selection**: Recommended "all" tools approach with database-level security - **AI Agent Optimized**: Configuration examples for Claude Desktop and Cursor IDE ## Technical Architecture ### Core Components - **MySQLMCPImplementation**: Main server class extending MCP framework - **DatabaseManager**: Database connection and query execution layer - **Security Utilities**: Input validation and SQL injection prevention - **Schema Validation**: Comprehensive type and constraint checking ### Dependencies - **@modelcontextprotocol/sdk**: MCP protocol implementation - **mysql2**: High-performance MySQL driver - **zod**: Schema validation and data parsing - **winston**: Logging framework - **commander**: CLI interface handling ### File Structure ``` src/ ├── index.ts # Main exports ├── implementation.ts # Core MCP implementation ├── database.ts # Database connection management ├── server.ts # MCP server base class ├── cli.ts # Command-line interface └── types.ts # Type definitions ``` ## Tool Permissions & Security Model ### 🎯 Recommended Approach: "All" Tools + Database Permissions - **Primary Strategy**: Enable all tools using `"all"` parameter for maximum AI agent compatibility - **Security Boundary**: Use MySQL user permissions to control actual database access - **Benefits**: Future-proof, simplified configuration, full AI agent functionality - **Best Practice**: Create dedicated database users with appropriate privileges ### 🔒 Granular Tool Selection (When Needed) - **Read-Only Access**: `"list,read,utility"` for analytics and reporting - **Data Operations**: `"list,read,create,update,delete,utility"` for applications without schema changes - **Full Access**: `"all"` for development and administrative tasks - **Security-First**: Manual tool selection for highly restricted environments ## Usage Scenarios ### AI Agent Integration - Connect AI assistants to MySQL databases for data querying and manipulation - Enable natural language database interactions with full tool visibility - Support for schema exploration and data analysis - Optimized configurations for Claude Desktop and Cursor IDE ### Database Management - Command-line database administration with comprehensive DDL support - Schema migration and modification using dedicated tools - Bulk data operations with efficient batch processing - Performance monitoring and optimization with utility tools ### Application Development - Backend service for database operations with flexible tool selection - Microservice architecture component with security-first design - Data migration and ETL processes with transaction support ## Security Highlights 1. **Layered Security Model**: Tool-level restrictions combined with database-level permissions 2. **Input Sanitization**: All identifiers are validated and sanitized 3. **Parameter Binding**: All queries use parameterized statements 4. **Flexible Access Control**: Support for both "all" tools and granular tool selection 5. **Database User Isolation**: Dedicated MySQL users with minimal required privileges 6. **Schema Safety**: Prevent modifications to system tables and unauthorized operations 7. **Connection Security**: Pool management with timeout controls and secure error handling ## Project Status - **Version**: 1.0.0 (Initial Release) - **License**: MIT - **Status**: Active development - **Compatibility**: Node.js 16+, MySQL 5.7+ ## Quick Start ```bash # Install dependencies npm install # Build the project npm run build # Run CLI with all tools enabled (recommended) node dist/cli.js "mysql://user:password@localhost:3306/database_name" "all" # Or with specific tools for restricted access node dist/cli.js "mysql://user:password@localhost:3306/database_name" "list,read,utility" # For development npm run dev ``` ## Configuration Supports standard MySQL connection strings: ``` mysql://[user[:password]@]host[:port]/database ``` Example configurations for AI agents (Claude Desktop, Cursor IDE) are provided in the documentation. ## Contributing This project follows standard open-source practices: 1. Fork the repository 2. Create feature branch 3. Make changes with tests 4. Submit pull request ## Documentation Comprehensive documentation includes: - **Tool Permissions Guide**: When to use "all" tools vs manual selection - **Security Best Practices**: Layered security model with database-level permissions - **AI Agent Configuration**: Optimized setups for Claude Desktop and Cursor IDE - **Detailed API Reference**: Complete documentation for all 15+ available tools - **Quick Reference Tables**: Easy tool selection based on use case - **Troubleshooting Guide**: Common issues and solutions - **Advanced Configuration**: Environment variables and connection options - **Usage Examples**: Practical scenarios and implementation patterns --- *This project bridges the gap between AI agents and MySQL databases, providing a secure, standardized interface for database operations while maintaining enterprise-grade security practices.*