# Directory Structure
```
├── .gitignore
├── index.js
├── package-lock.json
├── package.json
└── README.md
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | node_modules/
2 | *.log
3 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # MySQL MCP Server
2 |
3 | This project implements an MCP (Model Context Protocol) server for working with MySQL database.
4 |
5 | ## Repository
6 |
7 | This project is available on GitHub:
8 | https://github.com/vitalyDV/mysql-mcp
9 |
10 | ### Clone the repository
11 |
12 | ```bash
13 | git clone https://github.com/vitalyDV/mysql-mcp.git
14 | cd mysql-mcp
15 | npm install
16 | ```
17 |
18 | ## add config to mcp.json
19 | ```json
20 | {
21 | "mcpServers": {
22 | "mysql_mcp_readonly": {
23 | "command": "node",
24 | "args": [
25 | "./mysql-mcp/index.js"
26 | ],
27 | "env": {
28 | "MYSQL_HOST": "127.0.0.1",
29 | "MYSQL_PORT": "3306",
30 | "MYSQL_USER": "root",
31 | "MYSQL_PASS": "",
32 | "MYSQL_DB": "db",
33 | }
34 | }
35 | }
36 | }
37 | ```
38 |
39 | ## Environment Variables
40 |
41 | - `MYSQL_HOST` - MySQL server host
42 | - `MYSQL_PORT` - MySQL server port
43 | - `MYSQL_USER` - MySQL username
44 | - `MYSQL_PASS` - MySQL password
45 | - `MYSQL_DB` - MySQL database name
46 |
47 | ## Available MCP tools
48 |
49 | - `query` - execute SQL queries (only SELECT, SHOW, EXPLAIN, DESCRIBE)
50 | - `table-schema` - get table structure
51 | - `list-tables` - get list of all tables in the database
52 |
53 | ## Available MCP resources
54 |
55 | - `table://{name}` - get data from the specified table (up to 100 rows)
56 |
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "mysql-mcp",
3 | "version": "1.0.0",
4 | "description": "MySQL MCP server for database interaction",
5 | "main": "index.js",
6 | "type": "module",
7 | "scripts": {
8 | "start": "node index.js",
9 | "dev": "nodemon index.js"
10 | },
11 | "bin": {
12 | "mysql-mcp": "./index.js"
13 | },
14 | "repository": {
15 | "type": "git",
16 | "url": "https://github.com/vitalyDV/mysql-mcp.git"
17 | },
18 | "homepage": "https://github.com/vitalyDV/mysql-mcp",
19 | "bugs": {
20 | "url": "https://github.com/vitalyDV/mysql-mcp/issues"
21 | },
22 | "keywords": [
23 | "mcp",
24 | "mysql",
25 | "database",
26 | "claude",
27 | "cursor"
28 | ],
29 | "author": "",
30 | "license": "ISC",
31 | "dependencies": {
32 | "@modelcontextprotocol/sdk": "^1.9.0",
33 | "mysql2": "^2.3.3",
34 | "nodemon": "^3.1.9",
35 | "zod": "^3.22.4"
36 | }
37 | }
```
--------------------------------------------------------------------------------
/index.js:
--------------------------------------------------------------------------------
```javascript
1 | #!/usr/bin/env node
2 |
3 | import { McpServer, ResourceTemplate } from '@modelcontextprotocol/sdk/server/mcp.js';
4 | import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
5 | // Remove HttpServerTransport import as it's not found
6 | // import { HttpServerTransport } from "@modelcontextprotocol/sdk/server/http.js";
7 | import { z } from 'zod';
8 | import mysql from 'mysql2/promise';
9 |
10 | // Check for required environment variables
11 | const requiredEnvVars = ['MYSQL_HOST', 'MYSQL_PORT', 'MYSQL_USER', 'MYSQL_DB'];
12 | const missingEnvVars = requiredEnvVars.filter((varName) => !process.env[varName]);
13 |
14 | if (missingEnvVars.length > 0) {
15 | console.error('❌ Missing required environment variables:', missingEnvVars.join(', '));
16 | process.exit(1);
17 | }
18 |
19 | // Create MySQL configuration from environment variables
20 | const mysqlConfig = {
21 | host: process.env.MYSQL_HOST,
22 | port: parseInt(process.env.MYSQL_PORT, 3306),
23 | user: process.env.MYSQL_USER,
24 | password: process.env.MYSQL_PASS || '',
25 | database: process.env.MYSQL_DB,
26 | waitForConnections: true,
27 | connectionLimit: 10,
28 | queueLimit: 0,
29 | };
30 |
31 | // Variable to store connection pool
32 | let pool;
33 |
34 | // Function to initialize MySQL connection
35 | async function initMySqlConnection() {
36 | try {
37 | pool = mysql.createPool(mysqlConfig);
38 | const connection = await pool.getConnection();
39 | connection.release();
40 | console.log('Successfully connected to MySQL');
41 | return true;
42 | } catch (error) {
43 | console.error('Error connecting to MySQL:', error);
44 | return false;
45 | }
46 | }
47 |
48 | // Function to execute MySQL query
49 | async function executeQuery(query, params = []) {
50 | try {
51 | if (!pool) {
52 | await initMySqlConnection();
53 | }
54 |
55 | const [rows] = await pool.query(query, params);
56 | const result = Array.isArray(rows) ? rows : [rows];
57 |
58 | return {
59 | success: true,
60 | result,
61 | };
62 | } catch (error) {
63 | console.error('Error executing query:', error);
64 | return {
65 | success: false,
66 | error: error.message,
67 | };
68 | }
69 | }
70 |
71 | // Function to get database schema
72 | async function getSchema(table = null) {
73 | try {
74 | if (table) {
75 | // Get information about specific table
76 | const query = `SHOW COLUMNS FROM ${pool.escapeId(table)}`;
77 | const [columns] = await pool.query(query);
78 | return {
79 | success: true,
80 | schema: columns,
81 | };
82 | } else {
83 | // Get list of all tables
84 | const query = `
85 | SELECT
86 | TABLE_NAME,
87 | ENGINE,
88 | TABLE_ROWS,
89 | DATA_LENGTH,
90 | AUTO_INCREMENT
91 | FROM information_schema.TABLES
92 | WHERE TABLE_SCHEMA = ?
93 | `;
94 | const [tables] = await pool.query(query, [mysqlConfig.database]);
95 | return {
96 | success: true,
97 | schema: tables,
98 | };
99 | }
100 | } catch (error) {
101 | console.error('Error getting schema:', error);
102 | return {
103 | success: false,
104 | error: error.message,
105 | };
106 | }
107 | }
108 |
109 | // Create MCP server
110 | const server = new McpServer({
111 | name: 'MySQL MCP',
112 | version: '1.0.0',
113 | });
114 |
115 | // Add SQL query execution tool
116 | server.tool('query', { query: z.string() }, async ({ query }) => {
117 | // Check that query starts with SELECT, SHOW, EXPLAIN or DESCRIBE
118 | const lowercaseQuery = query.trim().toLowerCase();
119 | if (
120 | !lowercaseQuery.startsWith('select') &&
121 | !lowercaseQuery.startsWith('show') &&
122 | !lowercaseQuery.startsWith('explain') &&
123 | !lowercaseQuery.startsWith('describe')
124 | ) {
125 | return {
126 | content: [
127 | {
128 | type: 'text',
129 | text: 'Only SELECT, SHOW, EXPLAIN and DESCRIBE queries are allowed',
130 | },
131 | ],
132 | isError: true,
133 | };
134 | }
135 |
136 | const result = await executeQuery(query);
137 | if (result.success) {
138 | return {
139 | content: [
140 | {
141 | type: 'text',
142 | text: JSON.stringify(result.result, null, 2),
143 | },
144 | ],
145 | };
146 | } else {
147 | return {
148 | content: [
149 | {
150 | type: 'text',
151 | text: `Error: ${result.error}`,
152 | },
153 | ],
154 | isError: true,
155 | };
156 | }
157 | });
158 |
159 | // Add tool for getting table schema
160 | server.tool('table-schema', { table: z.string() }, async ({ table }) => {
161 | const result = await getSchema(table);
162 | if (result.success) {
163 | return {
164 | content: [
165 | {
166 | type: 'text',
167 | text: JSON.stringify(result.schema, null, 2),
168 | },
169 | ],
170 | };
171 | } else {
172 | return {
173 | content: [
174 | {
175 | type: 'text',
176 | text: `Error: ${result.error}`,
177 | },
178 | ],
179 | isError: true,
180 | };
181 | }
182 | });
183 |
184 | // Add tool for listing all tables
185 | server.tool('list-tables', {}, async () => {
186 | const result = await getSchema();
187 | if (result.success) {
188 | return {
189 | content: [
190 | {
191 | type: 'text',
192 | text: JSON.stringify(result.schema, null, 2),
193 | },
194 | ],
195 | };
196 | } else {
197 | return {
198 | content: [
199 | {
200 | type: 'text',
201 | text: `Error: ${result.error}`,
202 | },
203 | ],
204 | isError: true,
205 | };
206 | }
207 | });
208 |
209 | // Resource for accessing table data
210 | server.resource('table', new ResourceTemplate('table://{name}', { list: undefined }), async (uri, { name }) => {
211 | const result = await executeQuery(`SELECT * FROM ${pool.escapeId(name)} LIMIT 100`);
212 | if (result.success) {
213 | return {
214 | contents: [
215 | {
216 | uri: uri.href,
217 | text: JSON.stringify(result.result, null, 2),
218 | },
219 | ],
220 | };
221 | } else {
222 | return {
223 | contents: [
224 | {
225 | uri: uri.href,
226 | text: `Error getting data from table ${name}: ${result.error}`,
227 | },
228 | ],
229 | };
230 | }
231 | });
232 |
233 | // Initialize database connection before starting
234 | await initMySqlConnection();
235 |
236 | // Start server with stdio transport
237 | const transport = new StdioServerTransport();
238 | await server.connect(transport);
239 |
```