zabbix 监控mysql的方法

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内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！