暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 下一代DBA必备技能

下一代DBA必备技能

Bytebase 2025-03-28
37

下一代DBA必备技能

相信很多DBA都有私人珍藏例如各种常用SQL, 以下是我的一些珍藏,都在github里需要随时可取:

  • 《PostgreSQL (慢SQL|数据库整体变慢|性能抖动) 数据库性能分析与优化方法 - 珍藏级,建议收藏》
  • 《PostgreSQL AB表切换最佳实践 - 提高切换成功率，杜绝雪崩 - 珍藏级》
  • 《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》
  • 《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》
  • 《PostgreSQL 10 postgresql.conf 参数模板 - 珍藏级》
  • 《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
  • 《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级 - 数据库慢、卡死、连接爆增、慢查询多、OOM、crash、in recovery、崩溃等怎么办?怎么优化?怎么诊断?》

前面一篇文章介绍了 深入浅出 MCP 与 ACP：AI 时代的「连接器」与「协作网」  , 这些协议就像AI和其他应用的粘合剂, 核心目的是加速AI化进程, 让AI应用可以安全可靠的调用外部工具, 与其他应用进行协作.  另外还有一篇: 
为什么DBA会消失? 我的建议是什么? 我觉得在彻底消失之前, 可以再挣扎一下, 那就是如题所示自我革命: “下一代DBA必备技能: 开发MCP Server”.

甚至于, 开发MCP Server应该是数据库产品厂商自己的责任, 那就真没DBA啥事了. 就像openAPI一样, openMCP可能也会成为数据库产品的标配.

为了推进DBA AI化, 我们需要构建数据库的mcp server, 把过去的经验抽象成"function", 当然你也可以非常非常偷懒的进行抽象(开放所有SQL执行权限). 这样的话mcp client就可以通过mcp server开放的接口来接管数据库. 如果mcp server是高度抽象的，那么定制化任务剧本就更能体现DBA老手的能力了，剧本杀玩法参考：《留给DBA的时间不多了，PG智能体开源》

放眼望去：似乎各个行业都在打造人工岗位的AI替身。似乎各个应用都在开发mcp server，生怕被AI agent抛弃。

mcp 联通AI agent和应用则像bytebase dbhub (mcp server)提供的这张图, 非常形象:

 +------------------+    +--------------+    +------------------+  
 |                  |    |              |    |                  |  
 |                  |    |              |    |                  |  
 |  Claude Desktop  +--->+              +--->+    PostgreSQL    |  
 |                  |    |              |    |                  |  
 |      Cursor      +--->+    DBHub     +--->+    SQL Server    |  
 |                  |    |              |    |                  |  
 |     Other MCP    +--->+              +--->+     SQLite       |  
 |      Clients     |    |              |    |                  |  
 |                  |    |              +--->+     MySQL        |  
 |                  |    |              |    |                  |  
 |                  |    |              +--->+  Other Databases |  
 |                  |    |              |    |                  |  
 +------------------+    +--------------+    +------------------+  
      MCP Clients           MCP Server             Databases  

复制

例如将open WebUI或数据库管理软件等类AI前端连接到某数据库mcp server，可实现在前端AI应用与数据库的结合:

  • https://mcp.so/server/open-webui-mcp-connector

mcp server 例子1 DBHub

https://github.com/modelcontextprotocol/servers 这个开源项目里展示了很多产品的mcp server, 包括数据库的.

bytebase DBHub是其中之一, 天舟老哥果然是非常赶潮流的存在, 下面是这个项目的readme, 关注Supported Matrix.


  

    


DBHub is a universal database gateway implementing the Model Context Protocol (MCP) server interface. This gateway allows MCP-compatible clients to connect to and explore different databases.

 +------------------+    +--------------+    +------------------+  
 |                  |    |              |    |                  |  
 |                  |    |              |    |                  |  
 |  Claude Desktop  +--->+              +--->+    PostgreSQL    |  
 |                  |    |              |    |                  |  
 |      Cursor      +--->+    DBHub     +--->+    SQL Server    |  
 |                  |    |              |    |                  |  
 |     Other MCP    +--->+              +--->+     SQLite       |  
 |      Clients     |    |              |    |                  |  
 |                  |    |              +--->+     MySQL        |  
 |                  |    |              |    |                  |  
 |                  |    |              +--->+  Other Databases |  
 |                  |    |              |    |                  |  
 +------------------+    +--------------+    +------------------+  
      MCP Clients           MCP Server             Databases  

