zabbix 监控mysql5.6版本 及主从

时间:2022-09-15 10:57:12

本文中部分内容是网上摘抄,如版主看到后还望谅解!

一、前期工作

1.导入mysql模板文件到zabbix

百度网盘模板地址:http://pan.baidu.com/s/1o6DkTuE

2.mysql 授权

mysql> grant replication client on *.* to 'zabbix'@'localhost' identified by '密码';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.配置my.cnf文件

由于mysql5.6版本命令行输入用户名密码出现警告信息:Warning: Using a password on the command line interface can be insecure.

所有在mysql 的my.cnf文件中加入以下内容

[mysql]
host=localhost
user=zabbix
password=密码
socket = /tmp/mysql.sock
[mysqladmin]
host=localhost
user=zabbix
password=密码
socket=/tmp/mysql.sock

4.编写脚本

a脚本:(监控mysql 状态脚本)

#more checkmysqlperformance.sh

#!/bin/bash
mysql=$(which mysql)
VAR=$1
# mysql 5.6 use password has waring
# Warning: Using a password on the command line interface can be insecure.
# [ "${var}" = '' ] && echo "" || ${mysql} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -e 'show status' | grep -v Variable_name | grep "\b${VAR}\b" | awk '{print $2}'
# mysql --defaults-extra-file=./config.cnf -e 'show status'
[ "${VAR}" = '' ] && echo "" || ${mysql} -e 'show status' | grep -v Variable_name | grep "\b${VAR}\b" | awk '{print $2}'

b脚本:(监控mysqlslave 状态)

# more check_mysql_slave.sh
#!/bin/bash
declare -a slave_is
slave_is=($(mysql -e "show slave status\G"|grep -E "Slave_IO_Running|Slave_SQL_Running:"|awk '{print $2}'))
if [ "${slave_is[0]}" = "Yes" -a "${slave_is[1]}" = "Yes" ];then
    echo "OK -slave is running"
    exit 0
else
    echo "Critical -slave is error"
    exit 2
fi

测试结果:

]# sh check_mysql_slave.sh
OK -slave is running

二、.配置zabbix agent 端

1.

# cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
UserParameter=mysql.status[*], /usr/local/bin/checkmysqlperformance.sh $1
UserParameter=mysql.ping, /usr/bin/mysqladmin ping | grep alive | wc -l
UserParameter=mysql.version, mysql -V | cut -f6 -d" " | sed 's/,//'
UserParameter=mysql.slave, /usr/local/bin/check_mysql_slave.sh |grep OK -c

2.重启zabbix_agent

三、zabbix web 端配置监控

1.针对模板中没有配置mysql slave 端items 值进行配置(图中红色部分为服务器ip)

zabbix 监控mysql5.6版本 及主从

2.配置触发器(图片中红色部分为服务器主机名)

zabbix 监控mysql5.6版本 及主从

3.停止mysql 和 slave 端进行验证

# stop slave ;

验证图片(图中标红的部分是主机和个人邮箱)

故障发生时

zabbix 监控mysql5.6版本 及主从


故障恢复时邮件

zabbix 监控mysql5.6版本 及主从