笺注:这是在 MySQL5.6主从/主主同步 的基础上进行的。
笺注:使用以下方法,被监控主机不用安装zabbix-agent
从服务器配置MySQL数据库:( 使用数据库管理员root@localhost )
[root@mysql-2 ~]# mysql -u"root" -p"888" -h"localhost"
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.42 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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>
注释:
-h"localhost" 是可以省略的;
查看当前登录的数据库用户:
mysql> Select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
再授权数据库用户zhuohua@'%'(数据库用户可以不要密码的),可以从任意IP进行访问,对所有的库有完全控制的权限:
mysql> grant all on *.* to zhuohua@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
查看远程数据库用户zhuohua@'%'的权限:
mysql> Show grants for zhuohua@'%';
+----------------------------------------------+
| Grants for zhuohua@% |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql>
查看数据库用户zhuohua@'%'的主机信息:
mysql> select user,host from mysql.user where user='zhuohua';
+---------+------+
| user | host |
+---------+------+
| zhuohua | % |
+---------+------+
1 row in set (0.00 sec)
mysql>
使用数据库用户zhuohua@'%'在从服务器进行本地登录:
[root@mysql-2 ~]# mysql -u"zhuohua"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.42 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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>
查看当前登录的数据库用户:
mysql> Select user();
+-------------------+
| user() |
+-------------------+
| zhuohua@localhost |
+-------------------+
1 row in set (0.00 sec)
mysql>
显示所有的库:(数据库用户zhuohua@'%'在服务器本地的权限不是很多)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.08 sec)
mysql>
查看主从同步情况:(数据库用户zhuohua@'%'在服务器本地的权限不是很多)
mysql> show slave status;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
mysql>
############
监控原理:从服务器的状态中,以下两项(Slave_IO_Running、Slave_SQL_Running)的返回值都必须为Yes
在从服务器本地进行测试:( 使用数据库管理员root@localhost )
[root@mysql-2 ~]# mysql -u"root" -p"888" -h"localhost" -sNX -e "show slave status"
Warning: Using a password on the command line interface can be insecure.
Waiting for master to send event192.168.168.130happy330660binlog.000001454mysqld-relay-bin.000002401binlog.000001Slave_IO_Running">YesSlave_SQL_Running">Yes00454575None0NoSeconds_Behind_Master">0No0015bb1908b-7fcc-11e8-9ee0-000c292b173a/var/lib/mysql/master.info0Slave has read all relay log; waiting for the slave I/O thread to update it864000
[root@mysql-2 ~]#
注释:Seconds_Behind_Master是衡量Master与Slave之间主从同步延迟情况的一个重要参数,返回值为0时,表示没有延迟。
在Zabbix服务器上测试:( 使用数据库用户zhuohua@'%' )
[root@centos8 ~]# mysql -u"zhuohua" -h"192.168.168.131" -sNX -e "show slave status;"
Waiting for master to send event192.168.168.130happy330660binlog.000001454mysqld-relay-bin.000002401binlog.000001Slave_IO_Running">YesSlave_SQL_Running">Yes00454575None0NoSeconds_Behind_Master">0No0015bb1908b-7fcc-11e8-9ee0-000c292b173a/var/lib/mysql/master.info0Slave has read all relay log; waiting for the slave I/O thread to update it864000
[root@centos8 ~]#
注释:从服务器的IP地址为 192.168.168.131
#############
#############
修改Zabbix服务器的Agent配置文件:
[root@centos8 ~]# vi /usr/local/zabbix/etc/zabbix_agentd.conf
# UnsafeUserParameters=0
修改为:(启用该功能)
UnsafeUserParameters=1
接着插入:
UserParameter=mysql.get_status_variables[*], mysql -u"zhuohua" -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], mysqladmin -u"zhuohua" -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery[*], mysql -u"zhuohua" -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql -u"zhuohua" -h"$1" -P"$2" -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], mysql -u"zhuohua" -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql -u"zhuohua" -h"$1" -P"$2" -sNX -e "show slave status"
复制代码
保存好配置文件后,重启一下Zabbix本机的客户端和服务端:
pkill -9 -U zabbix
/usr/local/zabbix/sbin/zabbix_agentd
/usr/local/zabbix/sbin/zabbix_server
######
在Zabbix服务器测试,验证能否获取本机(127.0.0.1)的键值:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p10050 -k "net.tcp.listen[10050]"
1
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k "net.tcp.listen[10050]"
1
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k net.tcp.listen[10050]
1
[root@centos8 ~]#
测试模板中,关于主从同步情况的键值:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k mysql.slave_status["192.168.168.131","3306"]
Waiting for master to send event192.168.168.130happy330660binlog.000001454mysqld-relay-bin.000002401binlog.000001Slave_IO_Running">YesSlave_SQL_Running">Yes00454575None0NoSeconds_Behind_Master">0No0015bb1908b-7fcc-11e8-9ee0-000c292b173a/var/lib/mysql/master.info0Slave has read all relay log; waiting for the slave I/O thread to update it864000
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k mysql.replication.discovery["192.168.168.131","3306"]
Waiting for master to send event192.168.168.130happy330660binlog.000001454mysqld-relay-bin.000002401binlog.000001Slave_IO_Running">YesSlave_SQL_Running">Yes00454575None0NoSeconds_Behind_Master">0No0015bb1908b-7fcc-11e8-9ee0-000c292b173a/var/lib/mysql/master.info0Slave has read all relay log; waiting for the slave I/O thread to update it864000
[root@centos8 ~]#
注释:从服务器的IP地址为 192.168.168.131
##################Zabbix添加被监控主机:
配置》主机》创建主机: (主机名称无需与被监控主机的真实主机名一致;群组可以随便选;Interfaces的类型选择“客户端”;端口为10050)
模板Template DB MySQL 自动发现的触发器:(触发器的表达式要用到监控项中的键值)
触发器名称: MySQL: The slave I/O thread is not running
表达式: {MySQL_Slave_2:mysql.slave_io_running["192.168.168.130"].count(#1,"No",eq)}=1
在从服务器上本地测试:( 使用数据库管理员root@localhost )
[root@mysql-2 ~]# mysql -u"root" -p"888" -sNX -e "show slave status;"
Warning: Using a password on the command line interface can be insecure.
192.168.168.130happy330660binlog.000001454mysqld-relay-bin.000002401binlog.000001Slave_IO_Running">NoSlave_SQL_Running">No00454575None0NoSeconds_Behind_Master" xsi:nil="true" />
No0015bb1908b-7fcc-11e8-9ee0-000c292b173a/var/lib/mysql/master.info0864000
[root@mysql-2 ~]#
在Zabbix服务器上测试:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k mysql.slave_status["192.168.168.131","3306"]
192.168.168.130happy330660binlog.000001454mysqld-relay-bin.000002401binlog.000001Slave_IO_Running">NoSlave_SQL_Running">No00454575None0NoSeconds_Behind_Master" xsi:nil="true" />
No0015bb1908b-7fcc-11e8-9ee0-000c292b173a/var/lib/mysql/master.info0864000
[root@centos8 ~]#
在从服务器上关闭同步后,仪表板会如下图显示:(显示对应触发器的名称)
MySQL: The slave I/O thread is not running