Install and configure MySQLMTOP to monitor MySQL running performance,

Install and configure MySQLMTOP to monitor MySQL running performance,

I. Environment Description
1. Server role

2. System Environment
CentOS 6.2 x86_64

3. environment requirements
(1) MySQL 5.0 and above (used to store data collected by the Monitoring System)
(2) Apache 2.2 and above (WEB server running server)
(3) PHP 5.3 or above (WEB Interface)
(4). Python 2 (version 2.7 is recommended. Other versions are not tested and Data Collection and alarm tasks are executed)
(5) MySQLdb for python (Python interface for connecting to MySQL)

Ii. Python Basic 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 yum unavailability due to the python version being too high, you need to make the following changes:

# vim /usr/bin/yum

By #! /Usr/bin/python #! /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 installhttps://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.cfgmysql_config =/usr/local/mysql/bin/mysql_config……# python setup.py build# python setup.py install

Iii. mysqlmtop environment deployment
1. Create and authorize the monitoring database

# 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 the SQL file (table structure and data file) in the SQL folder)

# unzip mysqlmtop_v2.1.zip# cd mysqlmtop_v2.1# mysql -uroot mysqlmtop < sql/mysqlmtop.sql# mysql -uroot mysqlmtop < sql/mysqlmtop_data.sql

3. Create an authorized account on the monitored Machine

# 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: Use the dos2unix command to convert the format of *. py, *. sh, and mtopctl files; otherwise, the Operation will fail)

5. test whether 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                                                                       <LocationMatch "/data/web/.*\.php$">    Order Deny,Allow    Deny from All  </LocationMatch>                                                                       <Directory />    AllowOverride ALL  </Directory></VirtualHost># service httpd graceful

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

8. Monitoring item settings
Click "Management Center"> "Application Management" to add an application.

Click "Management Center"> "Server Management" to add a MySQL server.

Iv. Slow query Configuration
1. Install the following components on 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 install

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. Enable the slow query function of MySQL itself

slow_query_log = 1slow_query_log_file = /data/dbdata/slow_query.loglong_query_time = 1

3. Add the following crontab on the monitored server (10.160.22.14 ).

00 * * * * /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. Add the following crontab on the monitored server (10.160.22.47)

00 * * * * /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 tables mysql_slow_query_review and mysql_slow_query_review_history are suffixed with the host ID,

5. Enable slow query for the monitored Server

6. effect display

3. Tool component configuration
1. Add the following crontab on the monitored server (172.18.35.29)

*/30 * * * * cd /usr/local/mysqlmtop; ./check_mysql_widget_bigtable.py >/dev/null 2>&1*/1 * * * * cd /usr/local/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. effect display

Iv. Resource Monitoring Configuration
1. 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="China" \--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.confcom2sec notConfigUser 172.18.35.29   mysqlmtopgroup notConfigGroup v1  notConfigUsergroup notConfigGroup v2c notConfigUsergroup notConfigGroup usm notConfigUserview all  included .1                80access notConfigGroup ""   any    noauth  exact all none none......

Service startup:

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

2. Monitoring Server Settings
Installation of NET-SNMP Service:

# 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="China" \--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.shhost="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"

Restart the Monitoring Service:

# mtopctl stop && mtopctl start

Problem correction (otherwise the memory information cannot be obtained ):

# vim /usr/local/mysqlmtop/check_linux_resource.shtotalmem=`/usr/bin/snmpdf -v1 -c mysqlmtop ${ip} | awk '/Real Memory/ {print $3}'`usedmem=`/usr/bin/snmpdf -v1 -c mysqlmtop ${ip} | awk '/Real Memory/ {print $4}'`

Add crontab content:

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

Effect display

Articles you may be interested in:

• MySQL performance monitoring software Nagios installation and configuration tutorial
• Percona-toolkit tutorial on MySQL replication and monitoring operations