{"id":"e2354a59-c122-400f-92f7-0248aab9fbc2","slug":"ttpears-mysql-mcp","kind":"mcp","name":"mysql-mcp","summary":"","owner":"ttpears","repoUrl":"https://github.com/ttpears/mysql-mcp","license":"MIT","licenseTier":"permissive","trust":"community","stars":1,"match":null,"fewStars":true,"format":"mcp","createdAt":"2025-08-26T15:29:00.000Z","addedAt":"2026-10-21T15:48:07.789Z","logoUrl":"https://avatars.githubusercontent.com/u/5926359?u=fb4513b337cce7a8fcf8779f1b65070a048fe013&v=4","linkOnly":false,"collection":null,"skillPath":null,"categories":["databases"],"tags":[],"runtime":"node","local":true,"cloudCapable":false,"cloudCapability":"unknown","cloudReadyOnPlatform":false,"needsApiKey":false,"reviewed":false,"syncedAt":"2026-10-26T04:03:09.203Z","sample":false,"url":"https://conduits.dev/tools/ttpears-mysql-mcp","language":"TypeScript","transports":["stdio"],"install":{"npm":"@ttpears/mysql-mcp"},"requirements":{"env":[],"upstreamMayCharge":false},"tools":null,"readme":"# MySQL MCP Server 🚀\n\nAn intelligent MySQL MCP Server with **enterprise data federation** capabilities, enabling LLMs to query and analyze data across multiple disparate MySQL data sources. Goes beyond basic querying to provide comprehensive business intelligence, cross-system data correlation, and federated analytics with high-performance caching.\n\n[![npm version](https://badge.fury.io/js/mysql-mcp-server.svg)](https://badge.fury.io/js/mysql-mcp-server)\n[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)\n[![Smithery Compatible](https://img.shields.io/badge/Smithery-Compatible-blue.svg)](https://smithery.ai)\n\n## ✨ Features\n\n### 🏢 **Enterprise Data Federation**\n- **🌐 Multi-source MySQL access**: Connect to completely different business systems (CRM, ERP, E-commerce, etc.)\n- **🧠 Intelligent source classification**: Automatically identify and catalog CRM, E-commerce, ERP, Marketing, Support systems\n- **🔗 Cross-source query engine**: Execute federated queries combining data from multiple business systems\n- **📊 360° business intelligence**: Complete customer view across all touchpoints and systems\n- **🔍 Relationship detection**: Find connections between disparate systems (shared customer IDs, etc.)\n\n### 📈 **Advanced Analytics & Intelligence**\n- **👥 User behavior federation**: Track customers across CRM → Marketing → E-commerce → Support journey\n- **💼 Business process analytics**: End-to-end analysis from procurement → inventory → sales → finance\n- **🎯 Revenue attribution**: Multi-touch attribution across marketing, sales, and customer systems\n- **🔄 Data consistency auditing**: Compare and validate data integrity across business systems\n- **📈 Cross-system KPI reporting**: Unified executive dashboards combining all business data\n\n### ⚡ **Performance & Enterprise Features**\n- **🚀 High-performance caching**: Source-specific intelligent caching with TTL support\n- **🔒 Enterprise security**: Read-only access with query validation and connection timeouts\n- **🏗️ Architecture detection**: Automatic identification of star schemas and data warehouse patterns\n- **🔧 Connection pooling**: Efficient connection management across multiple business systems\n- **🚀 Smithery deployment**: Easy deployment with configuration UI on Smithery.ai\n- **MySQL 5.5+ compatible**: Works with MySQL 5.5 and later versions\n\n## Quick Start\n\n### Option 1: Direct Install from GitHub\n```bash\n# Clone and install\ngit clone https://github.com/ttpears/mcp-mysql.git\ncd mcp-mysql\nnpm install && npm run build\n\n# Add to Claude Code with your MySQL credentials\n# For server-wide access (recommended):\nclaude mcp add mysql-server npm start \\\n  -e MYSQL_HOST=localhost \\\n  -e MYSQL_USER=your_user \\\n  -e MYSQL_PASSWORD=your_password\n\n# For single database access:\nclaude mcp add mysql-server npm start \\\n  -e MYSQL_HOST=localhost \\\n  -e MYSQL_USER=your_user \\\n  -e MYSQL_PASSWORD=your_password \\\n  -e MYSQL_DATABASE=your_database\n```\n\n### Option 2: Deploy on Smithery.ai (Recommended)\nVisit [Smithery.ai MySQL MCP Server](https://smithery.ai/server/@ttpears/mysql-mcp-server) and click \"Deploy\" for easy setup with configuration UI.\n\nFeatures when deployed on Smithery:\n- 🎛️ **Configuration UI**: Easy setup with form-based configuration\n- 🔄 **Auto-updates**: Automatic updates when new versions are released\n- ⚡ **High performance**: Optimized deployment with caching enabled\n- 🔒 **Secure**: Environment-based secret management\n\n### Option 3: Manual Installation\n```bash\nnpm install mysql-mcp-server\n```\n\n## Configuration\n\n### Environment Variables\n\n#### Default Data Source\n| Variable | Default | Description |\n|----------|---------|-------------|\n| `MYSQL_HOST` | `localhost` | MySQL server hostname (default source) |\n| `MYSQL_PORT` | `3306` | MySQL server port (default source) |\n| `MYSQL_USER` | `root` | MySQL username (default source) |\n| `MYSQL_PASSWORD` | `` | MySQL password (required for default source) |\n| `MYSQL_DATABASE` | _(none)_ | MySQL database name (optional - server-wide access if omitted) |\n| `MYSQL_SSL` | `false` | Enable SSL connection (default source) |\n\n#### Multi-Source Data Federation\nFor additional business system data sources, use pattern: `MYSQL_HOST_<SOURCE>_*`\n\n| Pattern | Example | Description |\n|---------|---------|-------------|\n| `MYSQL_HOST_<SOURCE>_HOST` | `MYSQL_HOST_CRM_HOST` | Hostname for the data source |\n| `MYSQL_HOST_<SOURCE>_PORT` | `MYSQL_HOST_CRM_PORT` | Port for the data source (default: 3306) |\n| `MYSQL_HOST_<SOURCE>_USER` | `MYSQL_HOST_CRM_USER` | Username for the data source (default: root) |\n| `MYSQL_HOST_<SOURCE>_PASSWORD` | `MYSQL_HOST_CRM_PASSWORD` | Password for the data source (required) |\n| `MYSQL_HOST_<SOURCE>_DATABASE` | `MYSQL_HOST_CRM_DATABASE` | Database name for the data source (optional) |\n| `MYSQL_HOST_<SOURCE>_SSL` | `MYSQL_HOST_CRM_SSL` | Enable SSL for the data source (default: false) |\n\n**Note**: Each additional data source requires at least `MYSQL_HOST_<SOURCE>_HOST` and `MYSQL_HOST_<SOURCE>_PASSWORD` to be configured.\n\n### Caching Configuration\n\n| Variable | Default | Description |\n|----------|---------|-------------|\n| `MCP_MYSQL_CACHE_ENABLED` | `true` | Enable/disable intelligent caching system |\n| `MCP_MYSQL_CACHE_DIR` | `~/.mcp-mysql-cache` | Custom cache directory path |\n| `MCP_MYSQL_CACHE_MAX_SIZE` | `52428800` | Maximum cache file size in bytes (50MB) |\n| `MCP_MYSQL_CACHE_RETENTION_DAYS` | `30` | Delete JSON cache files older than this at startup |\n\n### Configuration Examples\n\n**Enterprise data federation setup:**\n```bash\n# Default data source (backward compatible)\nexport MYSQL_HOST=localhost\nexport MYSQL_PORT=3306\nexport MYSQL_USER=analytics_user\nexport MYSQL_PASSWORD=your_password\nexport MYSQL_SSL=true\nexport MCP_MYSQL_CACHE_ENABLED=true\n\n# CRM System (Salesforce MySQL backend)\nexport MYSQL_HOST_CRM_HOST=crm-db.company.com\nexport MYSQL_HOST_CRM_USER=crm_readonly\nexport MYSQL_HOST_CRM_PASSWORD=crm_secret\nexport MYSQL_HOST_CRM_DATABASE=salesforce_sync\nexport MYSQL_HOST_CRM_SSL=true\n\n# E-commerce Platform (Shopify/WooCommerce)\nexport MYSQL_HOST_ECOMMERCE_HOST=shop-db.company.com\nexport MYSQL_HOST_ECOMMERCE_USER=ecommerce_analytics\nexport MYSQL_HOST_ECOMMERCE_PASSWORD=shop_secret\nexport MYSQL_HOST_ECOMMERCE_DATABASE=store_analytics\n\n# ERP System (SAP/Oracle MySQL interface)\nexport MYSQL_HOST_ERP_HOST=erp-mysql.company.com\nexport MYSQL_HOST_ERP_USER=erp_reporting\nexport MYSQL_HOST_ERP_PASSWORD=erp_secret\n\n# Marketing & Support Systems\nexport MYSQL_HOST_MARKETING_HOST=marketing-db.company.com\nexport MYSQL_HOST_MARKETING_USER=marketing_readonly\nexport MYSQL_HOST_MARKETING_PASSWORD=marketing_secret\n\nexport MYSQL_HOST_SUPPORT_HOST=support-db.company.com\nexport MYSQL_HOST_SUPPORT_USER=support_analytics\nexport MYSQL_HOST_SUPPORT_PASSWORD=support_secret\n```\n\n**Single database access (legacy mode):**\n```bash\nexport MYSQL_HOST=localhost\nexport MYSQL_PORT=3306\nexport MYSQL_USER=readonly_user\nexport MYSQL_PASSWORD=your_password\nexport MYSQL_DATABASE=your_database\nexport MYSQL_SSL=true\n```\n\n**Custom caching configuration:**\n```bash\nexport MCP_MYSQL_CACHE_ENABLED=true\nexport MCP_MYSQL_CACHE_DIR=/custom/cache/path\nexport MCP_MYSQL_CACHE_MAX_SIZE=104857600  # 100MB\nexport MCP_MYSQL_CACHE_RETENTION_DAYS=7    # 1 week\n```\n\n## Usage\n\n### Running the Server\n\n```bash\nnpm start\n```\n\n### Available Tools\n\n#### `mysql_inventory`\n**🏢 START HERE** - Get comprehensive inventory of all configured business systems and their databases.\n\n**Parameters:**\n- `refresh` (boolean, optional): Force refresh of data source inventory\n- `host` (string, optional): Get inventory for specific data source only\n\n**Examples:**\n```json\n// Get complete enterprise data source inventory\n{}\n\n// Refresh and get inventory for specific source\n{\n  \"host\": \"crm\",\n  \"refresh\": true\n}\n```\n\n**What you get:**\n- 🏢 Business system classification (CRM, E-commerce, ERP, Marketing, Support, etc.)\n- 📊 Database inventory with record counts and connection health\n- 🔗 Federation capabilities and shared identifiers across systems\n- 💡 Cross-system analysis recommendations\n\n#### `mysql_cross_host_query`\n**🚀 ENTERPRISE POWER TOOL** - Execute federated queries across multiple business systems.\n\n**Parameters:**\n- `queries` (array, required): Array of queries to execute across different data sources\n  - `host` (string, required): Data source name to execute query on\n  - `database` (string, optional): Database name within the data source\n  - `query` (string, required): SQL query to execute\n  - `alias` (string, optional): Alias for this query result\n- `combine_strategy` (string, optional): How to combine results ('separate', 'union', 'comparison', 'correlation')\n- `analysis_focus` (string, optional): Focus area ('performance', 'data_consistency', 'user_behavior', 'business_metrics')\n\n**Example - 360° Customer Intelligence:**\n```json\n{\n  \"queries\": [\n    {\n      \"host\": \"crm\",\n      \"query\": \"SELECT customer_id, email, lead_source FROM customers WHERE status = 'active'\",\n      \"alias\": \"crm_customers\"\n    },\n    {\n      \"host\": \"ecommerce\",\n      \"query\": \"SELECT customer_email as email, SUM(order_total) as lifetime_value FROM orders GROUP BY customer_email\",\n      \"alias\": \"purchase_history\"\n    },\n    {\n      \"host\": \"support\",\n      \"query\": \"SELECT requester_email as email, COUNT(*) as ticket_count FROM tickets GROUP BY requester_email\",\n      \"alias\": \"support_interactions\"\n    }\n  ],\n  \"combine_strategy\": \"correlation\",\n  \"analysis_focus\": \"user_behavior\"\n}\n```\n\n#### `mysql_query`\nExecute read-only SQL queries against specific data sources.\n\n**Parameters:**\n- `query` (string, required): SQL query to execute\n- `params` (array, optional): Parameters for prepared statements\n- `database` (string, optional): Database name to connect to\n- `host` (string, optional): Data source name (defaults to 'default')\n\n**Examples:**\n```json\n// Query default data source\n{\n  \"query\": \"SELECT * FROM users WHERE status = ?\",\n  \"params\": [\"active\"]\n}\n\n// Query specific business system\n{\n  \"host\": \"crm\",\n  \"database\": \"salesforce_sync\",\n  \"query\": \"SELECT COUNT(*) as total_leads FROM leads WHERE created_date >= '2024-01-01'\"\n}\n```\n\n#### `mysql_schema`\nGet comprehensive schema information including relationships, constraints, and data patterns.\n\n**Parameters:**\n- `table` (string, optional): Specific table name to analyze\n- `include_relationships` (boolean, default: true): Include foreign key relationships\n- `include_sample_data` (boolean, default: false): Include sample data and statistics\n- `sample_size` (number, default: 100, max: 1000): Number of sample rows to analyze\n\n**Examples:**\n```json\n// Get database overview with relationships\n{}\n\n// Get detailed table analysis with sample data\n{\n  \"table\": \"users\",\n  \"include_sample_data\": true,\n  \"sample_size\": 50\n}\n\n// Get basic table schema without relationships\n{\n  \"table\": \"orders\",\n  \"include_relationships\": false\n}\n```\n\n#### `mysql_analyze_tables`\nAnalyze table relationships and suggest optimal query patterns for user behavior analysis.\n\n**Parameters:**\n- `tables` (array, required): List of table names to analyze\n- `analysis_type` (string, optional): Type of analysis ('relationships', 'user_behavior', 'data_flow')\n\n**Examples:**\n```json\n// Analyze table relationships\n{\n  \"tables\": [\"users\", \"orders\", \"products\"],\n  \"analysis_type\": \"relationships\"\n}\n\n// Analyze for user behavior patterns\n{\n  \"tables\": [\"users\", \"events\", \"sessions\"],\n  \"analysis_type\": \"user_behavior\"\n}\n\n// Trace data flow between tables\n{\n  \"tables\": [\"users\", \"orders\", \"order_items\"],\n  \"analysis_type\": \"data_flow\"\n}\n```\n\n#### `mysql_discover_analytics`\n**🚀 START HERE** - Intelligently discover and analyze your entire MySQL server with expert data analytics insights and intelligent caching.\n\n**Parameters:**\n- `databases` (array, optional): List of databases to analyze (if not specified, discovers all accessible databases)  \n- `focus_area` (string, optional): Analytics focus ('user_behavior', 'sales_analytics', 'engagement', 'general')\n- `include_recommendations` (boolean, default: true): Include expert query recommendations\n- `cross_database_analysis` (boolean, default: true): Analyze relationships across databases\n- `detail_level` (string, optional): Level of analysis ('summary', 'detailed', 'full')\n- `max_tables_per_db` (number, default: 20): Maximum tables to analyze per database\n- `page` (number, default: 1): Page number for pagination\n- `page_size` (number, default: 5): Number of databases per page\n\n**Examples:**\n```json\n// Discover entire MySQL server for user behavior analysis\n{\n  \"focus_area\": \"user_behavior\",\n  \"cross_database_analysis\": true\n}\n\n// Analyze specific databases only\n{\n  \"databases\": [\"ecommerce\", \"analytics\", \"logs\"],\n  \"focus_area\": \"sales_analytics\"\n}\n\n// Quick server overview\n{\n  \"focus_area\": \"general\"\n}\n```\n\n**What you get:**\n- 🌐 Complete multi-database server analysis with all executed SQL queries shown\n- 🧠 Intelligent table classification across all databases (fact tables, dimension tables, user tables, event tables)\n- 🔄 Cross-database relationship mapping and pattern detection\n- 📊 Expert analytics insights and performance recommendations per database\n- 📈 Ready-to-use SQL queries for cross-database analytics patterns\n- 🔍 Data quality assessments and optimization suggestions\n- 🏗️ Architecture analysis (star schema detection, data warehouse patterns)\n- ⚡ **High-performance caching**: All results cached with TTL for faster subsequent queries\n- 📄 **Pagination support**: Handle large servers with configurable page sizes\n\n## 🚀 Intelligent Caching System\n\nThe server includes a comprehensive caching system that dramatically improves performance for repeated operations:\n\n### Cache Types & TTL\n- **Query Results**: 1-hour TTL - `~/.mcp-mysql-cache/queries/[date]/`\n- **Schema Analysis**: 1-2 hour TTL - `~/.mcp-mysql-cache/schema-snapshots/`\n- **Discovery Reports**: 4-hour TTL - `~/.mcp-mysql-cache/reports/discovery-reports/`\n- **Relationship Analysis**: Cached - `~/.mcp-mysql-cache/reports/relationship-analysis/`\n\n### Cache Features\n- **Automatic TTL management**: Expired cache automatically detected and refreshed\n- **Enforced retention**: JSON cache files older than `MCP_MYSQL_CACHE_RETENTION_DAYS` are deleted at startup\n- **Private cache files**: Cache directories use mode `0700` and result files use mode `0600` on POSIX systems\n- **Intelligent key generation**: Complex queries cached with hash-based keys\n- **Size limits**: Configurable maximum file size (default: 50MB)\n- **Storage organization**: Organized by date and operation type\n- **Cache info**: All responses include cache hit/miss information\n- **Cross-session**: Cache persists across server restarts\n\n### Cache Benefits\n- **Faster repeated queries**: Instant responses for cached operations\n- **Reduced database load**: Less strain on your MySQL server\n- **Improved UX**: Near-instantaneous results for complex analytics\n- **Development efficiency**: Quick iterations during data exploration\n\n## Security Features\n\n- **Query Validation**: Only read-only operations allowed (SELECT, SHOW, DESCRIBE, EXPLAIN)\n- **Length Limits**: Queries limited to 10,000 characters\n- **Prepared Statements**: Parameterized queries to prevent SQL injection\n- **Connection Timeouts**: Configurable timeouts to prevent hanging connections\n- **Error Handling**: Comprehensive error catching and logging\n\n## Database User Setup\n\nFor security, create a dedicated read-only MySQL user:\n\n```sql\n-- Create read-only user\nCREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';\n\n-- Grant SELECT privileges\nGRANT SELECT ON your_database.* TO 'readonly_user'@'%';\n\n-- Grant SHOW privileges for schema inspection\nGRANT SHOW VIEW ON your_database.* TO 'readonly_user'@'%';\n\n-- Flush privileges\nFLUSH PRIVILEGES;\n```\n\n## Development\n\n```bash\n# Install dependencies\nnpm install\n\n# Run in development mode\nnpm run dev\n\n# Build for production\nnpm run build\n```\n\n## Compatibility\n\n- MySQL 5.5+\n- MariaDB 10.0+\n- Node.js 18+\n\n## Error Handling\n\nThe server provides detailed error messages for:\n- Connection failures\n- Invalid queries\n- Query execution errors\n- Parameter validation errors\n\nAll errors are logged to stderr while maintaining MCP protocol compliance.\n\n## Deployment\n\n### GitHub\n```bash\ngit add .\ngit commit -m \"Initial release of intelligent MySQL MCP Server\"\ngit push origin main\n```\n\n### Smithery.ai Registry\n1. Ensure `mcp.json` is properly configured\n2. Push to GitHub repository\n3. Submit to [Smithery.ai MCP Registry](https://smithery.ai)\n4. Include the `mcp.json` file for automatic discovery\n\n## Contributing\n\n1. Fork the repository\n2. Create a feature branch (`git checkout -b feature/amazing-feature`)\n3. Commit your changes (`git commit -m 'Add amazing feature'`)\n4. Push to the branch (`git push origin feature/amazing-feature`)\n5. Open a Pull Request\n\n## License\n\nThis project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.\n\n## Support\n\n- 🐛 **Issues**: [GitHub Issues](https://github.com/ttpears/mysql-mcp/issues)\n- 💬 **Discussions**: [GitHub Discussions](https://github.com/ttpears/mysql-mcp/discussions)\n- 📚 **Documentation**: [Full Documentation](https://github.com/ttpears/mysql-mcp#readme)\n- 🔎 **Security field notes**: [Why read-only query validation is not the complete boundary](https://hackyourworld.com/mysql-mcp-read-only-security-boundaries/)\n"}