With the CPU discovery reimplemented, let's try to discover MySQL databases. Instead of user parameters, let's use a Zabbix trapper item, which we will populate with Zabbix sender.

We explored Zabbix sender in Chapter 10, Advanced Item Monitoring.

We will use a different item type now. This is completely normal—the item type used for LLD does not matter as long as we can get the correct JSON into the Zabbix server. Let's start by creating the LLD rule with a number of item prototypes and proceed with generating JSON after that. With this rule, we could discover all MySQL databases and monitor their sizes using a user parameter. The following assumes that your Zabbix database is on A test host. Navigate to Configuration | Hosts, click on Discovery next to A test host, and then click on Create discovery rule. Fill in the following:

• Name: MySQL database discovery
• Type: Zabbix trapper
• Key: mysql.db.discovery

When done, click on Add. Now, click on Item prototypes next to MySQL database discovery, and then click on Create item prototype. Here, fill in the following:

• Name: Database $1 size
• Type: Zabbix agent (active)
• Key: mysql.db.size[{#MYSQL.DBNAME}]
• Units: B
• Update interval: 300
• Applications: MySQL

When done, click on the Add button at the bottom. For this item, we used an active agent, as this is suggested for user parameters, and we also set the update interval to 5 minutes—usually, the database size won't change that quickly. We are only interested in more long-term trends. We now have the item, which will be a UserParameter variable, and that item, in turn, will be created by an LLD rule that is populated by Zabbix sender. Let's set up the UserParameter variable now. In the Zabbix agent daemon configuration file for A test host, add the following:

UserParameter=mysql.db.size[*],HOME=/home/zabbix mysql -Ne select sum(data_length+index_length) from information_schema.tables where table_schema='$1'; 

This UserParameter variable will query the total database size, including both actual data and all indexes. Notice how we are setting the HOME variable again. Don't forget to save the file and restart the agent daemon afterward. It's also a good idea to test it right away:

$ zabbix_get -s 127.0.0.1 -k mysql.db.size[zabbix]  

This will most likely return a number:

147865600

If it fails, double-check the MySQL parameter configuration we used in Chapter 10, Advanced Item Monitoring.

Notice how it takes some time for this value to be returned. For large databases, it might be a better idea to use Zabbix sender for such an item as well.

With the LLD rule and item prototype in place, let's get to sending the JSON for discovery. The following should discover all databases that are accessible to the current user and generate the LLD JSON for Zabbix:

for db in $(mysql -u zabbix -Ne show databases;); do 
    dblist=$dblist,'{{#MYSQL.DBNAME}:'$db'}' 
done 
echo '{data:['${dblist#,}']}' 

We are removing the trailing comma in the JSON database list—JSON does not allow a trailing comma, and including it will make the discovery fail. Zabbix will complain that the incoming data is not a valid JSON.

The principle here is similar to the CPU discovery reimplementation from earlier—we find all the databases and list them in the JSON after the proper macro name. It should return a line similar to this:

{data:[{{#MYSQL.DBNAME}:information_schema},{{#MYSQL.DBNAME}:zabbix}]} 

And now on to actually sending this to our LLD rule—we will use Zabbix sender for that.

If you tested this and thus modified the dblist variable, run unset dblist before running the following command:

    $ zabbix_sender -z 127.0.0.1 -s A test host -k mysql.db.discovery -o $(for db in $(mysql -u zabbix -Ne show databases;); do dblist=$dblist,'{{#MYSQL.DBNAME}:'$db'}'; done; echo '{data:['${dblist#,}']}') 

Visiting the item list for A test host in the configuration should reveal that one item created for each database:

It might take up to 3 minutes for the first value to appear in the Latest data page first, up to a minute for the configuration cache to refresh, and then up to 2 minutes for the active agent to update its configuration from the server.

After some time has passed, the values should be visible in the Monitoring | Latest data page:

LLD rules cannot be nested—for example, we cannot discover tables in the databases we discovered. If the tables had to be discovered, it would required a separate, independent LLD rule.