7.2 zabbix实现Mysql的监控
7.2 zabbix实现Mysql的监控
监控MySQL连接数、主从同步、同步延迟等。
一：实现MySQL主从
1.1：MySQL Master
mysql-server-5.7.xx:
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
bind-address = 0.0.0.0
server-id = 10
log-bin = /var/lib/mysql/master-log
1.2：MySQL Slave
mysql-server-5.7.xx:
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
bind-address = 0.0.0.0
server-id = 105
relay-log = /var/lib/mysql/relay-log
1.3：MySQL Master授权账户
在mysql master服务器授权账户并导出数据，然后rsync到mysql backup服务器。
MySQL Master授权账户：
[root@mysql-master ~]#mysql
......
mysql> grant replication slave,replication client on *.* to magedu@'172.31.0.%' identified by "Mmagedu0!";
mysql> quit;
[root@mysql-master ~]#mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql
[root@mysql-master ~]#rsync /opt/backup.sql 172.31.0.105:/opt/
1.4：MySQL slave导入数据：
在MySQL Slave服务器导入数据开始同步数据，Position位置在sql文件
[root@mysql-slave ~]#mysql
.......
mysql> change master to
-> MASTER_HOST="172.31.0.104",
-> MASTER_USER="magedu",
-> MASTER_PASSWORD="Mmagedu0!",
-> MASTER_LOG_FILE="master-log.000003",
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.31.0.104
Master_User: magedu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
二：zabbix-server、zabbbix-agent、Percona、mysql-master
官方文档及下载地址：
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：安装配置zabbix-agent
[root@mysql-master ~]#apt install zabbix-agent -y
[root@mysql-master ~]#grep "^[a-Z]" /etc/zabbix/zabbix_agentd.conf
PidFile=/var/run/zabbix/zabbix_agentd.pid
LogFile=/var/log/zabbix-agent/zabbix_agentd.log
LogFileSize=0
Server=172.31.0.101
ListenPort=10050
ServerActive=172.31.0.101
Hostname=172.31.0.104
Include=/etc/zabbix/zabbix_agentd.conf.d/*.conf
[root@mysql-master ~]#systemctl restart zabbix-agent
[root@mysql-master ~]#systemctl enable zabbix-agent
2.2：安装配置Percona
[root@mysql-master ~]#
wget https://www.percona.com/downloads/percona-zabbix-templates_1.1.8-1.artful_all.deb
[root@mysql-master ~]#ls
percona-zabbix-templates_1.1.8-1.artful_all.deb
[root@mysql-master ~]#dpkg -i percona-zabbix-templates_1.1.8-1.artful_all.deb
dpkg-deb: error: 'percona-zabbix-templates_1.1.8-1.artful_all.deb' is not a Debian format archive
dpkg: error processing archive percona-zabbix-templates_1.1.8-1.artful_all.deb (--install):
dpkg-deb --control subprocess returned error exit status 2
Errors were encountered while processing:
这是由于下载的包不完整造成，重新下载。
[root@mysql-master ~]#dpkg -i percona-zabbix-templates_1.1.8-1.artful_all.deb
Selecting previously unselected package percona-zabbix-templates.
(Reading database ... 72774 files and directories currently installed.)
Preparing to unpack percona-zabbix-templates_1.1.8-1.artful_all.deb ...
Unpacking percona-zabbix-templates (1.1.8-1.artful) ...
Setting up percona-zabbix-templates (1.1.8-1.artful) ...
[root@mysql-master ~]#dpkg -l | grep percona
ii percona-zabbix-templates 1.1.8-1.artful all Percona Monitoring Plugins for Zabbix
[root@mysql-master ~]#
cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.conf.d/
[root@mysql-master ~]# systemctl restart zabbix-agent
安装php环境： 目前Percona与ubuntu 自带的php 7.2不兼容，需要安装php 5.6版本
[root@mysql-master ~]# add-apt-repository ppa:ondrej/php
[root@mysql-master ~]#apt-get -y update
[root@mysql-master ~]#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 = '';
?>
测试脚本能否获取数据：
[root@mysql-master /var/lib/zabbix/percona/scripts/]#./get_mysql_stats_wrapper.sh gg
18
[root@mysql-master /var/lib/zabbix/percona/scripts]#ps -ef | grep zabbix_agent
zabbix 12665 1 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd --foreground
zabbix 12678 12665 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd: collector [idle 1 sec]
zabbix 12679 12665 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #1 [waiting for connection]
zabbix 12680 12665 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #2 [waiting for connection]
zabbix 12681 12665 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #3 [waiting for connection]
zabbix 12682 12665 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #4 [waiting for connection]
zabbix 12683 12665 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd: listener #5 [waiting for connection]
zabbix 12684 12665 0 09:37 ? 00:00:00 /usr/sbin/zabbix_agentd: active checks #1 [idle 1 sec]
root 12697 1526 0 09:39 pts/0 00:00:00 grep --color=auto zabbix_agent
2.3 配置mysql实现用户授权
mysql 进入数据库创建账户并授权
[root@mysql-master ~]# mysql
mysql> create database zabbix_server character set utf8 collate utf8_bin;
mysql> grant all privileges on zabbix_server.* to magedu@'172.31.%.%' identified by 'Mmagedu0!';
mysql> flush privileges;
mysql> quit
2.4 Zabbix-server配置
(1) 修改Zabbix-server配置文件
[root@zabbix-server ~]# vim /etc/zabbix/zabbix_server.conf
DBHost=172.31.0.104
DBName=172.31.0.104
DBUser=magedu
DBPassword=Mmagedu0!
DBPort=3306
(2) 初始化zabbix_server数据库，使之成为zabbix数据库。
[root@zabbix-server ~]#zcat /usr/share/doc/zabbix-server-mysql/create.sql.gz | mysql -umagedu -pMmagedu0! -h172.31.0.104 zabbix_server
mysql: [Warning] Using a password on the command line interface can be insecure.
登录不上mysql有很大可能是在mysql配置文件的的bind-address=127.0.0.1
(3) 确认授权用户相关信息
[root@zabbix-server /var/log]#find / -name zabbix.conf.php
[root@zabbix-server /var/log]#vim /etc/zabbix/web/zabbix.conf.php
<?php
// Zabbix GUI configuration file.
global $DB;
$DB['TYPE'] = 'MYSQL';
$DB['SERVER'] = '172.31.0.104';
$DB['PORT'] = '3306';
$DB['DATABASE'] = 'zabbix_server';
$DB['USER'] = 'magedu';
$DB['PASSWORD'] = 'Mmagedu0!';
// Schema name. Used for IBM DB2 and PostgreSQL.
$DB['SCHEMA'] = '';
$ZBX_SERVER = '172.31.0.101';
$ZBX_SERVER_PORT = '10051';
$ZBX_SERVER_NAME = '172.31.0.101';
$IMAGE_FORMAT_DEFAULT = IMAGE_FORMAT_PNG;
查看日志很重要，日志目录一般在配置文件中有说明。
(4) 检查在zabbix-server上能否登录
[root@zabbix-server ~]#mysql -umagedu -pMmagedu0! -h172.31.0.104
mysql: [Warning] Using a password on the command line interface can be insecure.
.......
2.5 各个主机之间开启时间同步，同步到网络时间，重启服务
timedatectl set-timezone AsiaShanghai
systemctl restart systemd-timesyncd.service
[root@zabbix-server ~]#systemctl restart apache2 zabbix-server
[root@mysql-master ~]#systemctl restart zabbix-agent mysql
[root@mysql-slave ~]#systemctl restart mysql
三 、web网页端配置
3.1：zabbix web登录
http://172.31.0.101/zabbix/
查看日志
[root@zabbix-server /var/log]#tail -f syslog
[root@zabbix-server /var/log]#tail /var/log/zabbix/zabbix_server.log
修改Zabbix-server配置
[root@zabbix-server ~]# vim /etc/zabbix/zabbix_server.conf
DBHost=172.31.0.104
DBName=172.31.0.104
DBUser=magedu
DBPassword=Mmagedu0!
DBPort=3306
初始化zabbix_server数据库，使之成为zabbix数据库。
[root@zabbix-server ~]#zcat /usr/share/doc/zabbix-server-mysql/create.sql.gz | mysql -umagedu -pMmagedu0! -h172.31.0.104 zabbix_server
mysql: [Warning] Using a password on the command line interface can be insecure.
确认授权用户相关信息
[root@zabbix-server /var/log]#find / -name zabbix.conf.php
[root@zabbix-server /var/log]#vim /etc/zabbix/web/zabbix.conf.php
<?php
// Zabbix GUI configuration file.
global $DB;
$DB['TYPE'] = 'MYSQL';
$DB['SERVER'] = '172.31.0.104';
$DB['PORT'] = '3306';
$DB['DATABASE'] = 'zabbix_server';
$DB['USER'] = 'magedu';
$DB['PASSWORD'] = 'Mmagedu0!';
// Schema name. Used for IBM DB2 and PostgreSQL.
$DB['SCHEMA'] = '';
$ZBX_SERVER = '172.31.0.101';
$ZBX_SERVER_PORT = '10051';
$ZBX_SERVER_NAME = '172.31.0.101';
$IMAGE_FORMAT_DEFAULT = IMAGE_FORMAT_PNG;
查看日志很重要，日志目录一般在配置文件中有说明。
检查在zabbix-server上能否登录
[root@zabbix-server ~]#mysql -umagedu -pMmagedu0! -h172.31.0.104
mysql: [Warning] Using a password on the command line interface can be insecure.
.......
3.2：zabbix web导入Percona模板
3.3：创建groups
3.4：zabbbix web添加主机
3.5：zabbix web对主机关联模板
3.6：验证MySQL监控数据：
Percona模板中的监控项默认是五分钟收集一次监控项数据，会结合脚本检查agent上报错数据的文件的时间戳是否超过五分钟，安装percona脚本自动生成，脚本位置：/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh。
zabbix-server测试
[root@zabbix-server ~]#apt install zabbix-get
[root@zabbix-server ~]#zabbix_get -s 172.31.0.104 -p 10050 -k "MySQL.Key-read-requests"
6
图形没有获取到数据
更改一下监控模式，每页全钩上。
翻下来点击批量更新
弹出下面的窗口：类型钩上，然后选择主动模式。
等几分钟，结果如下 ：
