Nagios 監控 MySQL 性能完整部署指南：安裝、配置、NRPE 與圖形化展示

一、架構規劃

二、Nagios Server 端編譯安裝

# 依賴包
yum install -y httpd gcc make perl-ExtUtils-Embed gd-devel

# 創建用戶
useradd nagios
usermod -G nagios apache  # Apache 權限訪問 Nagios 目錄

# 編譯安裝 Nagios Core
tar jxf nagios-cn-3.2.3.tar.bz2
cd nagios-cn-3.2.3
./configure --enable-embedded-perl
make all
make install
make install-init
make install-commandmode
make install-config
make install-webconf  # 自動整合 Apache 配置

三、Nagios Plugins 編譯安裝

yum install -y mysql-devel openssl-devel
tar zxf nagios-plugins-1.4.15.tar.gz
cd nagios-plugins-1.4.15
./configure --with-nagios-user=nagios --with-nagios-group=nagios \
            --enable-extra-opts --enable-libtap --enable-perl-modules
make && make install

四、MySQL 端授權與驗證

# 建立監控專用庫與用戶
mysql> CREATE DATABASE nagdb DEFAULT CHARSET=utf8;
mysql> GRANT SELECT ON nagdb.* TO 'nagios'@'%' IDENTIFIED BY 'nagios';
mysql> FLUSH PRIVILEGES;

# 測試連通性
/usr/local/nagios/libexec/check_mysql -H 192.168.1.152 -u nagios -d nagdb -p nagios
# 輸出示例：
# Uptime: 3374 Threads: 1 Questions: 11 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.003

解決 libmysqlclient.so 缺失

ln -s /usr/local/mysql/lib/libmysqlclient.so.18.0.0 /usr/lib/libmysqlclient.so.18

五、NRPE 配置（被監控端執行插件）

MySQL Master (192.168.0.201) 上：

# /usr/local/nagios/etc/nrpe.cfg
command[check_mysql]=/usr/local/nagios/libexec/check_mysql -H 192.168.1.152 -u nagios -d nagdb -p nagios

# 重啟 NRPE
/usr/local/nagios/bin/nrpe -c /usr/local/nagios/etc/nrpe.cfg -d

六、Nagios 服務端服務定義

# /usr/local/nagios/etc/objects/services.cfg
define service{
  use                     local-service
  host_name               node2
  service_description     mysql
  check_command           check_nrpe!check_mysql
  notifications_enabled   1
}

hosts.cfg：

define host{
  use             linux-server
  host_name       vm2.example.com
  alias           Nagios vm2
  address         127.0.0.1
  icon_image      switch.gif
  statusmap_image switch.gd2
  2d_coords       100,200
  3d_coords       100,200,100
}

define hostgroup{
  hostgroup_name  linux-servers
  alias           Linux Servers
  members         *
}

七、nagios.cfg 主配置引用

# /usr/local/nagios/etc/nagios.cfg
cfg_file=/usr/local/nagios/etc/objects/services.cfg
cfg_file=/usr/local/nagios/etc/objects/hosts.cfg
# cfg_file=/usr/local/nagios/etc/objects/localhost.cfg  # 註釋掉默認

八、啟動驗證

十、總結

1. Nagios + NRPE 實現**中心化監控**，Server 端無需安裝 MySQL 客戶端
2. check_mysql 插件支援豐富閾值，覆蓋連接、查詢、緩存、複製核心指標
3. Web 界面提供直觀狀態、歷史圖表、報警通知（郵件/短信）
4. 生產環境建議配合 **PNP4Nagios** 或 **Grafana** 做長期趨勢圖形化
5. 配置管理建議用 **Ansible/Puppet** 批量部署 NRPE 客戶端