# MySQL MCP Server
A secure, feature-rich MySQL Model Context Protocol (MCP) server designed for integration with AI assistants like Claude and VS Code GitHub Copilot.
## Table of Contents
- [Features](#features)
- [Usage](#usage)
- [With Visual Studio Code](#with-visual-studio-code)
- [With Claude Desktop](#with-claude-desktop)
- [With Claude Code](#with-claude-code)
- [With Gemini CLI](#with-gemini-cli)
- [Within your project](#within-your-project)
- [Available Tools](#available-tools)
- [Configuration Options](#configuration-options)
- [Database Configuration](#database-configuration)
- [Feature Configuration](#feature-configuration)
- [Security Configuration](#security-configuration)
- [Logging Configuration](#logging-configuration)
- [Security Features](#security-features)
- [Error Handling](#error-handling)
- [Contributing](#contributing)
- [Support](#support)
## Features
- **Security First**: Built with security best practices, input validation, and configurable access controls
- **Configurable Operations**: Enable/disable CRUD operations and table creation based on your needs (read operations enabled by default)
- **Tabular Data Display**: Properly formatted responses for easy data visualization
- **Comprehensive Logging**: Detailed logging for debugging and monitoring
- **Environment-Based Configuration**: Easy setup using environment variables or configuration objects
- **NPM Package**: Ready to use as a dependency in your projects
## Usage
### With Visual Studio Code
Add this to your `.vscode/mcp.json`:
```json
{
"servers": {
"mysql": {
"command": "npx",
"args": ["@lakshya-mcp/mysql-mcp-server-claude"],
"env": {
"MYSQL_HOST": "{your_host}",
"MYSQL_PORT": "{your_port}",
"MYSQL_USER": "{your_username}",
"MYSQL_PASSWORD": "{your_password}",
"MYSQL_DATABASE": "{your_database}",
"MYSQL_ALLOW_CREATE": "false",
"MYSQL_ALLOW_UPDATE": "false",
"MYSQL_ALLOW_DELETE": "false",
"MYSQL_ALLOW_CREATE_TABLE": "false",
"MYSQL_READ_ONLY": "true" // Set to "true" for read-only mode; "false" allows writes.
}
}
}
}
```
### With Claude Desktop
Add this to your `claude_desktop_config.json`.
Follow these [instructions](https://modelcontextprotocol.io/quickstart/user#installing-the-filesystem-server) to locate file.
```json
{
"mcpServers": {
"mysql": {
"command": "npx",
"args": ["@lakshya-mcp/mysql-mcp-server-claude"],
"env": {
"MYSQL_HOST": "{your_host}",
"MYSQL_PORT": "{your_port}",
"MYSQL_USER": "{your_username}",
"MYSQL_PASSWORD": "{your_password}",
"MYSQL_DATABASE": "{your_database}",
"MYSQL_ALLOW_CREATE": "false",
"MYSQL_ALLOW_UPDATE": "false",
"MYSQL_ALLOW_DELETE": "false",
"MYSQL_ALLOW_CREATE_TABLE": "false",
"MYSQL_READ_ONLY": "true" // Set to "true" for read-only mode; "false" allows writes.
}
}
}
}
```
Save file and restart claude desktop. It should be visible under tools (check icon next to `+`).
### With Claude Code
Open terminal and run this command:
For windows (without wsl):
```bash
claude mcp add mysql -e MYSQL_HOST=localhost -e MYSQL_PORT=3306 -e MYSQL_USER=root -e MYSQL_PASSWORD={your_password} -e MYSQL_DATABASE={your_database} -e MYSQL_ALLOW_CREATE=false -e MYSQL_ALLOW_UPDATE=false -e MYSQL_ALLOW_DELETE=false -e MYSQL_ALLOW_CREATE_TABLE=false -e MYSQL_READ_ONLY=true -- cmd /c npx @lakshya-mcp/mysql-mcp-server-claude
```
For mac / windows (with wsl):
```bash
claude mcp add mysql -e MYSQL_HOST=localhost -e MYSQL_PORT=3306 -e MYSQL_USER=root -e MYSQL_PASSWORD={your_password} -e MYSQL_DATABASE={your_database} -e MYSQL_ALLOW_CREATE=false -e MYSQL_ALLOW_UPDATE=false -e MYSQL_ALLOW_DELETE=false -e MYSQL_ALLOW_CREATE_TABLE=false -e MYSQL_READ_ONLY=true -- npx -y @lakshya-mcp/mysql-mcp-server-claude
```
Then type: `claude` and run `/mcp`. It should show:
```
❯ 1. mysql ✔ connected · Enter to view details
```
### With Gemini CLI
Navigate to your home directory and look for a folder named `.gemini`.
Inside that folder, you will find the `settings.json` file.
Add this in your `.gemini/settings.json` file:
```json
{
"mcpServers": {
"mysql": {
"command": "npx",
"args": ["@lakshya-mcp/mysql-mcp-server-claude"],
"env": {
"MYSQL_HOST": "{your_host}",
"MYSQL_PORT": "{your_port}",
"MYSQL_USER": "{your_username}",
"MYSQL_PASSWORD": "{your_password}",
"MYSQL_DATABASE": "{your_database}",
"MYSQL_ALLOW_CREATE": "false",
"MYSQL_ALLOW_UPDATE": "false",
"MYSQL_ALLOW_DELETE": "false",
"MYSQL_ALLOW_CREATE_TABLE": "false",
"MYSQL_READ_ONLY": "true" // Set to "true" for read-only mode; "false" allows writes.
}
}
}
}
```
Then restart gemini cli. You should be able to see mysql mcp server.
You can verify by running `/mcp`.
### Within your project
1. Install package
```bash
npm install -g @lakshya-mcp/mysql-mcp-server-claude
```
2. Create server and use
```javascript
const { MySQLMCPServer } = require("@lakshya-mcp/mysql-mcp-server-claude");
// Using environment variables
const server = new MySQLMCPServer();
await server.start();
// Or with custom configuration
const server = new MySQLMCPServer({
database: {
host: "localhost",
port: 3306,
user: "username",
password: "password",
database: "mydb",
},
features: {
fetch: true,
create: false,
update: false,
delete: false,
createTable: false,
},
});
```
## Available Tools
The MySQL MCP Server provides several powerful tools for database interaction. Each tool is designed with security in mind and includes proper input validation:
### mysql_list_tables
**Purpose**: Lists all accessible tables in the database
- Returns a comprehensive list of all tables you have access to
- Useful for discovering the database structure
- No parameters required
- Respects table access controls if configured
### mysql_describe_table
**Purpose**: Get detailed information about a table structure including columns, types, and constraints
- Shows column names, data types, nullable status, and key information
- Essential for understanding table schema before querying
- Helps identify primary keys, foreign keys, and data constraints
- **Parameters:**
- `table_name` (string, required): Name of the table to describe
### mysql_select_data
**Purpose**: Select data from a table with optional filtering and pagination
- Flexible querying with support for filtering, sorting, and pagination
- Returns data in a tabular format for easy visualization
- Supports complex WHERE clauses for precise data retrieval
- Built-in row limiting for performance and security
- **Parameters:**
- `table_name` (string, required): Name of the table to query
- `columns` (array, optional): Specific columns to select (e.g., ["name", "email"])
- `where` (string, optional): WHERE clause conditions (e.g., "age > 25 AND status = 'active'")
- `order_by` (string, optional): ORDER BY clause (e.g., "name ASC" or "created_at DESC")
- `limit` (number, optional): Maximum number of rows to return
- `offset` (number, optional): Number of rows to skip for pagination
### mysql_insert_data (if enabled)
**Purpose**: Insert new data into a table
- Allows adding new records to the database
- Only available when CREATE operations are enabled in configuration
- Validates data against table schema before insertion
- Supports batch inserts for efficiency
- **Parameters:**
- `table_name` (string, required): Name of the table to insert into
- `data` (object, required): Data to insert as key-value pairs (e.g., {"name": "John", "age": 30})
### mysql_update_data (if enabled)
**Purpose**: Update existing data in a table
- Modifies existing records based on specified criteria
- Only available when UPDATE operations are enabled in configuration
- Requires WHERE clause to prevent accidental mass updates
- Validates updated data against table constraints
- **Parameters:**
- `table_name` (string, required): Name of the table to update
- `data` (object, required): Data to update as key-value pairs (e.g., {"status": "inactive"})
- `where` (string, required): WHERE clause to identify rows to update (e.g., "id = 123")
- `where_params` (array, optional): Parameters for parameterized WHERE clauses
### mysql_delete_data (if enabled)
**Purpose**: Delete data from a table
- Removes records from the database based on specified criteria
- Only available when DELETE operations are enabled in configuration
- Requires WHERE clause to prevent accidental mass deletions
- Includes safety checks and confirmation prompts
- **Parameters:**
- `table_name` (string, required): Name of the table to delete from
- `where` (string, required): WHERE clause to identify rows to delete (e.g., "status = 'expired'")
- `where_params` (array, optional): Parameters for parameterized WHERE clauses
### mysql_create_table (if enabled)
**Purpose**: Create new tables with specified columns and constraints
- Allows creating new database tables with custom schema definitions
- Only available when CREATE TABLE operations are enabled in configuration
- Supports various column types, constraints, and table options
- Includes safety features like IF NOT EXISTS option
- **Parameters:**
- `table_name` (string, required): Name of the table to create
- `columns` (array, required): Array of column definitions with the following properties:
- `name` (string, required): Column name
- `type` (string, required): Column data type (e.g., "VARCHAR", "INT", "TEXT", "DATETIME")
- `length` (number, optional): Column length for types that support it (e.g., VARCHAR(255))
- `nullable` (boolean, optional): Whether the column can be NULL (default: true)
- `primaryKey` (boolean, optional): Whether this column is part of the primary key (default: false)
- `autoIncrement` (boolean, optional): Whether this column auto-increments (default: false)
- `unique` (boolean, optional): Whether this column has a unique constraint (default: false)
- `defaultValue` (any, optional): Default value for the column
- `if_not_exists` (boolean, optional): Use CREATE TABLE IF NOT EXISTS to avoid errors if table exists (default: false)
- `engine` (string, optional): Storage engine (e.g., "InnoDB", "MyISAM")
- `charset` (string, optional): Character set (e.g., "utf8mb4")
- `collation` (string, optional): Collation (e.g., "utf8mb4_unicode_ci")
**Note**: Write operations (INSERT, UPDATE, DELETE, CREATE TABLE) are disabled by default for security. Enable them only when necessary and ensure proper access controls are in place.
## Configuration Options
### Database Configuration
```javascript
{
database: {
host: "localhost", // MySQL host
port: 3306, // MySQL port
user: "username", // MySQL username
password: "password", // MySQL password
database: "dbname", // Database name
ssl: false, // Enable SSL
connectionLimit: 10, // Connection pool limit
acquireTimeout: 60000 // Connection acquire timeout in ms
}
}
```
### Feature Configuration
```javascript
{
features: {
fetch: true, // Always enabled - read operations
create: false, // Enable INSERT operations
update: false, // Enable UPDATE operations
delete: false, // Enable DELETE operations
createTable: false // Enable CREATE TABLE operations
}
}
```
### Security Configuration
```javascript
{
security: {
allowedTables: ["users", "products"], // Only allow these tables
blockedTables: ["admin", "secrets"], // Block these tables
maxRows: 1000, // Maximum rows per query
readOnly: true // Disable all write operations
}
}
```
### Logging Configuration
```javascript
{
logging: {
level: "info", // error, warn, info, debug
file: "./mysql-mcp.log" // Optional log file path
}
}
```
## Security Features
- **Input Validation**: All inputs are validated using Joi schemas
- **SQL Injection Prevention**: Uses parameterized queries exclusively
- **Table Access Control**: Configurable allow/block lists for tables
- **Row Limiting**: Configurable maximum rows per query
- **Read-Only Mode**: Option to disable all write operations
- **Connection Pooling**: Secure connection management with timeouts
- **Audit Logging**: Comprehensive logging of all operations and security events
## Error Handling
The server includes comprehensive error handling:
- Database connection errors
- Invalid SQL queries
- Permission denied operations
- Configuration validation errors
- Runtime exceptions
All errors are logged with context and returned as structured responses.
## Contributing
Contributions are welcome! Please ensure all security best practices are maintained and add appropriate tests for new features.
## Support
For issues and questions, please create an issue in the GitHub repository.
