zabbix监控MySQL部署实战

1.部署zabbix监控。

1.1 建用户组和用户

 groupadd zabbix
 useradd -d /home/zabbix -g zabbix -m zabbix
 passwd zabbix

1.2 安装基础包

yum -y install wget unzip libxml2 libxml2-devel httpd php php-mysql php-common php-mbstring php-gd php-odbc php-pear curl curl-devel net-snmp net-snmp-devel perl-DBI php-xml ntpdate  php-bcmath zlib-devel glibc-devel curl-devel gcc automake libidn-devel openssl-devel net-snmp-devel rpm-devel OpenIPMI-devel

1.3 创建文件夹 赋权限

mkdir -p /apps/svr/zabbix
chown -R zabbix:zabbix /apps/svr/zabbix

1.4 下载zabbix源码包 编译安装

http://www.zabbix.com
zabbix-2.4.6.tar.gz
tar -xzvf zabbix-2.4.6.tar.gz
cd /apps/zabbix-2.4.6/



 ./configure --prefix=/apps/svr/zabbix  --enable-server --enable-agent --with-mysql --with-net-snmp --with-libxml2 --with-libcurl

 make&make install

1.5 数据库运行相关脚本

//创建数据库
create database zabbix default character set utf8;

//创建用户并且赋权
mysql> grant all privileges on *.* to 'zabbix'@'%' identified by 'zabbix';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'zabbix'@'localhost' identified by 'zabbix';
Query OK, 0 rows affected (0.01 sec)

//运行脚本 创建表结构
mysql>source /apps/zabbix-2.4.6/database/mysql/schema.sql

//运行脚本 加载表数据
mysql>source /apps/zabbix-2.4.6/database/mysql/data.sql

1.6 配置zabbix参数文件

修改以下地方:

[root@localhost etc]# cd /apps/svr/zabbix/etc/
[root@localhost etc]# vi ./zabbix_server.conf

1.7 配置php参数文件

//备份
cp /etc/php.ini /etc/php.ini.zabbixbak
//修改文件   
sed -i 's/max_execution_time = 30/max_execution_time = 300/g' /etc/php.ini
sed -i 'date.timezone =/a\date.timezone = Asia/Shanghai' /etc/php.ini
sed -i '/max_input_time =/s/60/300/' /etc/php.ini
sed -i '/mbstring.func_overload = 0/a\mbstring.func_overload = 1' /etc/php.ini
sed -i '/post_max_size =/s/8M/32M/' /etc/php.ini

//创建前端页面目录 拷贝前端文件
[root@localhost zabbix]# cd /var/www/html
[root@localhost html]# mkdir zabbix
[root@localhost frontends]# cp -r php/* /var/www/html/zabbix

//修改apache配置
[root@localhost frontends]# vim /etc/httpd/conf/httpd.conf 

//启动apache
root@localhost frontends]# service httpd start

//启动zabbix
[root@localhost zabbix-2.4.6]# cd /apps/svr/zabbix/sbin
./zabbix_server -c /apps/svr/zabbix/etc/zabbix_server.conf

//浏览器配置

**注意:**

//浏览器登陆

//配置agent
 [root@localhost zabbix]# cd /apps/svr/zabbix/etc
[root@localhost etc]# ll
总用量 28
-rw-r--r-- 1 zabbix zabbix  2537 7月  17 15:54 zabbix_agent.conf
drwxr-xr-x 2 zabbix zabbix 6 7月  17 15:54 zabbix_agent.conf.d
-rw-r--r-- 1 zabbix zabbix  7797 7月  17 15:54 zabbix_agentd.conf
drwxr-xr-x 2 zabbix zabbix 6 7月  17 15:54 zabbix_agentd.conf.d
-rw-r--r-- 1 zabbix zabbix 13300 7月  17 16:29 zabbix_server.conf
drwxr-xr-x 2 zabbix zabbix 6 7月  17 15:54 zabbix_server.conf.d
[root@localhost etc]# vim zabbix_agent.conf

[root@localhost etc]# vim zabbix_agentd.conf

//启动 agent
[root@localhost etc]# service zabbix_agent start  

2.自定义监控MySQL实例状态。

2.1建立参数目录

//key目录
[root@localhost etc]# mkdir -p /apps/parameters
//脚本目录
[root@localhost etc]# mkdir -p /apps/scripts/mysql

2.2 agentd参数文件中指定监控参数目录位置

[root@localhost etc]# vim /apps/svr/zabbix/etc/zabbix_agentd.conf
Include=/apps/parameters/

、
pkill -f zabbix_agentd
/apps/svr/zabbix/sbin/zabbix_agentd start

2.3 key文件以及参数文件

[root@localhost etc]# vim /apps/parameters/zabbix_my3306.conf
UserParameter=my3306.check_mysql_status,sh /apps/scripts/mysql/

[root@localhost etc]# vim /apps/scripts/mysql/check_mysql_status_3306.sh



