Shulou(Shulou.com)06/01 Report--

(1) brief introduction

Zabbix will use its own template Template App MySQL when monitoring mysql database, which cannot be used directly. Because there is no key and data cannot be obtained, the front end will report an error like "Warning: Using a password on the command line interface can be insecure." The reason for the error is that a password security policy has been added in the later version of mysql 5.6. adding a password to the command line will force the error to be reported, while the version before 5.6can be used directly.

There was a previous article about password-less login through mysql-login-path=local, while this article uses another way to do password-free authentication login by modifying / etc/my.cnf.

Error probe: this prompt will appear after mysql 5.5. Moreover, this prompt will be caught by zabbix-servre. In the zabbix-server.log log, 24123 sent 20160826 error reason for "110: mysql.status [Bytes _ sent]" changed: Received value [mysqladmin: [Warning] Using a password on the command line interface can be insecure.8991074] is not suitable for value type [Numeric (float)] prompt that the parameter does not match on the zabbix-server server. When using the zabbix-get command, [root@localhost ~] # / usr/local/zabbix/bin/zabbix_get-s 172.21.100.12-p10050-k mysql.status [uptime] mysqladmin: [Warning] Using a password on the command line interface can be insecure.522345 means that the key value obtained by zabbix_key is "mysqladmin: [Warning] Using a password on the command line interface can be insecure." Instead of 522345, the value type on the server side is incorrect. To solve this problem, you must log in without a password. For security, only localhost is allowed to log in. (2) background introduction IP hostname function 172.20.66.110zabbix_serverzabbix server 172. 21.100.12zabbix_agentzabbix client database

(3) specific implementation steps

One, zabbix_agentd client settings

1. Create a normal user lqb on mysql data

[root@localhost] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 756Server version: 5.5.58-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > grant all PRIVILEGES on *. * to lqb@'localhost' identified by '123456password; # create an authorized access user lqb password setting 123456Query OK, 0 rows affected (0.04 sec) mysql > update mysql.user set authentication_string=password (' 123456') where user='lqb' and Host = 'localhost'; # update the user's password Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > flush privileges Query OK, 0 rows affected (0.01 sec) mysql > exitBye

two。 Modify / etc/my.cnf file to create password-less login

[root@localhost ~] # vim / etc/ my.cnf [client] user=lqbpassword= 123456 [mysqladmin] host=localhostuser=lqbpassword=123456

3. Test whether it can be accessed directly, if you enter the command mysql-ulqb directly to indicate that it has been OK.

[root@localhost] # mysql-ulqbWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 761Server version: 5.5.58-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > select Host,User,authentication_string from mysql.user +-- +-+-- + | Host | User | authentication_string | +- -+-+-- + | localhost | root | localhost.localdomain | root | | | 127.0.0.1 | root |:: 1 | root |% | root | NULL | |% | | | zabbix | NULL | | localhost | lqb | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 172.21.100.12 | zabbix | NULL | +-- +-- | -- + 8 rows in set (0.01sec) mysql > exit Bye [root@localhost scripts] # mysqladmin extended-status | grep-w "Bytes_received" | cut-d "|"-f3 # indicates that the data returned is normal 10792596272

4. Create a mysql monitoring script in the directory / usr/local/zabbix/scripts/chk_mysql.sh and grant the relevant permissions.

