Zabbix 3.0.2 monitoring MySQL

Intranet has a MySQL server, version is 5.7.14

For the installation of this version, interested can refer to

http://xiao987334176.blog.51cto.com/2202382/1783509

Zabbix comes with a template templates App MySQL, used to monitor MySQL's

However, you cannot use it directly, otherwise you will not get the data because there is no key.

Detailed steps are described below.

First install zabbix-agent on MySQL server, please refer to

http://xiao987334176.blog.51cto.com/2202382/1768281

The bottom part.

###################################################################################

To explain a more important question.

In the shell, run MySQL-related commands directly, specify the password, there will be a hint

For example: Mysql-u zabbix-p123456 after running

Warning:using a password on the command line interface can is insecure.

This hint will appear after MySQL 5.5.

Also, this hint will be captured by Zabbix-servre and will appear in the Zabbix-server.log log.

24123:20160826:101433.609 error reason for "110:mysql.status[bytes_sent]" changed:received value [mysqladmin: [Warning ] Using a password on the command line interface can being insecure.8991074] is isn't suitable for value type [Numeric (float)]

Hint parameter does not match

Above the Zabbix-server server, when using the Zabbix-get command

[Email protected] ~]#/usr/local/zabbix/bin/zabbix_get-s 192.168.1.110-p10050-k Mysql.status[uptime]

Mysqladmin: [Warning] Using a password on the command line interface can be insecure.

57577

###################################################################################

So in order to solve this problem, you must login without a password, for security purposes, limit only on localhost login

Create a Zabbix user first (create user, password cannot be empty, otherwise error)

Grant all privileges on * * to [email protected] ' localhost ' identified by ' 123456 ';

Set Password to root

Update Mysql.user set Authentication_string=password (' root ') where user= ' Zabbix ' and Host = ' localhost ';

Refresh Permissions

Flush privileges;

Exit

Exit

Test Password-Free login

[Email protected] opt]# mysql-u Zabbix

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 930

Server version:5.7.14 Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Exit

Bye

Found a very strange phenomenon, the password settings and root, you can login without a password

Mysql> select host,user,authentication_string from Mysql.user;

+-----------+-----------+-------------------------------------------+

| Host | User | authentication_string |

+-----------+-----------+-------------------------------------------+

| localhost | Root | *81f5e21e35407d884a6cd4a731aebfb6af209e1b |

| localhost | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere |

| localhost | Zabbix | *81f5e21e35407d884a6cd4a731aebfb6af209e1b |

+-----------+-----------+-------------------------------------------+

3 Rows in Set (0.00 sec)

Enter the Zabbix-agent directory of the MySQL server

cd/usr/local/zabbix-agent/

mkdir alertscripts

Edit Script

Vim checkmysql.sh

The contents are as follows:

#!/bin/sh

#MYSQL_SOCK = "/data/3306/mysqld.sock"

#MYSQL_PWD = ' Cat/usr/local/zabbix-agent/alertscripts/.mysqlpassword '

Args=1

If [$#-ne "$ARGS"];then

echo "Please input one arguement:"

Fi

Case $ in

Uptime)

result= ' Mysqladmin-uzabbix status|cut-f2-d ":" |cut-f1-d "T" '

Echo $result

;;

Com_update)

result= ' Mysqladmin-uzabbix extended-status |grep-w "com_update" |cut-d "|"-f3 '

Echo $result

;;

Slow_queries)

result= ' Mysqladmin-uzabbix status |cut-f5-d ":" |cut-f1-d "O"

Echo $result

;;

Com_select)

result= ' Mysqladmin-uzabbix extended-status |grep-w "com_select" |cut-d "|"-f3 '

Echo $result

;;

Com_rollback)

result= ' Mysqladmin-uzabbix extended-status |grep-w "com_rollback" |cut-d "|"-f3 '

Echo $result

;;

Questions)

result= ' Mysqladmin-uzabbix status|cut-f4-d ":" |cut-f1-d "S" '

Echo $result

;;

Com_insert)

result= ' Mysqladmin-uzabbix extended-status |grep-w "Com_insert" |cut-d "|"-f3 '

Echo $result

;;

Com_delete)

result= ' Mysqladmin-uzabbix extended-status |grep-w "com_delete" |cut-d "|"-f3 '

Echo $result

;;

Com_commit)

result= ' Mysqladmin-uzabbix extended-status |grep-w "com_commit" |cut-d "|"-f3 '

Echo $result

;;

Bytes_sent)

result= ' Mysqladmin-uzabbix extended-status |grep-w "bytes_sent" |cut-d "|"-f3 '

Echo $result

;;

bytes_received)

result= ' Mysqladmin-uzabbix extended-status |grep-w "bytes_received" |cut-d "|"-f3 '

Echo $result

;;

Com_begin)

result= ' Mysqladmin-uzabbix 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

Set permissions

chmod 755 checkmysql.sh

Chown Zabbix:zabbix-r/usr/local/zabbix-agent/

Editing a configuration file

Vim/usr/local/zabbix-agent/etc/zabbix_agentd.conf

Add the purple section, which reads as follows:

Logfile=/usr/local/zabbix-agent/logs/zabbix_agentd.log

# # #zabbix Service-side address

server=192.168.1.109

# #agent服务监听地址, which is the native address

#ListenIP =192.168.1.105

# # #ServerActive =192.168.1.110

# #zabbix-server End host address (Zabbix server)

Hostname=zabbix Server

Userparameter=mysql.version,mysql-v

Userparameter=mysql.status[*],/usr/local/zabbix-agent/alertscripts/checkmysql.sh $

Userparameter=mysql.ping,mysqladmin-uzabbix Ping | Grep-c Alive

Restart Zabbix_agentd

/etc/init.d/zabbix_agentd restart

Check MySQL on the zabbix-server server

[Email protected] alertscripts]# /usr/local/zabbix/bin/zabbix_get-s 192.168.1.110-p10050-k mysql.status[uptime ]9479

[Email protected] alertscripts]#

If you can return a number directly, there is no extra character, it means success.

Adding a host to Zabbix-server

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/86/78/wKiom1e_1PXyeowlAABaU5gX3nw180.png-wh_500x0-wm_3 -wmp_4-s_4186108252.png "title=" 1.png "alt=" Wkiom1e_1pxyeowlaabau5gx3nw180.png-wh_50 "/>

When adding templates, choose Template App MySQL and template OS Linux

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/86/78/wKioL1e_1QDA7LctAABF08HqVyk658.png-wh_500x0-wm_3 -wmp_4-s_3276913125.png "title=" 2.png "alt=" Wkiol1e_1qda7lctaabf08hqvyk658.png-wh_50 "/>

Wait 10 minutes and the image will come out.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/86/78/wKiom1e_1Y3RRp_hAAEBwRFRqys035.png-wh_500x0-wm_3 -wmp_4-s_1620799521.png "title=" 3.png "alt=" Wkiom1e_1y3rrp_haaebwrfrqys035.png-wh_50 "/>

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/86/78/wKioL1e_1ZrAmlj_AAD3JVYNG0M361.png-wh_500x0-wm_3 -wmp_4-s_2336992150.png "title=" 4.png "alt=" Wkiol1e_1zramlj_aad3jvyng0m361.png-wh_50 "/>

This article is from the "Falling Star" blog, make sure to keep this source http://xiao987334176.blog.51cto.com/2202382/1842935

Zabbix 3.0.2 monitoring MySQL