Zabbix3.0.4 monitoring MySQL Database status

first on the Zabbix host configuration of the MySQL server information, add good Zabbix the template App mysql template, and then go to the MySQL server to install agent, Add the MySQL monitoring script again .

1.Zabbix officially provides a template for monitoring MySQL templates App MySQL, you can see the relevant items and keys.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/89/99/wKioL1gYJweDjC9iAACRyJDNUEU138.png-wh_500x0-wm_3 -wmp_4-s_3878936272.png "title=" 1.png "alt=" Wkiol1gyjwedjc9iaacryjdnueu138.png-wh_50 "/>

Open a monitoring item to view the added MySQL template

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/89/99/wKioL1gYJy-gs0LpAAEye0Xs7ks445.png-wh_500x0-wm_3 -wmp_4-s_3459397371.png "title=" 2.png "alt=" Wkiol1gyjy-gs0lpaaeye0xs7ks445.png-wh_50 "/>

The 2.zabbix_agent end combines the official provided key to write the shell script, removing the value of items from the database

Cat checkmysql.sh

#!/bin/bash
Mysqladmin=mysqladmin
mysql_sock= "/tmp/mysql.sock"
mysql_user=******
mysql_pwd=********

Args=1
If [$#-ne "$ARGS"];then
echo "Please input one arguement:"
Fi
Case $ in
Uptime)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock status|cut-f2-d ":" |cut-f1-d "T" '
Echo $result
;;
Com_update)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "com_update" |cut-d "|"-f3 '
Echo $result
;;
Slow_queries)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-s $MYSQL _sock status |cut-f5-d ":" |cut-f1-d "O"
Echo $result
;;
Com_select)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "com_select" |cut-d "|"-f3 '
Echo $result
;;
Com_rollback)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "com_rollback" |cut-d "|"- F3 '
Echo $result
;;
Questions)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock status|cut-f4-d ":" |cut-f1-d "S"
Echo $result
;;
Com_insert)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "Com_insert" |cut-d "|"-f3 '
Echo $result
;;
Com_delete)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "com_delete" |cut-d "|"-f3 '
Echo $result
;;
Com_commit)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "com_commit" |cut-d "|"-f3 '
Echo $result
;;
Bytes_sent)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "bytes_sent" |cut-d "|"-f3 '
Echo $result
;;
bytes_received)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "bytes_received" |cut-d "|" -f3 '
Echo $result
;;
Com_begin)
result= ' ${mysqladmin}-u${mysql_user}-p${mysql_pwd}-S $MYSQL _sock extended-status |grep-w "Com_begin" |cut-d "|"-f3 '
Echo $result
;;

*)
echo "Usage:$0 (uptime| com_update| slow_queries| Com_select| com_rollback| Questions) "
;;
Esac

3. Add Userparameter in zabbix_agentd.conf, the format is as follows, for Zabbix, the script is actually a plugin.

vim /usr/local/zabbix-3.0.4/etc/zabbix_agentd .conf #开启用户自定义配置 UnsafeUserParameters=1

#添加mysql监控

1. arameter=mysql.version,mysql -V 

2. UserParameter=mysql.ping,mysqladmin -u*** -p**** -S /tmp/mysql.sock

   ping | grep -c alive

 3.UserParameter=mysql.status[*],/usr/local/zabbix_agent/bin/checkmysqlperformance.sh $1 $2

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M00/89/99/wKioL1gYLRTANK0dAAA8PfxbUew853.png-wh_500x0-wm_3-wmp_4-s_2476881226.png" title="3.png" alt="wKioL1gYLRTANK0dAAA8PfxbUew853.png-wh_50" />

4. Restart the AGENTD server, and then use Zabbix_get on Zabbix server to take the value of key .

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/89/9A/wKioL1gYLaLAymtsAABQLgP1zqc752.png-wh_500x0-wm_3 -wmp_4-s_245243112.png "title=" 4.png "alt=" Wkiol1gylalaymtsaabqlgp1zqc752.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/89/9C/wKiom1gYLi3Sx3Y-AAAjqtf5iCk058.png-wh_500x0-wm_3 -wmp_4-s_563284409.png "title=" 5.png "alt=" Wkiom1gyli3sx3y-aaajqtf5ick058.png-wh_50 "/>

5. In the Zabbix front-end You can view the number of operations per second of SQL statements in real time.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/89/9A/wKioL1gYLyezzzPzAAFKH0NlM7o597.png-wh_500x0-wm_3 -wmp_4-s_181679432.png "title=" 6.png "alt=" Wkiol1gylyezzzpzaafkh0nlm7o597.png-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/89/9C/wKiom1gYLyigTiXuAAEi3aM4uyE432.png-wh_500x0-wm_3 -wmp_4-s_4265952265.png "title=" 7.png "alt=" Wkiom1gylyigtixuaaei3am4uye432.png-wh_50 "/>

6. Summary

Put the script on the server you want to monitor (Modify mysql user and password), modify the parameters of the Userparameter, and restart Agentd,link the official template App for MySQL templates.

I'm here to test the environment with the root account, the MySQL user can be granted readonly permissions during the online server security.

According to the actual demand, in addition to monitoring the above monitoring items, you can also monitor MySQL processlist,innodb and so on.

7. Problems encountered

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/89/9A/wKioL1gYMNfg9vvdAAAVU-dUllA036.png-wh_500x0-wm_3 -wmp_4-s_4053383840.png "title=" 1.png "alt=" Wkiol1gymnfg9vvdaaavu-dulla036.png-wh_50 "/>

resulting in

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/89/9C/wKiom1gYMaXSuVI1AAAo2yGjYlo164.png-wh_500x0-wm_3 -wmp_4-s_1274967840.png "title=" 3.png "alt=" Wkiom1gymaxsuvi1aaao2ygjylo164.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/89/9C/wKiom1gYMaXBefqwAAAbcg3rpa8364.png-wh_500x0-wm_3 -wmp_4-s_1513478713.png "title=" 4.png "alt=" Wkiom1gymaxbefqwaaabcg3rpa8364.png-wh_50 "/>

Add in MY.CNF

[Mysqladmin]

User=root

password=********

Restart Zabbix_agent re-Test no error

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/89/9A/wKioL1gYMlyAidOyAAARiIguBns860.png-wh_500x0-wm_3 -wmp_4-s_827326082.png "title=" 5.png "alt=" Wkiol1gymlyaidoyaaariigubns860.png-wh_50 "/>

Zabbix3.0.4 monitoring MySQL Database status