In Zabbix when we want to monitor some database, it is possible to do this by making use of the Open Database Connectivity (ODBC) software. ODBC is kind of a software sitting between the DBMS and the application (in our case Zabbix). Zabbix can query any database, which is supported by unixODBC or Independent Open DataBase Connectivity (iODBC).
We need of course, our Zabbix server setup. If you have compiled the server then you need to make sure that it was compiled with the option --with-unixODBC.
- Make sure you have the packages installed for ODBC on our CentOS / Red Hat; it can be done by installing the
unixODBCpackages.# yum install unixODBC -y # yum install unixODBC-devel -y (if you need sources to compile)
- Next, we need a proper connector for our database. In our case the database is MySQL. If you have another database, look for the specific connector for your database:
# yum install mysql-connector-odbc - Next we need to configure the
odbcinst.inifile. Here we have to add the location of our ODBC database driver. To find the location you can run the next command:# odbcinst -j unixODBC 2.2.14 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini
- So we can now to edit the line
odbcinst.iniand list our database driver:# vi /etc/odbcinst.ini # 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
This file should already be OK, just make sure that the library for the Driver64 option is really on the system in that location.
- Edit the
odbc.inifile to create ourdsn(data source) and add our databaseconfig.:# vi /etc/odbc.ini [mysql-test] => name of the dsn we will use Description = Mysql test DB Driver = mysql Server = 127.0.0.1 User = root Password = <root db password> Port = 3306 Database = <zabbix database>
- Now let's see if we can make a connection with our database:
# isql mysql-test
- The output should look like this, if you have an error, check all of the above again for typos. Another solution could be to run
isqlcommand with the-voption for verbose. - Now it's time to go to Zabbix and create a new item on our host. Configuration | Hosts | Items | Create item.
- For the Name, we just add a name easy for us to remember what item it is.
- Type is where we select
Database monitor. - Key is already filled in. We just need to replace the
<unique short description>with our own unique key naming and<dsn>with our DSN name from the one in theodbc.inifile. - We don't need to fill in username and password as we added it in the
odbc.inifile already. - SQL query is the field where we can put our SQL query that we want to run on our database. In our case, we added
select count(*) from items. - Type of information is in our case
Numeric. - Data type for us is
Decimal. - Now go to Latest data page and see as data comes in after some time if all went well.
To be able to get Zabbix to read data from our database, we need to keep a few simple steps in mind. We need to compile Zabbix with UnixODBC support for which we need the package unixODBC-devel. Zabbix does not connect directly to the database but makes use of ODBC for this so we need to install the unixODBC package as well. Depending on what database we want to use, we also need the proper ODBC driver for our database. So in our case we had to make use of the mysql-connector-odbc package.
Next, we had to configure unixODBC which was done by editing two files odbcinst.ini and odbc.ini. The odbcinst.ini file is used to configure the installed drivers. It seems Red Hat / Centos comes already with a basic configuration, so we didn't have to make any changes.
Next, we had to add a data source in the odbc.ini file which is what we call a DSN. The DSN name is always between [] and we need this name for our Zabbix item. We also had to add the driver, in our case, mysql- the server where our database was running and connection settings such as username, password, port, and database name.
In our case, it was easy to install the MySQL driver because it was already provided in a package from our OS. Sometimes it's not so easy to find the correct driver for instance when using Oracle. The website from unixODBC has a list of supported databases and drivers: http://www.unixodbc.org/drivers.html.
Some limitations to keep in mind:
- The SQL command must begin with the
selectcommand. - The SQL command may not include line breaks.
- The query can return only a single value.
- If the query returns more than one column, only the first column of Zabbix is considered.
- If the query returns more than one row only the first line is read.
- Queries can but must not be terminated with a semicolon.
- Macros are not replaced.
- The SQL command must start with
sql=in lowercase. - If the database is loaded, the response can come with a delay.
- Proxies if compiled also need the option
– with -unixODBC. - Every time a query runs, it executes a login.