npm package: @naganpm/mysql-mcp-server
Description: MCP server for local MySQL database access
Author: Naga Vadlapudi
Latest version: 2.0.5

# Quick Start

**step1:** Configure this in `.claude.json` globally.

```
"mcpServers": {
    "mysql-mcp": {
      "command": "npx",
      "args": [
        "-y",
        "@naganpm/mysql-mcp-server"
      ]
    }
  }
```

**step2:** Add `mysql-config.json` under `config` folder - under the directory you are working on.  

> sample `mysql-config.json` (You can change this config to any host and credentials and restart claude)
>   ```
>    {
>      "host": "localhost",
>      "port": 3306,
>      "user": "johndoe",
>      "password": "j8dka72047",
>      "database": "master"
>  }
>  ```
  


# MySQL MCP Server

A Model Context Protocol (MCP) server for accessing local MySQL databases. This server provides read and write capabilities for MySQL databases through standardized MCP tools.

## Features

- **Interactive Setup**: Easy configuration wizard for MySQL credentials
- **Environment Variables**: Support for configuration via environment variables (ideal for MCP usage)
- **Secure Connection**: Connection pooling and secure credential storage
- **Full CRUD Operations**: Support for SELECT, INSERT, UPDATE, and DELETE operations
- **Database Exploration**: List tables, describe table structures, and show databases
- **Connection Testing**: Built-in connection health checks
- **Prepared Statements**: Safe parameterized queries to prevent SQL injection

## Prerequisites

- Node.js 18.0.0 or higher
- MySQL server running locally
- Valid MySQL user credentials with appropriate permissions

## Installation

For package consumers:

```bash
npm install @naganpm/mysql-mcp-server
```

For development:

1. Clone or download this repository
2. Install dependencies:
   ```bash
   npm install
   ```

## Setup

### Option 1: Manual Configuration (Recommended)

Create a `config` directory in your project root and add a `mysql-config.json` file:

```bash
mkdir -p config
```

Create `config/mysql-config.json` with your MySQL connection details:

```json
{
    "host": "localhost",
    "port": 3306,
    "user": "your_username",
    "password": "your_password",
    "database": "your_database",
    "connectionLimit": 10,
    "acquireTimeout": 60000,
    "timeout": 60000
}
```

### Option 2: Interactive Setup Wizard

If you have access to the package source, you can run the interactive configuration wizard:

```bash
npm run setup
```

The setup wizard will prompt you for:
- MySQL host (default: localhost)
- MySQL port (default: 3306)
- Username
- Password (hidden input)
- Database name (with auto-detection of available databases)

The configuration will be saved to `config/mysql-config.json` in your project root directory.

## Environment Variables

As an alternative to the interactive setup, you can configure the server using environment variables. This is especially useful for MCP integrations where file-based configuration may cause startup delays.

### Supported Environment Variables

| Variable | Alternative | Default | Description |
|----------|-------------|---------|-------------|
| `MYSQL_HOST` | `DB_HOST` | `localhost` | MySQL server hostname |
| `MYSQL_PORT` | `DB_PORT` | `3306` | MySQL server port |
| `MYSQL_USER` | `DB_USER` | `root` | MySQL username |
| `MYSQL_PASSWORD` | `DB_PASSWORD` | `""` | MySQL password |
| `MYSQL_DATABASE` | `DB_DATABASE`, `MYSQL_DB`, `DB_NAME` | `""` | Database name |
| `MYSQL_CONNECTION_LIMIT` | - | `10` | Maximum number of connections in pool |
| `MYSQL_ACQUIRE_TIMEOUT` | - | `60000` | Connection acquire timeout (ms) |
| `MYSQL_TIMEOUT` | - | `60000` | Query timeout (ms) |

### Configuration Priority

The server loads configuration in this order:
1. **Environment Variables** (highest priority)
2. **JSON file** (`config/mysql-config.json` in your project root) as fallback

If any MySQL environment variable is set, the server will use environment-based configuration exclusively.

## Usage

### Starting the Server

```bash
npm start
```

Or for development with auto-restart:

```bash
npm run dev
```

### Available MCP Tools

#### 1. `mysql_query`
Execute SELECT queries to read data from the database.

**Parameters:**
- `query` (string, required): The SQL SELECT query
- `params` (array, optional): Parameters for prepared statements

**Example:**
```sql
SELECT * FROM users WHERE age > ?
```
Parameters: `["25"]`

#### 2. `mysql_insert`
Execute INSERT statements to add new data.

**Parameters:**
- `query` (string, required): The SQL INSERT statement
- `params` (array, optional): Parameters for prepared statements

**Example:**
```sql
INSERT INTO users (name, email, age) VALUES (?, ?, ?)
```
Parameters: `["John Doe", "john@example.com", "30"]`

#### 3. `mysql_update`
Execute UPDATE statements to modify existing data.

**Parameters:**
- `query` (string, required): The SQL UPDATE statement
- `params` (array, optional): Parameters for prepared statements

