Local MySQL Database MCP Server

A TypeScript Model Context Protocol (MCP) server that exposes read-only, least-privileged access to a locally hosted MySQL database for approved AI-agent workflows.

• Documentation index
• Tool reference
• Architecture
• Configuration
• Security
• Database layer
• Logging and audit
• Development
• Threat model
• Project rules (PUKU.md)

Status

Implemented (Phases 0–8 of the plan). Unit, security, and integration tests all run against your local MySQL — no container runtime is required.

---

Table of contents

1. Prerequisites
2. Clone and first build
3. Set up MySQL and the read-only user
4. Configure the server
5. Verify the server
6. Connect an MCP client
   • Claude Code
   • Claude Desktop
   • Cursor IDE
   • VS Code (Copilot Chat / Continue)
   • puku CLI
   • Other stdio MCP clients
7. Troubleshooting
8. Tests, lint, build
9. Privacy and logging
10. License

---

Prerequisites

Tool	Version	Notes
Node.js	22 LTS	The repo pins 22 in .nvmrc and engines.node in package.json. Use nvm use.
npm	10+	Bundled with Node 22.
MySQL Server	8.x	Local install on 127.0.0.1:3306. Any flavor that supports information_schema reads works (MySQL 8, MariaDB 10.5+).
Git	recent	To clone the repo.

OS support: tested on Windows 11, macOS 14, and Ubuntu 22.04. The pool defaults to 127.0.0.1 (TCP loopback), not the Unix socket — works identically across platforms.

Required command-line tools

Make sure each of these resolves on your PATH:

node --version     # v22.x
npm --version      # 10.x
mysql --version    # 8.x
git --version

---

Clone and first build

# 1. Clone
git clone https://github.com/<your-org>/db-mcp-server.git
cd db-mcp-server

# 2. Use the pinned Node version
nvm install        # only if you don't already have Node 22
nvm use            # reads .nvmrc

# 3. Install dependencies
npm install

# 4. Sanity-check the source
npm run typecheck
npm run lint

# 5. Build the dist/ that MCP clients will invoke
npm run build

After this you should see a populated dist/ directory and the file dist/index.js will be the MCP server entrypoint. The shipped .mcp.json points at this exact path, so any MCP client that auto-reads .mcp.json (Claude Code, puku-cli, Cursor) will pick up the server as soon as dist/index.js exists.

---

Set up MySQL and the read-only user

This server connects with a dedicated, read-only MySQL account. It does not use your root or app credentials. The bootstrap script scripts/setup-mysql-user.sql creates the user mcp_readonly, creates the mavenmovies schema if it is missing, grants SELECT only, and revokes everything else.

Step 1 — edit the SQL script

Open scripts/setup-mysql-user.sql and replace the two placeholders at the top:

SET @mcp_user   = 'mcp_readonly';
SET @mcp_pass   = 'CHANGE_ME_BEFORE_RUNNING';   -- use a strong password
SET @mcp_schema = 'mavenmovies';                -- your schema name

Do not commit the real password. The repo's .gitignore already excludes .env; treat scripts/setup-mysql-user.sql the same way if you paste a real password into it.

Step 2 — apply the grants

Run as a MySQL administrator (root or equivalent):

mysql -u root -p < scripts/setup-mysql-user.sql

Step 3 — load a test schema (optional but recommended)

The mavenmovies schema is a small, free sample DB. If you don't already have it, you can download it from the official MySQL sample and load it:

# Example: load the official mavenmovies dump.
# Replace the path with wherever you saved it.
mysql -u root -p mavenmovies < /path/to/mavenmovies.sql

You can use any schema; just set MYSQL_DATABASE and MCP_ALLOWED_SCHEMAS in .env to match.

Step 4 — verify the user can read

mysql -u mcp_readonly -p -h 127.0.0.1 -e "SELECT COUNT(*) FROM mavenmovies.actor;"

You should see a count. The same connection should fail for any write:

mysql -u mcp_readonly -p -h 127.0.0.1 -e "DELETE FROM mavenmovies.actor WHERE 1=0;"
# ERROR 1142 (42000): DELETE command denied to user 'mcp_readonly'@'...' for table 'actor'

