Zabbix实现对MySQL数据库主从监控

数据运维 2023-04-29 张二河手机阅读

1.主从关系建立配置MySQL-master端： 1）修改mysql-master的配置文件 [root@localhost ~]# vim /etc/my.cnf[mysqld]server_id=1innodb_file_per_tabledatadir=/data/mysqlsocket=/var/lib/mysql/mysql.socklog-bin=/data/log-bin/binbinlo

    <p><img src="https://img.mryunwei.com/uploads/2023/04/20230429111741186.png" alt="zabbix实现对mysql数据库主从监控"></p>

1.主从关系建立

配置MySQL-master端： 1）修改mysql-master的配置文件 [root@localhost ~]# vim /etc/my.cnf [mysqld] server_id=1 innodb_file_per_table datadir=/data/mysql socket=/var/lib/mysql/mysql.sock log-bin=/data/log-bin/bin binlog_format=row …… 2）启动mysql服务，并授权从服务用户 [root@localhost ~]# systemctl start mariadb [root@localhost ~]# systemctl enable mariadb 3）查看当前二进制日志位置 MariaDB [(none)]> show master logs; +------------+-----------+ | Log_name | File_size | +------------+-----------+ | bin.000001 | 307 | 4）授予从服务用户权限 MariaDB [(none)]> grant REPLICATION CLIENT on . to slave@'192.168.1.16' identified by 'slave'; 5）刷新权限 MariaDB [(none)]> flush privileges; 配置mysql-slave端： 1）修改从服务mysql-slave的数据库配置文件 [root@192 ~]# vim /etc/my.cnf [mysqld] server_id=2 read_only innodb_file_per_table datadir=/data/mysql socket=/var/lib/mysql/mysql.sock 2）登入数据库，建立组从连接 MariaDB [(none)]> change master to
master_host='192.168.1.15', master_user='slave', master_password='slave', master_log_file='bin.000001', master_log_pos=307; 3）启动主从连接 MariaDB [(none)]> start slave; 4）查看主从连接是否成功 ariaDB [(none)]> show slave statusG; 1. row Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.15 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000003 Read_Master_Log_Pos: 321 Relay_Log_File: mariadb-relay-bin.000018 Relay_Log_Pos: 531 Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes #两个io进程启动，主从连接启动 Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 321 Relay_Log_Space: 1113 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 #主从同步数据延迟 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative

2.在从服务端mysql-slave配置zabbix-agent代理客户端（CentOS7.6）

1）安装zabbix-agent客户端 root@localhost percona-zabbix-tools]# yum install zabbix-agent -y 2）修改agent配置文件 [root@localhost ~]# vim /etc/zabbix/zabbix_agentd.conf EnableRemoteCommands=1 #允许远程命令 Server=192.168.1.14,192.168.1.20 #必须都指定监控服务端和proxy代理服务端的ip，否则会导致监控端或proxy代理端无法zabbix_get key值 ServerActive=192.168.1.20 #主动模式下的代理地址 Hostname=192.168.1.16 #为方便区分主机名改为本机的ip地址 Include=/etc/zabbix/zabbix_agentd.d/*.conf #包含的子目录 UnsafeUserParameters=1 #允许参数中的所有字符传递给用户定义的参数 3）下载percona-zabbix监控mysql数据库包 https://www.percona.com/downloads/percona-monitoring-plugins/LATEST/ #percona-zabbix官网地址 [root@localhost percona-zabbix-tools]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/RedHat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm 4）查看安装的percona-zabbix-templates生成的列表 [root@localhost percona-zabbix-tools]# rpm -ql percona-zabbix-templates /var/lib/zabbix/percona /var/lib/zabbix/percona/scripts /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php /var/lib/zabbix/percona/templates /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /var/lib/zabbix/percona/templates/zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.8.xml 5）将percona的配置文件复制到/etc/zabbix/zabbix_agentd.d/目录下 [root@localhost ~]#cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/ 6）percona配置文件里保存的全是获取mysql数据库监控项的key [root@localhost ~]# cat /etc/zabbix/zabbix_agentd.d/userparameter_percona_mysql.conf UserParameter=MySQL.Com-select,/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh jx UserParameter=MySQL.Qcache-total-blocks,/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh js UserParameter=MySQL.Handler-read-prev,/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh mt UserParameter=MySQL.Sort-rows,/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh ks UserParameter=MySQL.Qcache-free-memory,/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh jm UserParameter=MySQL.pages-read,/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gu ….. UserParameter=MySQL.Key-read-requests,/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg 7）查看/etc/zabbix/zabbix_agentd.d/目录下的配置文件 [root@localhost ~]# ls /etc/zabbix/zabbix_agentd.d/ userparameter_mysql.conf userparameter_percona_mysql.conf 8）删除userparameter_mysql.conf 配置文件，为agent自带的配置文件，避免配置冲突 [root@localhost ~]# rm /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf 9）配置php环境 [root@localhost ~]#yum install php php-mysql -y 10）php连接本地数据库配置 [root@localhost ~]# vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf

