mysql-optimizer-mcp-server
Advanced tools
MCP server for MySQL database analysis and query optimization
A Model Context Protocol (MCP) server that provides MySQL database analysis and query optimization tools with read-only access.
npm install -g mysql-optimizer-mcp-server
git clone https://github.com/yourusername/mysql-optimizer-mcp-server.git
cd mysql-optimizer-mcp-server
npm install
Set these environment variables for database connection:
DB_HOST=localhost # Database host (default: localhost)
DB_PORT=3306 # Database port (default: 3306)
DB_USER=your_username # Database username
DB_PASSWORD=your_password # Database password
DB_NAME=your_database # Database name
npm start
show_table_detailsGet comprehensive information about a table.
Parameters:
table_name (string): Name of the table to analyzeReturns:
analyze_queryIntelligently analyze SQL queries with three-tier safety handling.
Parameters:
query (string): SQL query to analyzelimit (number): Maximum rows to return for executed queries (default: 100, max: 1000)Three-Tier Safety Model:
Returns:
suggest_indexesGet index suggestions for a table based on query patterns.
Parameters:
table_name (string): Name of the tablewhere_columns (array): Columns frequently used in WHERE conditionsReturns:
get_table_dataRetrieve sample or latest data from a table.
Parameters:
table_name (string): Name of the tablelimit (number): Number of rows to return (default: 10, max: 100)order_by (string): Column to order byorder_direction (string): ASC or DESC (default: DESC)Returns:
list_tablesList all tables in the database with metadata.
Returns:
// Get table details
await callTool('show_table_details', { table_name: 'users' });
// Analyze any query - automatically handles safety
await callTool('analyze_query', {
query: 'SELECT * FROM users WHERE email = ? AND status = ?',
limit: 50
});
// Safe queries are executed with optimization suggestions
await callTool('analyze_query', {
query: 'SHOW TABLES'
});
await callTool('analyze_query', {
query: 'DESCRIBE users'
});
await callTool('analyze_query', {
query: 'EXPLAIN SELECT * FROM users WHERE email = "test@example.com"'
});
// Analyzable queries (INSERT/UPDATE) - analyzed but not executed
await callTool('analyze_query', {
query: 'INSERT INTO users (name, email) VALUES ("John", "john@example.com")'
});
// Returns: Structure analysis, optimization suggestions, safe alternatives
await callTool('analyze_query', {
query: 'UPDATE products SET price = price * 1.1 WHERE category = "electronics"'
});
// Returns: Structure analysis, affected columns, optimization suggestions
// Dangerous queries are completely rejected
await callTool('analyze_query', {
query: 'DELETE FROM users WHERE last_login < "2023-01-01"'
});
// Returns: {"status": "REJECTED", "reason": "Dangerous operations are completely blocked"}
// Get index suggestions
await callTool('suggest_indexes', {
table_name: 'users',
where_columns: ['email', 'status']
});
// Get latest reports
await callTool('get_table_data', {
table_name: 'reports',
limit: 20,
order_by: 'created_at',
order_direction: 'DESC'
});
The server uses a three-tier security model with different handling for each category:
// Input: SELECT COUNT(*) FROM users WHERE status = 'inactive'
// Output:
{
"query": "SELECT COUNT(*) FROM users WHERE status = 'inactive'",
"safety_status": "SAFE - EXECUTED",
"query_type": "SELECT",
"result": [{"COUNT(*)": 42}],
"row_count": 1,
"optimization_suggestions": [...]
}
// Input: UPDATE products SET price = price * 1.1 WHERE category = 'electronics'
// Output:
{
"query": "UPDATE products SET price = price * 1.1 WHERE category = 'electronics'",
"safety_status": "ANALYZABLE - NOT EXECUTED",
"structure_analysis": {
"type": "UPDATE",
"tables": ["products"],
"columns": ["price"],
"conditions": ["category = 'electronics'"]
},
"analysis_recommendations": [
{
"type": "analysis",
"message": "UPDATE operations modify existing data",
"alternatives": [
"To preview affected rows: SELECT * FROM products WHERE category = 'electronics' LIMIT 10",
"To count affected rows: SELECT COUNT(*) FROM products WHERE category = 'electronics'"
]
}
],
"note": "This query was analyzed for structure and optimization but was NOT executed."
}
// Input: DELETE FROM users WHERE status = 'inactive'
// Output:
{
"query": "DELETE FROM users WHERE status = 'inactive'",
"status": "REJECTED",
"reason": "Dangerous operations are completely blocked",
"violations": ["DELETE: DELETE operations remove data from tables"],
"message": "Operations like DROP, DELETE, TRUNCATE, ALTER, CREATE, etc. are completely blocked for security.",
"allowed_operations": ["SELECT", "SHOW", "DESCRIBE", "DESC", "EXPLAIN", "HELP"],
"analyzable_operations": ["INSERT", "UPDATE"]
}
Add to your MCP client configuration:
{
"mcpServers": {
"mysql-optimizer": {
"command": "npx",
"args": ["mysql-optimizer-mcp-server"],
"env": {
"DB_HOST": "localhost",
"DB_PORT": "3306",
"DB_USER": "your_username",
"DB_PASSWORD": "your_password",
"DB_NAME": "your_database"
}
}
}
}
If you have uv installed, you can also use:
{
"mcpServers": {
"mysql-optimizer": {
"command": "uvx",
"args": ["mysql-optimizer-mcp-server"],
"env": {
"DB_HOST": "localhost",
"DB_PORT": "3306",
"DB_USER": "your_username",
"DB_PASSWORD": "your_password",
"DB_NAME": "your_database"
}
}
}
}
For local development or custom installations:
{
"mcpServers": {
"mysql-optimizer": {
"command": "node",
"args": ["/path/to/mysql-optimizer-mcp-server/index.js"],
"env": {
"DB_HOST": "localhost",
"DB_PORT": "3306",
"DB_USER": "your_username",
"DB_PASSWORD": "your_password",
"DB_NAME": "your_database"
}
}
}
}
The server provides suggestions for:
All suggestions are provided as SQL statements that you can review and execute manually.# mcp-db
FAQs
MCP server for MySQL database analysis and query optimization
We found that mysql-optimizer-mcp-server demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Product
Socket Fix 2.0 brings targeted CVE remediation, smarter upgrade planning, and broader ecosystem support to help developers get to zero alerts.
Security News
Socket CEO Feross Aboukhadijeh joins Risky Business Weekly to unpack recent npm phishing attacks, their limited impact, and the risks if attackers get smarter.
Product
Socket’s new Tier 1 Reachability filters out up to 80% of irrelevant CVEs, so security teams can focus on the vulnerabilities that matter.