复制

Demo SSE Endpoint

https://demo.dbhub.ai/sse connects a sample employee database. You can point Cursor or MCP Inspector to it to see it in action.

mcp-inspector

Supported Matrix

Database Resources

Resource Name
URI Format
PostgreSQL
MySQL
SQL Server
SQLite
schemas
db://schemas
✅
✅
✅
✅
tables_in_schema
db://schemas/{schemaName}/tables
✅
✅
✅
✅
table_structure_in_schema
db://schemas/{schemaName}/tables/{tableName}
✅
✅
✅
✅
indexes_in_table
db://schemas/{schemaName}/tables/{tableName}/indexes
✅
✅
✅
✅

Database Tools

Tool
Command Name
PostgreSQL
MySQL
SQL Server
SQLite
Execute Query
run_query
✅
✅
✅
✅
List Connectors
list_connectors
✅
✅
✅
✅

Prompt Capabilities

Prompt
Command Name
PostgreSQL
MySQL
SQL Server
SQLite
Generate SQL
generate_sql
✅
✅
✅
✅
Explain DB Elements
explain_db
✅
✅
✅
✅

Installation

Docker

# PostgreSQL example  
docker run --rm --init \  
   --name dbhub \  
   --publish 8080:8080 \  
   bytebase/dbhub \  
   --transport sse \  
   --port 8080 \  
   --dsn "postgres://user:password@localhost:5432/dbname?sslmode=disable"  

复制
# Demo mode with sample employee database  
docker run --rm --init \  
   --name dbhub \  
   --publish 8080:8080 \  
   bytebase/dbhub \  
   --transport sse \  
   --port 8080 \  
   --demo  

复制

NPM

# PostgreSQL example  
npx @bytebase/dbhub --transport sse --port 8080 --dsn "postgres://user:password@localhost:5432/dbname"  

复制
# Demo mode with sample employee database  
npx @bytebase/dbhub --transport sse --port 8080 --demo  

复制

Note: The demo mode includes a bundled SQLite sample "employee" database with tables for employees, departments, salaries, and more.

Claude Desktop

claude-desktop
  • Claude Desktop only supports stdio
     transport https://github.com/orgs/modelcontextprotocol/discussions/16
// claude_desktop_config.json  
{  
"mcpServers": {  
    "dbhub-postgres-docker": {  
      "command": "docker",  
      "args": [  
        "run",  
        "-i",  
        "--rm",  
        "bytebase/dbhub",  
        "--transport",  
        "stdio",  
        "--dsn",  
        // Use host.docker.internal as the host if connecting to the local db  
        "postgres://user:password@host.docker.internal:5432/dbname?sslmode=disable"
      ]  
    },  
    "dbhub-postgres-npx": {  
      "command": "npx",  
      "args": [  
        "-y",  
        "@bytebase/dbhub",  
        "--transport",  
        "stdio",  
        "--dsn",  
        "postgres://user:password@localhost:5432/dbname?sslmode=disable"
      ]  
    },  
    "dbhub-demo": {  
      "command": "npx",  
      "args": ["-y", "@bytebase/dbhub", "--transport", "stdio", "--demo"]  
    }  
  }  
}  

复制

Cursor

cursor
  • Cursor supports both stdio
     and sse
    .
  • Follow Cursor MCP guide and make sure to use Agent mode.

Usage

Configure your database connection

You can use DBHub in demo mode with a sample employee database for testing:

pnpm dev --demo  

复制

For real databases, a Database Source Name (DSN) is required. You can provide this in several ways:

  • Command line argument (highest priority):

    pnpm dev --dsn "postgres://user:password@localhost:5432/dbname?sslmode=disable"  

    复制

  • Environment variable (second priority):

    export DSN="postgres://user:password@localhost:5432/dbname?sslmode=disable"  
    pnpm dev  

    复制

  • Environment file (third priority):

    DSN=postgres://user:password@localhost:5432/dbname?sslmode=disable  

    复制
    • For development: Create .env.local
       with your DSN
    • For production: Create .env
       with your DSN