**Example:**
```sql
UPDATE users SET email = ? WHERE id = ?
```
Parameters: `["newemail@example.com", "1"]`

#### 4. `mysql_delete`
Execute DELETE statements to remove data.

**Parameters:**
- `query` (string, required): The SQL DELETE statement
- `params` (array, optional): Parameters for prepared statements

**Example:**
```sql
DELETE FROM users WHERE id = ?
```
Parameters: `["1"]`

#### 5. `mysql_show_tables`
List all tables in the current database.

**Parameters:** None

#### 6. `mysql_describe_table`
Show the structure of a specific table.

**Parameters:**
- `table_name` (string, required): Name of the table to describe

#### 7. `mysql_show_databases`
List all available databases.

**Parameters:** None

#### 8. `mysql_test_connection`
Test the database connection status.

**Parameters:** None

## Configuration File

The configuration is stored in `config/mysql-config.json` in your project root directory with the following structure:

```json
{
    "host": "localhost",
    "port": 3306,
    "user": "your_username",
    "password": "your_password",
    "database": "your_database",
    "connectionLimit": 10,
    "acquireTimeout": 60000,
    "timeout": 60000
}
```

## Security Considerations

- Passwords are stored in plain text in the configuration file. Ensure proper file permissions.
- Use prepared statements (parameterized queries) to prevent SQL injection.
- Grant minimal required permissions to the MySQL user account.
- Keep the configuration file out of version control.

## Troubleshooting

### Connection Issues

1. **"No database configuration found"**
   - Create a `config/mysql-config.json` file in your project root directory with your MySQL connection details
   - Or run the setup command from the package directory to create the configuration

2. **"Failed to connect to MySQL"**
   - Verify MySQL server is running
   - Check host, port, username, and password
   - Ensure the MySQL user has appropriate permissions

3. **"Database does not exist"**
   - Verify the database name is correct
   - Create the database if it doesn't exist
   - Check user permissions for the database

### Permission Issues

Ensure your MySQL user has the following permissions:
- `SELECT` for read operations
- `INSERT` for adding data
- `UPDATE` for modifying data
- `DELETE` for removing data
- `SHOW DATABASES` for listing databases
- `SHOW TABLES` for listing tables

Grant permissions example:
```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES;
```

## Development

The server is built using:
- [@modelcontextprotocol/sdk](https://github.com/modelcontextprotocol/typescript-sdk) - MCP Server SDK
- [mysql2](https://github.com/sidorares/node-mysql2) - MySQL client for Node.js

### Project Structure

```
mysql-mcp/
├── src/
│   ├── index.js       # Main MCP server implementation
│   ├── database.js    # Database connection and query management
│   ├── config.js      # Configuration management
│   └── setup.js       # Interactive setup wizard
├── package.json
├── config/
│   └── mysql-config.json  # Generated after setup (in consumer's project)
└── README.md
```

## Note
- Use `mysql-config.json` only for database configurations
- Tested and works only with CLAUDE

## Claude MCP Configuration

### Option 1: Using Environment Variables (Recommended)

Configure the server using environment variables in your Claude settings. This method is faster and avoids potential timeout issues during MCP initialization.

**`.claude/settings.json` or project configuration:**
```json
{
  "mcpServers": {
    "mysql-local-mcp-server": {
      "command": "npx",
      "args": ["@naganpm/mysql-mcp-server"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "",
        "MYSQL_DATABASE": "your_database_name"
      }
    }
  }
}
```

**Benefits of environment variable configuration:**
- ✅ Faster startup (no file I/O during initialization)
- ✅ Avoids 30-second MCP timeout issues
- ✅ More suitable for containerized environments
- ✅ Secure credential management


### Option 2: Using JSON Configuration File

If you prefer file-based configuration, first run the setup wizard, then configure Claude to use the server:

1. **Setup the configuration:**
   ```bash
   npm run setup
   ```

2. **Claude configuration:**
   ```json
   {
     "mcpServers": {
       "mysql-local-mcp-server": {
         "command": "node",
         "args": ["/path/to/your/mysql-mcp/src/stdio-server.js"]
       }
     }
   }
   ```

**Note:** Replace `/path/to/your/mysql-mcp/` with the actual path to your project directory.

## Quick Start Example

Here's a complete example of setting up the MySQL MCP server in your project:

1. **Install the package:**
   ```bash
   npm install @naganpm/mysql-mcp-server
   ```

2. **Create your configuration:**
   ```bash
   mkdir -p config
   cat > config/mysql-config.json << EOF
   {
       "host": "localhost",
       "port": 3306,
       "user": "root",
       "password": "your_password",
       "database": "your_database"
   }
   EOF
   ```

3. **Use in your MCP configuration:**
   ```json
   {
     "mcpServers": {
       "mysql-local": {
         "command": "npx",
         "args": ["@naganpm/mysql-mcp-server"]
       }
     }
   }
   ```

The server will automatically detect and use the `config/mysql-config.json` file from your project root.

## License

MIT License