zabbix监控mysql的⽅法zabbix部署完之后zabbix-agent操作1.监控mysql，⾸先要先安装mysql[root@localhost ~]# yum -y install mariadb mariadb-server2.编写mysql监控项的脚本在zabbix-agent先授权个⽤户不然测试时没有权限[root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 33Server version: 5.5.65-MariaDB MariaDB ServerCopyright (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/bashmysql="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 tpstps)mysqladmin status |awk '{print $6/$2}';;# mysql qps=(rollback+commit)/uptimeqps)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.txtcat /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';;esac3.⾃定义键值key 重启zabbix-agent[root@localhost scipts]# cd /etc/zabbix/zabbix_agentd.d/[root@localhost zabbix_agentd.d]# vim mysql.confUserParameter=mysql[*],/etc/zabbix/scipts/mysql.sh $1[root@localhost zabbix_agentd.d]# systemctl restart zabbix-agent4.在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 ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 33Server version: 5.5.65-MariaDB MariaDB ServerCopyright (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/bashmysql="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/f[root@localhost ~]# systemctl restart mariadb[root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7Server version: 5.5.65-MariaDB MariaDB ServerCopyright (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/f[root@localhost ~]# systemctl restart mariadb[root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.65-MariaDB MariaDB ServerCopyright (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 eventMaster_Host: 192.168.27.136Master_User: tomMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 175170Relay_Log_File: mysql-relay.000004Relay_Log_Pos: 529Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1146Last_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: 0Exec_Master_Log_Pos: 173424Relay_Log_Space: 2565Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1146Last_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: 11 row in set (0.00 sec)ERROR: No query specified报错处理[root@localhost ~]# vim /etc/f[root@localhost ~]# systemctl restart mariadb[root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 4Server version: 5.5.65-MariaDB MariaDB ServerCopyright (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 eventMaster_Host: 192.168.27.136Master_User: tomMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 199126Relay_Log_File: mysql-relay.000006Relay_Log_Pos: 3950Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 199126Relay_Log_Space: 4240Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)到此这篇关于zabbix 监控mysql的⽅法的⽂章就介绍到这了,更多相关zabbix 监控mysql内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！。