If the write succeeds, the grants were not applied correctly — re-run scripts/setup-mysql-user.sql after fixing it.

---

Configure the server

The server reads configuration in two layers, both of which you should set up:

Layer 1 — .env (secrets and connection details)

Copy the example file and edit the secrets:

cp .env.example .env

Open .env and fill in at least:

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_DATABASE=mavenmovies
MYSQL_USER=mcp_readonly
MYSQL_PASSWORD=<the password you set in step 1>
MCP_ALLOWED_SCHEMAS=mavenmovies

.env is gitignored, so this is the right place for the MySQL password.

The complete reference (every variable, defaults, validation rules, and safety overrides) lives in docs/CONFIGURATION.md.

Layer 2 — .mcp.json (MCP client wiring, committed)

A .mcp.json is checked into the repo root. It registers the server under the name mysql-db and points at the built entry point dist/index.js with a relative path. The env block lists non-secret configuration (loopback host, schema, policy, row cap, log level) and intentionally omits MYSQL_PASSWORD — the server picks that up from .env.

Most MCP clients (Claude Code, puku-cli, Cursor) read .mcp.json automatically. For clients that read a different config file, see Connect an MCP client below.

The full .env.example:

# ---- MySQL connection ----
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_DATABASE=example_db
MYSQL_USER=
MYSQL_PASSWORD=

# ---- Pool / timeouts (all bounded) ----
MYSQL_CONNECTION_LIMIT=5
MYSQL_CONNECT_TIMEOUT_MS=5000
MYSQL_QUERY_TIMEOUT_MS=3000

# ---- MCP policy ----
MCP_ALLOWED_SCHEMAS=mavenmovies
MCP_ALLOWED_TABLES=
MCP_MAX_ROWS=1000
MCP_AUDIT_LOG=

# ---- Logging ----
LOG_LEVEL=info

# ---- Safety overrides ----
ALLOW_REMOTE_MYSQL=0
ALLOW_WILDCARD_SCHEMAS=0

---

Verify the server

The server speaks stdio. There is no HTTP listener. To verify it boots and connects to MySQL without an MCP client, use the included health_check MCP method via any MCP client, or simply start it and watch the logs.

Quick stdio smoke test

In one terminal, start the server in dev mode (uses tsx, no build needed):

npm run dev

You should see one JSON log line on stderr like:

{"ts":"...","level":"info","msg":"boot","config":{"MYSQL_HOST":"127.0.0.1", ... "MYSQL_PASSWORD":"***", ...}}
{"ts":"...","level":"info","msg":"mcp.stdio.connected"}

The server is now waiting for an MCP JSON-RPC message on stdin. You can poke it with a hand-rolled request in another terminal:

# macOS / Linux
(echo '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"smoke","version":"0"}}}'; sleep 1; echo '{"jsonrpc":"2.0","method":"notifications/initialized"}'; echo '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"health_check","arguments":{}}}'; sleep 1) | node dist/index.js

You should see a JSON-RPC response with result.content[0].text containing {"ok":true,"latencyMs":<number>}. Press Ctrl+C to shut down cleanly.

A faster check is to wire it into one of the MCP clients below and call health_check from there.

---

Connect an MCP client

All clients below invoke the server as a stdio subprocess. They differ only in where they store their config file and which env-var names they use.

Path note (Windows): the examples below use the Windows path D:/projects/db-mcp-server/dist/index.js. On macOS/Linux substitute your own path (e.g. /Users/you/code/db-mcp-server/dist/index.js). The forward slashes work in JSON on every platform; do not escape them.

Secrets: never commit real MYSQL_PASSWORD / DB_PASSWORD values to the JSON config files below. Either use a placeholder and load the real value from your shell environment, or use a .env file consumed by the server. The server reads process.env, then loads .env via dotenv with override: false (the default), so any value already present in process.env wins — this is why shipped client configs can omit MYSQL_PASSWORD and still authenticate against the password in .env.

Claude Code

Claude Code reads MCP config from ~/.claude.json (user-wide) or .mcp.json in the project directory.

Project-local (recommended for this repo) — a .mcp.json is already shipped at the repo root and registers the server under the name mysql-db with a relative dist/index.js path, so no per-clone wiring is needed. Just npm run build, restart Claude Code, and the server will be loaded.

