Install the configuration Mysqlmtop to monitor the Mysql running performance Tutorial _mysql

I. Environmental description
1, Server role

2. System environment
CentOS 6.2 x86_64

3. Environmental requirements
(1), MySQL 5.0 and above (used to store the data collected by the monitoring system)
(2), Apache 2.2 and above (WEB server running server)
(3), PHP more than 5.3 (WEB interface)
(4), Python 2 (2.7 version is recommended, other versions are not tested, perform data acquisition and alarm tasks)
(5), mysqldb for Python (Python-connected MySQL interface)

Second, the Python base environment deployment

Https://www.python.org/ftp/python/2.7.6/Python-2.7.6.tgz

# tar Xvzf python-2.7.6.tgz
# cd Python-2.7.6
#./configure--prefix=/usr/local/python2.7
--enable-shared # make && make install

# echo '/usr/local/python2.7/lib ' >>/etc/ld.so.conf
#/sbin/ldconfig

# mv/usr/bin/python/usr/bin/python_old
# ln-s/usr/local/python2.7/bin/python2.7/usr/bin/python

To avoid a Python version that is too high to cause yum, you need to make the following modifications:

# Vim/usr/bin/yum

Modified by #!/usr/bin/python to #!/usr/bin/python_old

Http://pypi.python.org/packages/source/d/distribute/distribute-0.6.28.tar.gz

# tar Xvzf distribute-0.6.28.tar.gz
# cd distribute-0.6.28
# python setup.py install

https://github.com/ farcepest/mysqldb1/archive/mysqldb-1.2.5.tar.gz
# tar xvzf mysqldb1-mysqldb-1.2.5.tar.gz
# CD mysqldb1-mysqldb-1.2.5

# vim site.cfg
mysql_config =/usr/local/mysql/bin/mysql_config ...

# python setup.py build
# python setup.py install

III. mysqlmtop Environment Deployment
1, the establishment of the Monitoring machine database and authorization

# mysql-uroot-e "CREATE database mysqlmtop default character set UTF8;"
# MYSQL-UROOT-E "Grant all privileges on mysqlmtop.* to ' mtop_user ' @ '% ' identified by ' 123456 ';"
# MYSQL-UROOT-E "flush privileges;"

2. Import SQL files from SQL folders (table structure and data files)

# unzip Mysqlmtop_v2.1.zip
# CD mysqlmtop_v2.1
# mysql-uroot Mysqlmtop < sql/mysqlmtop.sql
# Mysql-uroot Mysqlmtop < Sql/mysqlmtop_data.sql

3, on the monitor machine to create an authorized account

# MYSQL-UROOT-E "Grant Select,super,process on *.* to ' monitor ' @ ' 172.18.35.29 ' identified by ' monitor ';"
# MYSQL-UROOT-E "flush privileges;"

4, Mysqlmtop Configuration

# cp-a mysqlmtop/usr/local/
# cp-a frontweb/data/web/data/#

 
cd/usr/local/mysqlmtop/#
vim etc/config. Ini

# chmod +x *.py *.sh mtopctl
# ln-s/usr/local/mysqlmtop/mtopctl/usr/local/bin

(Note: You need to use the Dos2unix command to format *.py, *.sh, mtopctl files, or you will fail to run)

5, test the MySQL connection is normal

# cd/usr/local/mysqlmtop &&/test_mysql.py

MySQLdb ok!

6, start the monitoring system

# Mtopctl Start

7, front-end Web display layer configuration

# cd/data/web/data/frontweb/#
chown-r nobody:nogroup/data/web/data/frontweb #
Chmod-r 750/data/web/data/ Frontweb

# Vim application/config/database.php

# vim/data/web/conf/mysqlmtop.conf

<virtualhost *:80>
  documentroot/data/web/data/frontweb
  ServerName mysqlmtop.test.com
  directoryindex index.php adddefaultcharset UTF-8
                                                                     
  "/ data/web/.*\.php$ "> Order
    deny,allow
    Deny to all
  </LocationMatch>
                                                                     
  <directory/>
    allowoverride all
  </Directory>
</VirtualHost>

# service httpd Graceful

Then local hosts bind (172.18.35.29 mysqlmtop.test.com)
Access via http://mysqlmtop.test.com (initial authorized Account: admin admin)

8, monitor the settings
Click Management Center--> Application Management Add application

Click Management Center--> Server Management Add MySQL server

Four, slow query configuration
1. Install the following components in the monitored server
http://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.628.tar.gz

# tar XVZSF dbi-1.628.tar.gz
# cd DBI-1.628
# perl makefile.pl
# make && make install

Http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.024.tar.gz

# tar Xvzf dbd-mysql-4.024.tar.gz
# cd dbd-mysql-4.024
# perl makefile.pl--mysql_config=/usr/local/mysql/bin/ Mysql_config--with-mysql=/usr/local/mysql
# make && make install

Http://search.cpan.org/CPAN/authors/id/S/SU/SULLR/IO-Socket-SSL-1.972.tar.gz