[root@localhost scripts] # cat / usr/local/zabbix/scripts/chk_mysql.sh #! / bin/bash#-# FileName: check_mysql.sh# Revision: 1. Website Date: 2018 Author: lqb# Email: # Website: # Description: # Notes: ~ # -# Copyright: 2015 (c) DengYun# License: GPL# user MYSQL_USER='zabbix'MYSQL_USER='lqb'# password MYSQL_PWD='123456'# host address / IPMYSQL_HOST='localhost'# port MYSQL_PORT='3306'# data connection # MYSQL_CONN= "/ usr/local/mysql/bin/mysqladmin-u$ {MYSQL_USER}-p$ {MYSQL_PWD}-h$ {MYSQL_HOST} -whether the parameter P$ {MYSQL_PORT} "MYSQL_CONN=" / usr/local/mysql/bin/mysqladmin "# is correct if [$#-ne" 1 "] Then echo "arg error!" Fi # get data case $1 in Uptime) result= `${MYSQL_CONN} status | cut-f2-d ":" | cut-F1-d "T" `echo $result;; Com_update) result=` ${MYSQL_CONN} extended-status | grep-w "Com_update" | cut-d "|"-f3` echo $result Slow_queries) result= `$ {MYSQL_CONN} status | cut-f5-d ":" | cut-F1-d "O" `echo $result;; Com_select) result= `$ {MYSQL_CONN} extended-status | grep-w "Com_select" | cut-d "|"-f3` echo $result " Com_rollback) result= `$ {MYSQL_CONN} extended-status | grep-w "Com_rollback" | cut-d "|"-f3` echo $result;; Questions) result= `$ {MYSQL_CONN} status | cut-f4-d ":" | cut-F1-d "S" `echo $result Com_insert) result= `$ {MYSQL_CONN} extended-status | grep-w "Com_insert" | cut-d "|"-f3` echo $result;; Com_delete) result= `$ {MYSQL_CONN} extended-status | grep-w "Com_delete" | cut-d "|"-f3` echo $result " Com_commit) result= `$ {MYSQL_CONN} extended-status | grep-w "Com_commit" | cut-d "|"-f3` echo $result;; Bytes_sent) result= `$ {MYSQL_CONN} extended-status | grep-w "Bytes_sent" | cut-d "|"-f3` echo $result " Bytes_received) result= `$ {MYSQL_CONN} extended-status | grep-w "Bytes_received" | cut-d "|"-f3` echo $result;; Com_begin) result= `$ {MYSQL_CONN} extended-status | 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 [root@localhost zabbix] # chmod + x scripts/chk_mysql.sh [root@localhost zabbix] # chown-R zabbix.zabbix scripts/chk_mysql.sh [root@localhost zabbix] # ll scripts/total 4-rwxr-xr-x 1 zabbix zabbix 2675 Jan 4 04:01 chk_mysql.shnull

5. Modify zabbix_agentd.conf to add the following parameters:

UserParameter=mysql.version,mysql-VUserParameter=mysql.status [*], / usr/local/zabbix/scripts/chk_mysql.sh $1UserParameterial mysql.pingql.pingGen usr/local/zabbix/scripts/chk_mysql.sh _ ulqb ping _ | grep _ c _ alive [root@localhost scripts] # grep'^ [aMuz]'/ usr/local/zabbix/etc/zabbix_agentd.confLogFile=/tmp/zabbix_agentd.logServer=172.20.66.110Hostname=172.21.100.12RefreshActiveChecks=120Timeout=20UserParameter=mysql.version,mysql-VUserParameter=mysql.status [*], / usr/local/zabbix/scripts/chk_mysql.sh $1UserParameter=mysql.ping. / usr/local/mysql/bin/mysqladmin-ulqb ping | grep-c alive

6. Restart the zabbix_agentd client service to see if there is an error.

[root@localhost scripts] # / etc/init.d/zabbix_agentd restartRestarting zabbix_agentd (via systemctl): [OK] # [root@localhost scripts] # tail-f / tmp/zabbix_agentd.log 24025 purl 20180104 via systemctl 041135.331 * Enabled features * 24025 OK 20180104via systemctl 04135.331 IPv6 support: NO 24025pur20180104purl 041135.331 TLS support: NO 24025purl 20180104purl 04135.331 * * * 24025 main process 20180104 using configuration file 041135.331 using configuration file: / main process 24025 20180104 agent # 0 started [main process] 24030 started 20180104agent # 4 started [listener # 3] 240282820180104purl 041135.332 agent # 2 started [listener # 1] 2480274purl 041135.332 agent # 1 started [collector] 24029140 20180104Rich 04135.333 agent # 3 started [listener # 2]

two。 Browser settings on the zabbix_ server side

1. Accept and check whether the mysql script information can be obtained normally on the zabbix_server side.

[root@localhost slow] # / usr/local/zabbix/bin/zabbix_get-s 172.21.100.12-p10050-k mysql.status [Uptime] 2165105

two。 Add a host. Configuration-the host fills in the relevant information.

3. Link related templates. Click the templates tab-Select-select Templeate DB MySQL templates-add-update

4. Check to see if there is a red alert in the monitoring item, even if it is not normal.

5. Wait two minutes (the data defaults to 1 minute to get the data), and the relevant data can be obtained.

6.

So far, the monitoring mysql is complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.