zabbix之监控MySQL服务器（可用于主从架构）

3441321

穿件监控MySQL的自定义参数
在zabbix的agent端添加配置文件


# vim /etc/zabbix/mysql/.my.cnf  用于连接MySQL的配置文件，无需显示输入用户和密码

1

<span style="font-size:14px;">[client]<br>host     = localhost<br>user     = root<br>password = 123456<br>socket   = /usr/local/mysql/mysql.sock<br>[mysql]<br>host     = localhost<br>user     = root<br>password =123456<br>socket   = /usr/local/mysql/mysql.sock<br>[mysqladmin]<br>host     = localhost<br>user     = root<br>password = 123456<br>socket   = /usr/local/mysql/mysql.sock<br></span>

# vim /usr/local/zabbix/etc/zabbix_agentd.conf.d/userparameter_mysql.conf 提供监控MySQL的用户自定义参数

1

<span style="font-size:14px;">UserParameter=mysql.version,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -V<br>UserParameter=mysql.ping,HOME=/etc/zabbix/mysql  /usr/local/mysql/bin/mysqladmin -uroot  ping | grep -c alive<br><br>UserParameter=mysql.status ,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysqladmin -uroot  status|cut -f2 -d":"|cut -f1 -d"T"<br>UserParameter=mysql.Ping,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysqladmin -uroot ping|grep alive|wc -l<br>UserParameter=mysql.Threads,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysqladmin -uroot status|cut -f3 -d":"|cut -f1 -d"Q"<br>UserParameter=mysql.Questions,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysqladmin -uroot status|cut -f4 -d":"|cut -f1 -d"S"<br>UserParameter=mysql.Slowqueries,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysqladmin -uroot status|cut -f5 -d":"|cut -f1 -d"O"<br>UserParameter=mysql.Qps,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysqladmin -uroot status|cut -f9 -d":"<br>UserParameter=mysql.Slave_IO_State,if [ "$(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show slave status\G"| grep Slave_IO_Running|awk '{print $2}')" == "Y<br>es" ];then echo 1; else echo 0;fi<br>UserParameter=mysql.Slave_SQL_State,if [ "$(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show slave status\G"| grep Slave_SQL_Running|sed -n '1p'|awk '{prin<br>t $2}')" == "Yes" ];then echo 1; else echo 0;fi<br>UserParameter=mysql.Slave_Delay,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show slave status\G"| grep Seconds_Behind_Master|sed -n '1p'|awk '{print $2}'<br>UserParameter=mysql.Key_buffer_size,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'key_buffer_size';"| grep -v Value |awk '{print $2/1024<br>^2}'<br>UserParameter=mysql.Key_reads,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Key_read_requests,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Key_cache_miss_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}')<br> $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}<br>'<br>UserParameter=mysql.Key_blocks_used,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_blocks_used';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Key_blocks_unused,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Key_blocks_used_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_blocks_used';"| grep -v Value |awk '{prin<br>t $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/(<br>$1+$2)*100)}'<br>UserParameter=mysql.Innodb_buffer_pool_size,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'innodb_buffer_pool_size';"| grep -v Value |awk<br> '{print $2/1024^2}'<br>UserParameter=mysql.Innodb_log_file_size,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'innodb_log_file_size';"| grep -v Value |awk '{pri<br>nt $2/1024^2}'<br>UserParameter=mysql.Innodb_log_buffer_size,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'innodb_log_buffer_size';"| grep -v Value |awk '<br>{print $2/1024^2}'<br>UserParameter=mysql.Table_open_cache,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'table_open_cache';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Open_tables,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Opened_tables,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'opened_tables';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Open_tables_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}') <br>$(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'opened_tables';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/($1+$2)*100)}<br>'<br>UserParameter=mysql.Table_open_cache_used_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'open_tables';"| grep -v Value |awk '{pr<br>int $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'table_open_cache';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",<br>$1/($1+$2)*100)}'<br>UserParameter=mysql.Thread_cache_size,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'thread_cache_size';"| grep -v Value |awk '{print $2}<br>'<br>UserParameter=mysql.Threads_cached,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Threads_cached';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Threads_connected,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Threads_connected';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Threads_created,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Threads_created';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Threads_running,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Threads_running';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Qcache_free_blocks,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_free_blocks';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Qcache_free_memory,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_free_memory';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Qcache_hits,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_hits';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Qcache_inserts,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_inserts';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Qcache_lowmem_prunes,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_lowmem_prunes';"| grep -v Value |awk '{print <br>$2}'<br>UserParameter=mysql.Qcache_not_cached,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_not_cached';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Qcache_queries_in_cache,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_queries_in_cache';"| grep -v Value |awk '{<br>print $2}'<br>UserParameter=mysql.Qcache_total_blocks,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_total_blocks';"| grep -v Value |awk '{print $2<br>}'<br>UserParameter=mysql.Qcache_fragment_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_free_blocks';"| grep -v Value |awk '{p<br>rint $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_total_blocks';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n"<br>,$1/$2*100)}'<br>UserParameter=mysql.Qcache_used_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'query_cache_size';"| grep -v Value |awk '{prin<br>t $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_free_memory';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",($1<br>-$2)/$1*100)}'<br>UserParameter=mysql.Qcache_hits_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_hits';"| grep -v Value |awk '{print $2}') <br>$(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Qcache_inserts';"| grep -v Value |awk '{print $2}')| awk '{if($1 == 0){print 0;}else {print<br>f("%1.4f\n",($1-$2)/$1*100);}}'<br>UserParameter=mysql.Query_cache_limit,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'query_cache_limit';"| grep -v Value |awk '{print $2}<br>'<br>UserParameter=mysql.Query_cache_min_res_unit,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'query_cache_min_res_unit';"| grep -v Value |a<br>wk '{print $2}'<br>UserParameter=mysql.Query_cache_size,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'query_cache_size';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Sort_merge_passes,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Sort_merge_passes';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Sort_range,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Sort_range';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Sort_rows,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Sort_rows';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Sort_scan,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Sort_scan';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Handler_read_first,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Handler_read_first';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Handler_read_key,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Handler_read_key';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Handler_read_next,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Handler_read_next';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Handler_read_prev,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Handler_read_prev';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Handler_read_rnd,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Handler_read_rnd';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Handler_read_rnd_next,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Handler_read_rnd_next';"| grep -v Value |awk '{prin<br>t $2}'<br>UserParameter=mysql.Com_select,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'com_select';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Com_insert,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'com_insert';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Com_insert_select,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'com_insert_select';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Com_update,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'com_update';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Com_replace,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'com_replace';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Com_replace_select,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'com_replace_select';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Table_scan_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Handler_read_rnd_next';"| grep -v Value |awk '{pri<br>nt $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'com_select';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)<br>}'<br>UserParameter=mysql.Open_files,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'open_files';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Open_files_limit,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'open_files_limit';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Open_files_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'open_files';"| grep -v Value |awk '{print $2}') $(<br>HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'open_files_limit';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'<br>UserParameter=mysql.Created_tmp_disk_tables,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'created_tmp_disk_tables';"| grep -v Value |awk '{<br>print $2}'<br>UserParameter=mysql.Created_tmp_tables,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'created_tmp_tables';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Created_tmp_disk_tables_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'created_tmp_disk_tables';"| grep -v V<br>alue |awk '{print $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'created_tmp_tables';"| grep -v Value |awk '{print $2}')| awk '{if($<br>2 == 0){print 0;}else {printf("%1.4f\n",$1/$2*100);}}'<br>UserParameter=mysql.Max_connections,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'max_connections';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Max_used_connections,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Max_used_connections';"| grep -v Value |awk '{print <br>$2}'<br>UserParameter=mysql.Max_connections_used_rate,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Max_used_connections';"| grep -v Value |<br>awk '{print $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show variables like 'max_connections';"| grep -v Value |awk '{print $2}')| awk '{if($2 == 0<br>){print 0;}else {printf("%1.4f\n",$1/$2*100);}}'<br>UserParameter=mysql.Table_locks_immediate,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Table_locks_immediate';"| grep -v Value |awk '{prin<br>t $2}'<br>UserParameter=mysql.Table_locks_waited,HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'table_locks_waited';"| grep -v Value |awk '{print $2}'<br>UserParameter=mysql.Engine_select,echo $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'Table_locks_immediate';"| grep -v Value |awk '{print<br> $2}') $(HOME=/etc/zabbix/mysql /usr/local/mysql/bin/mysql -uroot -e "show status like 'table_locks_waited';"| grep -v Value |awk '{print $2}')| awk '{if($2 == 0){print 0;}<br>else {printf("%1.4f\n",$1/$2);}}'<br></span>

在 /usr/local/zabbix/etc/zabbix_agentd.conf 添加如下一行
    Include=usr/local/zabbix/etc/zabbix_agentd.conf.d/userparameter_mysql.conf



重启zabb_agentd
    # /etc/init.d/zabbix_agentd restart


导入MySQL的模版
1）选择“组态”-->"模版"  点击“汇入”


2）然后选择要导入的模版，并点击导入即可

按照上边步骤导入MySQL Slave的监控模版即可

链接导入的模版即可

通过最新数据可以查看MySQL的状态，以及主从复制的状态和延迟



zabbix监控MySQL服务器的模版(具体模版在附件中)
    监控MySQL的通用模版：Template_MySQL.xm
    MySQL的Slave模版：Template_MySQL_Slave.xml
百度云附件：zabbixMySQL的Slave模版.xml等