# Directory Structure
```
├── .env.example
├── .gitignore
├── .npmignore
├── Dockerfile
├── LICENSE
├── package-lock.json
├── package.json
├── README.md
├── smithery.yaml
├── src
│ └── index.ts
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
[](https://smithery.ai/server/@sussa3007/mysql-mcp)
<a href="https://glama.ai/mcp/servers/@sussa3007/mysql-mcp">
<img width="380" height="200" src="https://glama.ai/mcp/servers/@sussa3007/mysql-mcp/badge" />
</a>
# MySQL MCP Server
[](https://opensource.org/licenses/MIT)
A Model Context Protocol (MCP) server for MySQL databases that enables AI models to interact with MySQL databases through a structured interface.
## Overview
The MySQL MCP Server provides a bridge between AI models and MySQL databases, allowing AI agents to query and analyze MySQL data. This implementation follows the Model Context Protocol specification and offers both web server and CLI modes of operation.
## Features
- MySQL database connection management
- SQL query execution
- Table listing and structure inspection
- Database listing and selection
- Real-time status monitoring via SSE (Server-Sent Events)
- Web interface for testing MCP tools
- Support for both stdio and SSE transport methods
- Docker deployment ready
## Installation
```bash
# Global installation
npm install -g mysql-mcp
# Local installation
npm install mysql-mcp
```
## Using with AI Assistants
### Using the Published Server on Smithery.ai
The MySQL MCP Server is published on Smithery.ai and can be easily used with various AI assistants:
1. **Access the server**: Visit [https://smithery.ai/server/@sussa3007/mysql-mcp](https://smithery.ai/server/@sussa3007/mysql-mcp)
2. **Configure the server**:
- Set your MySQL database connection details:
- MYSQL_HOST
- MYSQL_PORT
- MYSQL_USER
- MYSQL_PASSWORD
- MYSQL_DATABASE
- MYSQL_READONLY (optional, set to True for read-only access)
3. **Connect with supported AI platforms**:
- Anthropic Claude
- Cursor AI
- Windsurf
- Cline
- Witsy
- Enconvo
- Goose
4. **Authentication**: Login to Smithery.ai to save your configuration and generate authentication tokens.
5. **Use in AI prompts**: Once connected, you can utilize MySQL tools in your AI conversations by asking the assistant to perform database operations.
### Using After Local Installation
To use a locally developed version:
1. Run `npm link` in your project directory
2. Configure your settings file as follows:
```json
{
"mcpServers": {
"mysql": {
"command": "node",
"args": ["mysql-mcp"],
"env": {
"MYSQL_HOST": "localhost",
"MYSQL_PORT": "3306",
"MYSQL_USER": "root",
"MYSQL_PASSWORD": "your_password",
"MYSQL_DATABASE": "your_database",
"MYSQL_READONLY": "true"
}
}
}
}
```
## Tools
### status
Check the current database connection status.
- **Inputs**: No parameters required
- **Returns**: Connection status information, including host, port, database, and username if connected.
### connect
Connect to a MySQL database.
- **Inputs**:
- host (optional string): Database server hostname or IP address
- port (optional string): Database server port
- user (optional string): Database username
- password (optional string): Database password
- database (optional string): Database name to connect to
- **Returns**: Connection success message or error details.
### disconnect
Close the current MySQL database connection.
- **Inputs**: No parameters required
- **Returns**: Disconnection success message or error details.
### query
Execute an SQL query on the connected database.
- **Inputs**:
- sql (string): SQL query to execute
- params (optional array): Parameters for prepared statements
- **Returns**: Query results as JSON or error message.
### list_tables
Get a list of tables in the current database.
- **Inputs**: No parameters required
- **Returns**: List of table names in the current database.
### describe_table
Get the structure of a specific table.
- **Inputs**:
- table (string): Name of the table to describe
- **Returns**: Table structure details including columns, types, keys, and other attributes.
### list_databases
Get a list of all accessible databases on the server.
- **Inputs**: No parameters required
- **Returns**: List of database names available on the server.
### use_database
Switch to a different database.
- **Inputs**:
- database (string): Name of the database to switch to
- **Returns**: Confirmation message or error details.
## Keywords
mysql, mcp, database, ai, model context protocol
## License
MIT
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
{
"compilerOptions": {
"target": "ES2022",
"module": "NodeNext",
"moduleResolution": "NodeNext",
"esModuleInterop": true,
"forceConsistentCasingInFileNames": true,
"strict": true,
"skipLibCheck": true,
"outDir": "dist",
"rootDir": "src",
"sourceMap": true,
"declaration": true
},
"include": ["src/**/*"],
"exclude": ["node_modules", "dist"]
}
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
# Smithery.ai configuration
startCommand:
type: stdio
configSchema:
# JSON Schema defining the configuration options for the MCP.
type: object
properties:
MYSQL_HOST:
type: string
description: MySQL server address
MYSQL_PORT:
type: string
description: MySQL server port
MYSQL_USER:
type: string
description: MySQL username
MYSQL_PASSWORD:
type: string
description: MySQL password
MYSQL_DATABASE:
type: string
description: Initial database
MYSQL_READONLY:
type: boolean
description: Read-only mode, set true to enable
commandFunction:
# A function that produces the CLI command to start the MCP on stdio.
|-
(config) => ({
"command": "node",
"args": [
"dist/index.js"
],
"env": {
MYSQL_HOST: config.MYSQL_HOST,
MYSQL_PORT: config.MYSQL_PORT,
MYSQL_USER: config.MYSQL_USER,
MYSQL_PASSWORD: config.MYSQL_PASSWORD,
MYSQL_DATABASE: config.MYSQL_DATABASE,
MYSQL_READONLY: config.MYSQL_READONLY.toString()
}
})
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
{
"name": "mysql-mcp-connect",
"version": "0.1.1",
"description": "MySQL MCP 서버 - Model Context Protocol로 MySQL 데이터베이스에 접근하는 도구",
"main": "dist/index.js",
"types": "dist/index.d.ts",
"bin": {
"mysql-mcp": "./dist/index.js"
},
"files": [
"dist",
"bin",
"public"
],
"scripts": {
"start": "node dist/index.js",
"dev": "ts-node src/index.ts",
"build": "tsc",
"stdio": "node dist/index.js --stdio",
"stdio:dev": "ts-node src/index.ts --stdio",
"prepublishOnly": "npm run build"
},
"keywords": [
"mcp",
"mysql",
"cursor",
"model-context-protocol",
"database",
"ai",
"tool"
],
"author": "sussa3007",
"license": "MIT",
"dependencies": {
"@modelcontextprotocol/sdk": "^1.7.0",
"cors": "^2.8.5",
"dotenv": "^16.4.7",
"express": "^4.18.2",
"express-sse": "^0.5.3",
"mysql2": "^3.14.0",
"zod": "^3.24.2"
},
"devDependencies": {
"@types/content-type": "^1.1.8",
"@types/cors": "^2.8.13",
"@types/express": "^4.17.17",
"@types/node": "^20.3.1",
"ts-node": "^10.9.1",
"typescript": "^5.1.3"
},
"engines": {
"node": ">=14.0.0"
}
}
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
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 { createConnection, Connection, RowDataPacket } from "mysql2/promise";
// Set up logging
const logger = {
info: (message: string) => console.log(`[INFO] ${message}`),
error: (message: string) => console.error(`[ERROR] ${message}`),
debug: (message: string) => console.debug(`[DEBUG] ${message}`)
};
// Query type enum for categorizing SQL operations
enum QueryType {
SELECT = "SELECT",
INSERT = "INSERT",
UPDATE = "UPDATE",
DELETE = "DELETE",
CREATE = "CREATE",
DROP = "DROP",
ALTER = "ALTER",
TRUNCATE = "TRUNCATE",
USE = "USE",
SHOW = "SHOW",
DESCRIBE = "DESCRIBE",
UNKNOWN = "UNKNOWN"
}
// Determine if query type is a write operation
function isWriteOperation(queryType: QueryType): boolean {
const writeOperations = [
QueryType.INSERT,
QueryType.UPDATE,
QueryType.DELETE,
QueryType.CREATE,
QueryType.DROP,
QueryType.ALTER,
QueryType.TRUNCATE
];
return writeOperations.includes(queryType);
}
// Get query type from SQL query
function getQueryType(query: string): QueryType {
const firstWord = query.trim().split(/\s+/)[0].toUpperCase();
return Object.values(QueryType).includes(firstWord as QueryType)
? (firstWord as QueryType)
: QueryType.UNKNOWN;
}
// Global connection state
let connection: Connection | null = null;
let connectionConfig = {
host: process.env.MYSQL_HOST || "localhost",
port: parseInt(process.env.MYSQL_PORT || "3306", 10),
user: process.env.MYSQL_USER || "root",
password: process.env.MYSQL_PASSWORD || "",
database: process.env.MYSQL_DATABASE || "",
readonly: process.env.MYSQL_READONLY === "true" || false
};
// Initialize MCP server
const server = new Server(
{
name: "mysql-mcp",
version: "0.1.0"
},
{
capabilities: {
tools: {}
}
}
);
// Helper function to get current connection or throw error if not connected
async function getConnection(): Promise<Connection> {
if (!connection) {
throw new Error(
"Database not connected. Please use the 'connect' tool first."
);
}
return connection;
}
// Connect to MySQL with current config
async function connectToDatabase(
config = connectionConfig
): Promise<Connection> {
try {
if (connection) {
logger.info("Closing existing connection before creating a new one");
await connection.end();
}
connection = await createConnection({
host: config.host,
port: config.port,
user: config.user,
password: config.password,
database: config.database,
multipleStatements: true // Allow multiple statements in one query
});
connectionConfig = config;
logger.info(`Connected to MySQL database at ${config.host}:${config.port}`);
return connection;
} catch (error) {
logger.error(`Failed to connect to database: ${error}`);
throw error;
}
}
// Validate query against readonly mode
async function executeQuery(sql: string, params: any[] = []): Promise<any> {
const conn = await getConnection();
// Check if in readonly mode and validate query type
if (connectionConfig.readonly) {
const queryType = getQueryType(sql);
if (isWriteOperation(queryType)) {
throw new Error(
"Server is in read-only mode. Write operations are not allowed."
);
}
}
// Execute the query
const [rows] = await conn.query(sql, params);
return rows;
}
// Define available tools
server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: "status",
description: "Check the current database connection status.",
inputSchema: {
type: "object",
properties: {
random_string: {
type: "string",
description: "Dummy parameter for no-parameter tools"
}
},
required: ["random_string"]
}
},
{
name: "connect",
description: "Connect to a MySQL database.",
inputSchema: {
type: "object",
properties: {
host: {
type: "string",
description: "Database server hostname or IP address"
},
port: { type: "string", description: "Database server port" },
user: { type: "string", description: "Database username" },
password: { type: "string", description: "Database password" },
database: {
type: "string",
description: "Database name to connect to"
}
}
}
},
{
name: "disconnect",
description: "Close the current MySQL database connection.",
inputSchema: {
type: "object",
properties: {
random_string: {
type: "string",
description: "Dummy parameter for no-parameter tools"
}
},
required: ["random_string"]
}
},
{
name: "query",
description: "Execute an SQL query on the connected database.",
inputSchema: {
type: "object",
properties: {
sql: { type: "string", description: "SQL query to execute" },
params: {
type: "array",
description: "Parameters for prepared statements",
items: { type: "string" }
}
},
required: ["sql"]
}
},
{
name: "list_tables",
description: "Get a list of tables in the current database.",
inputSchema: {
type: "object",
properties: {
random_string: {
type: "string",
description: "Dummy parameter for no-parameter tools"
}
},
required: ["random_string"]
}
},
{
name: "describe_table",
description: "Get the structure of a specific table.",
inputSchema: {
type: "object",
properties: {
table: {
type: "string",
description: "Name of the table to describe"
}
},
required: ["table"]
}
},
{
name: "list_databases",
description: "Get a list of all accessible databases on the server.",
inputSchema: {
type: "object",
properties: {
random_string: {
type: "string",
description: "Dummy parameter for no-parameter tools"
}
},
required: ["random_string"]
}
},
{
name: "use_database",
description: "Switch to a different database.",
inputSchema: {
type: "object",
properties: {
database: {
type: "string",
description: "Name of the database to switch to"
}
},
required: ["database"]
}
},
{
name: "set_readonly",
description: "Enable or disable read-only mode",
inputSchema: {
type: "object",
properties: {
readonly: {
type: "boolean",
description:
"Set to true to enable read-only mode, false to disable"
}
},
required: ["readonly"]
}
}
]
};
});
// Handle tool calls
server.setRequestHandler(CallToolRequestSchema, async (request) => {
try {
switch (request.params.name) {
case "status": {
if (!connection) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
connected: false,
message:
"Database not connected. Need to use 'connect' tool after checking current environment variable information. The password is sensitive information and is stored in an environment variable, so it is not required in the request parameters.",
host: connectionConfig.host,
port: connectionConfig.port,
user: connectionConfig.user,
database: connectionConfig.database,
readonly: connectionConfig.readonly
},
null,
2
)
}
],
isError: false
};
}
return {
content: [
{
type: "text",
text: JSON.stringify(
{
connected: true,
host: connectionConfig.host,
port: connectionConfig.port,
user: connectionConfig.user,
database: connectionConfig.database,
threadId: connection.threadId,
readonly: connectionConfig.readonly
},
null,
2
)
}
],
isError: false
};
}
case "connect": {
const args = request.params.arguments || {};
const newConfig = {
host: (args.host as string) || connectionConfig.host,
port: parseInt(
(args.port as string) || connectionConfig.port.toString()
),
user: (args.user as string) || connectionConfig.user,
password: (args.password as string) || connectionConfig.password,
database: (args.database as string) || connectionConfig.database,
readonly: connectionConfig.readonly // Maintain existing readonly setting
};
try {
await connectToDatabase(newConfig);
} catch (error) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
connected: false,
message:
"Database connection failed, please request with new information or check the environment variable information."
},
null,
2
)
}
],
isError: true
};
}
return {
content: [
{
type: "text",
text: `Successfully connected to MySQL at ${newConfig.host}:${
newConfig.port
}, database: ${newConfig.database}, read-only mode: ${
newConfig.readonly ? "enabled" : "disabled"
}`
}
],
isError: false
};
}
case "disconnect": {
if (!connection) {
return {
content: [{ type: "text", text: "Not connected to any database" }],
isError: false
};
}
await connection.end();
connection = null;
return {
content: [
{
type: "text",
text: "Successfully disconnected from database"
}
],
isError: false
};
}
case "query": {
try {
const sql = request.params.arguments?.sql as string;
const params = (request.params.arguments?.params as any[]) || [];
// Execute query with validation
const rows = await executeQuery(sql, params);
return {
content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
isError: false
};
} catch (error) {
return {
content: [
{
type: "text",
text:
error instanceof Error
? error.message
: "Unknown error occurred"
}
],
isError: true
};
}
}
case "list_tables": {
try {
const rows = await executeQuery("SHOW TABLES");
return {
content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
isError: false
};
} catch (error) {
return {
content: [
{
type: "text",
text:
error instanceof Error
? error.message
: "Unknown error occurred"
}
],
isError: true
};
}
}
case "describe_table": {
try {
const tableName = request.params.arguments?.table as string;
if (!tableName) {
throw new Error("Table name is required");
}
const rows = await executeQuery("DESCRIBE ??", [tableName]);
return {
content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
isError: false
};
} catch (error) {
return {
content: [
{
type: "text",
text:
error instanceof Error
? error.message
: "Unknown error occurred"
}
],
isError: true
};
}
}
case "list_databases": {
try {
const rows = await executeQuery("SHOW DATABASES");
return {
content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
isError: false
};
} catch (error) {
return {
content: [
{
type: "text",
text:
error instanceof Error
? error.message
: "Unknown error occurred"
}
],
isError: true
};
}
}
case "use_database": {
try {
const dbName = request.params.arguments?.database as string;
if (!dbName) {
throw new Error("Database name is required");
}
await executeQuery("USE ??", [dbName]);
connectionConfig.database = dbName;
return {
content: [
{
type: "text",
text: `Successfully switched to database: ${dbName}`
}
],
isError: false
};
} catch (error) {
return {
content: [
{
type: "text",
text:
error instanceof Error
? error.message
: "Unknown error occurred"
}
],
isError: true
};
}
}
case "set_readonly": {
try {
const readonly = request.params.arguments?.readonly as boolean;
if (readonly === undefined) {
throw new Error("readonly parameter is required");
}
connectionConfig.readonly = readonly;
return {
content: [
{
type: "text",
text: `Read-only mode ${readonly ? "enabled" : "disabled"}`
}
],
isError: false
};
} catch (error) {
return {
content: [
{
type: "text",
text:
error instanceof Error
? error.message
: "Unknown error occurred"
}
],
isError: true
};
}
}
default:
throw new Error(`Unknown tool: ${request.params.name}`);
}
} catch (error) {
logger.error(`Error handling request: ${error}`);
return {
content: [
{
type: "text",
text:
error instanceof Error ? error.message : "Unknown error occurred"
}
],
isError: true
};
}
});
async function main() {
logger.info("Starting MySQL MCP server...");
const transport = new StdioServerTransport();
await server.connect(transport);
logger.info("Server connected to transport");
}
// Handle termination signals
process.once("SIGTERM", () => {
logger.info("SIGTERM received, closing server");
// Close database connection if open
if (connection) {
connection.end().catch((err) => {
logger.error(`Error closing database connection: ${err}`);
});
}
server.close().then(() => {
logger.info("Server closed, exiting");
process.exit(0);
});
});
process.once("SIGINT", () => {
logger.info("SIGINT received, closing server");
// Close database connection if open
if (connection) {
connection.end().catch((err) => {
logger.error(`Error closing database connection: ${err}`);
});
}
server.close().then(() => {
logger.info("Server closed, exiting");
process.exit(0);
});
});
main().catch((error) => {
logger.error(
error instanceof Error ? error.message : "Unknown error occurred"
);
process.exit(1);
});
```