代码改变世界

使用MySQLMTOP监控MySQL性能

2017-06-13 09:22  linkxu  阅读(912)  评论(0)  编辑  收藏  举报

一、服务器角色

服务器角色

172.18.35.29

10.160.22.14 (MySQL Master)

10.160.22.47 (MySQL Slave)

监控点

YES

NO

NO

被监控点

NO

YES

YES

 

=========================================================================================

二、慢查询配置

1、在被监控服务器安装如下组件

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、开启 MySQL 本身的慢查询功能

slow_query_log = 1

slow_query_log_file = /data/dbdata/slow_query.log

long_query_time = 1

 

-----------------------------------------------------------------------------------------------------------------------------------------------

3、在被监控服务器（10.160.22.14）上添加如下 crontab 项

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、在被监控服务器（0.160.22.47）上添加如下 crontab 项

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

 

以上数据表 mysql_slow_query_review、mysql_slow_query_review_history 的后缀为主机 ID，如图：

wKiom1MxqESSghHlAANbfow9RwM308.jpg

 

-----------------------------------------------------------------------------------------------------------------------------------------------

5、为被监控的服务器开启慢查询

wKiom1MxqJeTb91dAAJAFp9ueWk365.jpg

 

-----------------------------------------------------------------------------------------------------------------------------------------------

6、效果展示

wKiom1MxqMCxdZhQAAakiWX5u1g746.jpg

 

=========================================================================================

三、工具组件配置

 

1、在被监控服务器（172.18.35.29）上添加如下 crontab 项

*/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、效果展示

wKioL1MxqgWSxScQAAJFfv6ZO18733.jpg

 

=========================================================================================

四、资源监控配置

1、被监控服务器上的设置

NET-SNMP服务安装（注意版本）：

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.conf

com2sec notConfigUser  172.18.35.29      mysqlmtop

 

group notConfigGroup v1   notConfigUser

group notConfigGroup v2c  notConfigUser

group notConfigGroup usm  notConfigUser

 

view all    included  .1                               80

access  notConfigGroup ""      any       noauth    exact  all none none

......

 

服务启动：

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

 

-----------------------------------------------------------------------------------------------------------------------------------------------

2、监控服务器上的设置

NET-SNMP服务安装：

# 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.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"

 

重启监控服务：

# mtopctl stop && mtopctl start

 

问题修正（否则无法获取内存信息）：

# vim /usr/local/mysqlmtop/check_linux_resource.sh

totalmem=`/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}'`

 

crontab 内容添加：

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

 

-----------------------------------------------------------------------------------------------------------------------------------------------

3、效果展示

wKiom1Mxq22jhoPYAAKkjlQ2B8w110.jpg

 

 

其中服务器安装环境：

XAMPP+Python+MySQLdb

windows可直接执行：mtop.py运行数据采集服务。