If you want to customize it (different schema, different user, etc.), edit .mcp.json directly. Keep MYSQL_PASSWORD unset there and let .env provide it — see the secrets callout above.

The shipped .mcp.json looks like this:

{
  "mcpServers": {
    "mysql-db": {
      "type": "stdio",
      "command": "node",
      "args": ["dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_DATABASE": "mavenmovies",
        "MYSQL_USER": "mcp_readonly",
        "MCP_ALLOWED_SCHEMAS": "mavenmovies",
        "MCP_MAX_ROWS": "1000",
        "LOG_LEVEL": "info",
        "ALLOW_REMOTE_MYSQL": "0",
        "ALLOW_WILDCARD_SCHEMAS": "0"
      }
    }
  }
}

Note: MYSQL_PASSWORD is intentionally absent from the committed .mcp.json. The server picks it up from .env at boot. If you set MYSQL_PASSWORD in .mcp.json to anything (including an empty string), it will override .env and may break auth.

User-wide — add the same mcpServers block to ~/.claude.json.

Restart Claude Code. Confirm the server is loaded with /mcp — you should see mysql-db listed with four tools (list_tables, describe_table, get_rows, health_check).

Claude Desktop

Edit the Claude Desktop config file:

• Windows: %APPDATA%\Claude\claude_desktop_config.json
• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Linux: ~/.config/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "local-mysql": {
      "command": "node",
      "args": ["D:/projects/db-mcp-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_DATABASE": "mavenmovies",
        "MYSQL_USER": "mcp_readonly",
        "MYSQL_PASSWORD": "<set-locally>",
        "MCP_ALLOWED_SCHEMAS": "mavenmovies"
      }
    }
  }
}

Fully quit and reopen Claude Desktop. The hammer icon should show the four tools.

Cursor IDE

Create or edit .cursor/mcp.json in the project root:

{
  "mcpServers": {
    "local-mysql": {
      "command": "node",
      "args": ["D:/projects/db-mcp-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_DATABASE": "mavenmovies",
        "MYSQL_USER": "mcp_readonly",
        "MYSQL_PASSWORD": "<set-locally>",
        "MCP_ALLOWED_SCHEMAS": "mavenmovies"
      }
    }
  }
}

In Cursor: Settings → MCP → local-mysql → Refresh. The four tools should appear under the tools list.

VS Code (Copilot Chat / Continue)

VS Code reads MCP config from .vscode/mcp.json in the workspace (Copilot Chat and Continue both support this format).

{
  "servers": {
    "local-mysql": {
      "type": "stdio",
      "command": "node",
      "args": ["D:/projects/db-mcp-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_DATABASE": "mavenmovies",
        "MYSQL_USER": "mcp_readonly",
        "MYSQL_PASSWORD": "<set-locally>",
        "MCP_ALLOWED_SCHEMAS": "mavenmovies"
      }
    }
  }
}

For Continue (.continue/config.json) the same server block goes under mcpServers:

{
  "mcpServers": [
    {
      "name": "local-mysql",
      "command": "node",
      "args": ["D:/projects/db-mcp-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_DATABASE": "mavenmovies",
        "MYSQL_USER": "mcp_readonly",
        "MYSQL_PASSWORD": "<set-locally>",
        "MCP_ALLOWED_SCHEMAS": "mavenmovies"
      }
    }
  ]
}

Reload the VS Code window after editing.

puku CLI

puku-cli reads MCP config from .mcp.json at the project root (loaded automatically) and from ~/.puku-cli/settings.json (global). The shipped .mcp.json in this repo already registers the server as mysql-db — no extra steps are required beyond npm run build and a session restart.

Auto-approval note: the first time a session launches the server, puku-cli will prompt you to approve the MCP server it discovered in .mcp.json. If you want to skip the prompt for this project, add the following to ~/.puku-cli/settings.json:

{
  "enableAllProjectMcpServers": true
}

Or, to approve only this one server explicitly:

{
  "enabledMcpjsonServers": ["mysql-db"]
}

For a user-wide install (one entry used across all your projects, with an absolute path), add this to ~/.puku-cli/settings.json:

