Zabbix3.4 using your own template to monitor MySQL

192.168.254.127 (Zabbix)
192.168.254.128 (MySQL)

ZABBIX_AGENTD Client Settings
Create a normal user Zabbix on MySQL data, password Zabbix
[Email protected] scripts]# mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 8
Server Version:5.7.22-log MySQL Community Server (GPL)

Copyright (c), 2018, 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> Grant all privileges the [email protected] ' 192.168.254.128 ' identified by ' Zabbix '; # # #创建一个有权限的访问用户zabbix密码设置zabbix
Query OK, 0 rows affected (0.04 sec)

mysql> Update Mysql.user Set Authentication_string=password (' Zabbix ') where user= ' Zabbix ' and Host = ' 192.168.254.128 ‘; # # #更新下改用户的密码
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
View user
Mysql> select host,user,authentication_string from Mysql.user;
+-----------------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------------+---------------+-------------------------------------------+
| localhost | root | 182ea09a38f1141b2d7916812bd097d51167c3e4 |
| localhost | mysql.session | Thisisnotavalidpasswordthatcanbeusedhere |
| localhost | mysql.sys | Thisisnotavalidpasswordthatcanbeusedhere |
|% | root | 182ea09a38f1141b2d7916812bd097d51167c3e4 |
| 192.168.254.% | SLAVE_CP | 196bdede2ae4f84ca44c47d54d78478c7e2bd7b7 |
| 192.168.254.128 | zabbix | deef4d7d88cd046eca02a80393b7780a63e7e789 |
+-----------------+---------------+-------------------------------------------+
6 rows in Set (0.00 sec)

Mysql> exit
Bye
Modify the/etc/my.cnf file to create a login without password (add the following configuration to enter)
[Client]
User=zabbix
Password=zabbix

[Mysqladmin]
host=192.168.254.128
User=zabbix
Password=zabbix

Test whether direct access is not required to enter a password, if you enter the command directly to explain that it is OK.
[Email protected] scripts]# mysql-uzabbix-h192.168.254.128
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 189
Server Version:5.7.22-log MySQL Community Server (GPL)

Copyright (c), 2018, 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>

Now the database-side test can see the relevant data
Mysqladmin extended-status |grep-w "bytes_received" |cut-d "|"-F3 # # #有数据返回说明正常
[Email protected] scripts]# mysqladmin extended-status |grep-w "bytes_received" |cut-d "|"-f3
58691

Create a MySQL monitoring script in the directory/etc/zabbix/scripts/chk_mysql.sh and give the relevant permissions.
#!/bin/bash

-------------------------------------------------------------------------------FileName:check_mysql.shRevision : 1.0date:2018/07/17author: Fat tiger Email:Website:Description:Notes: ~------------------------------------------------- ------------------------------copyright:2018 (c) LICENSE:GPL user name

Mysql_user= ' Zabbix '

Password

Mysql_pwd= ' Zabbix '

Host address/IP

Mysql_host= ' 192.168.254.128 '

Port

Mysql_port= ' 3306 '

Data connection

#MYSQL_CONN = "/usr/local/mysql/bin/mysqladmin-u${mysql_user}-p${mysql_pwd}-h${mysql_host}-p${mysql_port}"
Mysql_conn= "/usr/local/mysql/bin/mysqladmin"

parameter is correct

If [$#-ne "1"];then
echo "Arg error!"
Fi

Get Data

Case $ 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

Related Configuration path files
[email protected] scripts]# LL
Total Dosage 4
-rwxr-xr-x 1 root root 2696 July 13:51 chk_mysql.sh
[Email protected] scripts]# pwd
/etc/zabbix/scripts

Modify zabbix_agentd.conf to add the following parameters:
vi/etc/zabbix/zabbix_agentd.conf (no added)
Pidfile=/tmp/zabbix_agentd.pid
Logfile=/var/log/zabbix/zabbix_agentd.log
Logfilesize=0
server=192.168.254.127
serveractive=192.168.254.127
hostname=192.168.254.128
refreshactivechecks=120
Timeout=20
include=/etc/zabbix/zabbix_agentd.d/. conf
Unsafeuserparameters=1
Userparameter=mysql.version,mysql-v
userparameter=mysql.status[],/etc/zabbix/scripts/chk_mysql.sh $
Userparameter=mysql.ping,/usr/local/mysql/bin/mysqladmin-uzabbix Ping | Grep-c Alive

Restart the ZABBIX_AGENTD client service to see if there are any errors.
[Email protected] scripts]#/etc/init.d/zabbix-agent restart
Restarting Zabbix-agent (via Systemctl): [OK]

Browser settings on the Zabbix_server side
Accept the MySQL script information on the Zabbix_server side to see if it can be retrieved normally
[Email protected] ~]#/usr/local/zabbix/bin/zabbix_get-s 192.168.254.128-p10050-k Mysql.status[uptime]
48
Add a host. Configuration--The host fills in the relevant information.

Link related templates. Click on the Templates tab--select--Select templeate DB mysql template--add--Update

Wait two minutes (data is the default 1 minutes to get the data), you can get the relevant data

This completes the monitoring of MySQL.

Zabbix3.4 using your own template to monitor MySQL