DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Log Mysql Status Across Network

10.22.2011
| 1890 views |
  • submit to reddit
        // script to log the connection status of mysql instances for IP starting with 10.10.10.*

#!/bin/sh
# script to log the connection status of mysql instances for IP starting with 10.10.10.*
# CREATE TABLE test.ipstatus (id int(11) NOT NULL auto_increment, ip_address varchar(100) default NULL, response_message varchar(500) default NULL, response_code int(11) default NULL, log_time timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY  (id)) ENGINE=MyISAM
# The cron to generate the status code for each connection
# daily cron setting to check and log mysql status
# 41 16 * * * time sh -xv /home/shantanu/mysign.sh > /home/shantanu/mysql_vm_status.txt 2> /home/shantanu/mysql_vm_status_err.txt
# 46 16 * * * mysql -e"SELECT concat('10.10.10.', a.ip_number) as IP_Address, a.response_code AS today_code, a.response_message, dt.rc AS yesterday_code, dt.rm as yesterday_message FROM test.ipstatus as a LEFT JOIN (SELECT ip_number, response_code as rc, response_message as rm FROM test.ipstatus WHERE date(log_time)= date_sub(curdate(), interval 1 day)) as dt ON a.ip_number = dt.ip_number WHERE a.response_code != dt.rc AND date(log_time) = curdate()\G" | mail -s"mysql connections status" monitor+`hostname`@companyname.com

for myip in {1..256}

do
my_error_code='0'
ip_address="10.10.10.$myip"
myresponse=`mysqladmin ping -h$ip_address -uwcuser -pwcuser@123 2>&1 | grep error`

echo "$myresponse" | grep 'connect to MySQL server on'
if [[ $? -eq 0 ]];then
my_error_code=101
fi

echo "$myresponse" | grep 'all backends are down'
if [[ $? -eq 0 ]];then
my_error_code=102
fi

echo "$myresponse" | grep 'using password: NO'
if [[ $? -eq 0 ]];then
my_error_code=103
fi

echo "$myresponse" | grep 'using password: YES'
if [[ $? -eq 0 ]];then
my_error_code=104
fi

echo "$myresponse" | grep 'is not allowed to connect to this MySQL server'
if [[ $? -eq 0 ]];then
my_error_code=105
fi

echo "$myresponse" | grep 'system error: 111'
if [[ $? -eq 0 ]];then
my_error_code=111
fi

echo "$myresponse" | grep 'system error: 113'
if [[ $? -eq 0 ]];then
my_error_code=113
fi

echo "$myresponse" | grep 'Unknown MySQL server host'
if [[ $? -eq 0 ]];then
my_error_code=114
fi

echo "$my_error_code"
mysql -e"insert into test.ipstatus values(NULL, \"$myip\", \"$ip_address\", \"$myresponse\", \"$my_error_code\", NULL)"

done