@ttpears/mysql-mcp
Advanced tools
Expert MySQL MCP Server with intelligent analytics and user behavior analysis capabilities
An 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.
# Clone and install
git clone https://github.com/ttpears/mcp-mysql.git
cd mcp-mysql
npm install && npm run build
# Add to Claude Code with your MySQL credentials
# For server-wide access (recommended):
claude mcp add mysql-server npm start \
-e MYSQL_HOST=localhost \
-e MYSQL_USER=your_user \
-e MYSQL_PASSWORD=your_password
# For single database access:
claude mcp add mysql-server npm start \
-e MYSQL_HOST=localhost \
-e MYSQL_USER=your_user \
-e MYSQL_PASSWORD=your_password \
-e MYSQL_DATABASE=your_database
Visit Smithery.ai MySQL MCP Server and click "Deploy" for easy setup with configuration UI.
Features when deployed on Smithery:
npm install mysql-mcp-server
| Variable | Default | Description |
|---|---|---|
MYSQL_HOST | localhost | MySQL server hostname (default source) |
MYSQL_PORT | 3306 | MySQL server port (default source) |
MYSQL_USER | root | MySQL username (default source) |
MYSQL_PASSWORD | `` | MySQL password (required for default source) |
MYSQL_DATABASE | (none) | MySQL database name (optional - server-wide access if omitted) |
MYSQL_SSL | false | Enable SSL connection (default source) |
For additional business system data sources, use pattern: MYSQL_HOST_<SOURCE>_*
| Pattern | Example | Description |
|---|---|---|
MYSQL_HOST_<SOURCE>_HOST | MYSQL_HOST_CRM_HOST | Hostname for the data source |
MYSQL_HOST_<SOURCE>_PORT | MYSQL_HOST_CRM_PORT | Port for the data source (default: 3306) |
MYSQL_HOST_<SOURCE>_USER | MYSQL_HOST_CRM_USER | Username for the data source (default: root) |
MYSQL_HOST_<SOURCE>_PASSWORD | MYSQL_HOST_CRM_PASSWORD | Password for the data source (required) |
MYSQL_HOST_<SOURCE>_DATABASE | MYSQL_HOST_CRM_DATABASE | Database name for the data source (optional) |
MYSQL_HOST_<SOURCE>_SSL | MYSQL_HOST_CRM_SSL | Enable SSL for the data source (default: false) |
Note: Each additional data source requires at least MYSQL_HOST_<SOURCE>_HOST and MYSQL_HOST_<SOURCE>_PASSWORD to be configured.
| Variable | Default | Description |
|---|---|---|
MCP_MYSQL_CACHE_ENABLED | true | Enable/disable intelligent caching system |
MCP_MYSQL_CACHE_DIR | ~/.mcp-mysql-cache | Custom cache directory path |
MCP_MYSQL_CACHE_MAX_SIZE | 52428800 | Maximum cache file size in bytes (50MB) |
MCP_MYSQL_CACHE_RETENTION_DAYS | 30 | Delete JSON cache files older than this at startup |
Enterprise data federation setup:
# Default data source (backward compatible)
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=analytics_user
export MYSQL_PASSWORD=your_password
export MYSQL_SSL=true
export MCP_MYSQL_CACHE_ENABLED=true
# CRM System (Salesforce MySQL backend)
export MYSQL_HOST_CRM_HOST=crm-db.company.com
export MYSQL_HOST_CRM_USER=crm_readonly
export MYSQL_HOST_CRM_PASSWORD=crm_secret
export MYSQL_HOST_CRM_DATABASE=salesforce_sync
export MYSQL_HOST_CRM_SSL=true
# E-commerce Platform (Shopify/WooCommerce)
export MYSQL_HOST_ECOMMERCE_HOST=shop-db.company.com
export MYSQL_HOST_ECOMMERCE_USER=ecommerce_analytics
export MYSQL_HOST_ECOMMERCE_PASSWORD=shop_secret
export MYSQL_HOST_ECOMMERCE_DATABASE=store_analytics
# ERP System (SAP/Oracle MySQL interface)
export MYSQL_HOST_ERP_HOST=erp-mysql.company.com
export MYSQL_HOST_ERP_USER=erp_reporting
export MYSQL_HOST_ERP_PASSWORD=erp_secret
# Marketing & Support Systems
export MYSQL_HOST_MARKETING_HOST=marketing-db.company.com
export MYSQL_HOST_MARKETING_USER=marketing_readonly
export MYSQL_HOST_MARKETING_PASSWORD=marketing_secret
export MYSQL_HOST_SUPPORT_HOST=support-db.company.com
export MYSQL_HOST_SUPPORT_USER=support_analytics
export MYSQL_HOST_SUPPORT_PASSWORD=support_secret
Single database access (legacy mode):
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=readonly_user
export MYSQL_PASSWORD=your_password
export MYSQL_DATABASE=your_database
export MYSQL_SSL=true
Custom caching configuration:
export MCP_MYSQL_CACHE_ENABLED=true
export MCP_MYSQL_CACHE_DIR=/custom/cache/path
export MCP_MYSQL_CACHE_MAX_SIZE=104857600 # 100MB
export MCP_MYSQL_CACHE_RETENTION_DAYS=7 # 1 week
npm start
mysql_inventory🏢 START HERE - Get comprehensive inventory of all configured business systems and their databases.
Parameters:
refresh (boolean, optional): Force refresh of data source inventoryhost (string, optional): Get inventory for specific data source onlyExamples:
// Get complete enterprise data source inventory
{}
// Refresh and get inventory for specific source
{
"host": "crm",
"refresh": true
}
What you get:
mysql_cross_host_query🚀 ENTERPRISE POWER TOOL - Execute federated queries across multiple business systems.
Parameters:
queries (array, required): Array of queries to execute across different data sources
host (string, required): Data source name to execute query ondatabase (string, optional): Database name within the data sourcequery (string, required): SQL query to executealias (string, optional): Alias for this query resultcombine_strategy (string, optional): How to combine results ('separate', 'union', 'comparison', 'correlation')analysis_focus (string, optional): Focus area ('performance', 'data_consistency', 'user_behavior', 'business_metrics')Example - 360° Customer Intelligence:
{
"queries": [
{
"host": "crm",
"query": "SELECT customer_id, email, lead_source FROM customers WHERE status = 'active'",
"alias": "crm_customers"
},
{
"host": "ecommerce",
"query": "SELECT customer_email as email, SUM(order_total) as lifetime_value FROM orders GROUP BY customer_email",
"alias": "purchase_history"
},
{
"host": "support",
"query": "SELECT requester_email as email, COUNT(*) as ticket_count FROM tickets GROUP BY requester_email",
"alias": "support_interactions"
}
],
"combine_strategy": "correlation",
"analysis_focus": "user_behavior"
}
mysql_queryExecute read-only SQL queries against specific data sources.
Parameters:
query (string, required): SQL query to executeparams (array, optional): Parameters for prepared statementsdatabase (string, optional): Database name to connect tohost (string, optional): Data source name (defaults to 'default')Examples:
// Query default data source
{
"query": "SELECT * FROM users WHERE status = ?",
"params": ["active"]
}
// Query specific business system
{
"host": "crm",
"database": "salesforce_sync",
"query": "SELECT COUNT(*) as total_leads FROM leads WHERE created_date >= '2024-01-01'"
}
mysql_schemaGet comprehensive schema information including relationships, constraints, and data patterns.
Parameters:
table (string, optional): Specific table name to analyzeinclude_relationships (boolean, default: true): Include foreign key relationshipsinclude_sample_data (boolean, default: false): Include sample data and statisticssample_size (number, default: 100, max: 1000): Number of sample rows to analyzeExamples:
// Get database overview with relationships
{}
// Get detailed table analysis with sample data
{
"table": "users",
"include_sample_data": true,
"sample_size": 50
}
// Get basic table schema without relationships
{
"table": "orders",
"include_relationships": false
}
mysql_analyze_tablesAnalyze table relationships and suggest optimal query patterns for user behavior analysis.
Parameters:
tables (array, required): List of table names to analyzeanalysis_type (string, optional): Type of analysis ('relationships', 'user_behavior', 'data_flow')Examples:
// Analyze table relationships
{
"tables": ["users", "orders", "products"],
"analysis_type": "relationships"
}
// Analyze for user behavior patterns
{
"tables": ["users", "events", "sessions"],
"analysis_type": "user_behavior"
}
// Trace data flow between tables
{
"tables": ["users", "orders", "order_items"],
"analysis_type": "data_flow"
}
mysql_discover_analytics🚀 START HERE - Intelligently discover and analyze your entire MySQL server with expert data analytics insights and intelligent caching.
Parameters:
databases (array, optional): List of databases to analyze (if not specified, discovers all accessible databases)focus_area (string, optional): Analytics focus ('user_behavior', 'sales_analytics', 'engagement', 'general')include_recommendations (boolean, default: true): Include expert query recommendationscross_database_analysis (boolean, default: true): Analyze relationships across databasesdetail_level (string, optional): Level of analysis ('summary', 'detailed', 'full')max_tables_per_db (number, default: 20): Maximum tables to analyze per databasepage (number, default: 1): Page number for paginationpage_size (number, default: 5): Number of databases per pageExamples:
// Discover entire MySQL server for user behavior analysis
{
"focus_area": "user_behavior",
"cross_database_analysis": true
}
// Analyze specific databases only
{
"databases": ["ecommerce", "analytics", "logs"],
"focus_area": "sales_analytics"
}
// Quick server overview
{
"focus_area": "general"
}
What you get:
The server includes a comprehensive caching system that dramatically improves performance for repeated operations:
~/.mcp-mysql-cache/queries/[date]/~/.mcp-mysql-cache/schema-snapshots/~/.mcp-mysql-cache/reports/discovery-reports/~/.mcp-mysql-cache/reports/relationship-analysis/MCP_MYSQL_CACHE_RETENTION_DAYS are deleted at startup0700 and result files use mode 0600 on POSIX systemsFor security, create a dedicated read-only MySQL user:
-- Create read-only user
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';
-- Grant SELECT privileges
GRANT SELECT ON your_database.* TO 'readonly_user'@'%';
-- Grant SHOW privileges for schema inspection
GRANT SHOW VIEW ON your_database.* TO 'readonly_user'@'%';
-- Flush privileges
FLUSH PRIVILEGES;
# Install dependencies
npm install
# Run in development mode
npm run dev
# Build for production
npm run build
The server provides detailed error messages for:
All errors are logged to stderr while maintaining MCP protocol compliance.
git add .
git commit -m "Initial release of intelligent MySQL MCP Server"
git push origin main
mcp.json is properly configuredmcp.json file for automatic discoverygit checkout -b feature/amazing-feature)git commit -m 'Add amazing feature')git push origin feature/amazing-feature)This project is licensed under the MIT License - see the LICENSE file for details.
FAQs
Expert MySQL MCP Server with intelligent analytics and user behavior analysis capabilities
We found that @ttpears/mysql-mcp demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Security News
The PHP Foundation says Chainguard broke a security embargo ahead of critical RCE patches, as leaks disrupted the coordinated release.
Security News
PHP releases emergency patches for two critical RCE vulnerabilities as agencies urge organizations to take unpatched sites offline.
Threat Research
/Security News
A supply chain attack compromised LiteLLM 1.107.0 on PyPI and Docker, spreading malware that steals cloud credentials, developer secrets, and AI provider keys.