笺注:这是在 MySQL5.6主从/主主同步 的基础上进行的。
清除从服务器的MySQL数据库管理员root@localhost的密码:
[root@mysql-2 ~]# service mysqld stop
停止 mysqld: [确定]
屏蔽权限: mysqld_safe --skip-grant-table
另外打开一个终端输入: mysql -u root mysql
UPDATE user SET Password=PASSWORD('') where USER='root';
FLUSH PRIVILEGES;
\q
[root@mysql-2 ~]# service mysqld start
正在启动 mysqld: [确定]
至此,从服务器的MySQL数据库管理员root@localhost无需密码验证就可以登录了:
监控原理:从服务器的状态中,以下两项(Slave_IO_Running、Slave_SQL_Running)的返回值都必须为Yes
[root@mysql-2 ~]# mysql -uroot -e "show slave status\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.168.130
Master_User: happy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 454
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 236
Relay_Master_Log_File: binlog.000001
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: 454
Relay_Log_Space: 570
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_UUID: 5bb1908b-7fcc-11e8-9ee0-000c292b173a
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
[root@mysql-2 ~]#
注释:Seconds_Behind_Master是衡量Master与Slave之间主从同步延迟情况的一个重要参数,返回值为0时,表示没有延迟。
测试输出的结果跟自定义的结果是否相符:
[root@mysql-2 ~]# mysql -uroot -e "show slave status\G;" |grep Slave_IO_Running |awk '{print $2}'|grep -c Yes
1
[root@mysql-2 ~]#
[root@mysql-2 ~]# mysql -uroot -e "show slave status\G" |grep "Slave_SQL_Running" |awk '{print $2}'|grep -c Yes
1
[root@mysql-2 ~]#
返回值: 1相符;0不相符。
测试Slave_IO_Running、Slave_SQL_Running的返回值是否都为Yes:
[root@mysql-2 ~]# mysql -uroot -e "show slave status\G" |grep -E "Slave_IO_Running|Slave_SQL_Running" |awk '{print $2}'|grep -c Yes
2
[root@mysql-2 ~]#
注释:返回值要为2才代表主从同步是正常。
###############
被监控主机的Agent的安装可参考 Zabbix使用Agent监控CentOS6/Redhat6
修改被监控主机的Agent配置文件:(只监控从服务器)
[root@mysql-2 ~]# vi /etc/zabbix/zabbix_agentd.conf
# UnsafeUserParameters=0
修改为:(启用该功能)
UnsafeUserParameters=1
接着插入:
- UserParameter=mysql.ping[*], mysqladmin -u"root" -h"$1" -P"$2" ping
- UserParameter=mysql.get_status_variables[*], mysql -u"root" -h"$1" -P"$2" -sNX -e "show global status"
- UserParameter=mysql.version[*], mysqladmin -u"root" -s -h"$1" -P"$2" version
- UserParameter=mysql.db.discovery[*], mysql -u"root" -h"$1" -P"$2" -sN -e "show databases"
- UserParameter=mysql.dbsize[*], mysql -u"root" -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"root" -h"$1" -P"$2" -sNX -e "show slave status"
- UserParameter=mysql.slave_status[*], mysql -u"root" -h"$1" -P"$2" -sNX -e "show slave status"
复制代码
保存好配置文件后,重启zabbix-agent:
[root@mysql-2 ~]# service zabbix-agent restart
Shutting down Zabbix agent: [确定]
Starting Zabbix agent: [确定]
[root@mysql-2 ~]#
######
在Zabbix服务器测试,验证能否获取被监控主机(192.168.168.131)的键值:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -k net.tcp.listen[10050]
1
[root@centos8 ~]#
测试模板中,关于主从同步情况的键值:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -k mysql.slave_status["localhost","3306"]
<?xml version="1.0"?>
<resultset statement="show slave status
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Slave_IO_State">Waiting for master to send event</field>
<field name="Master_Host">192.168.168.130</field>
<field name="Master_User">happy</field>
<field name="Master_Port">3306</field>
<field name="Connect_Retry">60</field>
<field name="Master_Log_File">binlog.000001</field>
<field name="Read_Master_Log_Pos">454</field>
<field name="Relay_Log_File">mysqld-relay-bin.000005</field>
<field name="Relay_Log_Pos">236</field>
<field name="Relay_Master_Log_File">binlog.000001</field>
<field name="Slave_IO_Running">Yes</field>
<field name="Slave_SQL_Running">Yes</field>
<field name="Replicate_Do_DB"></field>
<field name="Replicate_Ignore_DB"></field>
<field name="Replicate_Do_Table"></field>
<field name="Replicate_Ignore_Table"></field>
<field name="Replicate_Wild_Do_Table"></field>
<field name="Replicate_Wild_Ignore_Table"></field>
<field name="Last_Errno">0</field>
<field name="Last_Error"></field>
<field name="Skip_Counter">0</field>
<field name="Exec_Master_Log_Pos">454</field>
<field name="Relay_Log_Space">570</field>
<field name="Until_Condition">None</field>
<field name="Until_Log_File"></field>
<field name="Until_Log_Pos">0</field>
<field name="Master_SSL_Allowed">No</field>
<field name="Master_SSL_CA_File"></field>
<field name="Master_SSL_CA_Path"></field>
<field name="Master_SSL_Cert"></field>
<field name="Master_SSL_Cipher"></field>
<field name="Master_SSL_Key"></field>
<field name="Seconds_Behind_Master">0</field>
<field name="Master_SSL_Verify_Server_Cert">No</field>
<field name="Last_IO_Errno">0</field>
<field name="Last_IO_Error"></field>
<field name="Last_SQL_Errno">0</field>
<field name="Last_SQL_Error"></field>
<field name="Replicate_Ignore_Server_Ids"></field>
<field name="Master_Server_Id">1</field>
<field name="Master_UUID">5bb1908b-7fcc-11e8-9ee0-000c292b173a</field>
<field name="Master_Info_File">/var/lib/mysql/master.info</field>
<field name="SQL_Delay">0</field>
<field name="SQL_Remaining_Delay" xsi:nil="true" />
<field name="Slave_SQL_Running_State">Slave has read all relay log; waiting for the slave I/O thread to update it</field>
<field name="Master_Retry_Count">86400</field>
<field name="Master_Bind"></field>
<field name="Last_IO_Error_Timestamp"></field>
<field name="Last_SQL_Error_Timestamp"></field>
<field name="Master_SSL_Crl"></field>
<field name="Master_SSL_Crlpath"></field>
<field name="Retrieved_Gtid_Set"></field>
<field name="Executed_Gtid_Set"></field>
<field name="Auto_Position">0</field>
</row>
</resultset>
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -k mysql.replication.discovery["localhost","3306"]
<?xml version="1.0"?>
<resultset statement="show slave status
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Slave_IO_State">Waiting for master to send event</field>
<field name="Master_Host">192.168.168.130</field>
<field name="Master_User">happy</field>
<field name="Master_Port">3306</field>
<field name="Connect_Retry">60</field>
<field name="Master_Log_File">binlog.000001</field>
<field name="Read_Master_Log_Pos">454</field>
<field name="Relay_Log_File">mysqld-relay-bin.000005</field>
<field name="Relay_Log_Pos">236</field>
<field name="Relay_Master_Log_File">binlog.000001</field>
<field name="Slave_IO_Running">Yes</field>
<field name="Slave_SQL_Running">Yes</field>
<field name="Replicate_Do_DB"></field>
<field name="Replicate_Ignore_DB"></field>
<field name="Replicate_Do_Table"></field>
<field name="Replicate_Ignore_Table"></field>
<field name="Replicate_Wild_Do_Table"></field>
<field name="Replicate_Wild_Ignore_Table"></field>
<field name="Last_Errno">0</field>
<field name="Last_Error"></field>
<field name="Skip_Counter">0</field>
<field name="Exec_Master_Log_Pos">454</field>
<field name="Relay_Log_Space">570</field>
<field name="Until_Condition">None</field>
<field name="Until_Log_File"></field>
<field name="Until_Log_Pos">0</field>
<field name="Master_SSL_Allowed">No</field>
<field name="Master_SSL_CA_File"></field>
<field name="Master_SSL_CA_Path"></field>
<field name="Master_SSL_Cert"></field>
<field name="Master_SSL_Cipher"></field>
<field name="Master_SSL_Key"></field>
<field name="Seconds_Behind_Master">0</field>
<field name="Master_SSL_Verify_Server_Cert">No</field>
<field name="Last_IO_Errno">0</field>
<field name="Last_IO_Error"></field>
<field name="Last_SQL_Errno">0</field>
<field name="Last_SQL_Error"></field>
<field name="Replicate_Ignore_Server_Ids"></field>
<field name="Master_Server_Id">1</field>
<field name="Master_UUID">5bb1908b-7fcc-11e8-9ee0-000c292b173a</field>
<field name="Master_Info_File">/var/lib/mysql/master.info</field>
<field name="SQL_Delay">0</field>
<field name="SQL_Remaining_Delay" xsi:nil="true" />
<field name="Slave_SQL_Running_State">Slave has read all relay log; waiting for the slave I/O thread to update it</field>
<field name="Master_Retry_Count">86400</field>
<field name="Master_Bind"></field>
<field name="Last_IO_Error_Timestamp"></field>
<field name="Last_SQL_Error_Timestamp"></field>
<field name="Master_SSL_Crl"></field>
<field name="Master_SSL_Crlpath"></field>
<field name="Retrieved_Gtid_Set"></field>
<field name="Executed_Gtid_Set"></field>
<field name="Auto_Position">0</field>
</row>
</resultset>
[root@centos8 ~]#
##################Zabbix添加被监控主机:(只监控从服务器)
配置》主机》创建主机: (主机名称无需与被监控主机的真实主机名一致;群组可以随便选;IP地址那里要输入被监控主机的IP地址;端口为10050)
注释:
使用agent代理程序的接口。
使用 TCP 10050 端口。
主机MySQL_Slave创建成功:
给主机MySQL_Slave添加模板:( Template DB MySQL )
注释:模板Template DB MySQL 是Zabbix自带的。
模板添加成功:
模板Template DB MySQL 自带的自动发现规则:
点击“现在检查”:
会根据被监控主机的实际情况,自动发现一些监控项、触发器等:
模板Template DB MySQL 自动发现的监控项:
监控项名称: Replication: Slave IO Running 192.168.168.130
键值: mysql.slave_io_running["192.168.168.130"]
模板Template DB MySQL 自动发现的触发器:(触发器的表达式要用到监控项中的键值)
触发器名称: MySQL: The slave I/O thread is not running
表达式: {MySQL_Slave:mysql.slave_io_running["192.168.168.130"].count(#1,"No",eq)}=1
测试:
在从服务器上关闭同步:
stop slave;
在从服务器上本地测试:
[root@mysql-2 ~]# mysql -uroot -e "show slave status\G;"
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.168.130
Master_User: happy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 454
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 236
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 454
Relay_Log_Space: 570
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: NULL
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_UUID: 5bb1908b-7fcc-11e8-9ee0-000c292b173a
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
[root@mysql-2 ~]#
在Zabbix服务器上远程测试:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -k mysql.slave_status["localhost","3306"]
<?xml version="1.0"?>
<resultset statement="show slave status
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Slave_IO_State"></field>
<field name="Master_Host">192.168.168.130</field>
<field name="Master_User">happy</field>
<field name="Master_Port">3306</field>
<field name="Connect_Retry">60</field>
<field name="Master_Log_File">binlog.000001</field>
<field name="Read_Master_Log_Pos">454</field>
<field name="Relay_Log_File">mysqld-relay-bin.000005</field>
<field name="Relay_Log_Pos">236</field>
<field name="Relay_Master_Log_File">binlog.000001</field>
<field name="Slave_IO_Running">No</field>
<field name="Slave_SQL_Running">No</field>
<field name="Replicate_Do_DB"></field>
<field name="Replicate_Ignore_DB"></field>
<field name="Replicate_Do_Table"></field>
<field name="Replicate_Ignore_Table"></field>
<field name="Replicate_Wild_Do_Table"></field>
<field name="Replicate_Wild_Ignore_Table"></field>
<field name="Last_Errno">0</field>
<field name="Last_Error"></field>
<field name="Skip_Counter">0</field>
<field name="Exec_Master_Log_Pos">454</field>
<field name="Relay_Log_Space">570</field>
<field name="Until_Condition">None</field>
<field name="Until_Log_File"></field>
<field name="Until_Log_Pos">0</field>
<field name="Master_SSL_Allowed">No</field>
<field name="Master_SSL_CA_File"></field>
<field name="Master_SSL_CA_Path"></field>
<field name="Master_SSL_Cert"></field>
<field name="Master_SSL_Cipher"></field>
<field name="Master_SSL_Key"></field>
<field name="Seconds_Behind_Master" xsi:nil="true" />
<field name="Master_SSL_Verify_Server_Cert">No</field>
<field name="Last_IO_Errno">0</field>
<field name="Last_IO_Error"></field>
<field name="Last_SQL_Errno">0</field>
<field name="Last_SQL_Error"></field>
<field name="Replicate_Ignore_Server_Ids"></field>
<field name="Master_Server_Id">1</field>
<field name="Master_UUID">5bb1908b-7fcc-11e8-9ee0-000c292b173a</field>
<field name="Master_Info_File">/var/lib/mysql/master.info</field>
<field name="SQL_Delay">0</field>
<field name="SQL_Remaining_Delay" xsi:nil="true" />
<field name="Slave_SQL_Running_State"></field>
<field name="Master_Retry_Count">86400</field>
<field name="Master_Bind"></field>
<field name="Last_IO_Error_Timestamp"></field>
<field name="Last_SQL_Error_Timestamp"></field>
<field name="Master_SSL_Crl"></field>
<field name="Master_SSL_Crlpath"></field>
<field name="Retrieved_Gtid_Set"></field>
<field name="Executed_Gtid_Set"></field>
<field name="Auto_Position">0</field>
</row>
</resultset>
[root@centos8 ~]#
在从服务器上关闭同步后,仪表板会如下图显示:(显示对应触发器的名称)
MySQL: The slave I/O thread is not running
在从服务器上开启同步:
start slave;
在从服务器上开启同步后,仪表板的告警自动消失:
############
再修改被监控主机的Agent配置文件:(只监控从服务器)
[root@mysql-2 ~]# vi /etc/zabbix/zabbix_agentd.conf
在UnsafeUserParameters=1下面插入自定义的键值:
UserParameter=Replication_status,mysql -uroot -e "show slave status\G" |grep -E "Slave_IO_Running|Slave_SQL_Running" |awk '{print $2}'|grep -c Yes
保存好配置文件后,重启zabbix-agent:
[root@mysql-2 ~]# service zabbix-agent restart
Shutting down Zabbix agent: [确定]
Starting Zabbix agent: [确定]
[root@mysql-2 ~]#
######
在Zabbix服务器测试,验证能否获取被监控主机(192.168.168.131)的自定义键值:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -p10050 -k "Replication_status"
2
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -k "Replication_status"
2
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -k Replication_status
2
[root@centos8 ~]#
给“主从同步状态”创建监控项:
自定义名称: MySQL Replication_status
键值: Replication_status
信息类型: 数字(无正负)
备注:其他地方保持默认,点击底下的“添加”按键。
给监控项“MySQL Replication_status”创建触发器:(返回值不等于2就告警)
自定义名称: MySQL Replication_status is down
严重性: 严重
插入表达式:(监控项: MySQL_Slave: MySQL Replication_status )
自动生成的表达式:(触发器的表达式要用到监控项中的键值)
{MySQL_Slave:Replication_status.last()}<>2
本页拖下去:
给监控项“MySQL Replication_status”创建图形:
监控项:
图形“MySQL Replication_status iamge”创建成功:
查看图形:
监测》图形:
测试:
在从服务器上关闭同步:
stop slave;
在从服务器上本地测试:
[root@mysql-2 ~]# mysql -uroot -e "show slave status\G" |grep -E "Slave_IO_Running|Slave_SQL_Running" |awk '{print $2}'|grep -c Yes
0
[root@mysql-2 ~]#
在Zabbix服务器上远程测试:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.131 -k Replication_status
0
[root@centos8 ~]#
监控项“MySQL Replication_status”的返回值不等于2时,仪表板会如下图显示:(显示对应触发器的名称)
MySQL Replication_status is down
对应图形“MySQL Replication_status iamge”也会自动发生改变:
注释:图形中的数据线是从右往左延伸的。
注释:图形中的“最新”值不是 2
在从服务器上开启同步:
start slave;
对应图形“MySQL Replication_status iamge”也会自动发生改变:
注释:图形中的数据线是从右往左延伸的。
注释:图形中的“最新”值变为 2
在从服务器上开启同步后,仪表板的告警自动消失:
相关文章:
重置MySQL5.5/5.6/5.7的用户密码
Zabbix使用模板监控Linux下的MySQL
Zabbix调用Python3脚本监控MySQL主从同步状态
Zabbix5.0.12_使用模板监控MySQL主从同步状态 |