Shulou(Shulou.com)06/01 Report--

Construction of Lepus3.8- Heavenly Rabbit mysql Database Monitoring system

Lepus is an open source database monitoring software, which can monitor all kinds of database status in detail, such as mysql slow query and master-slave status.

This is the web monitoring page:

Build (this article is the CentOS 6.7 system environment):

First, install the lamp or lnmp environment:

There are many installation methods, such as source code package, yum, rpm and so on. Here we simply mention the simple installation method of yum.

Packages that need to be installed:

Httpd

Httpd-manual

Httpd-devel

Php

Php-mysql

Mysql

Mysql-server

You can install the above package by yum: yum install httpd httpd-manual httpd-devel php php-mysql mysql mysql-server-y

1) .mysql

[root@jk ~] # yum install mysqlmysql-server-y

[root@jk ~] # rpm-qa | grepmysql

[root@jk ~] # service mysqld start

[root@jk ~] # netstat-anplt | grep mysql to check whether the mysqld port is enabled and the service is normal.

2). Apache

[root@monitoring-45 lepus] # yuminstall httpd httpd-manual httpd-devel-y

[root@monitoring-45lepus] # rpm-qa | grep http*

[root@jk ~] # service httpd start

The above: httpd: Could not reliably determine the server's fully qualifieddomain name prompt is that apache will verify the configuration of ServerName when it starts. You can change the hosts file to add local parsing or modify line 276 of httpd.conf to change # ServerName www.example.com:80 to ServerName "hostname" to prevent it from reminding.

[root@jk ~] # netstat-anplt | grep httpd to check whether the mysqld port is enabled and the service is normal.

3) .php

[root@jk ~] # yum install phpphp-mysql-y

[root@monitoring-45lepus] # rpm-qa | grep php-5

[root@jk ~] # rpm-ql php View php related files

/ etc/httpd/conf.d/php.conf

/ usr/lib64/httpd/modules/libphp5.so

/ var/lib/php/session

/ var/www/icons/php.gif

4)。 Configure apache to make apache support php

[root@jk ~] # vim/etc/httpd/conf.d/php.conf configuration php profile

To see if there is any dynamic loading, the first page supported by apache is in the red box below.

[root@jk ~] # vim/etc/httpd/conf/httpd.conf configuration apache profile

AddType applition/x-httpd-php.php # add this line

DirectoryIndex index.html index.php # add index.php to let apache recognize the php home page

Restart apache

[root@jk ~] # touch/var/www/html/1.php

[root@jk ~] # vim / var/www/html/1.php

The browser opens the http:// server ip/1.php. The following figure indicates that the configuration is successful:

5)。 Let php support mysql

[root@jk ~] # yum install-y php-mysql

Check:

[root@jk ~] # vim / var/www/html/1.php

After restarting apache,service httpd restart

The browser opens the http:// server ip/1.php and appears

Connect success!

Is a success.

6)。 Set service self-startup:

[root@jk ~] # chkconfig httpd on

[root@jk ~] # chkconfig httpd--list

Httpd 0: off 1: off 2: enable 3: enable 4: enable 5: enable 6: close

[root@jk ~] # chkconfig mysqld on

2. Install python on the monitoring side (version 2.6.6 is also recommended)

……

Third, the monitoring side installs the driver package of the database connection Python

1)。 Install MySQLdb for python (must be installed if you need to monitor MySQL)

MySQLdb is a class library for Python to connect and operate MySQL, and this module must be installed if you are going to use the lepus system to monitor the MySQL database.

[root@jk ~] # wget http://cdn.lepus.cc/cdncache/software/MySQLdb-python.zip

[root@jk ~] # unzip MySQLdb-python.zip

[root@jk ~] # cd MySQLdb1-master/

[root@jk ~] # which mysql_config

/ usr/local/mysql/bin/mysql_config

[root@jk ~] # vim site.cfg is modified as follows:

Mysql_config = / usr/local/mysql/bin/mysql_config

[root@jk ~] # python setup.py build

[root@jk ~] # python setup.py install

If you have the following problem with compiling python

/ usr/bin/ld: cannot find-lpython2.7

Collect2: ld returned 1 exit status

Error: command 'gcc' failed with exit status1

Please follow these steps:

a. Check and install the python-devel package

[root@jk ~] # yum-y install python-devel

b. Soft connect the libpython2.7.so library file to / usr/lib

Execute under the 32-bit server:

