Monitoring the size of a MySQL database or table in Zabbix

To see a table with a list of databases and their size in megabytes, you can execute a SQL query:

SELECT table_schema "databases name", sum(data_length + index_length)/1024/1024  "DВ size in MB" FROM information_schema.TABLES GROUP BY table_schema;

To see the size of the tables of a particular database, for example zabbix, you can execute SQL:

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "zabbix";

Or, in bytes, see the size of a single database with the name zabbix:

SELECT SUM( data_length + index_length ) AS 'size'
FROM information_schema.TABLES
WHERE table_schema = 'zabbix'
LIMIT 1;

To see the size of a particular database table, for example, zabbix.history:

SELECT SUM( data_length + index_length ) AS 'size'
FROM information_schema.TABLES
WHERE table_schema = 'zabbix' AND table_name = 'history'
LIMIT 1;

We use the examples of previous requests for monitoring. Create a script in the script directory Zabbix, for example, with the name zabbix_database_size.sh and content:

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

In Zabbix network node or in a new template, we create a data element in which we indicate:

Name: Zabbix Database Size
Type: External check
Key: zabbix_database_size.sh
Type of information: Numeric (positive integer)
Unit of measurement: b

Accordingly, we will create a graph for it.

Done.

Leave a Reply