How can I create a script to monitor MySQL database size

[MuriloHB]

Hey guys!
So I want to create a script to monitor the database size of my Zabbix server and I'm not sure how to do this.
What I've done is:

I've ran this command on the Zabbix command line to see if it returns what I need:

mysql -u zabbix -pfsDlEis4bH -h 127.0.0.1 -e "SELECT SUM( data_lenght + index_lenght ) AS 'size' FROM information_schema.TABLES WHERE table_schema = 'zabbix' LIMIT 1;' -s -N

And the output is:

mysql: [Warning] Using a password on the command line interface can be insecure.
3300339776

Which is the size of my Zabbix database in bytes.

So I've created a file on /usr/lib/zabbix/externalscripts named zabbix-databasesize.pl with the following content:

#!/usr/bin/perl
mysql -u zabbix -pfsDlEis4bH -h 127.0.0.1 -e "SELECT SUM( data_lenght + index_lenght ) AS 'size' FROM information_schema.TABLES WHERE table_schema = 'zabbix' LIMIT 1;' -s -N

And I've already checked on the zabbix_server.conf file that ExternalScripts=/usr/lib/zabbix/externalscripts

At the Zabbix web configurator I've created a new item on the Template DB MySQL (that is linked to the Zabbix Server) with the following settings:
Name: Zabbix Database Size
Type: External check
Key: zabbix-databasesize.pl
Type of information: Text (because it is returning a error, that was the only way I could read it)
Unit of measurement:b

And in the Latest Data of the Zabbix Server it shows as Last Value the message:

Number found where operator expected at /usr/lib/zabbix/externalscripts/zabbix-databasesize.pl line2, near "h 127.0.0.1"
(Do you need to predeclare h?)
Warning: Use of "-s" without parentheses is ambiguous at /usr/lib/zabbix/externalscripts/zabbix-databasesize.pl line2.
syntax error at /usr/lib/zabbix/externalscripts/zabbix-databasesize.pl line 2, near "mysql -u "
Execution of /usr/lib/zabbix/externalscripts/zabbix-databasesize.pl aborted due to compilation errors.

I don't understand why I'm getting this error, because in the command line it returns what it should.
Sorry if I'm missing something stupid but I'm very new at Zabbix and don't understand database concepts or commands.

[Atsushi]

If you want to execute the mysql command, it should be a shell script, not a Perl script.
Please try to change shebang to 「#!/usr/bin/sh」.

[MuriloHB]

If you want to execute the mysql command, it should be a shell script, not a Perl script.
Please try to change shebang to 「#!/usr/bin/sh」.

It works!
But I had to use #!bin/sh only in the beginning of the script file to had it working.

Another way that I got it working was to set a UserParameter on "/etc/zabbix/zabbix_agentd.conf and set it up as:

UserParameter=mysql.questions,MYSQL_PWD=fsDlEis4bH mysql -uzabbix -e "SELECT SUM( data_length + index_length ) AS 'size' FROM information_schema.TABLES WHERE table_schema = 'zabbix' LIMIT 1;" -s -N

After that I created a new item on the template with the type set as "Zabbix Agent" and the key as "mysql.questions".

Now the questions that remains is: what is the best way of doing this? Is there a difference in terms of cost for the Zabbix server?

[brunohl]

I guess you could use like this. Alternatively, you could use ODBC Database monitoring (https://www.zabbix.com/documentation...es/odbc_checks)