# tar Xvzf io-socket-ssl-1.972.tar.gz
# cd io-socket-ssl-1.972
# perl makefile.pl
# make && make I Nstall

Http://www.percona.com/redir/downloads/percona-toolkit/LATEST/percona-toolkit-2.2.7.tar.gz

# tar Xvzf percona-toolkit-2.2.7.tar.gz
# cd percona-toolkit-2.2.7
# perl makefile.pl
# make && make Install

2, open the MySQL itself slow query function

Slow_query_log = 1
slow_query_log_file =/data/dbdata/slow_query.log
long_query_time = 1

3, on the monitored server (10.160.22.14) Add the following crontab items

* * * * */usr/bin/pt-query-digest--user=mtop_user--password=123456--port=3306--review h=172.18.35.29,D=mysqlmtop,t =mysql_slow_query_review_4--history h=172.18.35.29,d=mysqlmtop,t=mysql_slow_query_review_history_4--no-report-- limit=0%/data/dbdata/slow_query.log >/dev/null 2>&1

4, on the monitored server (10.160.22.47) Add the following crontab items

* * * * */usr/bin/pt-query-digest--user=mtop_user--password=123456--port=3306--review h=172.18.35.29,D=mysqlmtop,t =mysql_slow_query_review_5--history h=172.18.35.29,d=mysqlmtop,t=mysql_slow_query_review_history_5--no-report-- limit=0%/data/dbdata/slow_query.log >/dev/null 2>&1

The above data table Mysql_slow_query_review, mysql_slow_query_review_history suffix is the host ID, as shown in figure:

5, for the monitored server to open a slow query

6, the effect shows

Three, tool component configuration
1, on the monitored server (172.18.35.29) Add the following crontab items

*/30 * * * * CD/USR/LOCAL/MYSQLMTOP;/check_mysql_widget_bigtable.py >/dev/null 2>&1 */1 * * * * cd/usr/l
Ocal/mysqlmtop./check_mysql_widget_hit_rate.py >/dev/null 2>&1
*/1 * * * CD/USR/LOCAL/MYSQLMTOP; check_mysql_widget_connect.py >/dev/null 2>&1

2, the effect shows

Iv. Resource Monitoring and configuration
1. The settings on the monitored server
NET-SNMP Service Installation (note version):
Ftp://ftp.fi.freebsd.org/pub/FreeBSD/ports/distfiles/net-snmp-5.3.2.tar.gz

# tar Xvzf net-snmp-5.3.2.tar.gz
# cd net-snmp-5.3.2
#./configure--prefix=/usr/local/snmp \
- enable-mfd-rewrites \
--with-default-snmp-version= "2" \
--with-sys-contact= "lovezym5@qq.com" \
With-sys-location= "" "
--with-logfile="/var/log/snmpd.log "\
--with-persistent-directory="/var/ Net-snmp "

# make && make install
# CP example.conf/usr/local/snmp/share/snmp/snmpd.conf

# VIM/ usr/local/snmp/share/snmp/snmpd.conf
com2sec notconfiguser 172.18.35.29   mysqlmtop

Group Notconfiggroup v1  notconfiguser
Group Notconfiggroup v2c notconfiguser
Group Notconfiggroup USM Notconfiguser

View all  included 1
access notconfiggroup The Any    noauth  Exact All None None
...

Service startup:

#/usr/local/snmp/sbin/snmpd-c/usr/local/snmp/share/snmp/snmpd.conf

2, monitor the settings on the server
NET-SNMP Service Installation:

# tar Xvzf net-snmp-5.3.2.tar.gz
# cd net-snmp-5.3.2
#./configure--prefix=/usr/local/snmp \
- enable-mfd-rewrites \
--with-default-snmp-version= "2" \
--with-sys-contact= "lovezym5@qq.com" \
With-sys-location= "" "
--with-logfile="/var/log/snmpd.log "\
--with-persistent-directory="/var/ Net-snmp "

# make && make install

# cd/usr/local/mysqlmtop
# ln-s/usr/local/snmp/bin/snmpwalk/ Usr/bin/snmpwalk
# ln-s/usr/local/snmp/bin/snmpdf/usr/bin/snmpdf

# Vim/usr/local/mysqlmtop/check_linux _resource.sh
host= "172.18.35.29"
port= "3306" user= "Mtop_user"
password=
"123456" Dbname= "Mysqlmtop" ...

# Vim/usr/local/mysqlmtop/etc/config.ini
[linux_server]
server_ip= "10.160.22.14|10.160.22.47"

Reboot the Monitoring service:

# mtopctl Stop && mtopctl start

Problem fix (otherwise unable to get memory information):

# vim/usr/local/mysqlmtop/check_linux_resource.sh
totalmem= '/usr/bin/snmpdf-v1-c mysqlmtop ${ip} | awk '/Real memory/{print $} '
usedmem= '/usr/bin/snmpdf-v1-c mysqlmtop ${ip} | awk '/real memory/{print $} '

Crontab content Add:

*/1 * * * cd/usr/local/mysqlmtop;/check_linux_resource.py >/dev/null 2>&1

Effect Show