MySQL Database Server

# MCP MySQL Server A Model Context Protocol (MCP) server for MySQL database operations with built-in safety features and operation resistance. Perfect for use with Claude Code, Codex CLI, and other MCP-compatible AI assistants. ## Features - **Safe MySQL Operations**: Execute SELECT queries freely while controlling destructive operations - **Operation Resistance**: Configurable INSERT, UPDATE, and DELETE restrictions - **Schema Protection**: DROP, TRUNCATE, and ALTER operations are always blocked - **SQL Injection Prevention**: Uses prepared statements with parameter binding - **Multiple Tools**: Query execution, table listing, schema inspection, and database info - **Connection Pooling**: Efficient connection management for better performance - **Detailed Error Reporting**: Clear error messages with SQL codes ## Quick Start ### Option 1: Automated Setup (Easiest) ```bash # One command - does everything! ./setup.sh ``` The script will: - Auto-detect Bun or npm - Install dependencies - Create and optionally configure .env - Build the project - Test database connection ### Option 2: Manual Setup **Using Bun (Faster):** ```bash bun install cp .env.example .env && nano .env bun run build ``` **Using npm:** ```bash npm install cp .env.example .env && nano .env npm run build ``` ## Integration Guides - **[Claude Code Setup](examples/claude-code-setup.md)** - Complete guide for Claude Code integration - **[Codex CLI Setup](examples/codex-cli-setup.md)** - Command-line usage and automation - **[Usage Examples](examples/usage-examples.md)** - Comprehensive query examples and patterns ## Configuration ### Environment Variables Create a `.env` file (use `.env.example` as a template): ```env # MySQL Connection MYSQL_HOST=localhost MYSQL_PORT=3306 MYSQL_USER=your_username MYSQL_PASS=your_password MYSQL_DB=your_database # Operation Permissions (true/false) ALLOW_INSERT_OPERATION=false ALLOW_UPDATE_OPERATION=false ALLOW_DELETE_OPERATION=false ``` ### MCP Client Configuration For Claude Code, add to your `~/.config/claude-code/.mcp.json`: ```json { "mcpServers": { "mysql": { "command": "node", "args": ["/absolute/path/to/mcp-mysql-server/dist/index.js"], "env": { "MYSQL_HOST": "localhost", "MYSQL_PORT": "3306", "MYSQL_USER": "your_username", "MYSQL_PASS": "your_password", "MYSQL_DB": "your_database", "ALLOW_INSERT_OPERATION": "false", "ALLOW_UPDATE_OPERATION": "false", "ALLOW_DELETE_OPERATION": "false" } } } } ``` **Need more examples?** See [docs/mcp-config-examples.md](docs/mcp-config-examples.md) for: - Multiple databases - Remote/Docker configurations - Development vs production setups - Platform-specific paths See the [integration guides](#integration-guides) for detailed setup instructions. ## Safety Features ### Always Blocked Operations These operations are **always blocked** regardless of configuration: - `DROP` - Dropping tables/databases - `TRUNCATE` - Truncating tables - `ALTER` - Altering table structure ### Configurable Operations Control these operations via environment variables: - `INSERT` - Controlled by `ALLOW_INSERT_OPERATION` - `UPDATE` - Controlled by `ALLOW_UPDATE_OPERATION` - `DELETE` - Controlled by `ALLOW_DELETE_OPERATION` ### Always Allowed Operations - `SELECT` - Reading data is always permitted ## Available Tools ### 1. mysql_query Execute SQL queries with safety restrictions. **Parameters:** - `query` (string, required): The SQL query to execute - `params` (array, optional): Parameters for prepared statement **Example:** ```json { "query": "SELECT * FROM users WHERE id = ?", "params": [123] } ``` **Response:** ```json { "success": true, "rows": [...], "rowCount": 5, "fields": [...] } ``` ### 2. mysql_list_tables List all tables in the current database. **Example Response:** ```json { "success": true, "tables": [ { "Tables_in_db": "users" }, { "Tables_in_db": "products" } ] } ``` ### 3. mysql_describe_table Get the structure/schema of a specific table. **Parameters:** - `table` (string, required): Table name **Example Response:** ```json { "success": true, "table": "users", "columns": [ { "Field": "id", "Type": "int", "Null": "NO", "Key": "PRI", "Default": null, "Extra": "auto_increment" } ] } ``` ### 4. mysql_get_database_info Get information about the database connection and permissions. **Example Response:** ```json { "success": true, "connection": { "host": "localhost", "port": 3306, "user": "tommygun", "database": "db" }, "permissions": { "INSERT": false, "UPDATE": false, "DELETE": false, "SELECT": true, "DROP": false, "TRUNCATE": false, "ALTER": false } } ``` ## Usage Examples For comprehensive examples, see [examples/usage-examples.md](examples/usage-examples.md). ### Quick Examples **Safe SELECT Query:** ``` Get the first 10 users from the database ``` **Parameterized Query (Prevents SQL Injection):** ``` Find products where category is 'electronics' and price is less than 1000 ``` **Data Analysis:** ``` Show me monthly revenue for the last 6 months ``` **Schema Exploration:** ``` What tables are in my database? Show me the structure of the users table ``` ### Blocked Operations When operations are disabled, you'll receive clear error messages: ```json { "error": "INSERT operations are disabled. Set ALLOW_INSERT_OPERATION=true to enable.", "blocked": true } ``` ## Enabling Write Operations To enable write operations, update your `.env`: ```env # Enable INSERT operations ALLOW_INSERT_OPERATION=true # Enable UPDATE operations ALLOW_UPDATE_OPERATION=true # Enable DELETE operations (use with caution!) ALLOW_DELETE_OPERATION=true ``` After changing permissions, restart the MCP server. ## Security Best Practices 1. **Default to Read-Only**: Keep write operations disabled unless specifically needed 2. **Use Prepared Statements**: Always use the `params` array for user input 3. **Validate Table Names**: The server validates table names in describe operations 4. **Schema Protection**: DROP/TRUNCATE/ALTER are permanently blocked 5. **Connection Pooling**: Uses connection pooling for better performance and resource management ## Error Handling The server provides detailed error responses: ```json { "error": "Table 'db.nonexistent' doesn't exist", "code": "ER_NO_SUCH_TABLE", "sqlState": "42S02" } ``` ## Testing and Verification ### Quick Tests Once connected to your MCP client, try these commands: 1. **Check Connection:** ``` Show me the MySQL database information ``` 2. **List Tables:** ``` What tables are in my database? ``` 3. **Test Safety Features:** ``` Try to insert a test record into any table ``` (Should be blocked with a clear error message) 4. **Query Data:** ``` Show me sample data from the users table ``` ### Verification Checklist - [ ] Server shows as connected in MCP client - [ ] Database connection info displays correctly - [ ] Table listing works - [ ] SELECT queries execute successfully - [ ] INSERT/UPDATE/DELETE are blocked (default) - [ ] Clear error messages for blocked operations For detailed testing examples, see [examples/usage-examples.md](examples/usage-examples.md). ## Development ```bash # Build the project npm run build # Run the server directly (for testing) npm start # Build and run npm run dev # Watch mode (if needed) npm run build -- --watch ``` ### Project Structure ``` mcp-mysql-server/ ├── src/ │ └── index.ts # Main server implementation ├── dist/ # Compiled JavaScript output ├── examples/ # Integration guides and examples │ ├── claude-code-setup.md │ ├── codex-cli-setup.md │ └── usage-examples.md ├── .env.example # Environment template ├── package.json ├── tsconfig.json └── README.md ``` ### Contributing See [CONTRIBUTING.md](CONTRIBUTING.md) for development guidelines. ## Troubleshooting ### Connection Issues - Verify MySQL is running: `mysql -u username -p` - Check host/port configuration - Verify user credentials - Ensure database exists ### Permission Errors - Check MySQL user has required permissions - Verify `ALLOW_*_OPERATION` settings match your needs ### Tool Not Found - Ensure the server is properly configured in `.mcp.json` - Verify the build succeeded: `ls -la dist/index.js` - Check MCP client can find the server ## Resources - **[Quick Start Guide](docs/QUICKSTART.md)** - Get started in 5 minutes - **[Setup Checklist](docs/SETUP_CHECKLIST.md)** - Step-by-step setup guide - **[Documentation Index](docs/)** - All documentation organized - [MCP Protocol Documentation](https://modelcontextprotocol.io/) - [Integration Guides](examples/) - Claude Code & Codex CLI - [Usage Examples](examples/usage-examples.md) - [Contributing Guidelines](CONTRIBUTING.md) - [Security Policy](.github/SECURITY.md) - [Project Improvements](docs/PROJECT_IMPROVEMENTS.md) - What's new ## Support - Issues: Report bugs or request features via GitHub issues - Questions: Check the [examples](examples/) directory for common use cases - Security: For security concerns, please email the maintainers ## Changelog ### Version 1.0.0 - Initial release - Basic MySQL operations (SELECT, INSERT, UPDATE, DELETE) - Safety features and operation resistance - Prepared statements for SQL injection prevention - Connection pooling - Comprehensive documentation ## License MIT - see [LICENSE](LICENSE) file for details