【云原生】-Zabbix6监控MySQL最佳实践

📢📢📢📣📣📣

哈喽！大家好，我是【IT邦德】，10年DBA工作经验

一位上进心十足的【大数据领域博主】！😜😜😜

中国DBA联盟(ACDU)成员，目前从事DBA及程序编程

擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。

✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞

❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言

⛳️ 1.部署Zabbix服务器
⛳️ 2.Zabbix登陆使用
⛳️ 3.安装MySQL
⛳️ 4.安装Agent2
⛳️ 5.配置agent参数文件
⛳️ 6.Zabbix Server添加主机

前言

zabbix可以用来监控MySQL数据库可用性和性能，保证数据库的安全运营。并提供灵活的通知机制快速定位/解决存在的各种问题。

⛳️ 1.部署Zabbix服务器

1.zabbix镜像下载
docker pull techerwang/dbhub:jemzabbix62

2.创建一个新的bridge网络
[root@jeames ~]# docker network create --driver bridge --subnet=172.18.12.0/16 --gateway=172.18.1.1 mynet

3.创建容器
docker rm -f jemzabbix6

docker run -d --name jemzabbix6 -h jemzabbix6 \
-p 225:22 -p 23306:3306 -p 280:80 -p 20051:10051 \
--net=mynet --ip 172.18.0.3 \
-v /sys/fs/cgroup:/sys/fs/cgroup \
--privileged=true \
techerwang/dbhub:jemzabbix62 /usr/sbin/init

--查看创建的容器
[root@jeames ~]# docker ps --format "table {{.ID}}\t{{.Names}}\t{{.Status}}" | grep -i jemzabbix6
9b658b6e1a38   jemzabbix6    Up About a minute

--设置容器开机自启动
[root@jeames ~]# docker update --restart=always jemzabbix6

--进入容器
[root@jeames ~]# docker exec -it jemzabbix6 bash
[root@jemzabbix6 /]# cat /etc/redhat-release 
CentOS Linux release 8.2.2004 (Core)

⛳️ 2.Zabbix登陆使用

IP加端口号，端口号默认为80，可修改，后期下一步执行即可
默认用户名密码：Admin/zabbix

⛳️ 3.安装MySQL

本次采用yum在线rpm在线安装MySQL8.0

#repo下载
[root@mysql8030 ~]# rpm -Uvh https://repo.mysql.com//mysql80-community-release-el8.rpm
Retrieving https://repo.mysql.com//mysql80-community-release-el8.rpm
warning: /var/tmp/rpm-tmp.yHWBFD: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql80-community-release-el8-4  ################################# [100%]
   
#查询yum里的MySQL版本
[root@mysql8030 ~]# yum repolist all | grep mysql
mysql-cluster-8.0-community           MySQL Cluster 8.0 Community       disabled
mysql-cluster-8.0-community-debuginfo MySQL Cluster 8.0 Community - Deb disabled
mysql-cluster-8.0-community-source    MySQL Cluster 8.0 Community - Sou disabled
mysql-connectors-community            MySQL Connectors Community        enabled
mysql-connectors-community-debuginfo  MySQL Connectors Community - Debu disabled
mysql-connectors-community-source     MySQL Connectors Community - Sour disabled
mysql-tools-community                 MySQL Tools Community             enabled
mysql-tools-community-debuginfo       MySQL Tools Community - Debuginfo disabled
mysql-tools-community-source          MySQL Tools Community - Source    disabled
mysql-tools-preview                   MySQL Tools Preview               disabled
mysql-tools-preview-source            MySQL Tools Preview - Source      disabled
mysql80-community                     MySQL 8.0 Community Server        enabled
mysql80-community-debuginfo           MySQL 8.0 Community Server - Debu disabled
mysql80-community-source              MySQL 8.0 Community Server - Sour disabled

#配置安装的MySQL的版本
[root@mysql8030 ~]# yum -y install yum-utils
[root@mysql8030 ~]# yum-config-manager --enable mysql80-community

#查询安装的MySQL的版本
[root@mysql8030 ~]# yum repolist enabled | grep mysql
mysql-connectors-community MySQL Connectors Community
mysql-tools-community      MySQL Tools Community
mysql80-community          MySQL 8.0 Community Server

#安装 MySQL
先执行：yum module disable mysql
再执行：yum -y install mysql-community-server

#初始化MySQL
[root@mysql8030 ~]# systemctl start mysqld
[root@mysql8030 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-09-11 03:32:18 UTC; 2s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 570 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 643 (mysqld)
   Status: "Server is operational"
    Tasks: 39 (limit: 24036)
   Memory: 424.1M
   CGroup: /docker/5cc7e4ce4e1cd6246b7a22e4aad1a32a0eccdab18e67b685023fd37c366be956/system.slice/mysqld.service
           └─643 /usr/sbin/mysqld

Sep 11 03:31:38 mysql8030 systemd[1]: Starting MySQL Server...
Sep 11 03:32:18 mysql8030 systemd[1]: Started MySQL Server.

#查看临时密码
[root@mysql8030 ~]# grep 'temporary password' /var/log/mysqld.log
2022-09-11T03:32:09.311756Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ?th)lB4Xc-1q

