zabbix监控mysql

数据库做为生产环境中最重要的一环，那么它的状态必然是最值得关心的。特别是主从复制，为了保持数据的一致性，监控mysql的主从复制状态也是必不可少的。

部署mysql主从复制监控

部署系统及环境

[root@master ~]#cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core)#关闭selinux和防火墙[root@master ~]#setenforce 0[root@master ~]#systemctl stop firewalld

配置mysql数据库

mysql主服务器配置

[root@node1 ~]#yum install mariadb-server -y[root@node1 ~]#vim /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0#加入以下三行skip_name_resolvelog_binserver_id=1[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.dsystemctl start mariadbmysqlMariaDB [(none)]> show master logs; -------------------- ----------- | Log_name           | File_size | -------------------- ----------- | mariadb-bin.000001 |       264 || mariadb-bin.000002 |       264 || mariadb-bin.000003 |       245 | -------------------- ----------- 3 rows in set (0.00 sec)MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'zabbix_mysql'@'192.168.8.%' IDENTIFIED BY '123456';

mysql从服务器配置

[root@node2 ~]#yum install mariadb-server -y[root@node2 ~]#vim /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock#加入以下三行skip_name_resolveserver_id=2read_onlysymbolic-links=0[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d[root@node2 ~]#systemctl start mariadb

配置主从复制

[root@node2 ~]#mysqlMariaDB [(none)]> CHANGE MASTER TO    ->   MASTER_HOST='192.168.8.22',    ->   MASTER_USER='zabbix_mysql',    ->   MASTER_PASSWORD='123456',    ->   MASTER_PORT=3306,    ->   MASTER_LOG_FILE='mariadb-bin.000003',    ->   MASTER_LOG_POS=245;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show slave status \G; *************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.8.22                  Master_User: zabbix_mysql                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mariadb-bin.000003          Read_Master_Log_Pos: 245               Relay_Log_File: mariadb-relay-bin.000010                Relay_Log_Pos: 531        Relay_Master_Log_File: mariadb-bin.000004             Slave_IO_Running: Yes            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: 245              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: 0Master_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                 

配置mysql从服务器端的zabbix-agent

[root@node2 ~]#yum install zabbix-agent -y[root@node2 ~]#vim /etc/zabbix/zabbix_agentd.conf#主动监控ip地址98：Server=192.168.8.21#被动监控ip地址139：ServerActive=192.168.8.21#本机ip地址，也可写主机名150： Hostname=192.168.8.23[root@node2 ~]#systemctl start zabbix-agent[root@node2 ~]#systemctl enable zabbix-agen

在web界面添加监控主机

查看主机是否添加成功

配置主从复制监控

编写监控脚本

[root@node2 ~]#vim /etc/zabbix/zabbix_agentd.d/mysql_status.sh #!/bin/bashmysql_io_status=`mysql -uroot -hlocalhost -e "show slave status\G;" | grep "Slave_IO_Running" | awk '{print $2}'`mysql_sql_status=`mysql -uroot -hlocalhost -e "show slave status\G;" | grep "Slave_SQL_Running" | awk '{print $2}'`mysql_timeout=`mysql -uroot -hlocalhost -e "show slave status\G;" | grep "Seconds_Behind_Master" | awk '{print $2}'`case $1 in        status)                if [ ${mysql_io_status} == "Yes" -a ${mysql_sql_status} == "Yes" ]        then                echo 50        else                echo 100        fi        ;;        timeout)                if [ ${mysql_timeout} == "NULL" ]        then                echo 10000        else                echo ${mysql_timeout}        fi        ;;esac[root@node2 ~]#chown zabbix.zabbix /etc/zabbix/zabbix_agentd.d/mysql_status.sh

更改zabbix-agent配置文件，并重启

[root@node2 ~]#vim /etc/zabbix/zabbix_agentd.conf 287：nsafeUserParameters=1297：UserParameter=mysql.status[*],/etc/zabbix/zabbix_agentd.d/mysql_status.sh $1 $2[root@node2 ~]#systemctl restart zabbix-agent

zabbix-server端进行测试，查看是否能够检测到数据

[root@master ~]#zabbix_get -s 192.168.8.23 -k "mysql.status[status]"50[root@master ~]#zabbix_get -s 192.168.8.23 -k "mysql.status[timeout]"0

在web界面添加监控主从复制监控

添加监控模板

添加监控项

添加触发器

添加图形

让监控主机关联模板

查看监控数据

w10修改zabbix中文字体乱码

在电脑上随便找个中文字体

把字体拷贝到/usr/share/zabbix/assets/fonts/目录下

[root@master ~]#mv /usr/share/zabbix/assets/fonts/simsun.ttc /usr/share/zabbix/assets/fonts/simsun.ttf[root@master ~]#vim /usr/share/zabbix/include/defines.inc.php #改成字体文件的前缀69：define('ZBX_GRAPH_FONT_NAME',           'simsun'); // font file name

再次查看web界面的中文字体

来源：https://www.icode9.com/content-2-328901.html