Shulou(Shulou.com)06/01 Report--

There are a lot of oracle monitoring on the market, and the most famous one is quest's product spotlight. Basically everything that can be done has been done. However, the monitoring products for Mysql are still very few. Quest's spotlight on mysql has a very limited ability to monitor the engine of versions below 5 and MYISAM. Another better product is a mysql control on oracle grid control, which is developed by the private sector and has passed the oracle certification. But the trouble is that it requires 10.2.0.3 for grid control and 4.0.27 for mysql, so for some older versions, Or it is more difficult to deploy an environment where grid agent is problematic. Most DBA's solution is to write scripts, and here's a script that basically meets most of the mysql and os monitoring requirements. [@ more@]

Disk_pct=80

Errlog=/usr/local/mysql/data/ `hostname`.err

Data_dir=/usr/local/mysql/data

Excludfile= "(v3_buylog_080508.MYD | v3_buylog.MYD)"

Nas_flag=0

Myserver_flag=1

Conn_flag=1

Errlog_flag=1

Rep_flag=0

Bigfile_flag=1

Exclud_flag=1

Bk_flag=0

Diskspace_flag=1

Delay_flag=0

Catchsql_flag=0

Nasdir=/backup

Process_limit=300

Backup_dirlist=/root/bklist.lst

Bkinput_file=/root/bk.tmp

Log_dir=/usr/checklog

Mailadd=monitor@staff.9you.com

Bkday= `date-d "1 days ago" +% y% m% d`

Sqlday= `date-d "1 days ago" +% Y% m% d`

Tgzday= `date-d "1 days ago" +% Y% m% d`

Logday= `date-d "10 days ago" +% Y% m% d`

Echo "#" > $log_dir/ `date +% Y% m% d`.log

Echo "#" > $log_dir/ `date +% Y% m% d`.log

Echo "#" > $log_dir/ `date +% Y% m% d`.log

Echo "#" > $log_dir/ `date +% Y% m% d`.log

Echo "#" > $log_dir/ `date +% Y% m% d`.log

Echo "- time `date +% KRV% M:% S` monitor start--" > $log_dir/ `date +% Y% m% d`.log

# purge old logs

If [- f $log_dir/$logday.log]

Then

Rm $log_dir/$logday.log

Fi

# check nas

