MySQL-MCP-Local-Server

Kri8tivemike/MySQL-MCP-Local-Server

3.2

If you are the rightful owner of MySQL-MCP-Local-Server and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to henry@mcphub.com.

MySQL MCP Server v2.0 is a comprehensive server designed for efficient MySQL database management, offering a suite of 37 tools for various operations.

Tools
37
Resources
0
Prompts
0

MySQL MCP Server v2.0

A comprehensive Model Context Protocol (MCP) server for MySQL database operations with 37 powerful tools, advanced error handling, logging, and transaction support.

🚀 Features

  • ✅ 37 MySQL Tools: Complete database management toolkit
  • ✅ Core Operations: SELECT, INSERT, UPDATE, DELETE queries
  • ✅ Schema Management: Create/drop tables, indexes, alter structures
  • ✅ Advanced Queries: Joins, aggregations, bulk operations, upserts
  • ✅ Database Administration: Backup, restore, optimize, analyze
  • ✅ User & Security: User management and permissions (where supported)
  • ✅ Monitoring & Performance: Query analysis, status monitoring, table sizes
  • ✅ Import/Export: CSV, JSON data exchange, table cloning
  • ✅ Transaction Support: Multi-query transactions with rollback
  • ✅ Connection Management: Pooling, health checks, auto-reconnection
  • ✅ Error Handling: Comprehensive error handling with detailed logging
  • ✅ Testing: Complete test suite covering all 37 tools

📋 Prerequisites

  • Node.js 18.0.0 or higher
  • MySQL server 5.7+ or 8.0+
  • Database credentials configured

🛠️ Installation

  1. Install dependencies:
npm install
  1. Configure your database connection by updating the .env file:
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=your_database_name
DB_USERNAME=your_username
DB_PASSWORD=your_password

🎯 Usage

Starting the Server

# Production mode
npm start

# Development mode with auto-restart
npm run dev

Running Tests

npm test

🔧 Available Tools (37 Total)

Core Database Operations (4 tools)

1. mysql_query

Execute SELECT queries on the database.

Parameters:

  • sql (string, required): The SQL SELECT query
  • params (array, optional): Parameters for prepared statements

Example:

{
  "sql": "SELECT * FROM users WHERE age > ? AND department = ?",
  "params": [25, "IT"]
}
2. mysql_insert

Insert data into a table.

Parameters:

  • table (string, required): Table name
  • data (object, required): Key-value pairs of column names and values

Example:

{
  "table": "users",
  "data": {
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30,
    "department": "Engineering"
  }
}
3. mysql_update

Update data in a table.

Parameters:

  • table (string, required): Table name
  • data (object, required): Key-value pairs of columns to update
  • where (object, required): WHERE conditions

Example:

{
  "table": "users",
  "data": {
    "age": 31,
    "department": "Senior Engineering"
  },
  "where": {
    "email": "john@example.com"
  }
}
4. mysql_delete

Delete data from a table.

Parameters:

  • table (string, required): Table name
  • where (object, required): WHERE conditions

Example:

{
  "table": "users",
  "where": {
    "email": "john@example.com"
  }
}

Schema Management Tools (5 tools)

5. mysql_create_table

Create new tables with column definitions.

Parameters:

  • table_name (string, required): Name of the table to create
  • columns (array, required): Array of column definitions
  • options (object, optional): Additional table options

Example:

{
  "table_name": "products",
  "columns": [
    "id INT AUTO_INCREMENT PRIMARY KEY",
    "name VARCHAR(255) NOT NULL",
    "price DECIMAL(10,2)",
    "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
  ],
  "options": {
    "engine": "InnoDB",
    "charset": "utf8mb4"
  }
}
6. mysql_drop_table

Drop tables safely.

Parameters:

  • table_name (string, required): Name of the table to drop
  • if_exists (boolean, optional): Use IF EXISTS clause

Example:

{
  "table_name": "old_products",
  "if_exists": true
}
7. mysql_alter_table

