zabbix 监控mysql的方法
更新时间:2021年5月7日 16:35 点击:1894
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内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
C#使用FileSystemWatcher控件实现的文件监控功能示例
这篇文章主要介绍了C#使用FileSystemWatcher控件实现的文件监控功能,结合实例形式分析了C# FileSystemWatcher组件的功能及监控文件更改情况的具体使用技巧,需要的朋友可以参考下...2020-06-25- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
percona-toolkit对MySQL的复制和监控类操作教程
复制类工具 1. pt-heartbeat 功能介绍: 监控mysql复制延迟 用法介绍: pt-heartbeat [OPTION...] [DSN] --update|--monitor|--check|--stop 测量复制落后主mysql或者主PostgreSQL多少时间,你可以使用这个脚本去更新主或...2015-11-24Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02