Zabbix monitors mysql instance method

mysql> grant usage on *.* to [email protected] identified by '123456';

mysql> flush privileges;

# mysqladmin -h 127.0.0.1 -u zabbix -p123456 ping 
mysqld is alive

# mysqladmin -h 127.0.0.1 -u zabbix -p123456 ping |grep -c alive
1

user=zabbix
host=127.0.0.1
password='123456'

HOME=/var/lib/zabbix/mysqladmin ping |grep -c alive
1

UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep -c alive

# /usr/sbin/zabbix_agentd -t mysql.ping
mysql.ping [t|1]

vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

####Script for monitoring mysql performance UserParameter=mysql.status[*],/etc/zabbix/zabbix_agentd.d/check_mysql.sh $1

#####mysql version UserParameter=mysql.version,mysql -V

#!/bin/bash
# -------------------------------------------------------------------------------
# FileName: check_mysql.sh
# Revision: 1.0
# -------------------------------------------------------------------------------
# Copyright:
# License: GPL

# Username MYSQL_USER = 'zabbix'

# Password MYSQL_PWD = 'zabbix@123'

# Host address/IP
MYSQL_HOST='ip'

# Port MYSQL_PORT = '3306'

#Data connection MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

# Are the parameters correct? if [ $# -ne "1" ]; then
echo "arg error!"
fi

# Get data case $1 in
Uptime
result=`${MYSQL_CONN} status 2>/dev/null |cut -f2 -d":"|cut -f1 -d"T"`
echo $result
;;
Com_update)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_update"|cut -d"|" -f3`
echo $result
;;
Slow_queries
result=`${MYSQL_CONN} status 2>/dev/null |cut -f5 -d":"|cut -f1 -d"O"`
echo $result
;;
Com_select)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_select"|cut -d"|" -f3`
echo $result
;;
Com_rollback)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_rollback"|cut -d"|" -f3`
echo $result
;;
Questions)
result=`${MYSQL_CONN} status 2>/dev/null |cut -f4 -d":"|cut -f1 -d"S"`
echo $result
;;
Com_insert)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_insert"|cut -d"|" -f3`
echo $result
;;
Com_delete)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_delete"|cut -d"|" -f3`
echo $result
;;
Com_commit)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_commit"|cut -d"|" -f3`
echo $result
;;
Bytes_sent)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Bytes_sent" |cut -d"|" -f3`
echo $result
;;
Bytes_received)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Bytes_received" |cut -d"|" -f3`
echo $result
;;
Com_begin)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_begin"|cut -d"|" -f3`
echo $result
;;

*)
echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"
;;
esac

chmod +x /etc/zabbix/zabbix_agentd.d/check_mysql.sh

Chown zabbix.zabbix /etc/zabbix/zabbix_agentd.d/check_mysql.sh

version: database version key_buffer_size: index buffer size of MyISAM sort_buffer_size: sorting space of the session (one will be requested for each thread)
join_buffer_size: This is the minimum buffer size allocated for connection operations. These connections use normal index scans, range scans, or connections that do not apply indexes. max_connections: The maximum number of simultaneous connections allowed max_connect_errors: The maximum number of error connections allowed for a host. If exceeded, subsequent connections will be rejected (default 100). You can use the flush hosts command to remove the rejection of open_files_limits: the number of files that the operating system allows MySQL to open. You can determine whether you need to increase table_open_cache by the opened_tables status. If opened_tables is relatively large and is still increasing, it means that you need to increase table_open_cache
max-heap_tables_size: The maximum size of the memory table created (default 16M). This parameter and tmp_table_size together limit the maximum value of the internal temporary table (take the smaller of the two parameters). If the limit is exceeded, the table will be changed to innodb or myisam engine (before 5.7.5, the default is myisam, and starting from 5.7.6, it is innodb, which can be adjusted by the internal_tmp_disk_storage_engine parameter).
max_allowed_packet: Maximum size of a packet##########GET INNODB INFO
#INNODB variables
innodb_version:
innodb_buffer_pool_instances: Divide the innodb buffer pool into specified multiple instances (default is 1)
innodb_buffer_pool_size: innodb buffer pool size, 5.7.5 introduced innodb_buffer_pool_chunk_size,
innodb_doublewrite: whether to enable doublewrite (enabled by default)
innodb_read_io_threads: the number of IO read threads innodb_write_io_threads: the number of IO write threads########innodb status
innodb_buffer_pool_pages_total: the number of innodb buffer pool pages, the size is equal to innodb_buffer_pool_size/(16*1024)
innodb_buffer_pool_pages_data: the number of pages containing data in the innodb buffer pool########## GET MYSQL HITRATE
1. Query cache hit rate: If Qcache_hits+Com_select<>0, then it is Qcache_hits/(Qcache_hits+Com_select), otherwise it is 0

2. Thread cache hit rate: If Connections<>0, then it is 1-Threads_created/Connections, otherwise it is 0

3. MyISAM key cache hit rate is 1-Key_reads/Key_read_requests if Key_read_requests<>0, otherwise it is 0

4. MyISAM key cache write hit rate is 1-Key_writes/Key_write_requests if Key_write_requests<>0, otherwise it is 0

5. Key block usage rate: If Key_blocks_used+Key_blocks_unused<>0, then Key_blocks_used/(Key_blocks_used+Key_blocks_unused), otherwise 0

6. Create disk storage temporary table ratio If Created_tmp_disk_tables+Created_tmp_tables<>0, then Created_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables), otherwise 0

7. Connection usage rate: If max_connections<>0, then threads_connected/max_connections, otherwise 0

8. Open file ratio: if open_files_limit<>0, then open_files/open_files_limit, otherwise 0

9. Table cache usage If table_open_cache<>0, then open_tables/table_open_cache, otherwise 0