zabbix 监控案例之监控 MySQL

一、实现MySQL主从

部署mysql 主从同步，需要配置不同的server id并开启binlog
环境：MySQL 5.7.x
master 10.0.0.32
slave 10.0.0.35
zabbix server 10.0.0.31

1.1 Master配置

vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
server-id = 32
log-bin = /var/lib/mysql/master-log

root@ubuntu1804:~# systemctl restart mysql.service

1.2 Slave配置

vim /etc/mysql/mysql.conf.d/mysqld.cn
bind-address = 0.0.0.0
server-id = 35
relay-log = /var/lib/mysql/relay-log

root@ubuntu1804:~# systemctl restart mysql.service

1.3 MySQL Master授权账户

创建授权账户

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'lck'@'10.0.0.%' IDENTIFIED BY '123456';

在mysql master服务器授权账户并导出数据，然后scp到mysql backup服务器

mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql
scp /opt/backup.sql 10.0.0.35:/opt/

1.4 MySQL slave导入数据

mysql < /opt/backup.sql

root@ubuntu1804:/opt#  head -30 /opt/backup.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000002', MASTER_LOG_POS=154;

mysql
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.32',MASTER_USER='lck',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000002',MASTER_LOG_POS=154;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.32
                  Master_User: lck
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000002
          Read_Master_Log_Pos: 978181
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 586565
        Relay_Master_Log_File: master-log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

二、Procona 监控 MySQL

官方文档及下载地址

https://www.percona.com/doc/percona-monitoring-plugins/LATEST/zabbix/index.html #插件地址
https://www.percona.com/downloads/ #安装包下载地址
https://www.percona.com/doc/percona-monitoring-plugins/LATEST/zabbix/index.html #installation-instructions #安装教程

2.1 MySQL Master安装zabbix-agent

root@ubuntu1804:/opt# cd /usr/local/src/
root@ubuntu1804:/usr/local/src# wget wget https://repo.zabbix.com/zabbix/4.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_4.0-3+bionic_all.deb
root@ubuntu1804:/usr/local/src# dpkg -i zabbix-release_4.0-3+bionic_all.deb
root@ubuntu1804:/usr/local/src# apt update
root@ubuntu1804:/usr/local/src# apt -y install zabbix-agent
grep "^[a-Z]" /etc/zabbix/zabbix_agentd.conf 
PidFile=/var/run/zabbix/zabbix_agentd.pid 
LogFile=/var/log/zabbix/zabbix_agentd.log 
LogFileSize=0
Server=10.0.0.31
StartAgents=5 
ServerActive=127.0.0.1 
Hostname=10.0.0.31
Include=/etc/zabbix/zabbix_agentd.d/*.conf

systemctl restart zabbix-agent
systemctl enable zabbix-agent

2.2 MySQL Master安装Percona

#安装Percona软件包
dpkg -i percona-zabbix-templates_1.1.8-1.artful_all.deb
cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
systemctl restart zabbix-agent

#安装php环境： 目前Percona与ubuntu 自带的php 7.2不兼容，需要安装php 5.6版本
add-apt-repository ppa:ondrej/php
apt-get -y update
apt install -y php5.6 php5.6-mysql

#创建mysql认证文件：
cat /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf 
<?php 
$mysql_user = 'root';
$mysql_pass = '';

#测试脚本能否获取数据
/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
22

2.3 zabbix web导入Percona模板

2.4 zabbbix web添加主机

2.5 zabbix web对主机关联模板

2.6 验证MySQL监控数据

Percona模板中的监控项默认是五分钟收集一次监控项数据，会结合脚本检查agent上报错数据的文件的时间戳是
否超过五分钟，脚本位置在/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh。

/apps/zabbix_server/bin/zabbix_get -s 172.31.0.104 -p 10050 -k "MySQL.Key-read-requests"
22

三、自定义脚本监控MySQL

3.1 MySQL slave安装zabbix agent

apt install zabbix-agent
grep "^[a-Z]" /etc/zabbix/zabbix_agentd.conf 
PidFile=/var/run/zabbix/zabbix_agentd.pid 
LogFile=/var/log/zabbix/zabbix_agentd.log 
LogFileSize=0 Server=172.31.0.101 
StartAgents=5 
ServerActive=127.0.0.1 
Hostname=172.31.0.104 
Include=/etc/zabbix/zabbix_agentd.d/*.conf

systemctl restart zabbix-agent
systemctl enable zabbix-agent

3.2 编写监控脚本

vim /etc/zabbix/zabbix_agentd.d/mysql_monitor.sh
#!/bin/bash
Seconds_Behind_Master(){
	NUM=`mysql -uroot -e "show slave status\G;" | grep "Seconds_Behind_Master:" | awk -F: '{print $2}'`
	echo $NUM
}

master_slave_check(){
	NUM1=`mysql -uroot -e "show slave status\G;" | grep "Slave_IO_Running" | awk -F: '{print $2}' | sed 's/^[ \t]*//g'`
	#echo $NUM1
	NUM2=`mysql -uroot -e "show slave status\G;" | grep "Slave_SQL_Running:" | awk -F: '{print $2}' | sed 's/^[ \t]*//g'`
	#echo $NUM2
	if test $NUM1 == "Yes" && test $NUM2 == "Yes";then
		echo 50 
		else
		echo 100
	fi
}

main(){
	case $1 in
		Seconds_Behind_Master)
			Seconds_Behind_Master;
			;;
		master_slave_check)
			master_slave_check
			;;
	esac
}
main $1

chmod a+x mysql_monitor.sh
bash mysql_monitor.sh master_slave_check
50

3.3 zabbix agent添加自定义监控项

cd /etc/zabbix/zabbix_agentd.conf.d

root@ubuntu1804:/etc/zabbix/zabbix_agentd.d# grep "^[a-Z]" /etc/zabbix/zabbix_agentd.conf
PidFile=/var/run/zabbix/zabbix_agentd.pid
LogFile=/var/log/zabbix/zabbix_agentd.log
LogFileSize=0
Server=10.0.0.31,10.0.0.33
StartAgents=5
ServerActive=10.0.0.33
Hostname=10.0.0.31
AllowRoot=1
User=root
Include=/etc/zabbix/zabbix_agentd.d/*.conf
UserParameter=mysql_monitor[*],/etc/zabbix/zabbix_agentd.d/mysql_monitor.sh "$1"

root@ubuntu1804:/etc/zabbix/zabbix_agentd.d# cat /lib/systemd/system/zabbix-agent.service
User=root
Group=root

root@ubuntu1804:/usr/local/src# systemctl restart zabbix-agent
root@ubuntu1804:/usr/local/src# chmod a+x /etc/zabbix/zabbix_agentd.d/mysql_monitor.sh

3.4 zabbix server测试监控项数据

/apps/zabbix_server/bin/zabbix_get -s 172.31.0.105 -p 10050 -k "mysql_monitor[master_slave_check]"
50

3.5 自定义模板

创建模板

添加监控项

添加触发器

添加图形

3.6 模板关联至主机

3.7 验证监控数据