【MySQL】数据库监控方案（Nagios）

你的名字 2022-01-17 11:39 271阅读 0赞

[root@wallet03 ~]# yum install -y epel-release
    
    [root@wallet03 ~]# rpm --import http://packages.icinga.org/icinga.key
    [root@wallet03 ~]# wget http://packages.icinga.org/epel/ICINGA-release.repo -O /etc/yum.repos.d/ICINGA-release.repo
    [root@wallet03 ~]# yum install -y icinga icinga-gui icinga-idoutils
    
    [root@wallet03 ~]# yum install nagios-plugins-all
    
    [root@wallet03 ~]# wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
    [root@wallet03 ~]# rpm -ivh mysql-community-release-el6-5.noarch.rpm
    [root@wallet03 ~]# yum install -y mysql-community-server
    [root@wallet03 ~]# service mysqld start
    [root@wallet03 ~]# /usr/bin/mysql_secure_installation
    
    [root@wallet03 ~]# mysql -uroot -pabcd.1234
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.6.43    |
    +-----------+
    1 row in set (0.02 sec)
    
    [root@wallet03 ~]# mysql -uroot -pabcd.1234
    mysql> create database icinga default character set utf8;
    Query OK, 1 row affected (0.07 sec)
    
    mysql> grant all on icinga.* to 'icinga'@'localhost' identified by 'icinga';
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.04 sec)
    
    [root@wallet03 ~]# cd /usr/share/doc/icinga-idoutils-libdbi-mysql-1.14.0/db/mysql
    [root@wallet03 mysql]# mysql -uroot -pabcd.1234 icinga < mysql.sql
    
    [root@wallet03 ~]# htpasswd /etc/icinga/passwd admin
    New password: 
    Re-type new password: 
    Adding password for user admin
    
    [root@wallet03 ~]# chmod 644 /var/log/icinga/icinga.log 
    
    [root@wallet03 ~]# vi /etc/icinga/cgi.cfg
    :%s/icingaadmin/admin/g
    
    [root@wallet03 ~]# service ido2db start
    Starting Ido2db: done.
    
    [root@wallet03 ~]# service icinga start
    Running configuration check...OK
    Starting icinga: Starting icinga done.
    
    [root@wallet03 ~]# service httpd start
    
    [root@wallet03 ~]# yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes
    
    [root@wallet03 ~]# tar zxvf check_mysql_health-2.2.2.tar.gz 
    [root@wallet03 ~]# cd check_mysql_health-2.2.2
    [root@wallet03 check_mysql_health-2.2.2]# ./configure --prefix=/usr/local/nagios \
    --libexecdir=/usr/lib64/nagios/plugins \
    --with-nagios-user=nagios \
    --with-nagios-group=nagios
    [root@wallet03 check_mysql_health-2.2.2]# make
    [root@wallet03 check_mysql_health-2.2.2]# make install
    
    [root@wallet03 ~]# cd /usr/lib64/nagios/plugins
    [root@wallet03 plugins]# ./check_mysql_health 
    Please select a mode
    
    Copyright (c) 2009 Gerhard Lausser
      Check various parameters of MySQL databases 
      Usage:
        check_mysql_health [-v] [-t <timeout>] [[--hostname <hostname>] 
            [--port <port> | --socket <socket>]
            --username <username> --password <password>] --mode <mode>
            [--method mysql]
            
    [root@wallet03 plugins]# ./check_mysql_health --hostname 192.168.1.202 --username nagios --password nagios --mode connection-time 
    OK - 0.02 seconds to connect as nagios | connection_time=0.0169s;1;5
    
    [root@wallet03 plugins]# ./check_mysql_health --hostname 192.168.1.202 --username nagios --password nagios --mode uptime
    OK - database is up since 7542 minutes | uptime=452541s
    
    [root@wallet03 ~]# cd /etc/icinga/objects
    [root@wallet03 objects]# vi commands.cfg
    define command{
            command_name    check_mysql
            command_line    $USER1$/check_mysql_health --hostname $ARG1$ --username nagios --password nagios --mode $ARG2$
            }
            
    [root@wallet03 objects]# vi mysql.cfg
    define host{
            use                     linux-server
            host_name               wallet02
            alias                   wallet02
            icon_image              redhat.gif
            statusmap_image         redhat.gd2
            address                 192.168.1.202
            }
            
    define hostgroup{
            hostgroup_name  database
            alias           database  
            members         wallet02
            }
                    
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql connection time
            check_command                   check_mysql!192.168.1.202!connection-time
            }
            
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql uptime
            check_command                   check_mysql!192.168.1.202!uptime
            }
            
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql client-connected
            check_command                   check_mysql!192.168.1.202!threads-connected
            }
            
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql slow-queries
            check_command                   check_mysql!192.168.1.202!slow-queries
            }
            
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql tmp-disk-tables
            check_command                   check_mysql!192.168.1.202!tmp-disk-tables
            }
            
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql slave-lag
            check_command                   check_mysql!192.168.1.202!slave-lag
            }
            
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql slave-io-running
            check_command                   check_mysql!192.168.1.202!slave-io-running
            }
            
    define service{
            host_name                       wallet02
            use                             generic-service
            service_description             mysql slave-sql-running
            check_command                   check_mysql!192.168.1.202!slave-sql-running
            }
            
    [root@wallet03 ~]# vi /etc/icinga/icinga.cfg  
    cfg_file=/etc/icinga/objects/mysql.cfg 
                   
    [root@wallet03 ~]# service icinga restart
    Running configuration check...OK
    Stopping Icinga: Waiting for icinga to exit ..Stopping icinga done.
    Starting icinga: Starting icinga done.