DBHub supports the following database connection string formats:

Database
DSN Format
Example
PostgreSQL
postgres://[user]:[password]@[host]:[port]/[database]
postgres://user:password@localhost:5432/dbname?sslmode=disable
SQLite
sqlite:///[path/to/file]
 or sqlite::memory:
sqlite:///path/to/database.db
 or sqlite::memory:
SQL Server
sqlserver://[user]:[password]@[host]:[port]/[database]
sqlserver://user:password@localhost:1433/dbname
MySQL
mysql://[user]:[password]@[host]:[port]/[database]
mysql://user:password@localhost:3306/dbname

Transport

  • stdio (default) - for direct integration with tools like Claude Desktop:

    npx @bytebase/dbhub --transport stdio --dsn "postgres://user:password@localhost:5432/dbname?sslmode=disable"  

    复制

  • sse - for browser and network clients:

    npx @bytebase/dbhub --transport sse --port 5678 --dsn "postgres://user:password@localhost:5432/dbname?sslmode=disable"  

    复制

Command line options

Option
Description
Default
demo
Run in demo mode with sample employee database
false
dsn
Database connection string
Required if not in demo mode
transport
Transport mode: stdio
 or sse
stdio
port
HTTP server port (only applicable when using --transport=sse
)
8080

The demo mode uses an in-memory SQLite database loaded with the sample employee database that includes tables for employees, departments, titles, salaries, department employees, and department managers. The sample database includes SQL scripts for table creation, data loading, and testing.

Development

  1. Install dependencies:

    pnpm install  

    复制

  2. Run in development mode:

    pnpm dev  

    复制

  3. Build for production:

    pnpm build  
    pnpm start --transport stdio --dsn "postgres://user:password@localhost:5432/dbname?sslmode=disable"  

    复制

Debug with MCP Inspector

stdio

# PostgreSQL example  
TRANSPORT=stdio DSN="postgres://user:password@localhost:5432/dbname?sslmode=disable" npx @modelcontextprotocol/inspector node path/to/dbhub/dist/index.js  

复制

SSE

# Start DBHub with SSE transport  
pnpm dev --transport=sse --port=8080  
  
# Start the MCP Inspector in another terminal  
npx @modelcontextprotocol/inspector  

复制

Connect to the DBHub server /sse
 endpoint

mcp server 例子2 mcp_server_milvus

milvus的 mcp server非常值得学习, 如果你想自己开发mcp server, 建议可以学习一下, 以下是这个项目的readme

MCP Server for Milvus

The Model Context Protocol (MCP) is an open protocol that enables seamless integration between LLM applications and external data sources and tools. Whether you're building an AI-powered IDE, enhancing a chat interface, or creating custom AI workflows, MCP provides a standardized way to connect LLMs with the context they need.

This repository contains a MCP server that provides access to Milvus vector database functionality.

Prerequisites

Before using this MCP server, ensure you have:

  • Python 3.10 or higher
  • A running Milvus instance (local or remote)
  • uv installed (recommended for running the server)

Usage

The recommended way to use this MCP server is to run it directly with uv
 without installation. This is how both Claude Desktop and Cursor are configured to use it in the examples below.

If you want to clone the repository:

git clone https://github.com/zilliztech/mcp-server-milvus.git  
cd mcp-server-milvus  

复制

Then you can run the server directly:

uv run src/mcp_server_milvus/server.py --milvus-uri http://localhost:19530  

复制

Supported Applications

This MCP server can be used with various LLM applications that support the Model Context Protocol:

  • Claude Desktop: Anthropic's desktop application for Claude
  • Cursor: AI-powered code editor with MCP support in its Composer feature
  • Custom MCP clients: Any application implementing the MCP client specification

Usage with Claude Desktop

  1. Install Claude Desktop from https://claude.ai/download

  2. Open your Claude Desktop configuration:

    • macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

  3. Add the following configuration:

{  
  "mcpServers": {  
    "milvus": {  
      "command": "/PATH/TO/uv",  
      "args": [  
        "--directory",  
        "/path/to/mcp-server-milvus/src/mcp_server_milvus",  
        "run",  
        "server.py",  
        "--milvus-uri",  
        "http://localhost:19530"
      ]  
    }  
  }  
}  

