Create an MCP Server for MySQL Database

June 16, 2026

Create an MCP Server for MySQL Database

Introduction

In this tutorial, we will create a Model Context Protocol MCP server for a MySQL database using Python.

MCP allows AI applications to connect with external tools and data sources in a standardized way. The official Python SDK supports building MCP servers that expose tools, resources, and prompts to LLM applications.

Here, our MCP server will connect to a MySQL database and provide safe tools such as:

List all tables
Show table structure
Fetch student records
Search students by course

What We Will Build

AI Assistant
     ↓
MCP Client
     ↓
Python MCP Server
     ↓
MySQL Database

The AI assistant will not directly access MySQL. Instead, it will call controlled MCP tools.

Step 1: Install Requirements

Create a project folder:

mkdir mysql-mcp-server
cd mysql-mcp-server

Create a virtual environment:

python -m venv venv

Activate it:

venv\Scripts\activate

For macOS/Linux:

source venv/bin/activate

Install required packages:

pip install mcp mysql-connector-python python-dotenv

Step 2: Create MySQL Database

mysql -u root -p

Create database:

CREATE DATABASE college_db;
USE college_db;

Create a student table:

CREATE TABLE students (
    roll_no INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(50),
    semester INT,
    email VARCHAR(100)
);

Insert sample records:

INSERT INTO students VALUES
(101, 'Amit Sharma', 'MCA', 2, 'amit@example.com'),
(102, 'Neha Verma', 'BCA', 4, 'neha@example.com'),
(103, 'Ravi Kumar', 'MCA', 1, 'ravi@example.com'),
(104, 'Priya Singh', 'BSc CS', 3, 'priya@example.com');

Step 3: Create Environment File

Create a file named .env:

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=college_db

Do not hard-code passwords directly inside Python code.

Step 4: Create MCP Server File

Create a file named server.py.

import os
import mysql.connector
from dotenv import load_dotenv
from mcp.server.fastmcp import FastMCP

load_dotenv()

mcp = FastMCP("MySQL MCP Server")


def get_connection():
    """
    Create and return a MySQL database connection.
    """
    return mysql.connector.connect(
        host=os.getenv("MYSQL_HOST"),
        user=os.getenv("MYSQL_USER"),
        password=os.getenv("MYSQL_PASSWORD"),
        database=os.getenv("MYSQL_DATABASE")
    )


@mcp.tool()
def list_tables() -> list:
    """
    Return the list of tables in the MySQL database.
    """
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute("SHOW TABLES")
    tables = [table[0] for table in cursor.fetchall()]

    cursor.close()
    conn.close()

    return tables


@mcp.tool()
def describe_table(table_name: str) -> list:
    """
    Return the structure of a given table.
    """

    allowed_tables = ["students"]

    if table_name not in allowed_tables:
        return [{"error": "Access denied for this table"}]

    conn = get_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(f"DESCRIBE {table_name}")
    structure = cursor.fetchall()

    cursor.close()
    conn.close()

    return structure


@mcp.tool()
def get_all_students() -> list:
    """
    Return all student records.
    """
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT roll_no, name, course, semester, email
        FROM students
        LIMIT 50
    """)

    students = cursor.fetchall()

    cursor.close()
    conn.close()

    return students


@mcp.tool()
def get_student_by_roll_no(roll_no: int) -> dict:
    """
    Return student details using roll number.
    """
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT roll_no, name, course, semester, email
        FROM students
        WHERE roll_no = %s
    """, (roll_no,))

    student = cursor.fetchone()

    cursor.close()
    conn.close()

    if student:
        return student

    return {"message": "Student not found"}


@mcp.tool()
def search_students_by_course(course: str) -> list:
    """
    Search students by course name.
    """
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT roll_no, name, course, semester, email
        FROM students
        WHERE course = %s
        LIMIT 50
    """, (course,))

    students = cursor.fetchall()

    cursor.close()
    conn.close()

    return students


@mcp.resource("college://database-info")
def database_info() -> str:
    """
    Return basic information about the college database.
    """
    return """
    Database Name: college_db
    Main Table: students
    Purpose: Stores student academic records
    Available Tools:
    - list_tables
    - describe_table
    - get_all_students
    - get_student_by_roll_no
    - search_students_by_course
    """


if __name__ == "__main__":
    mcp.run()

Step 5: Run the MCP Server

python server.py

The MCP server will start and wait for client requests.

Step 6: Test Using MCP Inspector

Use the MCP Inspector:

npx @modelcontextprotocol/inspector python server.py

The inspector will open a local testing interface. You should see tools such as:

list_tables
describe_table
get_all_students
get_student_by_roll_no
search_students_by_course

Example Tool Calls

1. List Tables

Input:

{}

Output:

["students"]

2. Get Student by Roll Number

Input:

{
  "roll_no": 101
}

Output:

{
  "roll_no": 101,
  "name": "Amit Sharma",
  "course": "MCA",
  "semester": 2,
  "email": "amit@example.com"
}

3. Search Students by Course

Input:

{
  "course": "MCA"
}

Output:

[
  {
    "roll_no": 101,
    "name": "Amit Sharma",
    "course": "MCA",
    "semester": 2,
    "email": "amit@example.com"
  },
  {
    "roll_no": 103,
    "name": "Ravi Kumar",
    "course": "MCA",
    "semester": 1,
    "email": "ravi@example.com"
  }
]

Why We Use Parameterized Queries

Notice this query:

cursor.execute("""
    SELECT roll_no, name, course, semester, email
    FROM students
    WHERE roll_no = %s
""", (roll_no,))

This is safer than directly joining user input into SQL strings.

Avoid this:

query = "SELECT * FROM students WHERE roll_no = " + roll_no

Direct string concatenation may lead to SQL injection attacks.

Important Security Practices

When exposing a database through MCP, follow these rules:

Use a dedicated MySQL user with limited permissions.
Avoid DROP, DELETE, UPDATE, and unrestricted INSERT tools in beginner projects.
Use parameterized SQL queries.
Restrict allowed tables.
Never expose passwords in code.
Limit query results using LIMIT.
Do not allow raw SQL from the AI assistant.

The MCP specification allows servers to expose tools that language models can invoke to interact with external systems such as databases and APIs, so access control is important.

Create a Read-Only MySQL User

For safer use, create a read-only user:

CREATE USER 'mcp_reader'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT ON college_db.* TO 'mcp_reader'@'localhost';
FLUSH PRIVILEGES;

Then update .env:

MYSQL_HOST=localhost
MYSQL_USER=mcp_reader
MYSQL_PASSWORD=StrongPassword123!
MYSQL_DATABASE=college_db

Project Structure

mysql-mcp-server/
│
├── server.py
├── .env
└── venv/

Common Errors and Solutions

Error 1: Access Denied

Check username and password in .env.

Access denied for user

Error 2: Unknown Database

Make sure the database exists:

SHOW DATABASES;

Error 3: Module Not Found

Install packages again:

pip install mcp mysql-connector-python python-dotenv

Practical Applications

A MySQL MCP server can be used for:

Student record lookup
Attendance analysis
Course information retrieval
Inventory database access
Library management systems
Placement record search
Academic dashboard generation

Conclusion

In this tutorial, we created a Python MCP server for MySQL. We connected Python with MySQL, exposed controlled database operations as MCP tools, added a database resource, and tested the server using MCP Inspector.

This is a powerful starting point for building AI assistants that can interact with structured databases safely.