[root@wallet02 ~]# mysql -uroot -pabcd.1234
    mysql> create user 'nagios'@'%' identified by 'nagios';
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> grant replication client on *.* to 'nagios'@'%';
    mysql> flush privileges;
    Query OK, 0 rows affected (0.16 sec)

![1.png][]

[root@wallet03 ~]# yum install -y perl-CGI perl-rrdtool perl-GD perl-Time-HiRes perl-ExtUtils-MakeMaker
    [root@wallet03 ~]# wget http://xrl.us/cpanm --no-check-certificate -O /usr/bin/cpanm;chmod +x /usr/bin/cpanm
    [root@wallet03 ~]# cpanm Module::Build
    [root@wallet03 ~]# cpanm Nagios::Config
    
    [root@wallet03 ~]# tar zxvf nagiosgraph-1.5.2.tar.gz
    [root@wallet03 ~]# cd nagiosgraph-1.5.2
    [root@wallet03 nagiosgraph-1.5.2]# ./install.pl --install
    checking required PERL modules
      Carp...1.11
      CGI...3.51
      Data::Dumper...2.124
      Digest::MD5...2.39
      File::Basename...2.77
      File::Find...1.14
      MIME::Base64...3.08
      POSIX...1.17
      RRDs...1.3008
      Time::HiRes...1.9721
    checking optional PERL modules
      GD...2.44
      Nagios::Config...36
    checking nagios installation
      found nagios exectuable at /usr/bin/icinga
      found nagios init script at /etc/init.d/icinga
    checking web server installation
      found apache executable at /usr/sbin/httpd
      found apache init script at /etc/init.d/httpd
    Destination directory (prefix)? [/usr/local/nagiosgraph] 
    Location of configuration files (etc-dir)? [/usr/local/nagiosgraph/etc] 
    Location of executables? [/usr/local/nagiosgraph/bin] 
    Location of CGI scripts? [/usr/local/nagiosgraph/cgi] 
    Location of documentation (doc-dir)? [/usr/local/nagiosgraph/doc] 
    Location of examples? [/usr/local/nagiosgraph/examples] 
    Location of CSS and JavaScript files? [/usr/local/nagiosgraph/share] 
    Location of utilities? [/usr/local/nagiosgraph/util] 
    Location of state files (var-dir)? [/usr/local/nagiosgraph/var] 
    Location of RRD files? [/usr/local/nagiosgraph/var/rrd] 
    Location of log files (log-dir)? [/usr/local/nagiosgraph/var/log] 
    Path of log file? [/usr/local/nagiosgraph/var/log/nagiosgraph.log] 
    Path of CGI log file? [/usr/local/nagiosgraph/var/log/nagiosgraph-cgi.log] 
    Base URL? [/nagiosgraph] 
    URL of CGI scripts? [/nagiosgraph/cgi-bin] 
    URL of CSS file? [/nagiosgraph/nagiosgraph.css] 
    URL of JavaScript file? [/nagiosgraph/nagiosgraph.js] 
    URL of Nagios CGI scripts? [/nagios/cgi-bin] /icinga/cgi-bin
    Path of Nagios performance data file? [/tmp/perfdata.log] 
    username or userid of Nagios user? [nagios] icinga
    username or userid of web server user? [apache] 
    Modify the Nagios configuration? [n] 
    Modify the Apache configuration? [n] 
    configuration:
      ng_prefix            /usr/local/nagiosgraph
      ng_etc_dir           /usr/local/nagiosgraph/etc
      ng_bin_dir           /usr/local/nagiosgraph/bin
      ng_cgi_dir           /usr/local/nagiosgraph/cgi
      ng_doc_dir           /usr/local/nagiosgraph/doc
      ng_examples_dir      /usr/local/nagiosgraph/examples
      ng_www_dir           /usr/local/nagiosgraph/share
      ng_util_dir          /usr/local/nagiosgraph/util
      ng_var_dir           /usr/local/nagiosgraph/var
      ng_rrd_dir           /usr/local/nagiosgraph/var/rrd
      ng_log_dir           /usr/local/nagiosgraph/var/log
      ng_log_file          /usr/local/nagiosgraph/var/log/nagiosgraph.log
      ng_cgilog_file       /usr/local/nagiosgraph/var/log/nagiosgraph-cgi.log
      ng_url               /nagiosgraph
      ng_cgi_url           /nagiosgraph/cgi-bin
      ng_css_url           /nagiosgraph/nagiosgraph.css
      ng_js_url            /nagiosgraph/nagiosgraph.js
      nagios_cgi_url       /icinga/cgi-bin
      nagios_perfdata_file /tmp/perfdata.log
      nagios_user          icinga
      www_user             apache
      modify_nagios_config n
      nagios_config_file   
      nagios_commands_file 
      modify_apache_config n
      apache_config_dir    
      apache_config_file   
    Continue with this configuration? [y]
    
    [root@wallet03 ~]# vi /etc/icinga/icinga.cfg
    process_performance_data=1
    service_perfdata_file=/tmp/perfdata.log
    service_perfdata_file_template=$LASTSERVICECHECK$||$HOSTNAME$||$SERVICEDESC$||$SERVICEOUTPUT$||$SERVICEPERFDATA$
    service_perfdata_file_mode=a
    service_perfdata_file_processing_interval=30
    service_perfdata_file_processing_command=process-service-perfdata-for-nagiosgraph
    
    [root@wallet03 ~]# vi /etc/icinga/objects/commands.cfg
    define command {
      command_name process-service-perfdata-for-nagiosgraph
      command_line /usr/local/nagiosgraph/bin/insert.pl
    }
    
    [root@wallet03 ~]# vi /etc/icinga/objects/templates.cfg
    define service {
            name nagiosgraph
            action_url /nagiosgraph/cgi-bin/show.cgi?host=$HOSTNAME$&service=$SERVICEDESC$
            register 0
    }
    
    [root@wallet03 ~]# vi /etc/icinga/objects/mysql.cfg
    define service{
            host_name                       wallet02
            use                             generic-service,nagiosgraph
            service_description             mysql slave-lag
            check_command                   check_mysql!192.168.1.202!slave-lag
        
    [root@wallet03 ~]# vi /etc/httpd/conf/httpd.conf
    Include /usr/local/nagiosgraph/etc/nagiosgraph-apache.conf    
    
    [root@wallet03 ~]# service icinga restart
    [root@wallet03 ~]# service httpd restart

转载于:https://blog.51cto.com/13598811/2356241

[1.png]: https://s1.51cto.com/images/20190301/1551400899509399.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=