Day Rabbit plugin monitor MySQL

lepus3.8-Day Rabbit MySQL Database monitoring System build original ixhao2016-08-24 02:36:23 Comments (11) 3183 people read lepus3.8-MySQL database monitoring system build Lepus is an open-source database monitoring software, Detailed monitoring of various database status, such as MySQL slow query and master-slave status. This is the Web monitoring page: Wkiom1e9geesfevhaaes1wvvdwa795.png Build (CentOS 6.7 system environment): first, install lamp or LNMP environment: Many installation methods, there are source packages, yum, rpm and other installation, Here's a simple way to get a simple yum installation. Packages to install: Httpdhttpd-manualhttpd-develphpphp-mysqlmysqlmysql-server can install the above package with Yum: Yum installs httpd httpd-manual Httpd-devel php php-mysql mysql mysql-server-y1). mysql[[email protected] ~]# yum install mysqlmysql-server-y[[ Email protected] ~]# Rpm-qa |grepmysqlwkiol1e8luobtrqmaaani8uaxqo736.png[[email protected] ~]# service Mysqld start[[email protected] ~]# netstat-anplt|grep MySQL to see if the mysqld port is open properly, Service is normal wkiom1e8lu7y-gezaaai1ntzdby447.png2). Apache [[email protected] lepus]# yuminstall httpd httpd-manual httpd-devel-y [[email protected]]# Rpm-qa |grep http*wkiol1e8lvjihe0raaarigsop_0688.png [[email protected] ] ~]# service httpd startwkiom1e8lwlz8pw8aaaoqqhsjx8897.png above: Httpd:could not ReliAbly determine the server's fully qualifieddomain name hint is that Apache will validate the servername configuration when booting, and can add local parsing or modify by modifying the Hosts file httpd.conf 276 lines #servername www.example.com:80 to ServerName "hostname" to make it non-alert. [[email protected] ~]# netstat-anplt | grep httpd See if the mysqld port is open properly and the service is Wkiom1e8lwyinxvgaaalrzfy2lq695.png3). php[[email protected] ~]# Yum Install phpphp-mysql–y[[email protected]]# Rpm-qa |grep php-5wkiol1e8lxerhzsiaaahq4wgy64175.png [[email  Protected] ~]# rpm-ql php view php related files/etc/httpd/conf.d/php.conf/usr/lib64/httpd/modules/libphp5.so/var/lib/php/ses  SION/VAR/WWW/ICONS/PHP.GIF4). Configure Apache to allow Apache to support php[[email protected] ~]# vim/etc/httpd/conf.d/php.conf Configure PHP config file wkiom1e8lylt5c12aaascoakyrk780.png See if there is a dynamic loading, below the red box below the Apache supported by the first page [[email protected] ~]# vim/etc/ httpd/conf/httpd.conf configuring Apache configuration Files AddType applition/x-httpd-php.php #添加这一行 <ifmodule dir_module> Directo Ryindex index.html index.php #添加index. PHP is for Apache to recognize PHP home </IfModule> restart Apache[[emAil protected] ~]# touch/var/www/html/1.php[[email protected] ~]# vim/var/www/html/1.php <?php phpinfo ( );?> Browser opens http://server-side ip/1.php, appears as if the configuration was successful (WKIOL1E8LBLWYBQDAABYK2LCZTM868.PNG5). Let PHP support mysql[[email  Protected] ~]# yum install-y php-mysql check: [[email protected] ~]# Vim/var/www/html/1.php<?php$link=mysql_ Connect (' 127.0.0.1 ', ' root ', '), if ($link) echo "Connect success!"; Elseecho ' Connect faild! ';? > Restart Apache,service httpd Restart after the browser opens http://server-side ip/1.php, the Connect success! is successful 6). Set the service self-starter. [[email protected ] ~]# chkconfig httpd on[[email protected] ~]# chkconfig httpd--listhttpd 0: Off 1: Off 2: Enable 3: Enable 4: Enable 5: Enable 6: off [email  protected] ~]# chkconfig mysqld on second, the Monitoring side installation Python (in the text is 2.6.6 version, also recommended the use of 2.7 version) ... Third, the monitoring side installs the database to connect the Python driver package 1). Install MySQLdb for Python (must be installed if you need to monitor MySQL) MySQLdb to connect and manipulate MySQL's class library for Python, If you are planning to use the Lepus system to monitor the MySQL database, the module must be installed. [[email protected] ~]# wget http://cdn.lepus.cc/cdncache/software/MySQLdb-python.zip [[email protected] ~ ]# Unzip Mysqldb-pYthon.zip [[email protected] ~]# CD mysqldb1-master/[[email protected] ~]# which mysql_config/usr/local/ Mysql/bin/mysql_config [[email protected] ~]# vim site.cfg modified as follows: Mysql_config =/usr/local/mysql/bin/mysql_config [[email protected] ~]# python setup.py build [[email protected] ~]# python setup.py install if compiling Python has the following problem/ Usr/bin/ld:cannot find-lpython2.7 Collect2:ld returned 1 exit status Error:command ' GCC ' failed with exit STATUS1 please click below Step processing: A. Check and install the Python-devel package [[email protected] ~]# yum-y install Python-devel b. Setting up the libpython2.7.so library file soft connect to/usr/ Lib under 32-bit server execution: [[email protected] ~]# ln-s/usr/local/python2.7/lib/libpython2.7.so/usr/lib/libpython2.7.so Performed under 64-bit server: [[email protected] ~]# ln-s/usr/local/python2.7/lib/libpython2.7.so/usr/lib64/libpython2.7.so C. Check if the/etc/ld.so.conf contains/usr/local/python2.7/lib [[email protected] ~]# vi/etc/ld.so.conf Add/usr/local/ Python2.7/lib [[email protected] ~]#/sbin/ldconfig 2). Install Cx_oracleFor Python (must be installed if you need to monitor Oracle, this article is not available) download and install Oracle Instant Client:http://www.oracle.com/technetwork/database/features /instant-client/index-097480.html if it is 64-bit, please select. *x86_64.rpm, otherwise. *i386.rpm If it is Oracle 9i or higher, select Instantclient 11, If 8 or 8i, please select Instant Client [[email protected] ~]# RPM-IVH oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64. RPM [[email protected] ~]# RPM-IVH oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm [[email  Protected] ~]# RPM-IVH oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm [[email protected] ~]# Vim/root /.bash_profile oracle_home= "/usr/lib/oracle/11.2/client64" path= $PATH: $ORACLE _home/bin export PATH ld_library_path= $ORACLE _home/lib:/usr/lib:/usr/local/lib Export Ld_library_path [[email protected] ~] #vim/etc/ld.so.conf Join/ Usr/lib/oracle/11.2/client64/lib execution Ldconfig Effective [[email protected] ~]# ldconfig installation cx_oracle [[email  Protected] ~]# wget Http://cdn.lepus.cc/cdncache/software/cx_oracle-5.1.2.tar.gz[[email protEcted] ~]# tar-xvf cx_oracle-5.1.2.tar.gz [[email protected] ~]# CD cx_oracle-5.1.2 [[email protected] ~]# PYT Hon setup.py build [[email protected] ~]# python setup.py install 3). Install Pymongo for Python (must be installed if you need to monitor MongoDB) Pymong o to connect and manipulate MongoDB's class library for Python, the module must be installed if you are ready to monitor the Mongodbs database using the Lepus system. The installation steps are as follows: Download Pymongo https://pypi.python.org/packages/source/p/pymongo/[[email protected] ~]# wget/http cdn.lepus.cc/cdncache/software/pymongo-2.7.tar.gz [[email protected] ~]# tar-xvf pymongo-2.7.tar.gz [email  protected] ~]# cd pymongo-2.7 [[email protected] ~]# python setup.py install 4). Install Redis driver (must be installed if you need to monitor Redis) [ [email protected] ~]# wget http://cdn.lepus.cc/cdncache/software/redis-py-2.10.3.tar.gz[[email  Protected] ~]# tar-xvf redis-py-2.10.3.tar.gz [[email protected] ~]# CD redis-2.10.3/[[email protected] ~]# p Ython setup.py install 5). Test each driver for proper operation [[email protected]~]# wget Http://www.lepus.cc/soft/download/18[[email  protected]~]# Unzip Lepus database Enterprise Monitoring System version 3.7 official download. zip[[email protected]~]# CD lepus_v3.8_beta/[[email protected]~]# Python test_driver_mysql.py mysql python drivier is ok! [[email protected] ~]# python test_driver_oracle.py oracle python drivier is ok! [[email protected] ~]# python test_driver_mongodb.py mongodb python drivier is ok! [[email protected] ~]# python test_driver_redis.py redis python drivier is ok! Iv. Monitoring terminal Installation Lepus monitoring System 1). Download the package to the monitoring side and unzip it (download link: http://down.51cto.com/data/2237019) [[email protected]~]# wget/HTTP// www.lepus.cc/soft/download/18[[email protected]~]# Unzip Lepus database Enterprise Monitoring System version 3.7 official download. zip[[email protected] ~]# CD LEPUS_V3.8_BETA/2). Create a monitoring database and authorize it on the monitor machine. Monitoring side database execution: mysql>create lepus default Character set Utf8;mysql>grant Select,insert,update,delete,create On lepus.* to ' lepus_user ' @ ' localhost ' identified by ' 111.test '; mysql>flush privileges;3). Import Lepus_v3.8_beta SQL file (table structure and data file) in SQL folder [[email protected]~]# mysql-uroot-p Lepus < sql/lepus_table.sql[[email protected]~]# mysql-uroot-p Lepus < sql/lepus_data.sql 4). Installing the Lpeus program [[email  Protected]~] #pwdwKiom1e8libwgj27AAARrTAUvxk692. png[[email protected]_v3.8_beta]# CD python/[[email  protected]_v3.8_beta]# chmod +x install.sh Modify Installation location: [[email protected]]# Vim install.sh    The red mark is the mounting position wkiol1e8ljthujbyaaaquh4bppq056.png[[email protected]]# vim Lepus Modify the startup file Wkiom1e8lj2jcebdaaauz4ozhew268.png perform the installation [[email protected]_v3.8_beta]#./install.sh[note]lepus'll be Install on Basedir:/date/server/lepus[note]/date/server/lepus directory does isn't exist,will be created. [Note]/date/server/lepus Directory created success. [note]wait Copy files ..... [Note]change script permission. [Note]create links. [Note]install complete. 5). Modify the configuration file to enter the installation directory/date/server/lepus, default to/usr/local/lepus[[email protected] ~]# cd/date/server/ Lepuswkiom1e8lkfdhzkpaaa00jfxrku072.png[[email protected] lepus]# vim etc/config.ini## #监控机MySQL数据库连接地址 ###[ Monitor_server]host= "LocaLhost "port=3306user=" Lepus_user "passwd=" 111.test "Dbname=" Lepus "6). Start the monitoring system execute the following command to start the system acquisition process [[email protected] Lepus] #lepus startnohup:appending output to ' nohup.out ' Lepus monitor start success! Execute the following command to turn off the system acquisition process [[[email protected] Lepus] #lepus stop to monitor the system for normal operation by executing the following command [[email protected] Lepus] #lepus Statuslepus Server is running ... 7). Deploy the Lepus_monitor script as a scheduled task that can monitor whether Lepus is running. Open File/data/server/lepus/lepus_monitor Modify the database connection address inside the connection monitor, and then deploy the script as a scheduled task. [[email protected] lepus]# CRONTAB-E*/5 * * * */data/server/lepus/lepus_monitor >>/tmp/lepus_monitor.log 8) Deploy Web page Web interface copy files in PHP folder of installation folder to Apache corresponding Web root directory [[email protected] lepus]# cp-rf/root/bao/lepus_v3.8_beta/php/*/ var/www/html/Open the/var/www/html/application/config/database.php file to modify the database information for the PHP connection Monitoring server [[email protected] lepus]# vim/var/www/html/application/config/database.php$db[' default ' [' hostname '] = ' localhost '; $db [' Default '] [' Username '] = ' lepus_user '; $db [' Default '] [' password '] = ' 111.test '; $db [' Default '] [' database '] =lepus; $db [' Default '] [' dbdriver '] = ' mysql '; 9). Restart apache[[email protected] lepus]# service httpd restart10). Login to add the host and monitor to enter the system by opening the monitoring interface via the browser input IP address or domain name. Default Administrator account password admin/lepusadminwkiol1e8lpsape5uaaahmqx1aj8410.png after login, please modify the administrator password, increase the ordinary account, the need to monitor the database authorization, the Web to add the host to monitor. Iv. Lepus Monitoring Database Configuration 1). The monitored end Firewall opens 3306 ports to allow monitoring of IP, MySQL add monitoring account: [[email protected] ~]# iptables-a input-s "Monitor-side IP"-m state --state new-m tcp-p tcp--dport 3306-j accept monitored End Database execution: [[email protected] ~] #mysql > Grant Select,process,super o n * * to ' monitor ' @ ' Server monitor end IP ' identified by ' 222.test '; you can add MySQL database monitoring on the Monitoring side web page by creating accounts on the monitor side: Wkiol1e8lq3h_ W44aabczmrp8ng759.png Wait a few minutes to see the information. Note: You can only see Master and slave replicas when you monitor both the main and standby libraries. 2). Workaround for database tablespace analysis data: Adding Check_mysql_bigtable,wkiom1e8lrnadbx4aaagdjbekca956.png in lepus.py to "*120" is the set time interval, If the page is set to refresh data for 1 minutes, this is changed to 120. Then it will be 2 hours before the new data is monitored wkiom1e8lskiefexaaanescz2uw021.png Five, lepus monitoring database Slow query configuration 1). Monitor the configuration database account, let the monitored side write to the data monitoring side database execution:mysql> Grantselect,insert,update,delete,create on lepus.* to ' lepus_user ' @ ' monitored end IP ' identified by ' 333.test '; 2). MonitoredDatabase add account allows the script to obtain database information to be executed by the monitoring side database:mysql> grantselect,process,super on *. * to ' monitor ' @ ' localhost ' identified by ' 444. Test '; 3). The monitor side to install the PT tool [[email protected]mysql-master-39~]# yum-y install Perl-io-socket-ssl perl-dbi Perl-dbd-mysqlperl-time-hires ' Perl (time::hires) ' Download percona-toolkit:[[email protected]~]# wget https:// www.percona.com/downloads/percona-toolkit/2.2.6/rpm/percona-toolkit-2.2.6-1.noarch.rpm[[email protected]~ ]# RPM-IVH percona-toolkit-2.2.6-1.noarch.rpm4). Copy the lepus_slowquery.sh script from the client directory in the Lepus installation package to the monitored side [[email  protected]~]# ls lepus_slowquery.sh[[email protected]~]# chomd +x lepus_slowquery.sh[[email protected]~]# which mysqlwkiol1e8ltdjeo2gaaags5rbtxq743.png[[email protected]~]# which Pt-query-digestwkiom1e8ltnycfbcaaahu3mp00c240.png getting the database id:wkiol1e8luggnutnaaba2t87ntg104.png[[email  protected]~]# Vim lepus_slowquery.sh Red part is needed to modify the #!/bin/bash#****************************************************** # #ScriptName:/usr/local/sbin/lepus_slowquery.sh#create date:2014-03-25 10:01#modify date:2014-03-25 10:01#************************************ # #configlepusdatabase Server Monitor # Set up monitoring side account information lepus_db_host= "Monitor side IP" lepus_db_port= 3306lepus_db_user= "Lepus_user" lepus_db_password= "333.test" lepus_db_database= "Lepus" #configmysqlserver被监控机 # Set mysql command path and local database monitoring account information mysql_client= "/usr/local/mysql/bin/mysql" mysql_host= "localhost" mysql_port=3306mysql_ User= "Monitor" mysql_password= "444.test" #configslowqury慢日志位置 to get Slowquery_dir= "/data/logs/mysql/" from the MY.CNF configuration file # Set how much slow query time is collected slowquery_long_time=1slowquery_file= ' $mysql _client-h$mysql_host-p$mysql_port-u$mysql_user-p$ Mysql_password-e "Show variables like ' Slow_query_log_file '" |grep Log|awk ' {print $} ' #设置pt-query-digest path Pt_query_ Digest= "/usr/bin/pt-query-digest" #configserver_id被监控机id从lepus图形界面看lepus_server_id =274 last note VI Edit settings: Set FF, display the current format, The default is DOS, which cannot be performed at this time: The script can be executed only if the set Fileformat=unix is changed to UNIX. You can see the data on the page page by manually executing the script once: wkiol1e8lvxrqwhiaabxwwostvu673.png5). copy Script to/usr/locAl/sbin/and add scheduled tasks scheduled to execute (recommended interval within one hour) [[email protected]~]# CRONTAB-E*/27 * * * * sh/usr/local/sbin/lepus_ slowquery.sh >/dev/null 2>&16). Configure the mailbox configuration from: Click Configuration Center-Global settings, and then note that the alert recipient needs to be configured separately in the monitoring item settings. Wkiol1fszjfqkiyiaabcvktvebu486.jpgwkiom1fsz1lxa1swaaa-f7gsffe044.jpg Configuration recipient: Wkiol1fsz77q_ O4cAACXHwF262o545.pngwKiom1fSZ3WwKP7cAACqKici8Hc876.png Note: Both the sender and the recipient must write the whole. After the configuration is complete on the monitoring server side of the installation directory execution [[email protected] lepus]# python test_send_mail.pyTruesend_mail_status:success The success of the configuration is completed. 7). Slow query auto Push timing pushes the slow query top data collected by the system to the appropriate developer for optimization, eliminating the need to deploy scripts on each database. MySQL slow query auto push is done through scheduled tasks. If you need to push, you need to deploy the following scheduled tasks on the monitor machine. #crontab-e00 * * * links http://111.com/index.php/task/send_mysql_slowquery_mail >/dev/null 2>&1 after the scheduled task deployment is complete , the slow query is pushed to the corresponding person at the time configured for the scheduled task. Slow query push mail personnel need to set up in the MySQL host configuration, the mailbox is left blank the database host will not send slow query push. VI. Lepus Monitoring Database operating System Information configuration Lepus uses the SNMP protocol to obtain System Information 1). SNMPD service installation [[email protected]~]# yum install-y net-snmp NET-SNMP-UTILS2]. Modify the configuration file to allow the specified host to listen to itself [[email  protected]~]# vim/etc/snmp/snmpd.conf:set nu41 will change default to monitor server IP; change public to Lepus_monitor (Note: Lepus_monitor is a common entity configured in the day rabbit configuration file, if the configured entity is a different name, the corresponding modifications are made here) Com2secnotconfiguser the IP lepus_monitor62 line of the monitoring side    Change SystemView to all for all SNMP access access Notconfiggroup "" Any noauth exact all none None85 Line # comment symbol minus view all Included. 1 803). Modify the SNMPD log level snmpd The default log level will write a large amount of SNMP information in/var/log/message, which will affect our view of the system log, so we need to To change the following configuration file: Note The original Optinos, add the following line, restart the SNMPD service can be [[email protected]~]# vim/etc/init.d/snmpd#options=]-lsd-lf/dev/ Null-p/var/run/snmpd.pid-a "options="-ls 4 d-p/var/run/snmpd.pid-a "4). Start the SNMPD service [[email protected]~]#/etc/ini T.D/SNMPD start 5). Verify that the SNMPD service SNMPD uses tcp/udp161 port [[email protected]~]# grep ' simple Net Mgmt Proto '/etc/services s NMP 161/tcp # Simple net Mgmt Proto SNMP 161/udp # Simple net Mgmt Proto [[email  protected]~]# lsof-i: 161 6). Set the SNMP service on boot note setting up the firewall, allowing the monitoring side to connect to the monitored end of the tcp/udp161 Port 7). Use Snmpwalk command verification on the monitor side, Execute the following command on the monitor side snmpwalk-v 1-c lepus_monitor is the monitored end ip-v refers to the version,-C isRefers to the key, get to the system information is normal! After the installation is complete in the Lepus web page Configuration center-operating system column configuration operating system information, SNMP community name fill in just the key: Lepus_monitor. Wkiol1e8lwct3nnvaabjygk7tqw779.png wkiom1fod-lae55kaabuiupspf8162.png Wait a few minutes, or Lepus install directory on the server to execute Python lepus.py a moment to see the data

Day Rabbit plugin monitor MySQL