{
  "mcpServers": {
    "local-mysql": {
      "type": "stdio",
      "command": "node",
      "args": ["D:/projects/db-mcp-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_DATABASE": "mavenmovies",
        "MYSQL_USER": "mcp_readonly",
        "MCP_ALLOWED_SCHEMAS": "mavenmovies"
      }
    }
  }
}

Verify with /mcp inside puku-cli; you should see mysql-db (project config) and/or local-mysql (user config) and four tools (list_tables, describe_table, get_rows, health_check).

Project-root-wide MCP configuration (.mcp.json)

A project-root-wide MCP configuration is the recommended way to wire this server into any client. The repo ships a .mcp.json at the project root that registers the server under the name mysql-db and points at the built entrypoint dist/index.js with a relative path, so the same config works for every contributor regardless of where they cloned the repo.

{
  "mcpServers": {
    "mysql-db": {
      "type": "stdio",
      "command": "node",
      "args": ["dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_DATABASE": "mavenmovies",
        "MYSQL_USER": "mcp_readonly",
        "MYSQL_CONNECTION_LIMIT": "5",
        "MYSQL_CONNECT_TIMEOUT_MS": "5000",
        "MYSQL_QUERY_TIMEOUT_MS": "3000",
        "MCP_ALLOWED_SCHEMAS": "mavenmovies",
        "MCP_ALLOWED_TABLES": "",
        "MCP_MAX_ROWS": "1000",
        "MCP_AUDIT_LOG": "",
        "LOG_LEVEL": "info",
        "ALLOW_REMOTE_MYSQL": "0",
        "ALLOW_WILDCARD_SCHEMAS": "0"
      }
    }
  }
}

Why project-root-wide?

• One file, every client. Claude Code, puku-cli, and Cursor all auto-read .mcp.json from the working directory. Drop the file in the repo root and every contributor gets the same server wiring with zero per-machine setup.
• Relative paths are portable. args: ["dist/index.js"] resolves against the project root, so the same JSON works on Windows, macOS, and Linux without edits. Use absolute paths (e.g. D:/projects/db-mcp-server/dist/index.js) only when you need to launch the server from outside the project root.
• Secrets stay out of version control. The shipped .mcp.json intentionally omits MYSQL_PASSWORD. The server reads .env at boot via dotenv with override: false, so any value already present in process.env wins — your .env provides the password and the committed config never has to.

Customizing for your environment

Edit the .mcp.json block in place when you need to:

• Target a different schema — change MYSQL_DATABASE and MCP_ALLOWED_SCHEMAS together (both must be set; the allowlist is enforced server-side).
• Use a different read-only user — change MYSQL_USER and the matching MYSQL_PASSWORD in .env.
• Raise/lower the row cap — change MCP_MAX_ROWS.
• Allow a non-loopback MySQL — change ALLOW_REMOTE_MYSQL to "1". The server still requires a non-empty MYSQL_PASSWORD.
• Allow wildcards in MCP_ALLOWED_SCHEMAS — change ALLOW_WILDCARD_SCHEMAS to "1". Off by default for safety.

Auto-loading per client

Client	Reads .mcp.json automatically?	Where to place it
Claude Code	Yes (project-local)	repo root (./.mcp.json) — already there
puku-cli	Yes (project-local)	repo root (./.mcp.json) — already there
Cursor IDE	Yes (project-local)	repo root (./.mcp.json) — already there
Claude Desktop	No — uses claude_desktop_config.json	see Claude Desktop
VS Code	No — uses .vscode/mcp.json	see VS Code
Continue	No — uses .continue/config.json	see VS Code

After editing .mcp.json, restart your client. Confirm the server is loaded with /mcp — you should see mysql-db listed with four tools (list_tables, describe_table, get_rows, health_check).

Other stdio MCP clients

Any MCP client that supports stdio transport can launch the server with:

command: node
args:    ["<absolute-path>/db-mcp-server/dist/index.js"]
env:     { MYSQL_HOST, MYSQL_PORT, MYSQL_DATABASE, MYSQL_USER,
           MYSQL_PASSWORD, MCP_ALLOWED_SCHEMAS, ... }

