IT工程師數位筆記本

一、服務器角色

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

二、慢查詢配置

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，如圖：

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

5、為被監控的服務器開啟慢查詢

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

6、效果展示

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

三、工具組件配置

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

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

四、資源監控配置

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