[root@jk] # ln-s / usr/local/Python2.7/lib/libpython2.7.so/usr/lib/libpython2.7.so

Execute under 64-bit server:

[root@jk] # ln-s / usr/local/Python2.7/lib/libpython2.7.so/usr/lib64/libpython2.7.so

c. Check whether / etc/ld.so.conf contains / usr/local/Python2.7/lib

[root@jk ~] # vi / etc/ld.so.conf add / usr/local/Python2.7/lib

[root@jk ~] # / sbin/ldconfig

2)。 Install cx_oracle for python (if you need to monitor Oracle, you must install it, which is not used in this article)

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 it is. * i386.rpm

Select InstantClient 11 if it is oracle 9i or higher, or InstantClient 10 if it is 8 or 8i

[root@jk] # rpm-ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

[root@jk] # rpm-ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

[root@jk] # rpm-ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

[root@jk ~] # 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

[root@jk ~] # vim / etc/ld.so.conf join

/ usr/lib/oracle/11.2/client64/lib

Implementation of ldconfig takes effect

[root@jk ~] # ldconfig

Install cx_oracle

[root@jk ~] # wget http://cdn.lepus.cc/cdncache/software/cx_Oracle-5.1.2.tar.gz

[root@jk ~] # tar-xvf cx_Oracle-5.1.2.tar.gz

[root@jk ~] # cd cx_Oracle-5.1.2

[root@jk ~] # python setup.py build

[root@jk ~] # python setup.py install

3)。 Install Pymongo for python (must be installed if you need to monitor Mongodb)

Pymongo is a class library for Python to connect and operate MongoDB, and this module must be installed if you are going to use the lepus system to monitor the MongoDBs database.

The installation steps are as follows:

Download pymongo https://pypi.python.org/packages/source/p/pymongo/

[root@jk ~] # wget http://cdn.lepus.cc/cdncache/software/pymongo-2.7.tar.gz

[root@jk ~] # tar-xvf pymongo-2.7.tar.gz

[root@jk ~] # cd pymongo-2.7

[root@jk ~] # python setup.py install

4)。 Install the Redis driver (must be installed if you need to monitor Redis)

[root@jk ~] # wget http://cdn.lepus.cc/cdncache/software/redis-py-2.10.3.tar.gz

[root@jk ~] # tar-xvf redis-py-2.10.3.tar.gz

[root@jk ~] # cd redis-2.10.3/

[root@jk ~] # python setup.py install

5)。 Test whether each driver is running properly

[root@jk~] # wget http://www.lepus.cc/soft/download/18

[root@jk~] # unzip Lepus Database Enterprise Monitoring system 3.7 official download .zip

[root@jk~] # cd Lepus_v3.8_beta/

[root@jk~] # python test_driver_mysql.py

MySQL python drivier is ok!

[root@jk ~] # python test_driver_oracle.py

Oracle python drivier is ok!

[root@jk ~] # python test_driver_mongodb.py

MongoDB python drivier is ok!

[root@jk ~] # python test_driver_redis.py

Redis python drivier is ok!

4. Lepus monitoring system is installed at the monitoring end.

1)。 Download the software package to the monitor and decompress it

(download link: http://down.51cto.com/data/2237019)

[root@jk~] # wget http://www.lepus.cc/soft/download/18

[root@jk~] # unzip Lepus Database Enterprise Monitoring system 3.7 official download .zip

[root@jk~] # cd Lepus_v3.8_beta/

2)。 Create a monitoring database on the monitoring machine and authorize it.

Database execution on the monitoring side:

Mysql > create database 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 the SQL file (table structure and data file) in the sql folder of Lepus_v3.8_beta

[root@jk~] # mysql-uroot-p lepus

