How to monitor sql queries with zabbix

If you want to monitor some parameters in mysql database,  you can do it with ODBC monitoring in Zabbix.

ODBC monitoring corresponds to the Database monitor item type in the Zabbix frontend.

ODBC is a C programming language middle-ware API for accessing database management systems (DBMS). The ODBC concept was developed by Microsoft and later ported to other platforms.

Zabbix may query any database, which is supported by ODBC. To do that, Zabbix does not directly connect to the databases, but uses the ODBC interface and drivers set up in ODBC. This function allows for more efficient monitoring of different databases for multiple purposes – for example, checking specific database queues, usage statistics and so on. Zabbix supports unixODBC, which is one of the most commonly used open source ODBC API implementations.

Installing unixODBC

 

yum -y install unixODBC unixODBC-devel

install odbc
  You need to install unixODBC drivers also. I have it installed already.
already inst
  
Next step is configuring unixODBC. ODBC configuration is done by editing the odbcinst.ini and odbc.ini files. To verify the configuration file location, type:
odbcinst -j
[root@zabbix ~]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

odbcinst.ini is used to list the installed ODBC database drivers:

########################################################

# Example driver definitions

# Driver from the postgresql-odbc package

# Setup from the unixODBC package

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/lib/psqlodbc.so

Setup           = /usr/lib/libodbcpsqlS.so

Driver64        = /usr/lib64/psqlodbc.so

Setup64         = /usr/lib64/libodbcpsqlS.so

FileUsage       = 1

# Driver from the mysql-connector-odbc package

# Setup from the unixODBC package

[MySQL]

Description     = ODBC for MySQL

Driver          = /usr/lib/libmyodbc5.so

Setup           = /usr/lib/libodbcmyS.so

Driver64        = /usr/lib64/libmyodbc5.so

Setup64         = /usr/lib64/libodbcmyS.so

FileUsage       = 1

#########################################################

 

odbc.ini is used to define data sources:

#########################################################

[db.hostname.com]

Driver = MySQL

DATABASE = database.name

PWD = password.for.dbuser

SERVER = db.server.ip

PORT = 3306

UID = dbuser

#########################################################

 

Attribute Description

db.hostname.com

Data source name (DSN).
Driver Database driver name – as specified in odbcinst.ini
Database Database name.
PWD Database user password.
SERVER Database server IP/DNS.
PORT Database connection port.
UID Database user for connection.

To verify if ODBC connection is working successfully, a connection to database should be tested. That can be done with the isql utility (included in the unixODBC package):

# isql  db.hostname.com

connected

 

Voilà!