mycheckpoint功能及使用
本文主要介绍mycheckpoint的使用，包括自定义监控、设置告警阈值、监控远程主机、OS信息、提供方便的脚本。
欢迎转载，请注明作者、出处。
作者：张正
blog：http://space.itpub.net/26355921
QQ：176036317
如有疑问，欢迎联系。
1.设置自定义监控的值：
query_eval：为自定义查询的值，应为：一行结果&整数值
现在设置自定义监控一个
SELECT COUNT(*) FROM store.shopping_cart WHERE is_pending=1的查询结果。
INSERT INTO
custom_query (custom_query_id, enabled, query_eval, description, chart_type, chart_order)
VALUES (0, 1, 'SELECT COUNT(*) FROM store.shopping_cart WHERE is_pending=1',
'Number of pending carts', 'value', 0);
查看自定义监控的值：
custom_0为第一个自定义项
custom_1为第二个自定义项，
..........
SELECT id, ts, created_tmp_tables_psec, custom_0, custom_0_time, custom_1_psec FROM sv_sample
WHERE ts >= NOW() - INTERVAL 1 HOUR;
以下是摘自官网说明：
mysql> desc custom_query;
+-----------------+------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------------------------------+------+-----+---------+-------+
| custom_query_id | int(10) unsigned | NO | PRI | 0 | |
| enabled | tinyint(1) | NO | | 1 | |
| query_eval | varchar(4095) | NO | | NULL | |
| description | varchar(255) | YES | | NULL | |
| chart_type | enum('value','value_psec','time','none') | NO | | value | |
| chart_order | tinyint(4) | NO | | 0 | |
+-----------------+------------------------------------------+------+-----+---------+-------+
The columns of the custom_query table are:
custom_query_id: Unique identifier. This is not an AUTO_INCREMENT: you choose the id; you’ll reference it later on.
enabled: 1 for enabled, 0 for disabled (query will not get executed), value stored is NULL.
query_eval: The query to be executed. All tables must be fully qualified with database (Schema) scope. The query must return exactly one row, with exactly one column, which is a type of INTEGER.
description: A human readable explanation of the nature of the query. Used as title for custom charts.
chart_type: How to graphically represent the custom results. This is a type of enum(‘value’,'value_psec’,'time’).
‘value’ means charting the query result’s value;
‘value_psec’ charts the change per second of the value;
‘time’ charts the time it took to execute the query (regardless of the result).
chart_order: chart position within the HTML reports (works as of revision 160).
2.自定义告警：
seconds_behind_master是一个监控项，其表示主从结构中的复制延迟时间，为其设置一个alert阈值：
INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)
VALUES ('seconds_behind_master IS NULL', 'Slave not replicating', 0);
seconds_behind_master是mycheckpoint自带的监控项，如果要对自定义监控项设置阈值，
condition_eval可设为:custom_0>1000，意思为：如果第一个自定义监控项值大于1000，则报警。
以下是摘自官网说明：
mysql> DESC alert_condition;
+-------------------------------+---------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+---------------------------------------------------+------+-----+---------+----------------+
| alert_condition_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| enabled | tinyint(1) | NO | | 1 | |
| condition_eval | varchar(4095) | NO | | NULL | |
| monitored_host_condition_eval | varchar(4095) | NO | | NULL | |
| description | varchar(255) | YES | | NULL | |
| error_level | enum('debug','info','warning','error','critical') | NO | | error | |
| alert_delay_minutes | smallint(5) unsigned | NO | | 0 | |
| repetitive_alert | tinyint(1) | NO | | 0 | |
+-------------------------------+---------------------------------------------------+------+-----+---------+----------------+
Description:
enabled: a boolean value. 0 disables the condition: it will not be checked. 1 (default) enables it.
condition_eval: an SQL condition to evaluate (e.g. ‘seconds_behind_master IS NULL’. See following examples). For the condition to be raised, it must evaluate to a nonzero, not NULL integer value. A result of 0 or NULL means the condition is not met and that there is no problem.
monitored_host_condition_eval: an SQL condition to evaluate on the monitored server; typically this would be a call to a stored function on the monitored server. Same rules apply as for condition_eval.
description: a human readable explanation of the condition. This will be presented in various views and in email notifications.
error_level: condition severity. Defaults to ‘Error’.
alert_delay_minutes: how much time must pass with continuous met condition, before it is considered as a problem and becomes candidate for notification.
repetitive_alert: a boolean value. 1 means an email notification will include this condition whenever the condition is met. 0 (default) means an email notification is only set once in any continuous meeting of this condition.
3.监控远程MySQL服务器
监控主要是通过crontab+mycheckpoint+参数 采集数据库信息，写入数据库，然后通过数据库中的数据将图像展示出来。
a.监控本地主机（采集本地数据库运行状态的数据，也就意味着crontab+mycheckpoint在本地采集数据），写入到远程监控机上(将采集到的数据写入到远程mysql的监控数据库中)，本地主机上需要安装mycheckpoint
mycheckpoint --host=remote_monitor_server --port=3306 --user=moni_user --password=moni_passwd --database=check_host1 --monitored-host=localhost --monitored-socket=/tmp/mysql.sock
b.监控远程主机，本地通过crontab+mycheckpoint采集远程数据库中的运行状态信息，然后写入到本地mysql的监控数据库中，远程数据库上无需安装mycheckpoint，只需要有相应的用户及权限即可
mycheckpoint --host=localhost --port=3306 --user=moni_user --password=moni_passwd --database=check_host1 --monitored-host=host1 --monitored-port=3306
注意：只有本地安装了mycheckpoint，并使用crontab执行mycheckpoint .....的命令才能搜集到OS的信息。方式a中，每台服务器上都安装了mycheckpoint，本地通过crontab采集到信息后写入到远端服务器中(包括OS信息)。方式b中，因为是监控机搜集远程服务器上的信息是通过数据库用户搜集的，因此无法搜集到OS信息。
有时候搜集本地信息也会出现无法搜集OS信息的情况，是因为原本是搜集本地信息，但是在mycheckpoint命令中，写的是搜集远程主机信息的语句，只不过这个主机IP是本地的IP而已，但访问方式还是远程访问。只需要在mycheckpoint 命令后额外加-o 参数，即：强行搜集OS信息即可。
4.执行mycheckpoint http 后，才能通过网页访问监控信息界面。但是执行后，需要专门的窗口。因此我们需要将其放在后台执行。然是后台执行，往往需要手动找到这个进程并手动kill，因此在这里，我提供了一个脚本，以便更方便地管理mycheckpoint http进程。例如该脚本名为mysql_monitor.sh，
执行mysql_monitor.sh start/stop/restart
该脚本是放在监控机上执行的，需要先填下前面的几个参数，如HOST、USER、PASSWD等。
|
#!/bin/bash
#Edit by zheng.zhang@enmotech.com 2013-08-09
#This script. is helpful that using mycheckpoint monitor mysql server.
#Here are some parameters:
HOST=localhost
USER=moni_user
PASSWD=moni_passwd
DATABASE=check_master
SOCKET="-S xxxxxx "
PORT=
LOGFILE_PATH="/root/mycheckpoint_http.log"
pid=`ps -ef|grep mycheckpoint |grep -v grep|grep python|grep mycheckpoint|awk '{print $2}'`
case "$*" in
start)
test -z $pid
if (( $? > 0 ))
then
echo "-------------------------------------------------------"
echo "..........mycheckpoint_http is running(pid $pid) ......"
echo "-------------------------------------------------------"
else
nohup mycheckpoint --host=$HOST -u $USER -p$PASSWD --database=$DATABASE http >> $LOGFILE_PATH &
v_time1=`date "+%Y-%m-%d %H:%M:%S"`
echo "mycheckpoint started at : $v_time1 " >> $LOGFILE_PATH
sleep 2
new_pid=`ps -ef|grep mycheckpoint |grep -v grep|grep python|grep mycheckpoint|awk '{print $2}'`
echo "---------------------------------------------------------"
echo "..........mycheckpoint_http started,pid is $new_pid......"
echo "---------------------------------------------------------"
fi
;;
stop)
test -z $pid
if (( $? > 0 ))
then
kill -9 $pid
v_time=`date "+%Y-%m-%d %H:%M:%S"`
echo "mycheckpoint stopped at : $v_time " >> $LOGFILE_PATH
echo "-----------------------------------------"
echo "..........mycheckpoint_http stopped......"
echo "-----------------------------------------"
else
echo "------------------------------------------------"
echo "..........mycheckpoint_http is not running......"
echo "------------------------------------------------"
fi
;;
status)
test -z $pid
if (( $? > 0 ))
then
echo "------------------------------------------------------"
echo "..........mycheckpoint_http is running.....(PID $pid)"
echo "------------------------------------------------------"
else
echo "------------------------------------------------"
echo "..........mycheckpoint_http is not running......"
echo "------------------------------------------------"
fi
;;
restart)
######stop######
echo -n " stopping mysql_monitor......"
sleep 1
test -z $pid
if (( $? > 0 ))
then
kill -9 $pid
v_time=`date "+%Y-%m-%d %H:%M:%S"`
echo "mycheckpoint stopped at : $v_time " >> $LOGFILE_PATH
echo ".[OK] "
else
echo ".[FAILED] (mysql_mointor is not running)"
fi
sleep 1
######start######
pid=`ps -ef|grep mycheckpoint |grep -v grep|grep python|grep mycheckpoint|awk '{print $2}'`
echo -n " starting mysql_monitor......"
sleep 1
test -z $pid
if (( $? > 0 ))
then
echo ".[FAILED] "
echo " mycheckpoint_http is running(pid $pid) .........."
else
nohup mycheckpoint --host=$HOST -u $USER -p$PASSWD --database=$DATABASE http >> $LOGFILE_PATH &
v_time1=`date "+%Y-%m-%d %H:%M:%S"`
echo "mycheckpoint started at : $v_time1 " >> $LOGFILE_PATH
sleep 2
new_pid=`ps -ef|grep mycheckpoint |grep -v grep|grep python|grep mycheckpoint|awk '{print $2}'`
echo ".[OK] (pid is $new_pid)"
fi
;;
*)
echo "----------------------------------------------------"
echo " please input: start | status | stop | restart"
echo "----------------------------------------------------"
;;
esac
|
本文转自ITPUB博客84223932的博客，原文链接：功能及使用mycheckpoint，如需转载请自行联系原博主。
