14 ODBC监控

概述

ODBC监控对应于Zabbix前端中的 数据库监控 监控项类型。

ODBC是C语言编写的中间件API，用于访问数据库管理系统(DBMS)。 ODBC是由Microsoft开发的，后来被移植到了其它平台。

Zabbix可以查询任何支持ODBC的数据库。实现这个目标，Zabbix不直接连接数据库，而是使用ODBC接口并在ODBC中设置的驱动程序。该功能允许出于多种目的，更加有效地监视不同的数据库。例如，检测特定的数据库队列、使用统计信息等。Zabbix支持unixODBC，是最常用的开源ODBC API实现之一。

安装 unixODBC

安装unixODBC的建议方法是使用Linux操作系统默认包库，在主流的Linux发行版中，默认情况下unixODBC包含在镜像库中。如果找不到，可以在 unixODBC 主页上获取：http://www.unixodbc.org/download.html.

在RedHat/Fedora系统上使用yum包管理器安装unixODBC：

shell> dnf -y install unixODBC unixODBC-devel

使用zypper包管理器在基于SUSE的系统上安装unixODBC：

# zypper in unixODBC-devel

安装 unixODBC 驱动程序

应该为将被监控的数据库安装一个 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

MySQL

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.

PostgreSQL

To install the PostgreSQL unixODBC database driver for RedHat/Fedora-based systems, use the dnf package manager:

dnf install postgresql-odbc

For other systems, please refer to PostgreSQL documentation.

Oracle

To install the unixODBC database driver, please refer to Oracle documentation.

MSSQL

To install the MSSQL unixODBC database driver for RedHat/Fedora-based systems, use the dnf package manager:

dnf -y install freetds unixODBC

For other systems, please refer to FreeTDS user guide.

配置 unixODBC

ODBC配置是通过编辑 odbcinst.ini 和 odbc.ini 文件完成的，要验证配置文件的位置，输入：

shell> odbcinst -j

odbcinst.ini 用于列出已安装的ODBC数据库驱动程序：

mysql Description = ODBC for MySQL Driver · = /usr/lib/libmyodbc5.so

参数详情：

odbc.ini 用于自定义数据源：

[test] Description = MySQL test database Driver = mysql Server = 127.0.0.1 User = root Password = Port = 3306 Database = zabbix

参数详情：

要验证ODBC连接是否正常工作，应该测试到数据库的连接，这可以通过isql实用程序(包含在unixODBC包中)完成：

shell> isql test
       +---------------------------------------+
       | Connected!                            |
       |                                       |
       | sql-statement                         |
       | help [tablename]                      |
       | quit                                  |
       |                                       |
       +---------------------------------------+
       SQL>

odbcinst.ini

The odbcinst.ini file lists the installed ODBC database drivers. If odbcinst.ini is missing, it is necessary to create it manually.

[TEST_MYSQL]
       Description=ODBC for MySQL
       Driver=/usr/lib/libmyodbc5.so
       FileUsage=1

odbc.ini

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

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=

Testing ODBC 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                                  |
       |                                       |
       +---------------------------------------+

Zabbix 前端的监控项配置

配置数据库监控监控项。

所有必填字段都标有红色星号

特别是对于数据库监控项，您必须输入：

Item key details

Parameters without angle brackets are mandatory. Parameters marked with angle brackets < > are optional.

db.odbc.select[<unique short description>,<dsn>,<connection string>]

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:

• unique short description - a unique short description to identify the item (for use in triggers, etc);
• dsn - the data source name (as specified in odbc.ini);
• connection string - the connection string (may contain driver-specific arguments).

Comments:

• Although 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.
• If a query returns more than one column, only the first column is read. If a query returns more than one line, only the first line is read.

db.odbc.get[<unique short description>,<dsn>,<connection string>]

Transforms the SQL query result into a JSON array.
Return value: JSON object.

Parameters:

• unique short description - a unique short description to identify the item (for use in triggers, etc);
• dsn - the data source name (as specified in odbc.ini);
• connection string - the connection string (may contain driver-specific arguments).

Comments:

• Although 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.
• Multiple rows/columns in JSON format may be returned. This item may be used as a master item that collects all data in one system call, while JSONPath preprocessing may be used in dependent items to extract individual values. For more information, see an example of the returned format, used in low-level discovery.

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

db.odbc.discovery[<unique short description>,<dsn>,<connection string>]

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:

• unique short description - a unique short description to identify the item (for use in triggers, etc);
• dsn - the data source name (as specified in odbc.ini);
• connection string - the connection string (may contain driver-specific arguments).

Comments:

• Although 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.

注意事项

• Zabbix不限制查询执行时间。 用户可以选择在合理时间内执行的查询。
• Zabbix server的 Timeout参数值也用作于ODBC登陆超时时间（请注意，根据ODBC驱动，登录超时设置可能会被忽略）。
• 查询只能返回一个值。
• 如果查询返回多个列，则只读取第一列。
• 如果查询返回多行，则只读取第一行。
• SQL命令必须以select开头。
• SQL命令不能包含任何换行符。
• 另请参阅ODBC检查的 已知问题

错误信息

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将尝试把它们连接起来（用|分隔）。