7.2 zabbix实现Mysql的监控

2022/9/3 2:24:45

编程Tag： mysql root server 00 master zabbix 7.2

本文主要是介绍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

[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"

图形没有获取到数据

更改一下监控模式，每页全钩上。

翻下来点击批量更新

弹出下面的窗口：类型钩上，然后选择主动模式。

等几分钟，结果如下：

这篇关于7.2 zabbix实现Mysql的监控的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

7.2 zabbix实现Mysql的监控

相关编程文章