zabbix 监控mysql的方法

2024-11-27 05:22:05

zabbix部署文档

zabbix部署完之后

zabbix-agent操作

1.监控mysql，首先要先安装mysql

[root@localhost ~]# yum -y install mariadb mariadb-server

2.编写mysql监控项的脚本

在zabbix-agent先授权个用户不然测试时没有权限

[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant all on *.* to 'check'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql监控的内容主要有

主从的状态 (得先配置主从在下面)
流量检测发送，接受常规操作增删改查
某个库、某个表的大小
tps(每秒查询处理的事务数）qps(每秒能处理多少次请求数)

[root@localhost ~]# mkdir /etc/zabbix/scipts
[root@localhost ~]# cd /etc/zabbix/scipts/
[root@localhost scipts]# vim mysql.sh
#!/bin/bash
mysql="mysql -ucheck -p123"
case $1 in
 # mysql主从状态
 slave_status)
  $mysql -e "show slave status\G" |grep "Yes" |wc -l
 ;;
 # mysql流量 接受
 Bytes_received)
  mysqladmin extended-status |grep "Bytes_received" |awk '{print $4}'
 ;;
 # mysql流量 发送
 Bytes_sent)
  mysqladmin extended-status |grep "Bytes_sent" |awk '{print $4}'
 ;;
 # mysql常规操作 增
 Com_insert)
  mysqladmin extended-status |grep -w "Com_insert" |awk '{print $4}'
 ;;
 # mysql常规操作 删
 Com_delete)
  mysqladmin extended-status |grep -w "Com_delete" |awk '{print $4}'
 ;;
 # mysql常规操作 改
 Com_update)
  mysqladmin extended-status |grep -w "Com_update" |awk '{print $4}'
		;;
 # mysql常规操作 查
 Com_select)
  mysqladmin extended-status |grep -w "Com_select" |awk '{print $4}'
 ;;
 # mysql tps
 tps)
  mysqladmin status |awk '{print $6/$2}'
 ;;
 # mysql qps=(rollback+commit)/uptime
 qps)
  rollback=$(mysqladmin extended-status |grep -w "Com_rollback" |awk '{print $4}')
  commit=$(mysqladmin extended-status |grep -w "Com_commit" |awk '{print $4}')
  uptime=$(mysqladmin status |awk '{print $2}')
  count=$[$rollback+$commit]
  echo "$count $uptime" > /tmp/a.txt
  cat /tmp/a.txt |awk '{print $1/$2}'
 ;;
 # 库大小 我们这里拿mysql库举例
 db)
  $mysql -e "select sum(data_length) from information_schema.tables where table_schema='mysql'" |sed -n '2p'
 ;;
 # 表大小 我们这里拿mysql下面的user表举例
 tb)
  $mysql -e "select sum(data_length) from information_schema.tables where table_schema='mysql' and table_name='user'" |sed -n '2p'
 ;;
esac

3.自定义键值key 重启zabbix-agent

[root@localhost scipts]# cd /etc/zabbix/zabbix_agentd.d/
[root@localhost zabbix_agentd.d]# vim mysql.conf
UserParameter=mysql[*],/etc/zabbix/scipts/mysql.sh $1
[root@localhost zabbix_agentd.d]# systemctl restart zabbix-agent

4.在zabbix-server测试先安装zabbix-get

[root@localhost ~]# yum -y install zabbix-get

[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[slave_status]
2
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Bytes_received]
850970
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Bytes_sent]
224906
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_insert]
3001
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_delete]
135
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_update]
128
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_select]
19
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[qps]
0.864842
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[tps]
1.92936
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[db]
555118
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[tb]
420

报错处理