Modify table structure (add/drop columns).

Parameters:

  • table_name (string, required): Name of the table to alter
  • action (string, required): Action to perform (ADD, DROP, MODIFY)
  • column_definition (string, required): Column definition or name

Example:

{
  "table_name": "users",
  "action": "ADD",
  "column_definition": "phone VARCHAR(20)"
}
8. mysql_create_index

Create database indexes.

Parameters:

  • table_name (string, required): Table name
  • index_name (string, required): Index name
  • columns (array, required): Columns to index
  • unique (boolean, optional): Create unique index

Example:

{
  "table_name": "users",
  "index_name": "idx_email",
  "columns": ["email"],
  "unique": true
}
9. mysql_drop_index

Remove indexes.

Parameters:

  • table_name (string, required): Table name
  • index_name (string, required): Index name to drop

Example:

{
  "table_name": "users",
  "index_name": "idx_email"
}

Advanced Query Tools (5 tools)

10. mysql_aggregate_query

Execute aggregate functions (COUNT, SUM, AVG, etc.).

Parameters:

  • table (string, required): Table name
  • aggregates (array, required): Aggregate functions
  • where (object, optional): WHERE conditions
  • group_by (array, optional): GROUP BY columns

Example:

{
  "table": "orders",
  "aggregates": ["COUNT(*) as total_orders", "SUM(amount) as total_revenue"],
  "where": {"status": "completed"},
  "group_by": ["customer_id"]
}
11. mysql_join_query

Execute complex JOIN operations.

Parameters:

  • main_table (string, required): Primary table
  • joins (array, required): Join definitions
  • select (array, required): Columns to select
  • where (object, optional): WHERE conditions

Example:

{
  "main_table": "users",
  "joins": [
    {
      "type": "INNER",
      "table": "orders",
      "on": "users.id = orders.user_id"
    }
  ],
  "select": ["users.name", "users.email", "orders.total"],
  "where": {"orders.status": "completed"}
}
12. mysql_bulk_insert

Insert multiple records efficiently.

Parameters:

  • table (string, required): Table name
  • data (array, required): Array of objects to insert

Example:

{
  "table": "products",
  "data": [
    {"name": "Product A", "price": 19.99, "category": "Electronics"},
    {"name": "Product B", "price": 29.99, "category": "Electronics"},
    {"name": "Product C", "price": 39.99, "category": "Books"}
  ]
}
13. mysql_upsert

INSERT ON DUPLICATE KEY UPDATE operations.

Parameters:

  • table (string, required): Table name
  • data (object, required): Data to insert/update
  • update_on_duplicate (object, optional): Columns to update on duplicate

Example:

{
  "table": "products",
  "data": {"sku": "ABC123", "name": "Product A", "price": 24.99},
  "update_on_duplicate": {"price": "VALUES(price)", "updated_at": "NOW()"}
}
14. mysql_search

Full-text search with LIKE/MATCH operations.

Parameters:

  • table (string, required): Table name
  • search_columns (array, required): Columns to search
  • search_term (string, required): Search term
  • match_mode (string, optional): LIKE or MATCH (default: LIKE)

Example:

{
  "table": "articles",
  "search_columns": ["title", "content"],
  "search_term": "database optimization",
  "match_mode": "LIKE"
}

Database Administration Tools (6 tools)

15. mysql_show_databases

List all databases on server.

Parameters: None

16. mysql_create_database

Create new databases.

Parameters:

  • database_name (string, required): Name of database to create
  • charset (string, optional): Character set
  • collation (string, optional): Collation

Example:

{
  "database_name": "new_project",
  "charset": "utf8mb4",
  "collation": "utf8mb4_unicode_ci"
}
17. mysql_backup_table

Export table data to SQL.

Parameters:

  • table_name (string, required): Table to backup
  • include_data (boolean, optional): Include data (default: true)
  • file_path (string, optional): Output file path

Example:

