Let's try to find out what other interesting statistics we can gather this way. A common need is to monitor some statistics about databases. We could attempt to gather some MySQL query data; for example, how many queries per second are there? MySQL has a built-in query per second measurement, but that isn't quite what most users would expect. That particular value is calculated for the whole uptime MySQL has, which means it's quite useful, though only for the first few minutes. Longer-running MySQL instances have this number approaching the average value and only slightly fluctuating. When graphed, the queries per second graph gets flatter and flatter as time passes.

The flexibility of Zabbix allows us to use a different metric. Let's try to create a slightly more meaningful MySQL query items. We can get some data on the Select statements with a query like this:

mysql> show global status like 'Com_select';

That is something we should try to get working as a user parameter now. A test command to parse out only the number we're interested in would be as follows:

$ mysql -N -e "show global status like 'Com_select';" | awk '{print $2}'

We're using awk to print the second field. The -N flag for mysql tells it to omit column headers. Now, on to the agent daemon configuration—add the following near our other user parameters on our Zabbix server:

UserParameter=mysql.queries[*],mysql -u zabbix -N -e "show global status like 'Com_$1';" | awk '{print $$2}' 

It's basically the user parameter definition with the command appended, but we've made a few changes here. Notice how we used [*] after the key and replaced select in Com_select with $1—this way, we'll be able to use query type as an item key parameter. This also required adding the second dollar sign in the awk statement. If a literal dollar sign placeholder has to be used with a flexible user parameter, such dollar signs must be prefixed with another dollar sign. And the last thing we changed was adding -u zabbix to the mysql command. Of course, it's best not to use root or a similar access for database statistics, if possible—but if this command is supposed to be run by the Zabbix agent, why specify the username again? Mostly because of an old and weird bug where MySQL would sometimes attempt to connect with the wrong user. If you'd like to see the current status of that issue, see https://bugs.mysql.com/bug.php?id=64522. With the changes in place, save and close the file, then restart the agent daemon.

Now, the same as before, let's do a quick zabbix_get test on our Zabbix server command line:

$ zabbix_get -s 127.0.0.1 -k mysql.queries[select]

Well, you might have seen this one coming:

ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: NO) 

Our database user did require a password, but we specified none. How could we do that? The mysql utility allows us to specify a password on the command line with the -p flag (for example,  -p<password>, with no spaces between -p and the password), but it's best to avoid it. Placing passwords on the command line might allow other users to see this data in the process list, so it's a good idea to develop a habit: no secret information on the command line, ever.

How could we pass the password in a secure manner then? Fortunately, MySQL can read the password from a file that we could secure with permissions. A .my.cnf file is searched in several directories, and in our case the best option might be placing it in the user's home directory. On the Zabbix server, execute the following as the zabbix user:

$ touch ~zabbix/.my.cnf
$ chown zabbix:zabbix ~zabbix/.my.cnf
$ chmod 600 ~zabbix/.my.cnf
$ echo -e "[client]
password=<password>" > ~zabbix/.my.cnf  

Use the password that the Zabbix database user has. You can remind yourself what it was by taking a look at zabbix_server.conf. If running the preceding commands as root, also run chown -R zabbix.zabbix ~zabbix after creating the file. Note that we first create and secure the file, and only then place the password in it. Before we proceed with the agent side, let's test whether MySQL utilities pick up the password file. As the zabbix user, run the following:

$ mysqladmin -u zabbix status

Run the preceding code either in the same su session or as sudo -u zabbix mysqladmin -u zabbix status.

If everything went well with the file we put the password in, it should return some data:

Uptime: 10218  Threads: 23  Questions: 34045  Slow queries: 0  Opens: 114  Flush tables: 2  Open tables: 140  Queries per second avg: 3.331  

If that doesn't work, double-check the password, path, and permissions to the file. We use mysqladmin for this test, but both mysql and mysqladmin should use the same procedure for finding the .my.cnf file and reading the password from it.

Now that we know it's working, let's turn to zabbix_get again (no agent restart is needed as we didn't modify the agent configuration file this time):

$ zabbix_get -s 127.0.0.1 -k mysql.queries[select]

But the result seems weird:

ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: NO) 

It's failing still, and with the same error message. If we carefully read the full error, we'll see that the password is still not used. How could that be?

The environment isn't initialized for user parameter commands. This includes several common variables and one we're quite interested in: HOME. This variable is used by the MySQL client to determine where to look for the .my.cnf file. If the variable is missing, this file (and in turn, the password) can't be found. Does that mean we're doomed? Of course not, we wouldn't let such a minor problem stop us. We simply have to tell MySQL where to look for this file, and we can use a very simple method to do that. Edit zabbix_agentd.conf again and change our user parameter line to read as follows:

UserParameter=mysql.queries[*],HOME=/var/lib/zabbix mysql -u zabbix -N -e "show global status like 'Com_$1';" | awk '{print $$2}'

This sets the HOME variable for the mysql utility and that should allow the MySQL client to find the configuration file that specifies the password. Again, restart the Zabbix agent and then run the following:

$ zabbix_get -s 127.0.0.1 -k mysql.queries[select]
1788

You'll see a different value, and finally we can see the item is working. But what's that number? If you repeatedly run zabbix_get, you will see that the number is increasing. That looks a lot like another counter—and indeed, that's the number of SELECT queries since the database engine startup. We know how to deal with this. Back in the frontend, let's add an item to monitor the SELECT queries per second:

1. Navigate to Configuration | Hosts, click on Items next to A test host, then click on the Create item button. Fill in these values:

• • Name: MySQL $1 queries per second
  • Type: Zabbix agent (active)
  • Key: mysql.queries[select]
  • Type of information: Numeric (float)
  • Units: qps
  • Preprocessing tab: Preprocessing steps (Change per second)
  • New application: MySQL

The preceding details are shown in the following screenshot:

2. When you're done, click on the Add button at the bottom.

Notice how we used Delta (speed per second) together with Numeric (float) here. For the network traffic items, we chose Numeric (unsigned) instead, as there the value could overflow the float. For this query item, that's somewhere between highly unlikely and impossible, and we'll actually benefit a lot from increased precision here. The qps unit is just that—a string. It doesn't impact the displaying of data in any way besides appearing next to it.

Again, we might have to wait for a few minutes for any data to arrive. If you are impatient, feel free to restart the Zabbix agent daemon, then check the Latest data page:

The data is coming in nicely and we can see that our test server isn't too overloaded.

Let's benefit from making that user parameter flexible now:

1. Navigate back to Configuration | Hosts, click on Items next to A test host, then click on MySQL select queries per second in the Name column.
2. At the bottom of the form, click on the Clone button and change select in the key to update, then click on the Add button at the bottom. Clone this item two more times, changing the key parameter to insert and delete. Eventually, there should be four items:

The items should start gathering the data soon; let's try to see how they look all together:

1. Click on Graphs in the navigation header above the item list, then click on Create graph
2. Enter MySQL queries in the Name field and click on Add in the Items section.
3. Mark the check boxes next to the four MySQL items we created and click on Select at the bottom, then click on the Add button at the bottom

4. Let's go to Monitoring | Graphs, then select A test host in the Host drop-down menu and MySQL queries in the Graph drop-down menu. The graph, after some time, might look like this:

As we can see, the select queries are at the top here and the delete ones are almost non-existent. There are other query types, but this should be enough for our user parameter implementation.