ODBC监控对应于Zabbix前端中的 数据库监控 监控项类型。
ODBC是C语言编写的中间件API,用于访问数据库管理系统(DBMS)。 ODBC是由Microsoft开发的,后来被移植到了其它平台。
Zabbix可以查询任何支持ODBC的数据库。实现这个目标,Zabbix不直接连接数据库,而是使用ODBC接口并在ODBC中设置的驱动程序。该功能允许出于多种目的,更加有效地监视不同的数据库。例如,检测特定的数据库队列、使用统计信息等。Zabbix支持unixODBC,是最常用的开源ODBC API实现之一。
更多信息请查看 ODBC检查的 已知问题 。
安装unixODBC的建议方法是使用Linux操作系统默认包库,在主流的Linux发行版中,默认情况下unixODBC包含在镜像库中。如果找不到,可以在 unixODBC 主页上获取:http://www.unixodbc.org/download.html.
在RedHat/Fedora系统上使用yum包管理器安装unixODBC:
使用zypper包管理器在基于SUSE的系统上安装unixODBC:
需要 unixODBC-devel 包来编译支持 unixODBC 的 Zabbix。
应该为将被监控的数据库安装一个 unixODBC 数据库驱动程序。 unixODBC 有一个支持的数据库和驱动程序列表:http://www.unixodbc.org/drivers.html。 在某些 Linux 发行版中,数据库驱动程序包含在软件包存储库中。在 RedHat/Fedora 的系统上使用 yum 包管理器安装 MySQL 数据库驱动程序:
shell> dnf install mysql-connector-odbc
在 SUSE 的系统上使用 zypper 包管理器安装 MySQL 数据库驱动程序:
zypper in MyODBC-unixODBC
To install the MySQL unixODBC database driver, use the package manager for the system of your choice:
# For Ubuntu/Debian systems:
apt install odbc-mariadb
# For RedHat/Fedora-based systems:
dnf install mysql-connector-odbc
# For SUSE-based systems:
zypper install MyODBC-unixODBC
To install the database driver without a package manager, refer to MySQL documentation.
To install the PostgreSQL unixODBC database driver for RedHat/Fedora-based systems, use the dnf
package manager:
For other systems, please refer to PostgreSQL documentation.
To install the unixODBC database driver, please refer to Oracle documentation.
To install the MSSQL unixODBC database driver for RedHat/Fedora-based systems, use the dnf
package manager:
For other systems, please refer to FreeTDS user guide.
ODBC配置是通过编辑 odbcinst.ini 和 odbc.ini 文件完成的,要验证配置文件的位置,输入:
odbcinst.ini 用于列出已安装的ODBC数据库驱动程序:
mysql Description = ODBC for MySQL Driver · = /usr/lib/libmyodbc5.so
参数详情:
属性 | 描述 |
---|---|
mysql | 数据库驱动名 |
Description | 数据库驱动描述 |
Driver | 数据库驱动程序库文件位置 |
odbc.ini 用于自定义数据源:
[test] Description = MySQL test database Driver = mysql Server = 127.0.0.1 User = root Password = Port = 3306 Database = zabbix
参数详情:
属性 | 描述 |
---|---|
test | 数据源名称 (DSN) |
Description | 数据源描述 |
Driver | 数据库驱动程序名称 - 在 odbcinst.ini 中指定 |
Server | 数据库服务器 IP/DNS |
User | 用于连接的数据库用户 |
Password | 数据库用户密码 |
Port | 数据库连接端口 |
Database | 数据库名称 |
要验证ODBC连接是否正常工作,应该测试到数据库的连接,这可以通过isql实用程序(包含在unixODBC包中)完成:
shell> isql test
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
The odbcinst.ini
file lists the installed ODBC database drivers. If odbcinst.ini
is missing, it is necessary to create it manually.
Parameter | Description |
---|---|
TEST_MYSQL | Database driver name. |
Description | Database driver description. |
Driver | Database driver library location. |
FileUsage | Determines whether the database driver supports connecting to a database server without the support for accessing local files (0); supports reading data from files (1); supports writing data to files (2). |
Threading | Thread serialization level. Supported for PostgreSQL. Since 1.6, if the driver manager is built with thread support, you may add another driver entry. |
The odbc.ini
file is used to configure data sources.
[TEST_MYSQL]
Description=MySQL Test Database
Driver=mysql
Server=127.0.0.1
User=root
Password=
Port=3306
Socket=
Database=zabbix
Parameter | Description |
---|---|
TEST_MYSQL | Data source name (DSN). |
Description | Data source description. |
Driver | Database driver name (as specified in odbcinst.ini ). |
Server | Database server IP/DNS. |
User | Database user for connection. |
Password | Database user password. |
Port | Database connection port. |
Socket | Database connection socket. |
Database | Database name. |
For other possible configuration parameter options, see MySQL documentation.
The odbc.ini
file for PostgreSQL may contain additional parameters:
[TEST_PSQL]
Description=PostgreSQL Test Database
Driver=postgresql
Username=zbx_test
Password=zabbix
Servername=127.0.0.1
Database=zabbix
Port=5432
ReadOnly=No
Protocol=7.4+
ShowOidColumn=No
FakeOidIndex=No
RowVersioning=No
ShowSystemTables=No
Fetch=Yes
BoolsAsChar=Yes
SSLmode=Require
ConnSettings=
Parameter | Description |
---|---|
ReadOnly | Specifies whether the database connection allows only read operations (SELECT queries) and restricts modifications (INSERT , UPDATE , and DELETE statements); useful for scenarios where data should remain unchanged. |
Protocol | PostgreSQL backend protocol version (ignored when using SSL connections). |
ShowOidColumn | Specifies whether to include Object ID (OID) in SQLColumns. |
FakeOidIndex | Specifies whether to create a fake unique index on OID. |
RowVersioning | Specifies whether to enable applications to detect if data has been modified by other users while you are attempting to update a row. Note that this parameter can speed up the update process, since, to update a row, every single column does not need to be specified in the WHERE clause. |
ShowSystemTables | Specifies whether the database driver should treat system tables as regular tables in SQLTables; useful for accessibility, allowing visibility into system tables. |
Fetch | Specifies whether the driver should automatically use declare cursor/fetch to handle SELECT statements and maintain a cache of 100 rows. |
BoolsAsChar | Controls the mapping of Boolean types. If set to "Yes", Bools are mapped to SQL_CHAR ; otherwise, they are mapped to SQL_BIT . |
SSLmode | Specifies the SSL mode for the connection. |
ConnSettings | Additional settings sent to the backend on connection. |
To test if the ODBC connection is working successfully, you can use the isql
utility (included in the unixODBC
package):
isql test
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
配置数据库监控监控项。
所有必填字段都标有红色星号
特别是对于数据库监控项,您必须输入:
类型 | 在此处选择数据库监控 |
键值 | 输入两个受支持的监控项键之一: db.odbc.select[<unique short description>,<dsn>,<connection string>] - 此监控项旨在返回一个值,即SQL查询结果第一行的第一列如果查询返回多列,则仅读取第一列如果查询返回多行,则仅读取第一行 db.odbc.get[<unique short description>,<dsn>,<connection string>] - 此项为能够以 JSON 格式返回多行/列。因此,它可以用作在一个系统调用中收集所有数据的主项,而 JSONPath 预处理可以用于从属项中以提取单个值有关详细信息,请参阅底层自动发现中使用的返回格式的 示例。从 Zabbix 4.4 开始支持此监控项 唯一的描述将用于识别触发器等中的该监控项 虽然 dsn 和 connection string 是可选参数,但至少应该存在其中一个如果同时定义了数据源名称 (DSN) 和连接字符串,则 DSN 将被忽略如果使用数据源名称,则必须按照 odbc.ini 中的指定设置 连接字符串可能包含驱动程序 - 特定参数 示例(MySQL ODBC 驱动程序 5 的连接): => db.odbc.get[MySQL example,,"Driver=/usr/local/lib/libmyodbc5a.so;Database=master;Server=127.0.0.1;Port=3306"] |
用户名 | 输入数据库用户名 如果在odbc.ini中指定了用户,则此参数是可选的 如果使用连接字符串,并且用户名字段不为空,则附加到连接字符串为 UID=<user> |
密码 | 输入数据库用户密码 如果在odbc.ini中指定了密码,则此参数是可选的 如果使用连接字符串,并且Password字段不为空,则附加到连接中字符串为 PWD=<密码> |
SQL 查询 | 输入 SQL 查询 请注意,对于 db.odbc.select[] 监控项,查询必须只返回一个值 |
信息类型 | 了解查询将返回什么类型的信息很重要,这样才能在此处正确选择信息类型,如果信息类型不正确,监控项将不受支持 |
Parameters without angle brackets are mandatory. Parameters marked with angle brackets < > are optional.
Returns one value, i.e. the first column of the first row of the SQL query result. Return value: depending on the SQL query.
Parameters:
Comments:
dsn
and connection string
are optional parameters, at least one of them must be present. If both data source name (DSN) and connection string are defined, the DSN will be ignored.
Transforms the SQL query result into a JSON array.
Return value: JSON object.
Parameters:
Comments:
dsn
and connection string
are optional parameters, at least one of them must be present. If both data source name (DSN) and connection string are defined, the DSN will be ignored.Example:
db.odbc.get[MySQL example,,"Driver=/usr/local/lib/libmyodbc5a.so;Database=master;Server=127.0.0.1;Port=3306"] #connection for MySQL ODBC driver 5
Transforms the SQL query result into a JSON array, used for low-level disovery. The column names from the query result are turned into low-level discovery macro names paired with the discovered field values. These macros can be used in creating item, trigger, etc prototypes.
Return value: JSON object.
Parameters:
Comments:
dsn
and connection string
are optional parameters, at least one of them must be present. If both data source name (DSN) and connection string are defined, the DSN will be ignored.select
开头。ODBC错误消息被构造成字段,以提供详细信息.例如:
Cannot execute ODBC query: [SQL_ERROR]:[42601][7][ERROR: syntax error at or near ";"; Error while executing the query]
└───────────┬───────────┘ └────┬────┘ └──┬──┘└┬┘└─────────────────────────────┬─────────────────────────────────────┘
│ │ │ └─ Native error code └─ Native error message
│ │ └─ SQLState
└─ Zabbix message └─ ODBC return code
请注意,错误消息长度限制为2048字节,因此信息可以被截断。如果有多个ODBC诊断记录,只要长度允许,Zabbix将尝试把它们连接起来(用|
分隔)。