zabbix自定义监控mysql主从状态和延迟及模板

zabbix自定义监控mysql主从状态和延迟及模板

• 配置监控脚本

  目录

  zabbix自定义监控mysql主从状态和延迟及模板

  监控mysql主从状态

  配置mysql主从同步

  添加监控项

  添加触发器

  手动触发告警

  监控mysql主从延迟

  配置监控脚本

  添加监控项

  用户和组

  ---

监控mysql主从状态

配置mysql主从同步

//安装数据库

[root@master ~]# dnf install -y mariadb*

[root@web ~]# dnf install -y mariadb*


//启动并修改密码

//主库配置

[root@master ~]# systemctl enable --now mariadb

[root@master ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.3.28-MariaDB MariaDB Server


Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.


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


MariaDB [(none)]> set password = password('123com');

Query OK, 0 rows affected (0.000 sec)


MariaDB [(none)]> quit

Bye

[root@master ~]# mysql -uroot -p123com //验证密码


//从库配置

[root@web ~]# systemctl enable --now mariadb

[root@web ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.3.28-MariaDB MariaDB Server


Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.


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


MariaDB [(none)]> set password = password('123com');

Query OK, 0 rows affected (0.000 sec)


MariaDB [(none)]> quit

Bye

[root@web ~]# mysql -uroot -p123com //验证密码


//在主库上创建同步账号

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.171.135' identified by 'repl123!';

Query OK, 0 rows affected (0.001 sec)


MariaDB [(none)]> quit

Bye

//写配置文件

[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf

22 log-bin = mysql_bin

23 server-id = 10

[root@master ~]# systemctl restart mariadb

[root@master ~]# mysql -uroot -p123com

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql_bin.000001 | 328 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.000 sec)


//关闭防火墙和selinux

[root@master ~]# systemctl disable --now firewalld

Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# setenforce 0


//配置从库

[root@web ~]# vim /etc/my.cnf.d/mariadb-server.cnf

22 server-id = 20

23 relay-log = myrelay

[root@web ~]# systemctl restart mariadb

[root@web ~]# mysql -uroot -p123com

MariaDB [(none)]> change master to

-> master_host='192.168.171.134',

-> master_user='repl',

-> master_password='repl123!',

-> master_log_file='mysql_bin.000001',

-> master_log_pos=328;

Query OK, 0 rows affected (0.003 sec)


MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.001 sec)


MariaDB [(none)]> show slave status\G;

······

Slave_IO_Running: Yes

Slave_SQL_Running: Yes


······


//测试验证主从是否同步

[root@master ~]# mysql -uroot -p123com

MariaDB [(none)]> create database george;

Query OK, 1 row affected (0.000 sec)


MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| george |

| information_schema |

| mysql |

| performance_schema |

+--------------------+

4 rows in set (0.000 sec)

[root@web ~]# mysql -uroot -p123com

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| george |

| information_schema |

| mysql |

| performance_schema |

+--------------------+

4 rows in set (0.000 sec)

• 配置监控脚本

//在从库修改配置文件

[root@web ~]# vim /usr/local/etc/zabbix_agentd.conf

UserParameter=check_mysql_status,/bin/bash /scripts/mysql_status.sh //在末行加入此配置

//重启生效

[root@web ~]# pkill zabbix_agentd

[root@web ~]# zabbix_agentd

[root@web ~]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port Process

LISTEN 0 128 0.0.0.0:22 0.0.0.0:*

LISTEN 0 128 0.0.0.0:10050 0.0.0.0:*

LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*

LISTEN 0 128 [::]:22 [::]:*

//编写脚本

[root@web ~]# vim /scripts/mysql_status.sh

#!/bin/bash

yes=`mysql -uroot -p123com -e " show slave status\G" 2> /dev/null |grep "Running:"|grep -c "Yes"`

if [ $yes == 2 ]; then

echo "0"

else

echo "1"

fi


[root@web ~]# chmod +x /scripts/mysql_status.sh


//在服务端检测key是否可用

[root@zabbix ~]# zabbix_get -s 192.168.171.136 -k check_mysql_status

0

添加监控项

 

 

 

添加触发器

 

 

 

手动触发告警

[root@web ~]# mysql -uroot -p123com -e "stop slave;"

 

监控mysql主从延迟

配置监控脚本

//在从库添加配置

[root@web ~]# vim /usr/local/etc/zabbix_agentd.conf

UserParameter=check_mysqlyc,/bin/bash /scripts/mysqlyc.sh


//重启生效

[root@web ~]# pkill zabbix_agentd

[root@web ~]# zabbix_agentd


//编写脚本

[root@web ~]# vim /scripts/mysqlyc.sh

#!/bin/bash

yc=`mysql -uroot -p123com -e "show slave status\G" 2> /dev/null |awk '/Seconds_Behind_Master/ {print $2}' `

echo $yc


[root@web ~]# chmod +x /scripts/mysqlyc.sh


//在服务端检测key是否可用

[root@zabbix ~]# zabbix_get -s 192.168.171.135 -k check_mysqlyc

0

添加监控项

 

 

 

 

 

用户和组