README.md•33.5 kB
# KatCoder MySQL MCP Server
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.
## Features
### 🔒 Security First
- **SQL Injection Prevention**: Comprehensive input validation and sanitization
- **Identifier Validation**: Strict validation of table and column names
- **Query Whitelisting**: Read-only operations by default, write operations require explicit permission
- **Connection Pooling**: Secure connection management with timeout controls
- **Error Handling**: Secure error messages that don't expose sensitive information
### 🛠️ Database Operations
- **List**: Browse tables and view table structures
- **Read**: Query data with filtering, pagination, and sorting
- **Create**: Insert new records with validation
- **Add Column**: Add new columns to existing tables with full type and constraint support
- **Drop Column**: Remove columns from tables with safety checks
- **Modify Column**: Change column definitions (type, constraints, defaults)
- **Rename Column**: Rename existing columns while preserving data
- **Rename Table**: Rename tables with safety validation
- **Add Index**: Create indexes (BTREE, HASH, FULLTEXT, SPATIAL) with unique constraints
- **Drop Index**: Remove indexes from tables
- **Bulk Insert**: Efficiently insert multiple records in a single operation
- **Update**: Modify existing records safely
- **Delete**: Remove records with mandatory WHERE clauses
- **Execute**: Run custom SQL queries with security restrictions
- **DDL**: Execute Data Definition Language statements
- **Transaction**: Execute multiple operations atomically
- **Utility**: Database health checks and metadata operations
### 🔧 Configuration Options
- **Connection String**: Standard MySQL connection format
- **Tool Selection**: Enable only the tools you need
- **Connection Pooling**: Configurable pool settings
- **Timeout Controls**: Connection and query timeouts
## Tool Permissions & Security
### 🎯 Recommended Approach: Use "all" Tools
**For most use cases, we recommend enabling all tools** by using `"all"` as the tool parameter. This provides:
- **Full Functionality**: Access to all database operations including DDL, transactions, and advanced features
- **AI Agent Compatibility**: Ensures AI agents can see and use all available tools
- **Future-Proof**: Automatically includes new tools as they're added
- **Simplified Configuration**: No need to manually list specific tools
```bash
# Recommended: Enable all tools
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "all"
```
### 🔒 Security-First Approach: Manual Tool Selection
**Use manual tool selection only when you need to restrict access** for security or compliance reasons:
#### Read-Only Access
Perfect for reporting, analytics, or read-only AI agents:
```bash
npx katcoder-mysql-mcp "mysql://readonly:password@localhost:3306/mydb" "list,read,utility"
```
**Available tools:** `list`, `read`, `utility`
- **list**: Browse tables and schema
- **read**: Query data with filtering and pagination
- **utility**: Database health checks and metadata
#### Basic Write Access
For applications that need to modify data but not schema:
```bash
npx katcoder-mysql-mcp "mysql://writer:password@localhost:3306/mydb" "list,read,create,update,delete,utility"
```
**Available tools:** `list`, `read`, `create`, `update`, `delete`, `utility`
- Includes all read-only tools plus:
- **create**: Insert new records
- **update**: Modify existing records
- **delete**: Remove records (with mandatory WHERE clauses)
#### Full Database Access
For database administrators and development environments:
```bash
npx katcoder-mysql-mcp "mysql://admin:password@localhost:3306/mydb" "all"
```
**All available tools:** `list`, `read`, `create`, `update`, `delete`, `execute`, `ddl`, `transaction`, `bulk_insert`, `utility`, `add_column`, `drop_column`, `modify_column`, `rename_column`, `rename_table`, `add_index`, `drop_index`, `show_table_data`
### 🛡️ Security Considerations
#### Database User Permissions
**Always use MySQL user accounts with appropriate privileges:**
```sql
-- Read-only user
CREATE USER 'readonly'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON mydb.* TO 'readonly'@'%';
-- Write user (no DDL)
CREATE USER 'writer'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'writer'@'%';
-- Admin user (full access)
CREATE USER 'admin'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%';
FLUSH PRIVILEGES;
```
#### Tool-Level vs Database-Level Security
- **Tool-level restrictions** limit what operations the MCP server can perform
- **Database-level permissions** provide the ultimate security boundary
- **Best practice**: Use both layers for defense in depth
#### Production Recommendations
1. **Use specific database users** with minimal required privileges
2. **Enable only necessary tools** for production environments
3. **Use read-only connections** for reporting and analytics
4. **Monitor database access** and audit tool usage
5. **Use environment variables** for connection strings (never hardcode passwords)
### 📊 Tool Selection Quick Reference
| Use Case | Recommended Tools | Security Level |
|----------|------------------|----------------|
| **AI Development** | `"all"` | Medium (use dev database) |
| **Production AI** | `"all"` | High (restricted DB user) |
| **Reporting/Analytics** | `"list,read,utility"` | High |
| **Data Entry Apps** | `"list,read,create,update,delete,utility"` | Medium |
| **Database Admin** | `"all"` | Low (trusted environment) |
| **CI/CD Pipelines** | `"all"` | Medium (isolated environment) |
## Installation
> **Note**: This package is currently in development and not yet published to npm. Use the development installation method below.
### Development Installation (Recommended)
```bash
git clone https://github.com/katkoder/katcoder-mysql-mcp.git
cd katcoder-mysql-mcp
npm install
npm run build
```
### Future npm Installation (Coming Soon)
Once published to npm, you will be able to install globally:
```bash
# This will be available after publication
npm install -g katcoder-mysql-mcp
```
### Local npm Installation (Coming Soon)
```bash
# This will be available after publication
npm install katcoder-mysql-mcp
```
## Usage
### Command Line Interface
#### Current Development Usage
```bash
# After building the project (npm run build)
# Basic usage with all tools enabled
node dist/cli.js "mysql://user:password@localhost:3306/database_name"
# With all tools enabled (recommended)
node dist/cli.js "mysql://user:password@localhost:3306/database_name" "all"
# With specific tools enabled (if you need to limit access)
node dist/cli.js "mysql://user:password@localhost:3306/database_name" "list,read,utility"
# With verbose logging
node dist/cli.js "mysql://user:password@localhost:3306/database_name" "all" --verbose
```
#### Future npm Usage (After Publication)
```bash
# Basic usage with all tools enabled
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/database_name"
# With all tools enabled (recommended)
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/database_name" "all"
# With specific tools enabled (if you need to limit access)
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/database_name" "list,read,utility"
# With verbose logging
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/database_name" "all" --verbose
```
### Configuration for AI Agents
#### Current Development Configuration
**Claude Desktop Configuration:**
Add this configuration to your Claude Desktop configuration file:
```json
{
"mcpServers": {
"katkoder_mysql": {
"command": "node",
"args": [
"/path/to/katcoder-mysql-mcp/dist/cli.js",
"mysql://root:password@localhost:3306/production_db",
"all"
],
"cwd": "/path/to/katcoder-mysql-mcp"
}
}
}
```
**Cursor IDE Configuration:**
For Cursor IDE, add to your settings:
```json
{
"mcp.servers": {
"katkoder_mysql": {
"command": "node",
"args": [
"/path/to/katcoder-mysql-mcp/dist/cli.js",
"mysql://user:password@localhost:3306/development_db",
"all"
],
"cwd": "/path/to/katcoder-mysql-mcp"
}
}
}
```
#### Future npm Configuration (After Publication)
**Claude Desktop Configuration:**
```json
{
"mcpServers": {
"katkoder_mysql": {
"command": "npx",
"args": [
"-y",
"katcoder-mysql-mcp",
"mysql://root:password@localhost:3306/production_db",
"all"
]
}
}
}
```
**Cursor IDE Configuration:**
```json
{
"mcp.servers": {
"katkoder_mysql": {
"command": "npx",
"args": [
"-y",
"katcoder-mysql-mcp",
"mysql://user:password@localhost:3306/development_db",
"all"
]
}
}
}
```
### Connection String Format
```
mysql://[user[:password]@]host[:port]/database
```
#### Basic Examples:
- `mysql://root@localhost:3306/mydb` - Local database without password
- `mysql://user:password@localhost:3306/mydb` - Local database with password
- `mysql://user:password@192.168.1.100:3306/mydb` - Remote database
#### Advanced Examples:
- `mysql://user:password@db.example.com:3306/production?ssl=true` - Remote database with SSL
- `mysql://root:password@mysql-container:3306/docker_db` - Docker database
- `mysql://user:password@localhost:3307/alternative_port` - Different port
## Available Tools
### 1. List Tool
Browse database structure and table information.
**Parameters:**
- `table` (optional): Specific table name to get column information
**Examples:**
```json
{
"name": "list",
"arguments": {}
}
{
"name": "list",
"arguments": {
"table": "users"
}
}
```
**Practical Usage Scenarios:**
- **Database Discovery**: When connecting to a new database, use the list tool without parameters to see all available tables
- **Schema Exploration**: Use with a table name to understand the structure before writing queries
- **Data Modeling**: Examine relationships between tables by checking foreign key constraints
- **Migration Planning**: Understand existing schema before making changes
### 2. Read Tool
Query data from tables with filtering and pagination.
**Parameters:**
- `table` (required): Table name to query
- `columns` (optional): Array of specific columns to select
- `where` (optional): Object with filter conditions
- `limit` (optional): Maximum number of rows (max: 10,000)
- `offset` (optional): Number of rows to skip
- `orderBy` (optional): Order by clause
**Basic Examples:**
```json
{
"name": "read",
"arguments": {
"table": "users",
"columns": ["id", "name", "email"],
"where": {"status": "active"},
"limit": 10,
"orderBy": "created_at DESC"
}
}
{
"name": "read",
"arguments": {
"table": "products",
"where": {"category": "electronics", "price": {"$gt": 100}},
"limit": 50
}
}
```
**Advanced Filtering Examples:**
```json
{
"name": "read",
"arguments": {
"table": "users",
"columns": ["id", "email", "created_at"],
"where": {"status": "active", "created_at": {"$gte": "2024-01-01"}},
"limit": 25,
"offset": 50,
"orderBy": "last_login DESC"
}
}
```
### 3. Bulk Insert Tool
Efficiently insert multiple records into a table in a single operation.
**Parameters:**
- `table` (required): Target table name
- `data` (required): Array of objects with identical column-value pairs
**Examples:**
```json
{
"name": "bulk_insert",
"arguments": {
"table": "users",
"data": [
{
"name": "John Doe",
"email": "john@example.com",
"age": 30,
"status": "active"
},
{
"name": "Jane Smith",
"email": "jane@example.com",
"age": 25,
"status": "active"
},
{
"name": "Bob Wilson",
"email": "bob@example.com",
"age": 35,
"status": "inactive"
}
]
}
}
```
**Usage in Transactions:**
```json
{
"name": "transaction",
"arguments": {
"operations": [
{
"type": "bulk_insert",
"table": "users",
"data": [
{
"name": "Alice Brown",
"email": "alice@example.com",
"age": 28,
"status": "active"
}
]
},
{
"type": "update",
"table": "user_stats",
"data": { "total_users": 1 },
"where": { "id": 1 }
}
]
}
}
```
**Response Format:**
```json
{
"success": true,
"table": "users",
"recordCount": 3,
"affectedRows": 3,
"insertedId": 1,
"message": "Successfully inserted 3 records into users"
}
```
### 4. Create Tool
Insert new records into tables.
**Parameters:**
- `table` (required): Target table name
- `data` (required): Object with column-value pairs
**Examples:**
```json
{
"name": "create",
"arguments": {
"table": "users",
"data": {
"name": "John Doe",
"email": "john@example.com",
"status": "active"
}
}
}
```
### 4. Update Tool
Modify existing records safely.
**Parameters:**
- `table` (required): Target table name
- `data` (required): Object with column-value pairs to update
- `where` (required): Object with filter conditions
**Examples:**
```json
{
"name": "update",
"arguments": {
"table": "users",
"data": {
"status": "inactive",
"updated_at": "2024-01-01 12:00:00"
},
"where": {"id": 123}
}
}
```
### 5. Delete Tool
Remove records with mandatory WHERE clauses.
**Parameters:**
- `table` (required): Target table name
- `where` (required): Object with filter conditions
**Examples:**
```json
{
"name": "delete",
"arguments": {
"table": "sessions",
"where": {"expired": true}
}
}
```
### 6. Execute Tool
Run custom SQL queries with security restrictions.
**Parameters:**
- `query` (required): SQL query string
- `params` (optional): Array of query parameters
- `allowWrite` (optional): Boolean to allow write operations
**Basic Examples:**
```json
{
"name": "execute",
"arguments": {
"query": "SELECT COUNT(*) as total FROM users WHERE created_at > ?",
"params": ["2024-01-01"]
}
}
{
"name": "execute",
"arguments": {
"query": "UPDATE users SET last_login = NOW() WHERE id = ?",
"params": [123],
"allowWrite": true
}
}
```
**Complex Query Examples:**
```json
{
"name": "execute",
"arguments": {
"query": "SELECT u.email, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING order_count > 5"
}
}
{
"name": "execute",
"arguments": {
"query": "SELECT DATE(created_at) as date, COUNT(*) as daily_signups FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY date",
"params": []
}
}
```
### 7. DDL Tool
Execute Data Definition Language statements.
**Parameters:**
- `statement` (required): DDL statement
**Examples:**
```json
{
"name": "ddl",
"arguments": {
"statement": "CREATE INDEX idx_email ON users(email)"
}
}
```
### 8. Add Column Tool
Add new columns to existing tables with comprehensive type and constraint support.
**Parameters:**
- `table` (required): Target table name
- `column` (required): Object with column definition
- `name` (required): New column name
- `type` (required): Column data type (e.g., VARCHAR(255), INT, DATETIME)
- `nullable` (optional): Whether column can contain NULL values
- `default` (optional): Default value for the column
- `autoIncrement` (optional): Whether column should auto-increment
- `comment` (optional): Column comment
- `position` (optional): Object specifying column position
- `after` (optional): Place column after this existing column
- `first` (optional): Place column as the first column
**Examples:**
```json
{
"name": "add_column",
"arguments": {
"table": "users",
"column": {
"name": "email",
"type": "VARCHAR(255)",
"nullable": false,
"default": "no-email@example.com"
},
"position": {
"after": "name"
}
}
}
```
```json
{
"name": "add_column",
"arguments": {
"table": "products",
"column": {
"name": "is_active",
"type": "BOOLEAN",
"default": true,
"comment": "Product availability status"
}
}
}
```
### 9. Drop Column Tool
Remove columns from tables with safety validation.
**Parameters:**
- `table` (required): Table name to remove column from
- `column` (required): Column name to drop
**Examples:**
```json
{
"name": "drop_column",
"arguments": {
"table": "users",
"column": "old_field"
}
}
```
### 10. Modify Column Tool
Change existing column definitions including type, constraints, and defaults.
**Parameters:**
- `table` (required): Table name containing the column
- `column` (required): Column name to modify
- `newDefinition` (required): Object with new column definition
- `type` (required): New column data type
- `nullable` (optional): Whether column can contain NULL values
- `default` (optional): New default value
- `comment` (optional): Column comment
**Examples:**
```json
{
"name": "modify_column",
"arguments": {
"table": "users",
"column": "age",
"newDefinition": {
"type": "INT",
"nullable": true,
"default": null
}
}
}
```
### 11. Rename Column Tool
Rename existing columns while preserving data.
**Parameters:**
- `table` (required): Table name containing the column
- `oldName` (required): Current column name
- `newName` (required): New column name
- `newDefinition` (optional): Column definition for the renamed column
**Examples:**
```json
{
"name": "rename_column",
"arguments": {
"table": "users",
"oldName": "user_name",
"newName": "username"
}
}
```
### 12. Rename Table Tool
Rename tables with safety validation.
**Parameters:**
- `oldName` (required): Current table name
- `newName` (required): New table name
**Examples:**
```json
{
"name": "rename_table",
"arguments": {
"oldName": "user_profiles",
"newName": "user_settings"
}
}
```
### 13. Add Index Tool
Create indexes on tables for improved query performance.
**Parameters:**
- `table` (required): Table name to add index to
- `name` (required): Index name
- `columns` (required): Array of column names to include in the index
- `type` (optional): Index type (BTREE, HASH, FULLTEXT, SPATIAL)
- `unique` (optional): Whether the index should be unique
**Examples:**
```json
{
"name": "add_index",
"arguments": {
"table": "users",
"name": "idx_email",
"columns": ["email"],
"unique": true
}
}
```
```json
{
"name": "add_index",
"arguments": {
"table": "products",
"name": "idx_category_price",
"columns": ["category_id", "price"],
"type": "BTREE"
}
}
```
### 14. Drop Index Tool
Remove indexes from tables.
**Parameters:**
- `table` (required): Table name containing the index
- `name` (required): Index name to drop
**Examples:**
```json
{
"name": "drop_index",
"arguments": {
"table": "users",
"name": "idx_temp"
}
}
```
### 15. Transaction Tool
Execute multiple operations atomically.
**Parameters:**
- `operations` (required): Array of operations to execute in transaction
**Basic Examples:**
```json
{
"name": "transaction",
"arguments": [
{
"type": "create",
"table": "orders",
"data": {"user_id": 123, "total": 99.99}
},
{
"type": "update",
"table": "users",
"data": {"last_order_date": "2024-01-01"},
"where": {"id": 123}
}
]
}
```
**Advanced Transaction Examples with Schema Changes:**
```json
{
"name": "transaction",
"arguments": {
"operations": [
{
"type": "add_column",
"table": "users",
"column": {
"name": "phone",
"type": "VARCHAR(20)",
"nullable": true
}
},
{
"type": "add_index",
"table": "users",
"name": "idx_phone",
"columns": ["phone"],
"unique": true
},
{
"type": "update",
"table": "users",
"data": {"phone": "+1234567890"},
"where": {"id": 1}
}
]
}
}
```
**Response Format:**
```json
{
"success": true,
"operations": 3,
"results": [
{
"description": "Add column 'phone' to table 'users'",
### v1.1.0 (Latest)
- **New Feature**: Added Comprehensive Schema Modification Tools
- Implemented `add_column` tool for adding new columns with full type and constraint support
- Implemented `drop_column` tool for safely removing columns from tables
- Implemented `modify_column` tool for changing column definitions
- Implemented `rename_column` tool for renaming existing columns
- Implemented `rename_table` tool for renaming tables
- Implemented `add_index` tool for creating various types of indexes
- Implemented `drop_index` tool for removing indexes from tables
- Added comprehensive schema validation and security measures
- Enhanced transaction support for schema operations with rollback mechanisms
- Added detailed documentation with examples and usage scenarios
### v1.0.1
- **New Feature**: Added Bulk Insert Tool for efficient multi-record insertion
- Implemented `bulk_insert` tool for batch data imports
- Supports inserting multiple records in a single database operation
- Includes comprehensive validation and error handling
- Can be used within transactions for atomic operations
- Added detailed documentation with examples and usage scenarios
### v1.0.0
- Initial release
- All database operations implemented
- Comprehensive security features
- Full documentation
"affectedRows": 0
},
{
"description": "Create unique index 'idx_phone' on table 'users'",
"affectedRows": 0
},
{
"description": "Update user record with phone number",
"affectedRows": 1
}
]
}
```
{
"name": "ddl",
"arguments": {
"statement": "ALTER TABLE users ADD COLUMN phone VARCHAR(20)"
}
}
```
### 8. Transaction Tool
Execute multiple operations atomically.
**Parameters:**
- `operations` (required): Array of operations to execute
**Basic Examples:**
```json
{
"name": "transaction",
"arguments": {
"operations": [
{
"type": "create",
"table": "orders",
"data": {"user_id": 123, "total": 99.99}
},
{
"type": "update",
"table": "users",
"data": {"last_order_date": "2024-01-01"},
"where": {"id": 123}
}
]
}
}
```
**Advanced Transaction Examples:**
```json
{
"name": "transaction",
"arguments": {
"operations": [
{
"type": "create",
"table": "orders",
"data": {"user_id": 123, "total": 99.99, "status": "pending"}
},
{
"type": "update",
"table": "users",
"data": {"last_order_date": "2024-01-01"},
"where": {"id": 123}
},
{
"type": "create",
"table": "order_items",
"data": {"order_id": "LAST_INSERT_ID()", "product_id": 456, "quantity": 2}
}
]
}
}
```
### 9. Utility Tool
Database health checks and metadata operations.
**Parameters:**
- `action` (required): Utility action (ping, version, stats, describe_table)
- `table` (optional): Table name (required for describe_table)
**Examples:**
```json
{
"name": "utility",
"arguments": {
"action": "ping"
}
}
{
"name": "utility",
"arguments": {
"action": "stats"
}
}
{
"name": "utility",
"arguments": {
"action": "describe_table",
"table": "users"
}
}
```
### 10. Show Table Data Tool
Display table data with advanced formatting, pagination, and schema information.
**Parameters:**
- `table` (required): Table name to display data from
- `limit` (optional): Maximum number of rows to display (default: 50, max: 1000)
- `offset` (optional): Number of rows to skip for pagination (default: 0)
- `columns` (optional): Array of specific columns to display (default: all columns)
- `where` (optional): Object with filter conditions (same format as read tool)
- `orderBy` (optional): Column name to sort by (defaults to primary key or first column)
- `orderDirection` (optional): Sort direction - 'ASC' or 'DESC' (default: 'ASC')
- `showSchema` (optional): Include table schema information (default: true)
- `format` (optional): Output format - 'table', 'json', or 'csv' (default: 'table')
**Basic Examples:**
```json
{
"name": "show_table_data",
"arguments": {
"table": "users"
}
}
{
"name": "show_table_data",
"arguments": {
"table": "products",
"limit": 25,
"columns": ["id", "name", "price", "category"],
"orderBy": "price",
"orderDirection": "DESC"
}
}
```
**Advanced Examples:**
```json
{
"name": "show_table_data",
"arguments": {
"table": "orders",
"where": {"status": "pending", "created_at": {"$gte": "2024-01-01"}},
"limit": 100,
"offset": 50,
"format": "csv",
"showSchema": false
}
}
{
"name": "show_table_data",
"arguments": {
"table": "users",
"columns": ["id", "email", "last_login"],
"where": {"status": "active"},
"orderBy": "last_login",
"orderDirection": "DESC",
"format": "json"
}
}
```
**Response Format:**
```json
{
"success": true,
"table": "users",
"format": "table",
"pagination": {
"currentPage": 1,
"totalPages": 5,
"limit": 50,
"offset": 0,
"totalRows": 247,
"hasMore": true,
"showing": "1-50 of 247"
},
"data": [...],
"displayInfo": "formatted table string (for table format)",
"count": 50,
"schema": {
"columns": [...],
"totalColumns": 8
}
}
```
**Practical Usage Scenarios:**
- **Data Exploration**: Quickly browse table contents with automatic formatting
- **Data Export**: Export table data in CSV format for external analysis
- **Debugging**: View specific rows with filtering and pagination
- **Schema Analysis**: Examine table structure alongside data
- **Report Generation**: Generate formatted data displays for documentation
## Security Features
### SQL Injection Prevention
- **Input Sanitization**: All table and column names are sanitized
- **Parameter Binding**: All queries use parameterized statements
- **Query Validation**: Dangerous SQL patterns are blocked
- **Write Operation Protection**: Write operations require explicit permission
### Identifier Validation
- **Table Names**: Only alphanumeric characters and underscores allowed
- **Column Names**: Validated against SQL injection patterns
- **Where Conditions**: Values are checked for dangerous content
### Connection Security
- **Connection Pooling**: Secure connection management
- **Timeout Controls**: Prevents hanging connections
- **Error Handling**: Secure error messages without sensitive data
## Security Best Practices
### 1. Recommended Setup: All Tools with Restricted Database User
**Best practice for production and AI agents:**
```sql
-- Create user with appropriate database-level permissions
CREATE USER 'mcp_ai_agent'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON myapp.* TO 'mcp_ai_agent'@'localhost';
FLUSH PRIVILEGES;
```
```bash
# Enable all tools - database permissions provide the security boundary
npx katcoder-mysql-mcp "mysql://mcp_ai_agent:secure_password@localhost:3306/myapp" "all"
```
**Why this approach works:**
- ✅ AI agents can see and use all available tools
- ✅ Database user permissions control actual access
- ✅ Future-proof as new tools are automatically available
- ✅ Simplified configuration management
### 2. Security-First Scenarios
#### Read-Only Analytics/Reporting
```sql
CREATE USER 'mcp_readonly'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp.* TO 'mcp_readonly'@'localhost';
FLUSH PRIVILEGES;
```
```bash
# Restrict tools to read-only operations
npx katcoder-mysql-mcp "mysql://mcp_readonly:secure_password@localhost:3306/myapp" "list,read,utility"
```
#### Data Entry Applications (No Schema Changes)
```sql
CREATE USER 'mcp_writer'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'mcp_writer'@'localhost';
FLUSH PRIVILEGES;
```
```bash
# Allow data operations but restrict DDL tools
npx katcoder-mysql-mcp "mysql://mcp_writer:secure_password@localhost:3306/myapp" "list,read,create,update,delete,bulk_insert,utility"
```
### 3. Development Environment
```bash
# Development: Use all tools with admin user
npx katcoder-mysql-mcp "mysql://root:password@localhost:3306/dev_db" "all"
```
### 4. Environment Variables (Recommended)
```bash
# Set connection string as environment variable
export MYSQL_URL="mysql://mcp_ai_agent:secure_password@localhost:3306/myapp"
# Use with all tools enabled
npx katcoder-mysql-mcp "$MYSQL_URL" "all"
# Or with specific tools for restricted access
npx katcoder-mysql-mcp "$MYSQL_URL" "list,read,utility"
```
### 5. Docker/Container Environments
```bash
# Using Docker secrets or environment variables
export MYSQL_URL="mysql://mcp_user:${DB_PASSWORD}@mysql-container:3306/production_db"
npx katcoder-mysql-mcp "$MYSQL_URL" "all"
```
## Quick Reference: Choosing the Right Permission Approach
| Use Case | Recommended Tools | Database Permissions | Security Level |
|----------|------------------|---------------------|----------------|
| **AI Development & Prototyping** | `"all"` | Full admin access | Low (dev only) |
| **Production AI Agent** | `"all"` | Limited to specific database/schema | High ⭐ |
| **Read-only Analytics** | `"list,read,utility"` | SELECT only | High |
| **Data Entry App** | `"list,read,create,update,delete,bulk_insert,utility"` | No DDL permissions | Medium |
| **Schema Migration Tool** | `"all"` | DDL permissions required | Medium |
| **Reporting Dashboard** | `"list,read,utility"` | SELECT only | High |
### 🎯 **Most Common Setup (Recommended)**
```bash
# 1. Create restricted database user
CREATE USER 'mcp_agent'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON your_app.* TO 'mcp_agent'@'localhost';
# 2. Use all tools - security handled by database permissions
npx katcoder-mysql-mcp "mysql://mcp_agent:secure_password@localhost:3306/your_app" "all"
```
**Why this works:** Database permissions provide the real security boundary, while "all" tools ensure AI agents can see and use all available functionality.
## Error Handling
The server provides detailed error messages while maintaining security:
```json
{
"error": true,
"message": "Table 'nonexistent_table' does not exist",
"details": "Check the table name and try again"
}
```
## Development
### Building the Project
```bash
npm run build
```
### Running in Development Mode
```bash
npm run dev
```
### Testing
```bash
npm test
```
## Environment Variables
- `LOG_LEVEL`: Set logging level (debug, info, warn, error)
- `NODE_ENV`: Set environment (development, production)
## Troubleshooting
### Connection Issues
- Verify MySQL server is running
- Check connection string format
- Ensure database exists
- Verify user permissions
#### Test Connection
```bash
# Test with all tools enabled
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "all"
# Then use: {"name": "utility", "arguments": {"action": "ping"}}
```
#### Check Database Version
```bash
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "all"
# Then use: {"name": "utility", "arguments": {"action": "version"}}
```
### Permission Errors
- Check MySQL user privileges
- Ensure database access is granted
- Verify table-level permissions
### Performance Issues
- Monitor connection pool usage
- Check query execution times
- Optimize database indexes
#### Monitor Performance
```bash
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "all"
# Then use: {"name": "utility", "arguments": {"action": "stats"}}
```
## Advanced Configuration
### Custom Connection Pool Settings
```bash
# Environment variables for connection tuning
export MYSQL_CONNECTION_LIMIT=20
export MYSQL_ACQUIRE_TIMEOUT=30000
export MYSQL_TIMEOUT=45000
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb"
```
### Logging Configuration
```bash
# Enable debug logging
export LOG_LEVEL=debug
# Enable verbose output
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "all" --verbose
```
## Contributing
1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests
5. Submit a pull request
## License
MIT License - see LICENSE file for details.
## Support
For issues and questions:
- GitHub Issues: https://github.com/katkoder/katcoder-mysql-mcp/issues
- Documentation: https://github.com/katkoder/katcoder-mysql-mcp/wiki
## Changelog
### v1.0.1 (Latest)
- **New Feature**: Added Bulk Insert Tool for efficient multi-record insertion
- Implemented `bulk_insert` tool for batch data imports
- Supports inserting multiple records in a single database operation
- Includes comprehensive validation and error handling
- Can be used within transactions for atomic operations
- Added detailed documentation with examples and usage scenarios
### v1.0.0
- Initial release
- All database operations implemented
- Comprehensive security features
- Full documentation