Zabbix 使用之监控 Mysql 主从状态

使用Zabbix简单监控Mysql主从状态,主要是检查Slave_IO_RunningSlave_SQL_Running状态是否为Yes

查看从库状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
$ docker exec mysql-slave mysql -uroot -proot密码 -e 'show slave status\G'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.87
Master_User: bpkuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000214
Read_Master_Log_Pos: 191000947
Relay_Log_File: mysql-slave-relay-bin.000497
Relay_Log_Pos: 191001160
Relay_Master_Log_File: mysql-bin.000214
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: 191000947
Relay_Log_Space: 191001420
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: 0e41b818-5404-11e8-a330-0242ac110002
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

# 统计 Yes 的个数,如果为 2,则正常,其它则从库同步有问题。
$ docker exec mysql-slave mysql -uroot -proot密码 -e 'show slave status\G' \
| grep -E "Slave_IO_Running:|Slave_SQL_Running:" \
| awk '{print $2}' \
| grep -c Yes
mysql: [Warning] Using a password on the command line interface can be insecure.
2

创建.my.cnf保存mysql密码信息

从上面的运行结果,我们可以看到一个警告mysql: [Warning] Using a password on the command line interface can be insecure.,这个会影响zabbix取值,所以我们要把密码信息保存在.my.cnf文件中。

1
2
3
4
5
6
7
8
$ cat /etc/my.cnf.d/.my.cnf
[mysql]
user=root
password=root密码

[mysqladmin]
user=root
password=root密码

配置zabbix-agent

1
2
3
4
5
6
7
8
9
$ cat /etc/zabbix/zabbix_agentd.conf
# 启用允许从 Zabbix Server 远程执行命令
EnableRemoteCommands=1

$ cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
# 在文件尾部添加以下内容
UserParameter=mysql.slavestatus,HOME=/etc/my.cnf.d docker exec mysql-slave mysql -e 'show slave status\G' | grep -E "Slave_IO_Running:|Slave_SQL_Running:" | awk '{print $2}' | grep -c Yes

$ sudo systemctl restart zabbix-agent

Zabbix 添加监控项及触发器

  1. 点选Configuration->Hosts,选择从库主机aws-prod02->Items

    选择从库主机

  2. 创建一个监控项目,填写NameKeymysql.slavestatusApplications选择MySQL

    创建监控项目

  3. 创建一个触发器,填写NameSeverity选择HighExpression选择mysql.slavestatus 最后值不等于2

    创建触发器

查看结果

这是今天凌晨拆分项目数据库停止同步(stop slave;)的告警邮件。

一分、两分都是爱!