本文介绍了如何利用nagios监控MySQL数据库的表结构，包括两种实现方法的详细步骤，以及在实施过程中遇到的问题及解决方案。
引言
为了给客户演示对mysql表结构的监控，在搜了很久之后发现不得不自己写一个脚本了。percona这么牛B的公司竟然没有提供一个这方面的工具，看来客户的要求有点花儿不实啊。。。这个问题一共花费了我两天时间去解决（个人shell脚本一般，能力也一般），所以总结一下。
实现设想
过程中一共有三种想法：
1. 由于mysql的informarion_schema.columns这个表存储这所有表的表结构，因此第一个设想是想对information_schema.columns这个表不停的扫描，保留老数据，将本次扫描的数据与老数据进行比较，如果发现有所不同则肯定发生了 alter table的操作，即表结构发生了变化
2. mysql的表都存在/data目录下，表结构都有一个单独的文件***.frm文件进行存储（innodb表需要设置innodb_file_per_table），所以第二个想法是监控/data目录的frm文件带大小，如果大小发生变化则认为是发生了表结构的变化，这种方法实现起来比第一种简单，但是准确性肯定不是很好，很有可能表结构更改了为frm文件的大小没变。
方法一实现：
一共需要三个临时文件，一个用于存储当前表结构信息，一个用于存储上一次检查的表结构信息，一个用于存储表结构变化的表名。
脚本名为check_mysql_table，代码如下
#!/bin/sh
# ########################################################################
# This program is used to check whether mysqld run on this machine
# ########################################################################
# ########################################################################
# Redirect STDERR to STDOUT; Nagios doesn't handle STDERR.
# ########################################################################
exec 2>&1
# ########################################################################
# Set up constants, etc.
# ########################################################################
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
STATE_DEPENDENT=4
TMPFILENEW="/tmp/table_frm.new"
TMPFILEOLD="/tmp/table_frm.old"
TMPFILERESULT="/tmp/alter_table"
# ########################################################################
# Run the program.
# ########################################################################
main() {
# Get options
for a; do
case "${a}" in
-c) shift; OPT_CRIT="${1}"; shift; ;;
--defaults-file) shift; OPT_DEFT="${1}"; shift; ;;
-g) shift; OPT_UNIX_GROUP="${1}"; shift; ;;
-H) shift; OPT_HOST="${1}"; shift; ;;
-l) shift; OPT_USER="${1}"; shift; ;;
-L) shift; OPT_LOPA="${1}"; shift; ;;
-p) shift; OPT_PASS="${1}"; shift; ;;
-P) shift; OPT_PORT="${1}"; shift; ;;
-S) shift; OPT_SOCK="${1}"; shift; ;;
-u) shift; OPT_UNIX_USER="${1}"; shift; ;;
-w) shift; OPT_WARN="${1}"; shift; ;;
-e) shift; OPT_EMAIL="${1}"; shift; ;;
-d) shift; OPT_DATABASE="${1}"; shift; ;;
--version) grep -A2 '^=head1 VERSION' "$0" | tail -n1; exit 0 ;;
--help) perl -00 -ne 'm/^ Usage:/ && print' "$0"; exit 0 ;;
-*) echo "Unknown option ${o}. Try --help."; exit 1; ;;
esac
done
OPT_UNIX_GROUP="${OPT_UNIX_GROUP:-mysql}"
OPT_UNIX_USER="${OPT_UNIX_USER:-mysql}"
if [ -e '/etc/smartmonitor/mysql.cnf' ]; then
OPT_DEFT="${OPT_DEFT:-/etc/smartmonitor/mysql.cnf}"
fi
if is_not_sourced; then
if [ -n "$1" ]; then
echo "WARN spurious command-line options: $@"
exit 1
fi
fi
if [ "${OPT_DEFT}${OPT_HOST}${OPT_USER}${OPT_PASS}${OPT_PORT}${OPT_SOCK}" ]; then
if [ ! "${OPT_DATABASE}" ]
then
OPT_DATABASE=" not in ('mysql','information_schema','performance_schema','test')"
TABLE_DATA=`mysql_exec "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema not in ('mysql','information_schema','test','performance_schema')"`
else
OPT_DATABASE="='"${OPT_DATABASE}"'"
TABLE_DATA=`mysql_exec "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema"${OPT_DATABASE} `
fi
fi
if [ ! "${TABLE_DATA}" ]
then
echo "WARNING no tables in the databases"
exit 1
fi
echo $TABLE_DATA|awk '{gsub("def","\ndef");print}'>$TMPFILENEW
sed -i '1d' $TMPFILENEW
if [ -f "$TMPFILEOLD" ]
then
cat $TMPFILEOLD|while read Line
do
grep -q """${Line}""" $TMPFILENEW
if [ $? -ne 0 ]
then
Alter_table=`echo $Line|awk {'print $2"."$3"."$4'}`
echo $TABLE_NAMES |grep -q "$Alter_table"
if [ $? -ne 0 ]
then
TABLE_NAMES=${TABLE_NAMES}" "${Alter_table}
echo $TABLE_NAMES>$TMPFILERESULT
fi
fi
done
fi
cp $TMPFILENEW $TMPFILEOLD
if [ ! -f $TMPFILERESULT ]
then
NOTE="OK,no table alter"
else
TABLE_NAMES=`cat $TMPFILERESULT`
NOTE="WARNING, alter table:"${TABLE_NAMES}
if [ "${OPT_EMAIL}" ]
then
echo $NOTE | /bin/mail -s "SERVICE NOTIFITATION:ALTER TABLE" $OPT_EMAIL
fi
fi
rm -f $TMPFILERESULT
echo $NOTE
}
# ########################################################################
# Execute a SmartSQL command.
# ########################################################################
mysql_exec() {
mysql ${OPT_DEFT:+--defaults-file="${OPT_DEFT}"} ${OPT_HOST:+-h"${OPT_HOST}"} ${OPT_USER:+-u"${OPT_USER}"} \
${OPT_PASS:+-p"${OPT_PASS}"} ${OPT_SOCK:+-S"${OPT_SOCK}"} ${OPT_PORT:+-P"${OPT_PORT}"} \
${OPT_LOPA:+--login-path="${OPT_LOPA}"} -ss -e "$1"
}
# ########################################################################
# Determine whether this program is being executed directly, or sourced/included
# from another file.
# ########################################################################
is_not_sourced() {
[ "${0##*/}" = "check_mysql_table" ] || [ "${0##*/}" = "bash" -a "$_" = "$0" ]
}
# ########################################################################
# Execute the program if it was not included from another file.
# This makes it possible to include without executing, and thus test.
# ########################################################################
if is_not_sourced; then
OUTPUT=$(main "$@")
EXITSTATUS=$STATE_UNKNOWN
case "${OUTPUT}" in
UNK*) EXITSTATUS=$STATE_UNKNOWN; ;;
OK*) EXITSTATUS=$STATE_OK; ;;
WARN*) EXITSTATUS=$STATE_WARNING; ;;
CRIT*) EXITSTATUS=$STATE_CRITICAL; ;;
esac
echo "${OUTPUT}"
exit $EXITSTATUS
fi
# ############################################################################
# Documentation
# ############################################################################
: <<'DOCUMENTATION'
=pod
=head1 NAME
check-mysql-table - Alert when MySQL table alter happen.
=head1 SYNOPSIS
Usage: check-mysql-table [OPTIONS]
Options:
-c CRIT ignore now
--defaults-file FILE Only read mysql options from the given file.
Defaults to /etc/nagios/mysql.cnf if it exists.
-g GROUP MySQL groupusername
-H HOST MySQL hostname.
-l USER MySQL username.
-L LOGIN-PATH Use login-path to access MySQL (with MySQL client 5.6).
-p PASS MySQL password.
-P PORT MySQL port.
-S SOCKET MySQL socket file.
-w WARN When table alter happen return Warning.default value
-e EMAIL Once Alter send a email to this address
-d DATABASE The tables of this databases will be monitored.
Defaults to all databases except mysql information_scheme test and performance_schema
--help Print help and exit.
--version Print version and exit.
Options must be given as --option value, not --option=value or -Ovalue.
Use perldoc to read embedded documentation with more details.
=back
Examples:
# /usr/local/nagios/libexec/check-mysql-table -H 127.0.0.1 -P 3306 -l root -d weibo
OK no alter table
=head1 PRIVILEGES
This plugin executes the following commands against MySQL:
=over
=item *
C<SELECT * FROM INFORMATION_SCHMEMA.COLUMNS;>
=back
This plugin executes no UNIX commands that may need special privileges.
=head1 VERSION
GreatOpenSource Monitoring Plugins check_mysql_table 1.0
=cut
check_mysql_table - Return WARNING if table alter happen
DOCUMENTATION程序的框架参考了percona的mysql监控脚本格式。
虽然代码较多，但是思想很简单。
遇到的问题
1 临时文件路径问题，一开始程序中的临时文件用的是相对路径，但是nagios运行时，会将所有的临时文件都在temp_path=/tmp ,这个配置选项指出了临时文件的位置。
2.管道问题，在处理文件时候，也就是下面这段代码
cat $TMPFILEOLD|while read Line
do
grep -q """${Line}""" $TMPFILENEW
if [ $? -ne 0 ]
then
Alter_table=`echo $Line|awk {'print $2"."$3"."$4'}`
echo $TABLE_NAMES |grep -q "$Alter_table"
if [ $? -ne 0 ]
then
TABLE_NAMES=${TABLE_NAMES}" "${Alter_table}
echo $TABLE_NAMES>$TMPFILERESULT
fi
fi
done这里的变量TABLE_NAME保存着表结构发生变化的表名字，但是由于是在一个管道中进行的，这个变量的值传不出来，出了这个while循环后就不能用了，所以把这个变量的值写入了一个临时文件中。
方法二的实现
思想简单，代码如下
#!/bin/sh
# ########################################################################
# This program is used to check whether mysqld run on this machine
# ########################################################################
# ########################################################################
# Redirect STDERR to STDOUT; Nagios doesn't handle STDERR.
# ########################################################################
exec 2>&1
# ########################################################################
# Set up constants, etc.
# ########################################################################
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
STATE_DEPENDENT=4
TMPFILE="/tmp/table_frm_size"
# ########################################################################
# Run the program.
# ########################################################################
main() {
# Get options
for o; do
case "${o}" in
-c) shift; OPT_CRIT="${1}"; shift; ;;
--defaults-file) shift; OPT_DEFT="${1}"; shift; ;;
-g) shift; OPT_UNIX_GROUP="${1}"; shift; ;;
-H) shift; OPT_HOST="${1}"; shift; ;;
-l) shift; OPT_USER="${1}"; shift; ;;
-L) shift; OPT_LOPA="${1}"; shift; ;;
-p) shift; OPT_PASS="${1}"; shift; ;;
-P) shift; OPT_PORT="${1}"; shift; ;;
-S) shift; OPT_SOCK="${1}"; shift; ;;
-u) shift; OPT_UNIX_USER="${1}"; shift; ;;
-w) shift; OPT_WARN="${1}"; shift; ;;
-e) shift; OPT_EMAIL="${1}"; shift; ;;
-d) shift; OPT_DATABASE="${1}"; shift; ;;
--version) grep -A2 '^=head1 VERSION' "$0" | tail -n1; exit 0 ;;
--help) perl -00 -ne 'm/^ Usage:/ && print' "$0"; exit 0 ;;
-*) echo "Unknown option ${o}. Try --help."; exit 1; ;;
esac
done
OPT_UNIX_GROUP="${OPT_UNIX_GROUP:-smartsql}"
OPT_UNIX_USER="${OPT_UNIX_USER:-smartsql}"
OPT_EMAIL="${OPT_EMAIL:-}"
if [ -e '/etc/smartmonitor/smartsql.cnf' ]; then
OPT_DEFT="${OPT_DEFT:-/etc/smartmonitor/smartsql.cnf}"
fi
if is_not_sourced; then
if [ -n "$1" ]; then
echo "WARN spurious command-line options: $@"
exit 1
fi
fi
#NOTE="UNK could not determine the datadir location."
if [ ! "${OPT_DATABASE}" ]
then
echo "CRITICAL,no database use"
exit 2
fi
DATADIR=
NOTE="UNKOWN can not get the data dir "
TABLENAMES=""
if [ "${OPT_DEFT}${OPT_HOST}${OPT_USER}${OPT_PASS}${OPT_PORT}${OPT_SOCK}" ]; then
DATADIR=`smartsql_exec "SELECT IF(@@datadir LIKE '/%', @@datadir, CONCAT(@@basedir, @@datadir))" `
fi
if [ ! $? ]
then
echo "UNKOWN error"
exit 3
fi
FindDir=${DATADIR}${OPT_DATABASE}"/"
if [ ! -f "$TMPFILE" ]
then
touch $TMPFILE
for File in `find $FindDir -name "*.frm"`
do
tmp=${File##*/}
filename=${tmp%.*}
set `ls -il $File`
echo ${filename}":"$6>>$TMPFILE
done
else
for File in `find $FindDir -name "*.frm"`
do
tmp=${File##*/}
filename=${tmp%.*}
set `ls -il $File`
size=$6
searchstr=${filename}
result=`grep "$searchstr" $TMPFILE`
if [ $? -eq 0 ]
then
oldsize=${result##*:}
if [ $size -ne $oldsize ]
then
TABLE_NAMES=${TABLE_NAMES}" "${filename}
fi
fi
done
fi
if [ x"$TABLE_NAMES" = x ]
then
NOTE="OK,no table alter"
else
NOTE="WARNING, alter table:"${TABLE_NAMES}
if [ "${OPT_EMAIL}" ]
then
echo $NOTE | /bin/mail -s "SERVICE NOTIFITATION:ALTER TABLE" $OPT_EMAIL
fi
rm -rf $TMPFILE
touch $TMPFILE
for File in `find $FindDir -name "*.frm"`
do
tmp=${File##*/}
filename=${tmp%.*}
set `ls -il $File`
echo ${filename}":"$6>>$TMPFILE
done
fi
echo $NOTE
}
# ########################################################################
# update $TMPFILE.
# ########################################################################
# ########################################################################
# Execute a SmartSQL command.
# ########################################################################
smartsql_exec() {
smartsql ${OPT_DEFT:+--defaults-file="${OPT_DEFT}"} ${OPT_HOST:+-h"${OPT_HOST}"} ${OPT_USER:+-u"${OPT_USER}"} \
${OPT_PASS:+-p"${OPT_PASS}"} ${OPT_SOCK:+-S"${OPT_SOCK}"} ${OPT_PORT:+-P"${OPT_PORT}"} \
${OPT_LOPA:+--login-path="${OPT_LOPA}"} -ss -e "$1"
}
# ########################################################################
# A wrapper around pidof, which might not exist. The first argument is the
# command name to match.
# ########################################################################
_pidof() {
if ! pidof "${1}" 2>/dev/null; then
ps axo pid,ucomm | awk -v comm="${1}" '$2 == comm { print $1 }'
fi
}
# ########################################################################
# Determine whether this program is being executed directly, or sourced/included
# from another file.
# ########################################################################
is_not_sourced() {
[ "${0##*/}" = "check_smartsql_table" ] || [ "${0##*/}" = "bash" -a "$_" = "$0" ]
}
# ########################################################################
# Execute the program if it was not included from another file.
# This makes it possible to include without executing, and thus test.
# ########################################################################
if is_not_sourced; then
OUTPUT=$(main "$@")
EXITSTATUS=$STATE_UNKNOWN
case "${OUTPUT}" in
UNK*) EXITSTATUS=$STATE_UNKNOWN; ;;
OK*) EXITSTATUS=$STATE_OK; ;;
WARN*) EXITSTATUS=$STATE_WARNING; ;;
CRIT*) EXITSTATUS=$STATE_CRITICAL; ;;
esac
echo "${OUTPUT}"
exit $EXITSTATUS
fi
# ############################################################################
# Documentation
# ############################################################################
: <<'DOCUMENTATION'
check_smartsql_table - Return WARNING if table alter happen
Usage: check_smartsql_table
DOCUMENTATION
扫一扫
674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
