MySQL MCP Server Guide: Setup and Queries

-- 1. Create dedicated user with strong password authentication
CREATE USER 'mcp_reader'@'%' IDENTIFIED WITH caching_sha2_password BY 'Secure_Reader_Password_2026!';

-- 2. Grant SELECT privileges only on designated application databases
GRANT SELECT, SHOW VIEW ON application_db.* TO 'mcp_reader'@'%';

-- 3. Grant schema introspection rights
GRANT SELECT ON information_schema.tables TO 'mcp_reader'@'%';
GRANT SELECT ON information_schema.columns TO 'mcp_reader'@'%';

-- 4. Apply execution constraints to prevent resource starvation
ALTER USER 'mcp_reader'@'%' WITH MAX_QUERIES_PER_HOUR 5000 MAX_USER_CONNECTIONS 10;

FLUSH PRIVILEGES;

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/opt/mcp-servers/mysql/dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "mcp_reader",
        "MYSQL_PASSWORD": "Secure_Reader_Password_2026!",
        "MYSQL_DATABASE": "application_db"
      }
    }
  }
}

{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["./dist/index.js"],
      "env": {
        "MYSQL_HOST": "127.0.0.1",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "mcp_reader",
        "MYSQL_PASSWORD": "Secure_Reader_Password_2026!",
        "MYSQL_DATABASE": "application_db"
      }
    }
  }
}

[mcp_servers.mysql]
command = "node"
args = ["/opt/mcp-servers/mysql/dist/index.js"]

[mcp_servers.mysql.env]
MYSQL_HOST = "127.0.0.1"
MYSQL_PORT = "3306"
MYSQL_USER = "mcp_reader"
MYSQL_PASSWORD = "Secure_Reader_Password_2026!"
MYSQL_DATABASE = "application_db"

import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import { CallToolRequestSchema, ListToolsRequestSchema } from '@modelcontextprotocol/sdk/types.js';
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST || 'localhost',
  port: parseInt(process.env.MYSQL_PORT || '3306', 10),
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 5,
  idleTimeout: 30000,
  queueLimit: 0,
  charset: 'utf8mb4'
});

const server = new Server(
  { name: 'mysql-mcp-server', version: '1.0.0' },
  { capabilities: { tools: {} } }
);

server.setRequestHandler(ListToolsRequestSchema, async () => {
  return {
    tools: [
      {
        name: 'list_tables',
        description: 'List all table names available in the current database.',
        inputSchema: { type: 'object', properties: {} }
      },
      {
        name: 'describe_table',
        description: 'Retrieve column names, data types, nullability, and primary keys for a table.',
        inputSchema: {
          type: 'object',
          properties: {
            table_name: { type: 'string', description: 'Target table name' }
          },
          required: ['table_name']
        }
      },
      {
        name: 'query',
        description: 'Execute a read-only SQL SELECT query. Maximum 200 records returned.',
        inputSchema: {
          type: 'object',
          properties: {
            sql: { type: 'string', description: 'SQL SELECT query string' }
          },
          required: ['sql']
        }
      }
    ]
  };
});

server.setRequestHandler(CallToolRequestSchema, async (request) => {
  const { name, arguments: args } = request.params;

  try {
    if (name === 'list_tables') {
      const [rows] = await pool.query('SHOW TABLES');
      return {
        content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }]
      };
    }

    if (name === 'describe_table') {
      const tableName = args?.table_name as string;
      const [rows] = await pool.query('DESCRIBE ??', [tableName]);
      return {
        content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }]
      };
    }

    if (name === 'query') {
      const sql = (args?.sql as string).trim();

      // Guardrail: Enforce SELECT only
      if (!sql.toLowerCase().startsWith('select') && !sql.toLowerCase().startsWith('show')) {
        return {
          isError: true,
          content: [{ type: 'text', text: 'Error: Only SELECT and SHOW statements are permitted.' }]
        };
      }

      const boundedSql = sql.toLowerCase().includes('limit') ? sql : `${sql} LIMIT 200`;
      const [rows]: [any[], any] = await pool.query(boundedSql);

      if (rows.length === 0) {
        return { content: [{ type: 'text', text: 'Query returned 0 rows.' }] };
      }

      const headers = Object.keys(rows[0]);
      const headerLine = `| ${headers.join(' | ')} |`;
      const dividerLine = `| ${headers.map(() => '---').join(' | ')} |`;
      const dataLines = rows
        .map(row => `| ${headers.map(h => String(row[h] ?? 'NULL')).join(' | ')} |`)
        .join('\n');

      return {
        content: [{ type: 'text', text: `${headerLine}\n${dividerLine}\n${dataLines}` }]
      };
    }

    throw new Error(`Unrecognized tool: ${name}`);
  } catch (err: any) {
    return {
      isError: true,
      content: [{ type: 'text', text: `Database Error: ${err.message}` }]
    };
  }
});

async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
}

main().catch((err) => {
  process.stderr.write(`Fatal error: ${err.message}\n`);
  process.exit(1);
});

{
  "jsonrpc": "2.0",
  "id": "mysql-query-101",
  "method": "tools/call",
  "params": {
    "name": "describe_table",
    "arguments": {
      "table_name": "orders"
    }
  }
}

{
  "jsonrpc": "2.0",
  "id": "mysql-query-101",
  "result": {
    "content": [
      {
        "type": "text",
        "text": "[\n  {\"Field\": \"id\", \"Type\": \"bigint unsigned\", \"Null\": \"NO\", \"Key\": \"PRI\"},\n  {\"Field\": \"total_amount\", \"Type\": \"decimal(10,2)\", \"Null\": \"NO\", \"Key\": \"\"}\n]"
      }
    ]
  }
}