3.在zabbix-server监控服务端创建zabbix-proxy的数据库，和授权用户

1）创建zabbix-proxy服务端的数据库 MariaDB [(none)]> create database zabbix_proxy_active character set utf8 collate utf8_bin; 2）创建zabbix-proxy服务端的数据库授权用户 MariaDB [(none)]> grant all privileges on zabbix_proxy_active. to zabbix_proxy_active@192.168.1.20 identified by 'zabbix_proxy_active'; 3）查看创建的zabbix-proxy服务端数据库 MariaDB [(none)]> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | | zabbix | | zabbix_proxy_active | +---------------------+ 5 rows in set (0.04 sec) 4）权限刷新 MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) 5）查看授权用户所拥有的权限 MariaDB [(none)]> show grants for zabbix_proxy_active@'192.168.1.20'; +-------------------------------------------------------------------------------------------------------------------------------+ | Grants for zabbix_proxy_active@192.168.1.20 | +-------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'zabbix_proxy_active'@'192.168.1.20' IDENTIFIED BY PASSWORD 'A63355661598C0599A07E41CE45344B7131CDF89' | | GRANT ALL PRIVILEGES ON zabbix_proxy_active.* TO 'zabbix_proxy_active'@'192.168.1.20' | +-------------------------------------------------------------------------------------------------------------------------------+

4.在zabbix-proxy代理服务端进行配置（Ubuntu系统18.4）

1）配置好仓库源 root@ubuntu1804:~# wget https://repo.zabbix.com/zabbix/4.0/debian/pool/main/z/zabbix-release/zabbix-release_4.0-2+stretch_all.deb root@ubuntu1804:~# dpkg -i zabbix-release_4.0-2+stretch_all.deb root@ubuntu1804:~# apt update 2）下载安装proxy代理软件 root@ubuntu1804:~# apt install zabbix-proxy-mysql -y 3）安装zabbix_get测试命令包 root@ubuntu1804:~# apt install zabbix-get -y 4）查看安装好的zabbix-proxy-mysql root@ubuntu1804:~# dpkg -l | grep zabbix ii zabbix-get 1:4.0.10-1+bionic amd64 Zabbix network monitoring solution - get ii zabbix-proxy-mysql 1:4.0.10-1+bionic amd64 Zabbix network monitoring solution - proxy (MySQL) ii zabbix-release 1:4.0-2+bionic all Zabbix official repository configuration 5）测试是否能够访问zabbix-proxy的数据库 root@ubuntu1804:~# mysql -uzabbix_proxy_active -pzabbix_proxy_active -h192.168.1.14 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 58 Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