< sql/lepus_table.sql [root@jk~]# mysql -uroot -p lepus < sql/lepus_data.sql 4).安装Lpeus程序 [root@jk~]#pwd [root@monitoring-45Lepus_v3.8_beta]# cd python/ [root@monitoring-45Lepus_v3.8_beta]# chmod +x install.sh 修改安装位置： [root@monitoring-45python]# vim install.sh 下图红色标记即为安装位置 [root@monitoring-45lepus]# vim lepus 修改启动文件 执行安装 [root@monitoring-45Lepus_v3.8_beta]# ./install.sh [note]lepus will be install on basedir: /date/server/lepus [note]/date/server/lepus directory does not 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).修改配置文件 进入到安装目录/date/server/lepus，默认为/usr/local/lepus [root@monitoring-45 ~]# cd /date/server/lepus [root@monitoring-45 lepus]# vim etc/config.ini ###监控机MySQL数据库连接地址### [monitor_server] host="localhost" port=3306 user=" lepus_user " passwd="111.test" dbname="lepus" 6).启动监控系统 执行以下命令启动系统采集进程 [root@monitoring-45 lepus]#lepus start nohup: appending output to`nohup.out' lepus monitor start success! 执行以下命令关闭系统采集进程 [root@monitoring-45 lepus]#lepus stop 执行以下命令监控系统是否正常运行 [root@monitoring-45 lepus]#lepus status lepus server is running... 7).将lepus_monitor脚本部署为计划任务，该脚本可以监控Lepus是否正在运行。 打开文件/data/server/lepus/lepus_monitor修改里面连接监控机的数据库连接地址，然后将该脚本部署为计划任务。 [root@monitoring-45 lepus]# crontab -e */5 * * * * /data/server/lepus/lepus_monitor >

> / tmp/lepus_monitor.log

8) deploy the web page WEB interface

Copy the files in the php folder of the installation folder to the website root directory corresponding to Apache

[root@monitoring-45 lepus] # cp-rf / root/bao/Lepus_v3.8_beta/php/* / var/www/html/

Open / var/www/html/application/config/database.php file to modify database information of PHP connection monitoring server

[root@monitoring-45 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

[root@monitoring-45 lepus] # service httpd restart

10)。 Log in to add hosts and monitor

Enter the IP address or domain name through the browser to open the monitoring interface and log in to the system. Default administrator account password admin/Lepusadmin

After logging in, please change the administrator password, add an ordinary account, authorize the database that needs to be monitored, and add a host in WEB to monitor.

4. Configuration of Lepus monitoring database

1)。 The firewall of the monitored end opens port 3306 that allows the monitoring side ip, and mysql adds the monitoring account:

[root@jk] # iptables-An INPUT-s "Monitoring side ip"-m state-- state NEW-m tcp- p tcp--dport 3306-j ACCEPT

Database execution on the monitored side:

[root@jk ~] #

Mysql > grant select,process,super on *. * to 'monitor'@'server monitor ip' identified by' 222.test'

After the account is created by the monitored end, you can add mysql database monitoring on the web page of the monitoring side:

Wait a few minutes and you will see the message.

Note: master-slave replication can only be seen by monitoring both the master and standby libraries.

2)。 The solution for not getting the database tablespace analysis data:

Add check_mysql_bigtable to lepus.py, as shown in the figure

The "* 120" in the picture sets the time interval. If the web page is set to refresh the data for 1 minute, change it to 120 here. In the end, it will take 2 hours to monitor and get new data.

5. Slow query configuration of Lepus monitoring database

1)。 The monitoring side configures the database account to allow the monitored side to write data.

Database execution on the monitoring side:

Mysql > grantselect,insert,update,delete,create on lepus.* to 'lepus_user'@' is monitored by ip' identified by' 333.test'

2)。 Adding an account to the monitored database allows the script to obtain database information

Database execution on the monitored side:

Mysql > grantselect,process,super on *. * to 'monitor'@'localhost' identified by' 444.test'

3)。 The monitored end needs to install pt tools.

[root@mysql-master-39~] # yum-y install perl-IO-Socket-SSL perl-DBI perl-DBD-MySQLperl-Time-HiRes' perl (Time::HiRes)'

Download percona-toolkit:

[root@mysql-master-39~] # wget https://www.percona.com/downloads/percona-toolkit/2.2.6/RPM/percona-toolkit-2.2.6-1.noarch.rpm

[root@mysql-master-39~] # rpm-ivh percona-toolkit-2.2.6-1.noarch.rpm

4)。 Copy the lepus_slowquery.sh script from the client directory in the lepus installation package to the monitored end

[root@mysql-master-39~] # ls lepus_slowquery.sh

[root@mysql-master-39~] # chomd + x lepus_slowquery.sh

[root@mysql-master-39~] # which mysql

[root@mysql-master-39~] # which pt-query-digest

Get the database id:

[root@mysql-master-39~] # the red part of vim lepus_slowquery.sh needs to be modified

#! / 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 the account information of the monitoring end

Lepus_db_host= "Monitoring side ip"

Lepus_db_port=3306

Lepus_db_user= "lepus_user"

Lepus_db_password= "333.test"

Lepus_db_database= "lepus"

# configmysqlserver monitored computer

# set mysql command path and local database monitoring account information