#登陆MySQL后修改密码
[root@mysql8030 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER root@'localhost' IDENTIFIED BY 'Jeames@007';
Query OK, 0 rows affected (0.00 sec)

mysql> create user root@'%' identified by 'Jeames@007';
mysql> grant all on *.* to root@'%' with grant option;

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

mysql> status
--------------
mysql  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.30
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 1 min 4 sec

Threads: 2  Questions: 7  Slow queries: 0  Opens: 131  Flush tables: 3  Open tables: 47  Queries per second avg: 0.109

⛳️ 4.安装Agent2

dnf install zabbix-agent2 zabbix-agent2-plugin-mongodb

[root@mysql8030 zabbix]# systemctl restart zabbix-agent2
[root@mysql8030 zabbix]# systemctl enable zabbix-agent2

[root@mysql8030 zabbix]# systemctl state zabbix-agent2       
Unknown operation state.
[root@mysql8030 zabbix]# systemctl status zabbix-agent2 
● zabbix-agent2.service - Zabbix Agent 2
   Loaded: loaded (/usr/lib/systemd/system/zabbix-agent2.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-09-11 05:57:57 UTC; 13s ago
 Main PID: 1212 (zabbix_agent2)
    Tasks: 7 (limit: 24036)
   Memory: 11.1M
   CGroup: /docker/5cc7e4ce4e1cd6246b7a22e4aad1a32a0eccdab18e67b685023fd37c366be956/system.slice/zabbix-agent2.service
           └─1212 /usr/sbin/zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf

Sep 11 05:57:57 mysql8030 systemd[1]: Started Zabbix Agent 2.
Sep 11 05:57:57 mysql8030 zabbix_agent2[1212]: Starting Zabbix Agent 2 (6.2.2)
Sep 11 05:57:57 mysql8030 zabbix_agent2[1212]: Zabbix Agent2 hostname: [Zabbix server]
Sep 11 05:57:57 mysql8030 zabbix_agent2[1212]: Press Ctrl+C to exit.

⛳️ 5.配置agent参数文件

[root@mysql8030 ~]# vi /etc/zabbix/zabbix_agent2.conf
Server=172.18.0.3
ServerActive=172.18.0.4
Hostname=mysql8030
Timeout=10

参数说明：
server改成zabbix服务端的IP地址，Zabbix-server 地址，多个用","隔开
serveractive改成zabbix服务端的IP地址，主动检查本主机数据发送给 Zabbix-server
hostname改成本机的主机名，或本机的IP地址，被监控的主机名，需要和后续 server 端监控的主机名保持一致


sed -i "s/Server=127.0.0.1/Server=172.18.0.3/g" /etc/zabbix/zabbix_agent2.conf
sed -i "s/ServerActive=127.0.0.1/ServerActive=172.18.0.4/g" /etc/zabbix/zabbix_agent2.conf


##重启进程生效
[root@mysql8030 ~]# systemctl restart zabbix-agent2

⛳️ 6.Zabbix Server添加主机

配置-->主机-->创建主机中操作
Zabbix6 自带MySQL模版，无须再使用第三方，也不用自己写监控脚本，
而且模板zabbix-agent2比zabbix-agent，功能更强大，应用也更简单

##主机server进程
[root@jemzabbix6 ~]# netstat -anp | grep 10051

##客户端服务器进程
[root@mysql8030 ~]# netstat -anp | grep 10050
tcp        0      0 0.0.0.0:10050           0.0.0.0:*               LISTEN      301/zabbix_agentd   
tcp6       0      0 :::10050                :::*                    LISTEN      301/zabbix_agentd  

说明：
1.主机名称和zabbix-agent2.conf文件中Hostname匹配即可
2.agent配置要监控的MySQL服务器IP,端口默认为10050


##zabbix测试是否监控到数据，以下在Zabbix Server端操作
--是否连接mysql
[root@jemzabbix6 ~]# mysql -uroot -p -h172.18.0.4

#查看Server日志
LogFile=/var/log/zabbix/zabbix_server.log
[root@jemzabbix6 ~]# tail -n 50 /var/log/zabbix/zabbix_server.log

#Web监控界面中文乱码处理
1.在windows下面文件夹C:\Windows\Fonts找一个简体字文件例如simkai.ttf
2.复制到zabbix server的文件夹/usr/share/zabbix/fonts
3.注意：zabbix5的文件夹位置为/usr/share/zabbix/assets/fonts
4.覆盖原来的graphfont.ttf文件即可

[root@jeames ~]# docker cp simkai.ttf jemzabbix6:/
[root@jemzabbix6 fonts]# pwd
/usr/share/zabbix/assets/fonts
[root@jemzabbix6 fonts]# cd /
[root@jemzabbix6 /]# cp simkai.ttf /usr/share/zabbix/assets/fonts
[root@jemzabbix6 fonts]# rm -rf graphfont.ttf

5.找到zabbix 字符配置，切换即可,刷新页面，无需重启
find / -name defines.inc.php
/usr/share/zabbix/include/defines.inc.php

define('ZBX_GRAPH_FONT_NAME',           'graphfont'); // font file

【云原生】-Zabbix6监控MySQL最佳实践

文章目录

前言

⛳️ 1.部署Zabbix服务器

⛳️ 2.Zabbix登陆使用

⛳️ 3.安装MySQL

⛳️ 4.安装Agent2

⛳️ 5.配置agent参数文件

⛳️ 6.Zabbix Server添加主机

继续阅读

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

(SpringBoot)日志种类：log、monitor、access、out、gc、backup

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

CentOS 7,docker安装

mysql使用source命令导入.sql文件

【Docker】端口映射问题操作步骤