MariaDB [(none)]> MariaDB [(none)]> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | zabbix_proxy_active | #可以看到此数据库 +---------------------+ 2 rows in set (0.00 sec) 6）返回shell的命令行初始化zabbix-proxy服务端的数据库 root@ubuntu1804:~# zcat /usr/share/doc/zabbix-proxy-mysql/schema.sql.gz | mysql -uzabbix_proxy_active -pzabbix_proxy_active -h192.168.1.14 zabbix_proxy_active 7）在查看数据库，zabbix-proxy的数据中是否导入表格 root@ubuntu1804:~# mysql -uzabbix_proxy_active -pzabbix_proxy_active -h192.168.1.14 MariaDB [(none)]> use zabbix_proxy_active; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed MariaDB [zabbix_proxy_active]> show tables; …… | task_acknowledge | | task_check_now | | task_close_problem | | task_remote_command | | task_remote_command_result | | timeperiods | | trends | | trends_uint | | trigger_depends | | trigger_discovery | | trigger_tag | | triggers | | users | | users_groups | | usrgrp | | valuemaps | | widget | | widget_field | +-------------------------------+ 144 rows in set (0.00 sec) 8）修改proxy代理服务的配置文件 root@ubuntu1804:~# grep '^[a-Z]' /etc/zabbix/zabbix_proxy.conf ProxyMode=0 #0表示开启主动模式 Server=192.168.1.14 #服务端的ip地址 Hostname=zabbix_proxy_active #proxy代理主机名,在web端添加此代理是必须与此处主机名相同 ListenPort=10051 #proxy监听的端口 LogFileSize=0 #关闭日志自动轮换 DBHost=192.168.1.14 #数据库主机 DBName=zabbix_proxy_active #数据库名 DBUser=zabbix_proxy_active #数据库用户 DBPassword=zabbix_proxy_active #数据库密码 ProxyLocalBuffer=24 #数据访问服务端后，本地保存数据时间为720小时 ProxyOfflineBuffer=24 #未提交数据到服务端，本地保存数据的时间为720小时 ConfigFrequency=10 #间隔多久到服务端获取监控项信息，默认3600秒 DataSenderFrequency=5 #数据发送时间间隔，默认为1秒 CacheSize=2G #保存监控项的最大内存，默认为8M HistoryCacheSize=2G #保存监控历史数据的最大内存，默认为16M Timeout=30 #监控项超时时间，单位秒 LogSlowQueries=3000 #多久的数据库查询会被记录到日志，单位毫秒 9）重新启动proxy服务，载入配置好的配置文件 root@ubuntu1804:~# systemctl restart zabbix-proxy root@ubuntu1804:~# systemctl enable zabbix-proxy 10）查看proxy是否启动 ● zabbix-proxy.service - Zabbix Proxy Loaded: loaded (/lib/systemd/system/zabbix-proxy.service; disabled; vendor preset: enabled) Active: active (running) since Tue 2019-07-16 18:06:43 CST; 2h 36min ago Process: 4514 ExecStop=/bin/kill -SIGTERM $MAINPID (code=exited, status=0/SUCCESS) Process: 4515 ExecStart=/usr/sbin/zabbix_proxy -c $CONFFILE (code=exited, status=0/SUCCESS) Main PID: 4527 (zabbix_proxy) Tasks: 25 (limit: 2293) CGroup: /system.slice/zabbix-proxy.service ├─4527 /usr/sbin/zabbix_proxy -c /etc/zabbix/zabbix_proxy.conf ├─4531 /usr/sbin/zabbix_proxy: configuration syncer [synced config 4069 bytes in 0.056334 sec, idle 3600 sec] ├─4532 /usr/sbin/zabbix_proxy: heartbeat sender [sending heartbeat message success in 0.001186 sec, idle 60 sec] ├─4533 /usr/sbin/zabbix_proxy: data sender [sent 0 values in 0.014112 sec, idle 1 sec] ├─4534 /usr/sbin/zabbix_proxy: housekeeper [deleted 1454 records in 0.135417 sec, idle for 1 hour(s)] ├─4535 /usr/sbin/zabbix_proxy: http poller #1 [got 0 values in 0.003211 sec, idle 5 sec] ……