Use the env-var names listed in docs/CONFIGURATION.md. The server refuses to start against non-loopback hosts unless ALLOW_REMOTE_MYSQL=1, and refuses wildcards in MCP_ALLOWED_SCHEMAS unless ALLOW_WILDCARD_SCHEMAS=1.

---

Troubleshooting

Symptom	Cause	Fix
boot failed: Validation: Invalid configuration: MYSQL_DATABASE: ...	.env missing or env not loaded	Confirm .env exists at the path the server reads (project root); confirm each required var is set.
Validation: MYSQL_HOST=x is not loopback. Refusing to start unless ALLOW_REMOTE_MYSQL=1.	You set MYSQL_HOST to a non-loopback name	Use 127.0.0.1 (recommended), or set ALLOW_REMOTE_MYSQL=1 and make sure your MYSQL_USER grants match.
Validation: Wildcards in MCP_ALLOWED_SCHEMAS require ALLOW_WILDCARD_SCHEMAS=1.	MCP_ALLOWED_SCHEMAS=* (or contains *)	Use an explicit comma-separated list of schemas, or set ALLOW_WILDCARD_SCHEMAS=1.
connection: ER_ACCESS_DENIED_ERROR	Wrong user/password or user not bound to the host you're connecting from	Re-run scripts/setup-mysql-user.sql; verify with mysql -u mcp_readonly -p -h 127.0.0.1 -e 'SELECT 1'.
connection: ECONNREFUSED	MySQL not running, or wrong port	Start MySQL; verify with mysqladmin ping -h 127.0.0.1 -P 3306.
Timeout: Query exceeded 3000ms	Slow query, lock wait, or MYSQL_QUERY_TIMEOUT_MS too low	Raise MYSQL_QUERY_TIMEOUT_MS for ad-hoc work; check SHOW PROCESSLIST for blockers.
Authorization: Schema 'mavenmovies' is not in the allowlist	MCP_ALLOWED_SCHEMAS is empty or doesn't match	Edit .env, restart the server.
Authorization: Column 'mavenmovies.actor.password' cannot be queried without an explicit column allowlist	get_rows called without columnAllowlist	Pass a columnAllowlist listing every column you intend to read. Required, not optional. See docs/TOOLS.md.
MCP client lists zero tools from local-mysql	Stdio path wrong, or server crashed at startup	Run node dist/index.js directly; you should see mcp.stdio.connected on stderr. Confirm the absolute path in the client config exists.
Error: Cannot find module 'mysql2/promise'	npm install not run, or dist/ from before dependencies	npm install && npm run build.
MCP client shows mysql-db but zero tools, or doesn't show it at all after editing .mcp.json	Some clients only watch .mcp.json for changes made before the session started	Restart the client, or open /mcp once to force a config reload.
ER_ACCESS_DENIED_ERROR despite correct .env password	.mcp.json sets MYSQL_PASSWORD to an empty string, which overrides .env because dotenv does not override existing process.env	Remove the MYSQL_PASSWORD key from .mcp.json entirely so .env provides it.

---

Tests, lint, build

npm run typecheck     # strict TypeScript
npm run lint          # ESLint
npm run format:check  # Prettier
npm test              # unit + security + integration suites
npm run build         # produces dist/
npm start             # runs dist/index.js with source maps
npm run dev           # tsx src/index.ts (no build step)

Integration tests run against the MySQL you configured in .env (MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD). They assume the schema and grants from scripts/setup-mysql-user.sql are in place — the read-only user must be able to SELECT from every schema listed in MCP_ALLOWED_SCHEMAS.

Run a single suite:

npm run test:unit
npm run test:integration
npm run test:security

---

Privacy and logging

• Tool descriptions and error responses never include credentials, SQL strings, or result sets beyond what each tool is designed to return. See docs/TOOLS.md.
• The structured logger redacts well-known secret keys (password, passwd, token, secret, api[_-]?key, authorization, credential, access[_-]?token) and any string containing password=…. See docs/LOGGING.md.
• Set MCP_AUDIT_LOG=/path/to/audit.log to write JSONL audit events per tool call. Without it, audit events go to stderr.
• The server is loopback-only by default. To target a remote MySQL, you must set ALLOW_REMOTE_MYSQL=1 and the server still requires a non-empty MYSQL_PASSWORD.

---

License

MIT — see LICENSE.