{
  "table_name": "users",
  "include_data": true,
  "file_path": "./backups/users_backup.sql"
}
18. mysql_restore_table

Import data from SQL backup.

Parameters:

  • file_path (string, required): Path to SQL backup file
  • table_name (string, optional): Specific table to restore

Example:

{
  "file_path": "./backups/users_backup.sql",
  "table_name": "users"
}
19. mysql_optimize_table

Optimize table performance.

Parameters:

  • table_names (array, required): Tables to optimize

Example:

{
  "table_names": ["users", "orders", "products"]
}
20. mysql_analyze_table

Analyze table statistics.

Parameters:

  • table_names (array, required): Tables to analyze

Example:

{
  "table_names": ["users", "orders"]
}

User & Security Tools (4 tools)

21. mysql_show_users

List database users.

Parameters: None

22. mysql_show_grants

Show user permissions.

Parameters:

  • username (string, optional): Specific user (default: current user)

Example:

{
  "username": "app_user"
}
23. mysql_create_user

Create database users.

Parameters:

  • username (string, required): Username
  • password (string, required): Password
  • host (string, optional): Host (default: %)

Example:

{
  "username": "new_user",
  "password": "secure_password",
  "host": "localhost"
}
24. mysql_grant_privileges

Grant user permissions.

Parameters:

  • username (string, required): Username
  • privileges (array, required): Privileges to grant
  • database (string, optional): Database name
  • table (string, optional): Table name

Example:

{
  "username": "app_user",
  "privileges": ["SELECT", "INSERT", "UPDATE"],
  "database": "app_db",
  "table": "*"
}

Monitoring & Performance Tools (5 tools)

25. mysql_show_processlist

Show running queries.

Parameters:

  • full (boolean, optional): Show full queries

Example:

{
  "full": true
}
26. mysql_explain_query

Analyze query execution plan.

Parameters:

  • sql (string, required): Query to analyze
  • format (string, optional): Output format (TRADITIONAL, JSON)

Example:

{
  "sql": "SELECT * FROM users WHERE age > 25 ORDER BY created_at",
  "format": "JSON"
}
27. mysql_show_status

Database server status.

Parameters:

  • pattern (string, optional): Status variable pattern

Example:

{
  "pattern": "Connections"
}
28. mysql_show_variables

Server configuration variables.

Parameters:

  • pattern (string, optional): Variable name pattern

Example:

{
  "pattern": "innodb%"
}
29. mysql_table_size

Get table size information.

Parameters:

  • table_names (array, optional): Specific tables (default: all)

Example:

{
  "table_names": ["users", "orders"]
}

Data Import/Export Tools (4 tools)

30. mysql_export_csv

Export query results to CSV.

Parameters:

  • sql (string, required): Query to export
  • file_path (string, required): Output CSV file path
  • headers (boolean, optional): Include headers

Example:

{
  "sql": "SELECT name, email, age FROM users",
  "file_path": "./exports/users.csv",
  "headers": true
}
31. mysql_import_csv

Import CSV data to tables.

Parameters:

  • table_name (string, required): Target table
  • file_path (string, required): CSV file path
  • columns (array, optional): Column mapping
  • skip_header (boolean, optional): Skip first row

Example:

{
  "table_name": "users",
  "file_path": "./imports/users.csv",
  "columns": ["name", "email", "age"],
  "skip_header": true
}
32. mysql_export_json

Export data as JSON.

Parameters:

  • sql (string, required): Query to export
  • file_path (string, required): Output JSON file path
  • pretty (boolean, optional): Pretty print JSON

Example:

{
  "sql": "SELECT * FROM products WHERE category = 'Electronics'",
  "file_path": "./exports/electronics.json",
  "pretty": true
}
33. mysql_clone_table

Copy table structure/data.

Parameters:

  • source_table (string, required): Source table name
  • target_table (string, required): Target table name
  • include_data (boolean, optional): Copy data (default: true)
  • include_indexes (boolean, optional): Copy indexes (default: true)