[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[slave_status]
sh: /etc/zabbix/scipts/mysql.sh: 权限不够

脚本执行权限不够 去zabbix-agent 加权限
[root@localhost zabbix_agentd.d]# chmod +x /etc/zabbix/scipts/mysql.sh 

[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[slave_status]
ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation

是因为用户没有权限查看 去zabbix-agent 授权个用户在脚本里面加上
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant all on *.* to 'check'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

[root@localhost scipts]# vim mysql.sh
#!/bin/bash
mysql="mysql -ucheck -p123"
case $1 in
 # mysql主从状态
 slave_status)
  $mysql -e "show slave status\G" |grep "Yes" |wc -l
 ;;

zabbix页面上添加监控项和图形

查看mysql流量数据

查看mysql qps tps

查看mysql主从状态

查看mysql常规操作

查看mysql库表大小

mysql主从配置

一.zabbix-server端

[root@localhost ~]# vim /etc/my.cnf

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 175170 |  |   |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant all on *.* to 'tom'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

二.zabbix-agent端

[root@localhost ~]# vim /etc/my.cnf

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> change master to
 -> master_host='192.168.27.136',
 -> master_user='tom',
 -> master_password='123',
 -> master_log_file='mysql-bin.000001',
 -> master_log_pos=175170;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
   Master_Host: 192.168.27.136
   Master_User: tom
   Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mysql-bin.000001
  Read_Master_Log_Pos: 175170
  Relay_Log_File: mysql-relay.000004
  Relay_Log_Pos: 529
 Relay_Master_Log_File: mysql-bin.000001
  Slave_IO_Running: Yes
  Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
  Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
   Last_Errno: 1146
   Last_Error: Error 'Table 'zabbix.history_uint' doesn't exist' on query. Default database: 'zabbix'. Query: 'insert into history_uint (itemid,clock,ns,value) values (23287,1602301747,810415730,1)'
   Skip_Counter: 0
  Exec_Master_Log_Pos: 173424
  Relay_Log_Space: 2565
  Until_Condition: None
  Until_Log_File:
  Until_Log_Pos: 0
  Master_SSL_Allowed: No
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
  Master_SSL_Cert:
  Master_SSL_Cipher:
  Master_SSL_Key:
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
  Last_IO_Errno: 0
  Last_IO_Error:
  Last_SQL_Errno: 1146
  Last_SQL_Error: Error 'Table 'zabbix.history_uint' doesn't exist' on query. Default database: 'zabbix'. Query: 'insert into history_uint (itemid,clock,ns,value) values (23287,1602301747,810415730,1)'
 Replicate_Ignore_Server_Ids:
  Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: No query specified

报错处理

[root@localhost ~]# vim /etc/my.cnf

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
   Master_Host: 192.168.27.136
   Master_User: tom
   Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mysql-bin.000001
  Read_Master_Log_Pos: 199126
  Relay_Log_File: mysql-relay.000006
  Relay_Log_Pos: 3950
 Relay_Master_Log_File: mysql-bin.000001
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Replicate_Do_DB:
  Replicate_Ignore_DB:
  Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
   Last_Errno: 0
   Last_Error:
   Skip_Counter: 0
  Exec_Master_Log_Pos: 199126
  Relay_Log_Space: 4240
  Until_Condition: None
  Until_Log_File:
  Until_Log_Pos: 0
  Master_SSL_Allowed: No
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
  Master_SSL_Cert:
  Master_SSL_Cipher:
  Master_SSL_Key:
 Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
  Last_IO_Errno: 0
  Last_IO_Error:
  Last_SQL_Errno: 0
  Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
  Master_Server_Id: 1
1 row in set (0.00 sec)

到此这篇关于zabbix 监控mysql的方法的文章就介绍到这了,更多相关zabbix 监控mysql内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

zabbix监控MySQL主从状态的方法详解

搭建MySQL主从后,很多时候不知道从的状态是否ok,有时候出现异常不能及时知道,这里通过shell脚本结合zabbix实现监控并告警一般情况下,在MySQL的从上查看从的运行状态是通过Slave_IO_Running线程和Slave_SQL_Running线程是否ok,通过命令"show slave status\G;"即可查看.所以这里根据这两个值进行判断. agent端脚本编写及配置说明:所有zabbix相关的脚本我都放在了/etc/zabbix/script/ 目录里面,下
Zabbix 2.4.5自带MySQL监控的配置使用教程

本文主要给大家介绍了关于Zabbix 2.4.5自带MySQL监控配置使用的相关内容,分享出来供大家参考学习,下面来一起看看详细的介绍: 一.相关说明生产线上使用zabbix 2.4.5 对所有业务及系统网络进行监控,当然也需要监控mysql数据库相关的信息,zabbix自带的mysql监控模板就可以监控mysql,本文在zabbix 2.4.5版本下通过,其他版本请自行实验. 二.配置要监控的mysql 1.先授权让zabbixagent连接数据库: 以mysql roott身份登录到mys
zabbix监控Nginx/Tomcat/MySQL的详细教程

zabbix监控Nginx A机器:zabbix服务端(192.168.234.128) B机器:zabbix客户端(192.168.234.125) 在B机器(zabbix客户端)操作: 编辑nginx虚拟主机配置文件: [root@centos ~]# vi /etc/nginx/conf.d/default.conf 在server{}中添加以下内容: location /nginx_status { stub_status on; access_log off; allow 127.0.
安装配置Zabbix来监控MySQL的基本教程

Zabbix的简单安装配置说明 1.在已有的LAMP或者LNMP的基础上安装zabbix,安装一些依赖包: yum -y install mysql-devel libcurl-devel net-snmp-devel 2.添加用户: groupadd zabbix useradd zabbix -g zabbix 3.创建数据库,添加授权账号 create database zabbix character set utf8; grant all privileges on zabbix.*
Zabbix实现监控多个mysql过程解析

一台服务器上开启了3个mysql实例进程,占用不同的端口 3306.3307.3308 原理说明: 通过自动发现规则来获取MySQL实例的端口,自动发现规则上的{$MYSQLPORT}是要传递给agent自动发现脚本的参数,这个值是从主机定义的宏{$MYSQLPORT}获取过来的,自动发现的脚本将其解析成{#MYSQLPORT}:端口的形式,监控项原型再根据{#MYSQLPORT}的值来生成监控项,大致流程如下: 主机定义宏{$MYSQLPORT}->自动发现规则键值{$MYSQLPORT}->
zabbix 监控mysql的方法

zabbix部署文档 zabbix部署完之后 zabbix-agent操作 1.监控mysql,首先要先安装mysql [root@localhost ~]# yum -y install mariadb mariadb-server 2.编写mysql监控项的脚本在zabbix-agent先授权个用户不然测试时没有权限 [root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g.
zabbix通过percona插件监控mysql的方法

1.安装PHP脚本运行环境 yum install -y php php-mysql 2.加载官方percona模板 [root@cat /]# wget https://www.percona.com/downloads/percona-monitoring-plugins/1.1.6/percona-zabbix-templates-1.1.6-1.noarch.rpm [root@cat /]# rpm -ivh percona-zabbix-templates-1.1.6-1.noarc
zabbix监控mysql的实例方法

1.监控规划在创建监控项之前要尽量考虑清楚要监控什么,怎么监控,监控数据如何存储,监控数据如何展现,如何处理报警等.要进行监控的系统规划需要对Zabbix很了解,这里只是提出监控的需求. 需求一:监控MySQL的状态,当状态发生异常,发出报警: 需求二:监控MySQL的操作,并用图表展现: 2.自定义脚本监控扩展Agent Zabbix Server与Agent之间监控数据的采集主要是通过Zabbix Server主动向Agent询问某个Key的值,Agent会根据Key去调用相应的函数去获取
Zabbix监控交换机设置方法

说明: Zabbix监控服务端已经配置完成,现在要使用Zabbix对交换机进行监控. 具体操作: 以下操作在被监控的交换机上进行,这里以Cisco交换机为例. 一.登录到Cisco交换机,开启snmp服务注意:使用telnet或者仿真终端登录到交换机特权配置模式 enable #切换到特权模式 configure terminal #进入全局配置模式 snmp-server community public ro #打开交换机snmp服务,设置团体名称为public,只读 snmp-serve
使用zabbix监控mongodb的方法

MongoDB 是一个基于分布式文件存储的数据库.由 C++ 语言编写.旨在为 WEB 应用提供可扩展的高性能数据存储解决方案. MongoDB 是一个介于关系数据库和非关系数据库之间的产品,是非关系数据库当中功能最丰富,最像关系数据库的. Mongodb如今越来越火,要做好对mongodb的监控就需要从它的安装配置,到简单的command语句使用,再到对它运行机制以及状态获取方法的掌握. mongodb有三种基本的状态获取方式: 1.mongostat 2.开启28017的监听端口,curl
Zabbix监控多个JVM进程的方法

一.场景说明: 我们这边的环境用的是微服务,每个程序都是有单独的进程及单独的端口号,但用jps查询出来的结果有些还会有重名的情况,所以某些脚本不太适用本场景: 二.需求说明: 需使用Zabbix-server监控每个Agent上的jvm进程(监控项具体在模板中展示) 三.准备环境: 1.jvm.py脚本(非本人原创,从Github上直接copy的,此处附上Github地址,可直接去目标地址查看相关说明) 2.https://github.com/qiueer/zabbix/blob/mast
解决zabbix监控因php问题导致图形界面中文乱码方法

解决因编译php中添加了-enable-gd-jis-conv选项导致Zabbix监控系统图形界面中文乱码问题现象: php编译参数: 说明: 如果PHP编译时启用–enable-gd-jis-conv选项的话,那么非ASCII字符(例如汉字.拼音.希腊文和箭头) 会被当成EUC-JP编码 (phpinfo中美其名曰"支持JIS编码的字体"), 从而导致乱码(由于西文字体没有假名或汉字,一般表现为全部是方框).imagettftext()函数是将字符写入到图片的函数,这个问题就是由这

zabbix 监控mysql的方法

相关推荐

随机推荐