If [$nas_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` nas monitor is on" > > $log_dir/ `date +% Y% m% d`.log

Df-h $nasdir | sed-n'$p' | awk'{print $4}'| sed's sed% Accord'> / root/nas.txt

While read line

Do

If [$line-gt $disk_pct]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:nas out of space" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` nas space is ok" > > $log_dir/ `date +% Y% m% d`.log

Fi

Doneelse

Echo "`date +% KRV% M:% S` nas monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

# Check mysql process

If [$myserver_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` mysqld process monitor is on" > > $log_dir/ `date +% Y% m% d`.log

Live_flag= `mysqladmin ping | grep-c alive`

If [$live_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` Mysql is alive" > > $log_dir/ `date +% Y% m% d`.log

Else

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:Mysql is dead" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "`date +% KRV% M:% S` mysqld process monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

# Check mysql status

If [$conn_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` mysql connection monitor is on" > > $log_dir/ `date +% Y% m% d`.log

Prscnt= `mysql-e "show processlist" | wc-l`

If [$prscnt-gt $process_limit]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:There is to many connections,the number is $prscnt,pls check mysql status" > > $log_dir/ `date +% Y% m% d`.log

Mysql-e "show full processlist" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` current connections of mysql:$prscnt" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "`date +% KRV% M:% S` mysql connection monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

# Check mysql errorlog

If [$errlog_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` mysql alert log monitor is on" > > $log_dir/ `date +% Y% m% d`.log

If [$delay_flag-eq 0]

Then

If [- f $errlog]

Then

If [`grep-ci err $errlog`-gt 0]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:There is some error with mysql,pls check log" > > $log_dir/ `date +% Y% m% d`.log

Grep-I error $errlog > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` mysql is running,no mistake in alertlog" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:no errorlog" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Fi

If [$delay_flag-eq 1]

Then

If [- f $errlog]

Then

If [`grep-I err $errlog | awk'$0! ~ /'"(Error reading relay log event)" / {print $0}'| grep-ci err`-gt 0]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:There is some error with mysql,pls check log" > > $log_dir/ `date +% Y% m% d`.log

Grep-I error $errlog > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` mysql is running,no mistake in alertlog" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:no errorlog" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Fi

Else

Echo "`date +% KRV% M:% S` mysql alert log monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

# Replication Check

If [$delay_flag-eq 0]

Then

Echo "`date +% KRV% M:% S` not delay replication, so slave monitor is on" > > $log_dir/ `date +% Y% m% d`.log

If [$rep_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` slave monitor is on" > > $log_dir/ `date +% Y% m% d`.log

Slave_flag= `/ usr/local/mysql/bin/mysqladmin extended-status | grep Slave_running | grep-c ON`

If [$slave_flag-eq 0]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:replication error" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` replication running" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "`date +% KRV% M:% S` slave monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "`date +% KRV% M:% S` delay replication, so slave monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

# Check table which is more than 3Gb

If [$bigfile_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` MYD 3G monitor is on" > > $log_dir/ `date +% Y% m% d`.log

If [$exclud_flag-eq 0]

Then

Cd $data_dir

Ls-lR | grep MYD | awk'{if ($5 > = 3221225472) print $9 recording 5}'> file.txt

If [`print-l file.txt | awk'{print $1}'`- gt 0]

Then

While read line

Do

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S`Error:datafile `echo $line | awk'{print $1} '`is bigger than 3Gb" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Doneelse

Echo "`date +% KRV% M:% S` no datafile bigger than 3Gb" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Cd $data_dir

Ls-lR | grep MYD | awk'{if ($5 > = 3221225472) print $9 "5}'| awk'$1! ~ /'$excludfile'/ {print $1 charge 2}'> file.txt

If [`print-l file.txt | awk'{print $1}'`- gt 0]

Then

While read line

Do

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S`Error:datafile `echo $line | awk'{print $1} '`is bigger than 3Gb" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Doneelse

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` file bigger than 3Gb but it is a big table" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Fi

Else

Echo "`date +% KRV% M:% S` MYD 3G monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

# Check backup

Grep-v "#" $backup_dirlist > $bkinput_file

If [$bk_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` backup monitor is on" > > $log_dir/ `date +% Y% m% d`.log

While read line

Do

Dbname= `echo $line | awk'{print $1}'`

Bk_dir= `echo $line | awk'{print $2}'`

Bk_type= `echo $line | awk'{print $3}'`

If [$bk_type-eq 1]

Then

Cd $bk_dir

If [- d data$bkday]

Then

If [`du data$bkday | sed-n'$p' | awk'{print $1}'`- lt 1000]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:backup of $dbname is too small" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` backup of $dbname is ok" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:no directory,backup of $dbname error" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Elif [$bk_type-eq 0]

Then

Cd $bk_dir

If [- f $dbname'_'$sqlday.sql]

Then

If [`du * $sqlday* | awk'{print $1}'`- lt 1000]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:backup of $dbname is too small" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` backup of $dbname is ok" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:no file,backup of $dbname error" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Cd $bk_dir

If [- f db.$tgzday.tgz]

Then

If [`du db.$tgzday.tgz | awk'{print $1}'`- lt 1000]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:backup of $dbname is too small" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` backup of $dbname is ok" > > $log_dir/ `date +% Y% m% d`.log

Fi

Else

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:no file,backup of $dbname error" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Fi

Done > $log_dir/ `date +% Y% m% d`.log

Fi

# Check datadir partition

If [$diskspace_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` disk space monitor is on" > > $log_dir/ `date +% Y% m% d`.log

Ct= `df-h | wc-l`

If [$nas_flag-eq 1]

Then

Ct= `expr $ct-2 `

Fi

Df-h | awk'{print $5}'| sed-n'2'"$ct"'p' | sed's root/disk.txt% root/disk.txt

While read dsk

Do

If [$dsk-gt $disk_pct]

Then

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error:some disk partition out of space" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Else

Echo "`date +% KRV% M:% S` disk space is ok" > > $log_dir/ `date +% Y% m% d`.log

Fi

Doneelse

Echo "`date +% KRV% M:% S` disk space monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

# check catchsql daemon

If [$catchsql_flag-eq 1]

Then

Echo "`date +% KRV% M:% S` catchsql daemon monitor is on" > > $log_dir/ `date +% Y% m% d`.log

Runflag= `ps-ef | grep catchsql.sh | wc-l`

If [$runflag-eq 1]

Then

Cd / root/crontab_scripts

If [- f catchsql.sh]

Then

/ usr/bin/nohup. / catchsql.sh > / dev/null &

Else

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Echo "`date +% KRV% M:% S` Error: no catchsql script" > > $log_dir/ `date +% Y% m% d`.log

Echo "-" > $log_dir/ `date +% Y% m% d`.log

Fi

Fi

Else

Echo "`date +% KRV% M:% S` catchsql daemon monitor is off" > > $log_dir/ `date +% Y% m% d`.log

Fi

Echo "- time `date +% KRV% M:% S` monitor end--" > $log_dir/ `date +% Y% m% d`.log

Log=/usr/checklog/ `date +% Y% m% d`.log

HOST_IP= "`/ sbin/ifconfig eth0 | grep 'inet addr' | awk' {print $2}'| sed-e's Unip.

HOST_IP2= "`/ sbin/ifconfig eth2 | grep 'inet addr' | awk' {print $2}'| sed-e's Unip.

Errorct= `grep-c Error $log`

If [$errorct-gt 0]

Then

Mail-s "Error Report~~,machine `hostname` has some error with it,the ip is $HOST_IP and $HOST_IP2" $mailadd > $log

Mail-s "Server $HOST_IP $HOST_IP2 is OK" $mailadd $monitorlog

Cat $errlog > $old_errlog

> $errlog

/ root/crontab_scripts/monitor.sh

Errorct= `grep-c Error $monitorlog`

If [$errorct-gt 0]

Then

Echo "there is still so mistake"

Else

Echo "system is ok"

Fi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.