Use zabbix’s automatic discovery function to monitor the data size, index size, fragment size and self-increment of tables in the database.
Usually, there are many tables in the database. If you want to monitor all the tables, it is very troublesome and time-consuming to configure monitoring items one table at a time. You can directly deploy monitoring using zabbix’s automatic discovery function.
You can use information_schema.tables to query the data size, index size, fragment size, and auto-increment of tables in the database.
1. Ubuntu 16.04 Zabbix Server 4.0.30 MySQL 5.7.33 2 Client Operations 2.1. Configuring discovery Rules Configure automatic discovery rules on the mysql server.
root@DB-Summary-002:/etc/zabbix/script/mysql/tables_status# cat table_discover.sh #! /bin/bash
#1 Message
Auth: ZJ
Purpose: Obtains the table name in JSON format.
#2 Defined Varibales User=root Passwd=xxx
#3 Operate diskarray=(/usr/bin/mysql -u${User} -p${Passwd} -Ne “use ipoc_db; show tables;” 2>/dev/null) length={#diskarray[@]} printf “{\n” printf ‘\t'”\”data\”:[” for ((i=0;i
shell> cat /etc/zabbix/script/mysql/tables_status/table_status.sh #! /bin/bash
#1 Message
Auth: ZJ
Purpose: Monitors the number of rows in a table
#2 Defind Variables Dir=/etc/zabbix/script/mysql/tables_status User=root Passwd=xxx Table=
2 DB=ipoc_db
#3 Operate cd ${Dir}
Getdata () {/usr/bin/mysql -u User−p{User} -puser −p{Passwd} -ne “use information_schema; The select TableStatusfromtableswheretablename = ‘{Table_Status} from tables where table_name = ‘TableStatusfromtableswheretablename =’ {Table} ‘and table_schema =’ ${DB} ‘;” 2>/dev/null | grep [0123456789] }
case ${Table_Status} in table_rows) getdata ;; data_length) getdata ;; index_length) getdata ;; data_free) getdata ;; auto_increment) getdata ;; Esac 2.3. Updating the Configuration and Restarting the Agent Update the configuration file on the mysql server
shell> vim /etc/zabbix/zabbix_agent.conf …. # # 2021-05-25 myqsl said the number of rows, data length, length of the index, the value-added, debris UserParameter = mysql. The table, / bin/bash /etc/zabbix/script/mysql/tables_status/table_discover.sh UserParameter=mysql.table_status[*],/bin/bash /etc/zabbix/script/mysql/tables_status/table_status.sh 11 12
Zabbix-agent shell> /etc/init.d/zabbix-agent restart 3
2. Fill in the information
3. Configure the monitoring item prototype
4. Host link template, configuration → Host → select host → Batch update
Template → Select template link → Select template → Select template → Update
After a few minutes, the host automatically adds monitoring items for all tables as follows:
Some tables are not updated or are not updated at all, so you can optimize it to remove unnecessary table monitoring.
4. Optimize the automatic table discovery rules. Generally, it is not necessary to monitor all tables, but only some tables with large changes, so we need to optimize the automatic discovery rules.
Execute the script for table_discover. Sh and find the following:
shell> sh table_discover.sh |more { “data”:[ {“{#TABLE_NAME}”:”_t_session_call_doing_del”}, {“{#TABLE_NAME}”:”checksums”}, {“{#TABLE_NAME}”:”heartbeat”}, … … {“{#TABLE_NAME}”:”tbl_message”}, {“{#TABLE_NAME}”:”tbl_notice_read”}, {“{#TABLE_NAME}”:”tbl_user_notice_cnt”}]} then we create a file with the same format and add the tables we need to monitor.
shell> cat tables { “data”:[ {“{#TABLE_NAME}”:”t_system_sp_user”}, {“{#TABLE_NAME}”:”t_task_member”}, {“{#TABLE_NAME}”:”t_user”}, {“{#TABLE_NAME}”:”t_user_life_record”}, {“{#TABLE_NAME}”:”t_user_life_stat”}]} Updates the zabbix_agent configuration file
shell> cat /etc/zabbix/zabbix_agentd.conf … . # monitoring database data size, and the size of the index, fragment size, the value-added UserParameter = mysql. The table, the cat/etc/zabbix/script/mysql/tables_status/table UserParameter = mysql. Table_status [*], / bin/bash/etc/zabbix. / script/mysql/tables_status table_status sh $1 add configurable template to the host
After a period of time, monitoring items are automatically added
OK! That’s what we need.
Please refer to the website: www.zj1120.com/?p=13210