复制
  1. Restart Claude Desktop

Usage with Cursor

Cursor also supports MCP tools through its Agent feature in Composer. You can add the Milvus MCP server to Cursor in two ways:

Option 1: Using Cursor Settings UI

  1. Go to Cursor Settings
     > Features
     > MCP

  2. Click on the + Add New MCP Server
     button

  3. Fill out the form:

    ⚠️ Note: Use 127.0.0.1
     instead of localhost
     to avoid potential DNS resolution issues.

    • Type: Select stdio
       (since you're running a command)
    • Name: milvus
    • Command: /PATH/TO/uv --directory path/to/mcp-server-milvus/src/mcp_server_milvus run server.py --milvus-uri http://127.0.0.1:19530

Option 2: Using Project-specific Configuration (Recommended)

Create a .cursor/mcp.json
 file in your project root:

  1. Create the .cursor
     directory in your project root:

    mkdir -p path/to/your/project/.cursor  

    复制

  2. Create a mcp.json
     file with the following content:

    {  
      "mcpServers": {  
        "milvus": {  
          "command": "/PATH/TO/uv",  
          "args": [  
            "--directory",  
            "/path/to/mcp-server-milvus/src/mcp_server_milvus",  
            "run",  
            "server.py",  
            "--milvus-uri",  
            "http://127.0.0.1:19530"
          ]  
        }  
      }  
    }  

    复制

  3. Restart Cursor or reload the window

After adding the server, you may need to press the refresh button in the MCP settings to populate the tool list. The Composer Agent will automatically use the Milvus tools when relevant to your queries.

Verifying the Integration

To verify that Cursor has successfully integrated with your Milvus MCP server:

  1. Open Cursor Settings > Features > MCP
  2. Check that "Milvus" appears in the list of MCP servers
  3. Verify that the tools are listed (e.g., milvus_list_collections, milvus_vector_search, etc.)
  4. If the server is enabled but shows an error, check the Troubleshooting section below

Available Tools

The server provides the following tools:

Search and Query Operations

  • milvus_text_search
    : Search for documents using full text search

    • collection_name
      : Name of collection to search
    • query_text
      : Text to search for
    • limit
      : Maximum results (default: 5)
    • output_fields
      : Fields to include in results
    • drop_ratio
      : Proportion of low-frequency terms to ignore (0.0-1.0)
    • Parameters:

  • milvus_vector_search
    : Perform vector similarity search on a collection

    • collection_name
      : Name of collection to search
    • vector
      : Query vector
    • vector_field
      : Field containing vectors to search (default: "vector")
    • limit
      : Maximum results (default: 5)
    • output_fields
      : Fields to include in results
    • metric_type
      : Distance metric (COSINE, L2, IP) (default: "COSINE")
    • Parameters:

  • milvus_query
    : Query collection using filter expressions

    • collection_name
      : Name of collection to query
    • filter_expr
      : Filter expression (e.g. 'age > 20')
    • output_fields
      : Fields to include in results
    • limit
      : Maximum results (default: 10)
    • Parameters:

Collection Management

  • milvus_list_collections
    : List all collections in the database

  • milvus_create_collection
    : Create a new collection with specified schema

    • collection_name
      : Name for the new collection
    • collection_schema
      : Collection schema definition
    • index_params
      : Optional index parameters
    • Parameters:

  • milvus_load_collection
    : Load a collection into memory for search and query

    • collection_name
      : Name of collection to load
    • replica_number
      : Number of replicas (default: 1)
    • Parameters:

  • milvus_release_collection
    : Release a collection from memory

    • collection_name
      : Name of collection to release
    • Parameters:

Data Operations

  • milvus_insert_data
    : Insert data into a collection

    • collection_name
      : Name of collection
    • data
      : Dictionary mapping field names to lists of values
    • Parameters:

  • milvus_delete_entities
    : Delete entities from a collection based on filter expression

    • collection_name
      : Name of collection
    • filter_expr
      : Filter expression to select entities to delete
    • Parameters:

Environment Variables

  • MILVUS_URI
    : Milvus server URI (can be set instead of --milvus-uri)
  • MILVUS_TOKEN
    : Optional authentication token
  • MILVUS_DB
    : Database name (defaults to "default")

Development

To run the server directly:

uv run server.py --milvus-uri http://localhost:19530  

复制

Examples

Using Claude Desktop

Example 1: Listing Collections

What are the collections I have in my Milvus DB?  

复制

Claude will then use MCP to check this information on your Milvus DB.

I'll check what collections are available in your Milvus database.  
  
Here are the collections in your Milvus database:  
  
1. rag_demo  
2. test  
3. chat_messages  
4. text_collection  
5. image_collection  
6. customized_setup  
7. streaming_rag_demo  

复制

Example 2: Searching for Documents

Find documents in my text_collection that mention "machine learning"  

复制

Claude will use the full-text search capabilities of Milvus to find relevant documents:

I'll search for documents about machine learning in your text_collection.  
  
> View result from milvus-text-search from milvus (local)  
  
Here are the documents I found that mention machine learning:  
[Results will appear here based on your actual data]  

复制

Using Cursor

Example: Creating a Collection

In Cursor's Composer, you can ask:

Create a new collection called 'articles' in Milvus with fields for title (string), content (string), and a vector field (128 dimensions)  

复制

Cursor will use the MCP server to execute this operation:

I'll create a new collection called 'articles' with the specified fields.  
  
Collection 'articles' has been created successfully with the following schema:  
- title: string  
- content: string  
- vector: float vector[128]  

复制

Troubleshooting

Common Issues

Connection Errors

If you see errors like "Failed to connect to Milvus server":

  1. Verify your Milvus instance is running: docker ps
     (if using Docker)
  2. Check the URI is correct in your configuration
  3. Ensure there are no firewall rules blocking the connection
  4. Try using 127.0.0.1
     instead of localhost
     in the URI

Authentication Issues

If you see authentication errors:

  1. Verify your MILVUS_TOKEN
     is correct
  2. Check if your Milvus instance requires authentication
  3. Ensure you have the correct permissions for the operations you're trying to perform

Tool Not Found

If the MCP tools don't appear in Claude Desktop or Cursor:

  1. Restart the application
  2. Check the server logs for any errors
  3. Verify the MCP server is running correctly
  4. Press the refresh button in the MCP settings (for Cursor)

Getting Help

If you continue to experience issues:

  1. Check the GitHub Issues for similar problems
  2. Join the Zilliz Community Discord for support
  3. File a new issue with detailed information about your problem


参考

如果你对function call, tools call没什么概念, 可以看一下mistral下面这个文档:

  • https://docs.mistral.ai/capabilities/function_calling/

https://github.com/zilliztech/mcp-server-milvus/blob/main/src/mcp_server_milvus/server.py

https://github.com/modelcontextprotocol/servers/tree/main/src/postgres

https://mcp.so/server/postgres

https://github.com/bytebase/dbhub/

https://github.com/nahmanmate/postgresql-mcp-server

 

postgresqlbytebasecursordocker
文章转载自Bytebase，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

明明想执行的SQL是DELETE、UPDATE，但为什么看到的是SELECT（FDW的实现原理解析）
小满未满、
376次阅读
2025-03-19 23:11:26
PostgreSQL初/中/高级认证考试（3.15）通过考生公示
开源软件联盟PostgreSQL分会
344次阅读
2025-03-20 09:50:36
IvorySQL 4.4 发布 - 基于 PostgreSQL 17.4，增强平台支持
通讯员
219次阅读
2025-03-20 15:31:04
命名不规范，事后泪两行
xiongcc
200次阅读
2025-03-13 14:26:08
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
198次阅读
2025-04-07 12:14:29
PG vs MySQL 执行计划解读的异同点
进击的CJR
141次阅读
2025-03-21 10:50:08
版本发布｜ IvorySQL 4.4 发布
IvorySQL开源数据库社区
129次阅读
2025-03-13 09:52:33
PostgreSQL分区管理扩展——pg_partman
chirpyli
109次阅读
2025-03-19 15:48:31
手把手教你在 openKylin 上部署 IvorySQL 4.4
严少安
106次阅读
2025-03-27 20:41:28
postgresql+patroni+etcd高可用安装
necessary
105次阅读
2025-03-28 10:11:23