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