5.在zabbix-server端配置

1）在web端添加proxy代理

zabbix实现对mysql数据库主从监控

2）web端创建mysql-slave从服务主机并添加代理

zabbix实现对mysql数据库主从监控

3）添加一个mysql模板

zabbix实现对mysql数据库主从监控

4）监控服务端命令行测试，获取msyql数据库key的值，能够获取值，则证明监控端与被监控端成功建立连接

root@ubuntu1804:~# zabbix_get -s 192.168.1.16 -p 10050 -k MySQL.Key-read-requests4root@ubuntu1804:~# zabbix_get -s 192.168.1.16 -p 10050 -k MySQL.Qcache-free-memory1031336

5）web端查看成功添加被监控的mysql从服务

zabbix实现对mysql数据库主从监控

6）在mysql从服务端查看到新生成的localhost-mysql_cacti_stats.txt文件是有监控服务端创建的，且属主和属组都为zabbix [root@localhost ~]# ll /tmp/localhost-mysql_cacti_stats.txt -rw-rw-r-- 1 zabbix zabbix 1235 Jul 16 01:00 /tmp/localhost-mysql_cacti_stats.txt

6.创建自定义mysql主从监控模板、监控项

在mysql-slave端： 1）编写自定义监控脚本 [root@localhost ~]# vim /etc/zabbix/zabbix_agentd.d/mysql_monitor.sh #!/bin/bash master_slave_check(){ num1=mysql -uroot -e "show slave statusG" | grep "Slave_IO_Running" | awk -F":" '{print $2}' num2=mysql -uroot -e "show slave statusG" | grep "Slave_SQL_Running" | awk -F":" '{print $2}' if test $num1 == "Yes" && test $num2 == "Yes";then echo 60 else echo 120 fi } mysql_sync_delay(){ num=mysql -uroot -e "show slave statusG" | grep "Seconds_Behind_Master" | awk -F":" '{print $2}' echo $num }

menu(){
case $1 in  
    master_slave_check)
    master_slave_check
    ;;
    mysql_sync_delay)  

mysql_sync_delay
;;

esac } menu $1 2）编写自定义监控项的配置文件，含有监控项的键值 [root@localhost ~]# vim /etc/zabbix/zabbix_agentd.d/mysql-master-slave.conf UserParameter=mysql_test[*],/etc/zabbix/zabbix_agentd.d/mysql_monitor.sh $1 3）脚本加上执行权限，避免在monitor-server端无权限获取key的值 [root@localhost ~]# chmod a+x mysql_monitor.sh 4）重新启动本机的agent服务 [root@localhost ~]# systemctl restart zabbix-agent 在zabbix-proxy服务端测试，是否能获取被监控端mysql-slave服务的key的值 root@ubuntu1804:~# zabbix_get -s 192.168.1.16 -p 10050 -k "mysql_test[mysql_sync_delay]" 0 root@ubuntu1804:~# zabbix_get -s 192.168.1.16 -p 10050 -k "mysql_test[master_slave_check]" 60 在zabbix-server端先执行测试，是否能获取被监控端的key的值 1）利用zabbix-get命令测试 root@ubuntu1804:~# zabbix_get -s 192.168.1.16 -p 10050 -k "mysql_test[mysql_sync_delay]" 0 root@ubuntu1804:~# zabbix_get -s 192.168.1.16 -p 10050 -k "mysql_test[master_slave_check]" 60 2）当将mysql-slave服务停止，可以看到显示的数值为120，证明mysql-slave服务已断开 root@ubuntu1804:~# zabbix_get -s 192.168.1.16 -p 10050 -k "mysql_test[master_slave_check]" 120 3）在监控web端进行模板创建

zabbix实现对mysql数据库主从监控