Shulou(Shulou.com)06/01 Report--

I. Overview

The type of monitoring entry for ODBC is basically the same as that for configuring the database at the front end of Zabbix.

ODBC is a middleware written in C language for connecting database management systems. It was first developed by Microsoft and later developed to various platforms.

With the support of ODBC, Zabbix can query many kinds of databases, because Zabbix itself does not connect to the database directly, but through the interface and driver of ODBC, so it can monitor different databases more efficiently.

II. Installation and configuration

1. Compile Zabbix server

To support the ODBC function, you need to add-- with-unixodbc when compiling. To solve the dependency, you need to install the following software packages

Yum-y install unixODBC unixODBC-devel

2. Install the unixODBC driver

The driver is used for ODBC to connect to the database, it can support a variety of drivers, in most Linux distributions in the warehouse, there are these drivers, we install a MySQL driver to monitor the MySQL database.

Yum install mysql-connector-odbc

3. Configure unixODBC

The configuration of ODBC is mainly by modifying the odbcinst.ini and odbc.ini files, and the following command can be used to specify the location of the configuration file.

# odbcinst-junixODBC 2.2.14DRIVERS.S: / etc/odbcinst.iniSYSTEM DATA SOURCES: / etc/odbc.iniFILE DATA SOURCES..: / etc/ODBCDataSourcesUSER DATA SOURCES..: / root/.odbc.iniSQLULEN Size.: 8SQLLEN Size.: 8SQLSETPOSIROW Size.: 8

Odbcinst.ini is mainly used to configure and install ODBC database drivers, such as MySQL,oracle. I will not introduce the meaning of each field.

[MySQL] # driver name, Description = ODBC for MySQLDriver = / usr/lib/libmyodbc5.soSetup = / usr/lib/libodbcmyS.soDriver64 = / usr/lib64/libmyodbc5.soSetup64 = / usr/lib64/libodbcmyS.soFileUsage = 1 is required in the database file

Odbc.ini is mainly about configuring data sources.

[test] # data source name Data source name. Configuration at the front end of zabbix requires Description = MySQL test databaseDriver = mysql # get Server = 127.0.0.1User = zabbixPassword = zabbixPort = 3306Database = zabbix from the driver file odbcinst.ini

We can determine whether we can successfully connect to the database by installing isql, a command that is installed with unixODBC.

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

4. Configure Zabbix front-end web

First create a monitoring entry.

These entries are required and are explained as follows:

Type, here we choose Database monitorKey.

Select db.odbc.Select [unique _ description,data_source_name]

Unique_description will be used for entries in triggers

Data_source_name fill in the test in odbc.ini

User name input user name in odbc.ini Password input password in odbc.ini SQL query input SQL query statement Type of information what is the return value of our previous query before the correct choice will be supported

5. Points for attention

The execution time of the query cannot exceed the configured timeout

The query is allowed to return only one value.

If the query statement returns multiple columns, it reads only the first column

If the query statement returns multiple rows, it reads the first

SQL statements must start with select and can only be query statements.

SQL statements cannot contain newline characters

6. Error message

Starting with zabbix 2.08, ODBC provides the following detailed error messages:

Cannot execute ODBC query: [SQL _ ERROR]: [42601] [7] [ERROR: syntax error at or near ";" Error while executing the query] |-- |-| | `- Native error code`-error message. `- Record separator | |`-SQLState `- Zabbix message`-ODBC return code

The maximum length of an error message is 128 bytes, so an error message that is too long will be truncated.

III. Verification

Since we did not create an image, we can view the entry we created from the latest database.

You can write more complex SQL queries and create triggers to monitor the database in more detail.

Appendix:

The above is the monitoring of the monitoring MySQL, the following I will briefly introduce the monitoring of Oracle.

1. Download the oracle client

Http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

The download speed of the official website is relatively slow, or you can download it from the place where I uploaded to the 51CTO download center.

Http://down.51cto.com/data/2328882

Oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpmoracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpmoracle-instantclient11.2-odbc-11.2.0.3.0-1.x86_64.rpmoracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

2. Installation

Yum localinstall oracle-instantclient11.2-*

3. Create local monitoring

The default locations for installation are at

/ usr/share/oracle/11.2/client64/usr/include/oracle/11.2/client64/usr/lib/oracle/11.2/client64

Before setting the environment variable, create a network/admin folder under / usr/lib/oracle/11.2/client64

Mkdir-p / usr/lib/oracle/11.2/client64/network/admin

Create a tnsnames.ora under this folder, and enter its contents below:

Test_oracle= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.1.14) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = basicdb)

Create a sqlnet.ora under the / usr/lib/oracle/11.2/client64/network/admin file, enter:

NAME_DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

4. Configure environment variables

Export ORACLE_HOME=/usr/lib/oracle/11.2/client64export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:$LD_LIBRARY_PATHexport TNS_ADMIN=$ORACLE_HOME/network/adminexport PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

5. Configure the required libraries

Chmod + x / usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1cd / usr/lib64/ln-s libodbcinst.so.2.0.0 libodbcinst.so.1

6. Add oracle driver

# vim / etc/ odbcinst.ini [oracle] Description = Oracle ODBC driver for Oracle 11gDriver = / usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

7. Add oracle data sources

# vim / etc/odbc.ini [test1] Driver= oracleDSN= test_oracleServerName= yournameUserID= basicdbPassword= yourpasswd

8. Sqlplus test connection

# sqlplus yourname/yourpasswd@test_oracleSQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 19 11:39:18 2017Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL > exit

9. Configure the zabbix front end

Reference document: https://www.zabbix.com/documentation/3.2/manual/config/items/itemtypes/odbc_checks

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.