Example:

{
  "source_table": "users",
  "target_table": "users_backup",
  "include_data": true,
  "include_indexes": false
}

Utility Tools (4 tools)

34. mysql_transaction

Execute multiple queries in a transaction.

Parameters:

  • queries (array, required): Array of query objects

Example:

{
  "queries": [
    {
      "sql": "INSERT INTO users (name, email) VALUES (?, ?)",
      "params": ["Alice", "alice@example.com"]
    },
    {
      "sql": "UPDATE users SET last_login = NOW() WHERE email = ?",
      "params": ["alice@example.com"]
    }
  ]
}
35. mysql_list_tables

List all tables in the database.

Parameters:

  • pattern (string, optional): Table name pattern

Example:

{
  "pattern": "user%"
}
36. mysql_describe_table

Get detailed information about a table structure.

Parameters:

  • table_name (string, required): Table name

Example:

{
  "table_name": "users"
}
37. mysql_health_check

Check the health of the database connection.

Parameters: None

⚙️ Configuration

The server uses the following environment variables:

VariableDefaultDescription
DB_HOSTlocalhostMySQL server host
DB_PORT3306MySQL server port
DB_USERNAMErootDatabase username
DB_PASSWORD(empty)Database password
DB_DATABASEtestDatabase name
DB_CONNECTION_LIMIT10Connection pool limit

📝 Logging

The server uses Winston for comprehensive logging:

  • Console: Colored output for development
  • File: logs/combined.log for all logs
  • File: logs/error.log for error logs only

Log levels: error, warn, info, debug

🛡️ Error Handling

Comprehensive error handling includes:

  • Connection Errors: Automatic reconnection and connection pooling
  • SQL Errors: Detailed error messages with SQL state and error codes
  • Transaction Errors: Automatic rollback on failure
  • Validation Errors: Input validation for all operations
  • Permission Errors: Graceful handling of insufficient privileges
  • File I/O Errors: Proper error handling for import/export operations

🧪 Testing

The comprehensive test suite validates all 37 tools:

  • Database connection and health checks
  • All CRUD operations with various data types
  • Schema management operations
  • Advanced query operations (joins, aggregates, bulk operations)
  • Transaction handling with commit and rollback
  • Database administration tools
  • User and security operations (where permissions allow)
  • Monitoring and performance tools
  • Import/export functionality
  • Error handling for invalid operations

Run tests with:

npm test

📁 Project Structure

mysql-mcp-server/
├── src/
│   ├── index.js          # Main MCP server with all 37 tools
│   └── database.js       # Database connection and operations
├── test/
│   └── test-operations.js # Comprehensive test suite
├── logs/
│   └── .gitkeep         # Logs directory
├── .env                 # Environment configuration
├── package.json         # Project dependencies
└── README.md           # This documentation

🔒 Security Considerations

  • Uses prepared statements to prevent SQL injection
  • Connection pooling with limits to prevent resource exhaustion
  • No sensitive data logged (passwords, connection strings)
  • Input validation and sanitization for all operations
  • Secure file handling for import/export operations
  • Proper error messages without exposing system internals

🚀 Performance Features

  • Connection pooling for optimal performance
  • Bulk operations for large data sets
  • Query optimization tools and analysis
  • Table optimization and maintenance tools
  • Efficient transaction handling
  • Streaming for large data exports

📊 Tool Categories Summary

CategoryToolsDescription
Core Operations4Basic CRUD operations
Schema Management5Table and index management
Advanced Queries5Complex queries and bulk operations
Database Admin6Backup, restore, optimization
User & Security4User management and permissions
Monitoring5Performance monitoring and analysis
Import/Export4Data exchange in various formats
Utilities4Helper tools and health checks
Total37Complete MySQL management toolkit

📄 License

MIT License

MySQL MCP Server v2.0 - The most comprehensive MySQL MCP server with 37 powerful tools for complete database management.