MySQL Database Server

# Setting Up MCP MySQL Server with Codex CLI This guide shows you how to integrate the MCP MySQL Server with Codex CLI for command-line AI-powered database interactions. ## Prerequisites - Node.js 18 or higher - MySQL database running - Codex CLI installed ## Step 1: Install and Build ```bash cd mcp-mysql-server npm install npm run build ``` ## Step 2: Configure Environment Create a `.env` file in your project directory: ```env # MySQL Connection MYSQL_HOST=localhost MYSQL_PORT=3306 MYSQL_USER=your_username MYSQL_PASS=your_password MYSQL_DB=your_database # Safety Settings (recommended to start with read-only) ALLOW_INSERT_OPERATION=false ALLOW_UPDATE_OPERATION=false ALLOW_DELETE_OPERATION=false ``` ## Step 3: Configure Codex CLI Add the MCP server to your Codex CLI configuration file (typically `~/.config/codex/config.json` or similar): ```json { "mcpServers": { "mysql": { "command": "node", "args": ["/absolute/path/to/mcp-mysql-server/dist/index.js"], "env": { "MYSQL_HOST": "localhost", "MYSQL_PORT": "3306", "MYSQL_USER": "your_username", "MYSQL_PASS": "your_password", "MYSQL_DB": "your_database", "ALLOW_INSERT_OPERATION": "false", "ALLOW_UPDATE_OPERATION": "false", "ALLOW_DELETE_OPERATION": "false" } } } } ``` ## Step 4: Start Codex CLI ```bash codex ``` The MCP MySQL server should automatically connect. ## Step 5: Verify Connection ```bash # In Codex CLI > Show me the MySQL database information ``` ## Command-Line Usage Examples ### Database Exploration ```bash # List all tables > What tables are in the database? # Show table structure > Describe the users table # Get database info > What's the database connection info? ``` ### Data Queries ```bash # Simple query > Select all users # Filtered query > Find users where email contains 'gmail.com' # Aggregate query > Count the number of orders per customer # Join query > Show me orders with customer names ``` ### Data Analysis ```bash # Statistics > Calculate average order value # Trends > Show monthly revenue for the past year # Comparisons > Compare sales between product categories # Summary reports > Generate a summary of user activity ``` ### Working with Query Results ```bash # Export format > Give me the top 10 products as a CSV # Pretty tables > Show me user statistics as a markdown table # JSON output > Export the entire users table as JSON ``` ## Automation Scripts ### Shell Script Integration Create a script `db-report.sh`: ```bash #!/bin/bash echo "Generating database report..." codex << EOF Connect to MySQL and generate a report including: 1. Total number of tables 2. Row count for each table 3. Top 10 largest tables by row count 4. Database size information Format it as a markdown report. EOF ``` ### Scheduled Reports Using cron: ```cron # Daily database summary at 9 AM 0 9 * * * /path/to/db-report.sh > /path/to/reports/daily-$(date +\%Y\%m\%d).md ``` ## Advanced Use Cases ### Data Migration Helper ```bash > Help me migrate data from the old_users table to the users table, mapping old field names to new ones ``` ### Schema Documentation ```bash > Document all tables in the database with their relationships and create an ERD in markdown format ``` ### Query Optimization ```bash > Analyze this query and suggest optimizations: SELECT * FROM orders WHERE created_at > '2024-01-01' ``` ### Data Validation ```bash > Check for data inconsistencies in the users table: - NULL values in required fields - Invalid email formats - Duplicate entries ``` ## Safety Modes ### Read-Only Mode (Recommended Default) Perfect for: - Data analysis - Report generation - Schema exploration - Query development Configuration: ```env ALLOW_INSERT_OPERATION=false ALLOW_UPDATE_OPERATION=false ALLOW_DELETE_OPERATION=false ``` ### Development Mode For testing with write access: ```env ALLOW_INSERT_OPERATION=true ALLOW_UPDATE_OPERATION=true ALLOW_DELETE_OPERATION=false # Keep delete disabled for safety ``` ### Full Access Mode (Use with Caution) Only for trusted environments: ```env ALLOW_INSERT_OPERATION=true ALLOW_UPDATE_OPERATION=true ALLOW_DELETE_OPERATION=true ``` **Note**: DROP, TRUNCATE, and ALTER are always blocked for safety. ## CLI Flags and Options ### Environment Override ```bash # Override database temporarily MYSQL_DB=testing codex # Connect to different host MYSQL_HOST=prod-db.example.com codex ``` ### Verbose Mode To see MCP server logs: ```bash # Check stderr for connection info codex 2>&1 | tee codex-session.log ``` ## Batch Operations ### Process Multiple Queries ```bash # queries.txt List all tables Show schema for users Count total rows in orders Calculate sum of order amounts ``` ```bash cat queries.txt | while read query; do echo "Query: $query" echo "$query" | codex --one-shot echo "---" done ``` ## Integration Examples ### CI/CD Pipeline ```yaml # .github/workflows/db-check.yml name: Database Validation on: [push] jobs: validate: runs-on: ubuntu-latest steps: - uses: actions/checkout@v2 - name: Setup Node.js uses: actions/setup-node@v2 with: node-version: '18' - name: Install MCP MySQL Server run: | cd mcp-mysql-server npm install npm run build - name: Run Database Checks env: MYSQL_HOST: ${{ secrets.DB_HOST }} MYSQL_USER: ${{ secrets.DB_USER }} MYSQL_PASS: ${{ secrets.DB_PASS }} MYSQL_DB: ${{ secrets.DB_NAME }} run: | codex << EOF Validate the database schema matches our requirements: 1. All required tables exist 2. Primary keys are defined 3. Foreign key constraints are valid EOF ``` ### Monitoring Scripts ```bash #!/bin/bash # db-health-check.sh THRESHOLD=1000 count=$(codex --one-shot "Count rows in error_logs table" | grep -oP '\d+') if [ "$count" -gt "$THRESHOLD" ]; then echo "WARNING: Error log table has $count rows" # Send alert curl -X POST https://alerts.example.com/notify \ -d "Database error_logs exceeds threshold: $count rows" fi ``` ## Troubleshooting ### Server Not Starting ```bash # Test the server directly node dist/index.js # Check for errors echo '{}' | node dist/index.js ``` ### Connection Issues ```bash # Test MySQL connection mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS $MYSQL_DB -e "SELECT 1" # Verify environment variables env | grep MYSQL ``` ### Permission Errors ```bash # Check MySQL user permissions mysql -u root -p -e "SHOW GRANTS FOR 'your_username'@'localhost'" ``` ### Debug Mode ```bash # Enable verbose logging DEBUG=* codex ``` ## Best Practices 1. **Use Read-Only by Default**: Start with all write operations disabled 2. **Parameterized Queries**: Always use parameter binding for user input 3. **Test in Development**: Test queries in dev environment first 4. **Backup Before Writes**: Always backup before enabling write operations 5. **Monitor Logs**: Keep an eye on MCP server stderr output 6. **Limit Permissions**: Give MySQL user only necessary permissions 7. **Rotate Credentials**: Regularly update database passwords ## Performance Tips 1. **Connection Pooling**: The server uses connection pooling automatically 2. **Limit Result Sets**: Use LIMIT clauses for large tables 3. **Index Usage**: Ask Codex to help optimize queries with proper indexes 4. **Batch Operations**: Process multiple similar queries together 5. **Async Operations**: Use background jobs for long-running reports ## Security Considerations 1. **Secure .env Files**: Never commit `.env` files to version control 2. **Use Environment Variables**: Prefer env vars over config files for secrets 3. **Network Security**: Use SSL/TLS for remote database connections 4. **User Permissions**: Create dedicated MySQL users with minimal permissions 5. **Audit Logs**: Monitor query execution through MySQL logs 6. **Rate Limiting**: Consider adding rate limits for production use ## Next Steps - Explore [usage-examples.md](./usage-examples.md) for more query patterns - Read the [main README](../README.md) for API documentation - Check [claude-code-setup.md](./claude-code-setup.md) for GUI setup