Mysql_client= "/ usr/local/mysql/bin/mysql"

Mysql_host= "localhost"

Mysql_port=3306

Mysql_user= "monitor"

Mysql_password= "444.test"

# configslowqury slow log location, obtained from my.cnf configuration file

Slowquery_dir= "/ data/logs/mysql/"

# set the slow query time to be collected

Slowquery_long_time=1

Slowquery_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 $2}'`

# set pt-query-digest path

Pt_query_digest= "/ usr/bin/pt-query-digest"

# configserver_id monitored computer id from the lepus graphical interface

Lepus_server_id=274

Finally, note the vi editing settings

: set ff?

Displays the current format, defaults to dos, and cannot be executed at this time

Need to:

: set fileformat=unix

The script cannot be executed until it is changed to unix.

Execute the script manually once and you can see the data on the web page:

5)。 Copy the script to / usr/local/sbin/ and add scheduled tasks for scheduled execution (recommended interval within one hour)

[root@mysql-master-39~] # crontab-e

* / 27 * sh / usr/local/sbin/lepus_slowquery.sh > / dev/null 2 > & 1

6)。 Configure mailbox

Configure the sender:

Click the configuration Center-Global Settings, and then as shown in the figure, note that the alarm recipient needs to be configured separately in the monitoring item settings.

Configure recipients:

Note: both the sender and the recipient should write it all.

Execute in the installation directory of the monitoring server after the configuration is completed.

[root@monitoring-45 lepus] # python test_send_mail.py

True

Send_mail_status:success

After the report is successful, the configuration is complete.

7)。 Slow query automatic push

Regularly push the slow query TOP data collected by the system to the corresponding developers for optimization, and there is no need to deploy scripts on each database.

MySQL slow query automatic push is accomplished through scheduled tasks. If you need to push, you need to deploy the following scheduled tasks on the monitor.

# crontab-e

00 09 * links http://111.com/index.php/task/send_mysql_slowquery_mail > / dev/null 2 > & 1

After the deployment of the scheduled task is completed, the slow query will be pushed to the corresponding personnel according to the time of the scheduled task configuration. The email staff for slow query push needs to be set in the MySQL host configuration. If the mailbox is left empty, the database host will not send slow query push.

6. Lepus monitoring database operating system information configuration

Lepus uses snmp protocol to obtain system information.

1)。 Snmpd service installation

[root@mysql-master-39~] # yum install-y net-snmp net-snmp-utils

2)。 Modify the configuration file to allow specified hosts to listen on themselves

[root@mysql-master-39~] # vim / etc/snmp/snmpd.conf

: set nu

Line 41 change default to monitoring server ip; change public to lepus_monitor

(note: lepus_monitor is a common entity configured in the Sky Rabbit configuration file. If the configured entity is a different name, you need to modify it accordingly.)

Com2secnotConfigUser monitor side ip lepus_monitor

Line 62 change systemview to all for all snmp access

Access notConfigGroup "" any noauth exact all none none

Line 85 removes the # comment symbol

View all included. 1 80

3)。 Modify snmpd log level

The snmpd default log level will write a large amount of snmp information at / var/log/message, which will affect our view of the system log, so we need to change the configuration file:

Comment on the original OPTINOS, add the following line, and restart the snmpd service

[root@mysql-master-39~] # 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

[root@mysql-master-39~] # / etc/init.d/snmpd start

5)。 Verify the snmpd service

Snmpd uses tcp/udp161 port

[root@mysql-master-39~] # grep 'Simple Net Mgmt Proto' / etc/services

Snmp 161/tcp # Simple Net Mgmt Proto

Snmp 161/udp # Simple Net Mgmt Proto

[root@mysql-master-39~] # lsof-I: 161

6)。 Set the SNMP service to boot

Pay attention to setting the firewall to allow the monitoring side to connect to the tcp/udp161 port of the monitored side.

7)。 Use the snmpwalk command to verify on the monitoring side, and execute the following command on the monitoring side

Snmpwalk-v 1-c lepus_monitor monitored side ip

-v refers to the version,-c refers to the key, it is normal to get the system information!

After the installation is completed, configure the operating system information in the configuration center-operating system section of the lepus web page, and fill in the SNMP community name with the key: lepus_monitor.

Wait a few minutes, or execute python lepus.py in the server lepus installation directory to see the data for a moment.

Reference:

Http://www.lepus.cc/post/65

Http://suifu.blog.51cto.com/9167728/1770672

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.