Install and configure MySQLMTOP to monitor MySQL running performance,

Source: Internet
Author: User
Tags snmpwalk percona

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.