UserParameter=my3306.check_mysql_status,sh /apps/scripts/mysql/check_mysql_status_3306.sh

[root@localhost parameters]# vim /apps/scripts/mysql/check_mysql_status_3306.sh

#!/bin/bash
host=192.168.2.130
username=zabbix
password=zabbix
port=3307
CHECK_TIME=3
#mysql  is working MYSQL_IS_OK is 1 , mysql down MYSQL_IS_OK is 0
MYSQL_IS_OK=1
function check_mysql_status (){
 mysql -h$host  -u$username -p"$password" -P$port -e "select user();" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_IS_OK=1
else
MYSQL_IS_OK=0
fi
return $MYSQL_IS_OK
}

while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_status

if [ $MYSQL_IS_OK = 1 ] ; then
CHECK_TIME=0
echo 0
exit 0
fi
if [ $MYSQL_IS_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
echo 1
exit 1
fi
sleep 3
done

2.4 web新建模板

[root@localhost]$ ./zabbix_server -c /apps/svr/zabbix/etc/zabbix_server.conf
./zabbix_server: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

vim /etc/ld.so.conf
/apps/svr/mysql5/lib
/sbin/ldconfig

2.5 新建items

2.6 新建trigger

2.7 新建group和用户

2.8 关联模板到host

2.9 模拟Mysql实例down掉

Mysql正常运行时 查看 lastest data 0 表示正常运行

停止3307实例,再次查看 lastest data 此时已经为1

2.10 实验中出现的问题

2.10.1 无法通过 service stop 停止 zabbix_server 和 zabbix_agentd
解决方法:
pkill -f zabbix_server
pkill -f zabbix_agentd

**2.10.2 模拟MySql down掉,发现zabbix 无法采集数据 **
解决方法:
因为脚本是循环3s后才返回值,zabbix_agent的默认超时时间正好是3s,修改zabbix_server.conf，设置Timeout=8，重启zabbix_server!

2.10.3 启动 zabbix_server 报错 cannot open shared object file
解决方法:

 vim /etc/ld.so.conf
/apps/svr/mysql5/lib
/sbin/ldconfig 
 /apps/svr/zabbix/bin/zabbix_get -s 192.168.2.130 -p 10050 -k my3307.check_mysql_status

3.模拟MySQL实例crash后，监控告警发邮件到邮箱。

3.1 安装 sendmail

wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz 
tar xvzf sendEmail-v1.56.tar.gz
cp sendEmail-v1.56/sendEmail   /usr/local/bin/
chmod  +x  /usr/local/bin/sendEmail 

cd /apps/svr/zabbix/share/zabbix /alertscripts
vim sendEmail.sh

#!/bin/bash
SMTP_server='smtp.163.com'# SMTP服务器
username='zabbixfish@163.com' # 用户名
password='xxxxxxxxxx'  # 密码
from_email_address='zabbixfish@163.com' # 发件人Email地址
to_email_address="$1"   # 收件人Email地址，zabbix传入的第一个参数
message_subject_utf8="$2"   # 邮件标题，zabbix传入的第二个参数
message_body_utf8="$3"  # 邮件内容，zabbix传入的第三个参数

# 转换邮件标题为GB2312，解决邮件标题含有中文，收到邮件显示乱码的问题。
message_subject_gb2312=`iconv -t GB2312 -f UTF-8 << EOF
$message_subject_utf8
EOF`
[ $? -eq 0 ] && message_subject="$message_subject_gb2312" || message_subject="$message_subject_utf8"

# 转换邮件内容为GB2312
message_body_gb2312=`iconv -t GB2312 -f UTF-8 << EOF
$message_body_utf8
EOF`
[ $? -eq 0 ] && message_body="$message_body_gb2312" || message_body="$message_body_utf8"

# 发送邮件
sendEmail='/usr/local/bin/sendEmail'
$sendEmail -s "$SMTP_server" -xu "$username" -xp "$password" -f "$from_email_address" -t "$to_email_address" -u "$message_subject" -m "$message_body" -o message-content-type=text -o message-charset=gb2312 tls=no

[root@localhost alertscripts]# chmod +x sendEmail.sh
[root@localhost alertscripts]# ./sendEmail.sh  406466520@qq.com zabbix  hello
Jul 21 13:31:30 localhost sendEmail[84369]: Email was sent successfully!

3.2 zabbix web配置

配置邮件提醒

配置action
Configuration-->Actions-->单击“Report problems to Zabbix administrators”-->"Operations"标签页-->new

**配置zabbix_server.conf **
AlertScriptsPath=/apps/svr/zabbix/share/zabbix/alertscripts

重启 zabbix_server

3.3 模拟mysql crash 验证邮件

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

mysql> 2017-07-21T05:44:13.465897Z mysqld_safe mysqld from pid file /u01/mysql/my3307/run/mysqld.pid ended

收到邮件！！